Introducation
Database backup we have 3 typr from backup Full backup , Diffrential backup , log transaction backup now i write stored procedure make koop on all database on SQl Server instance then take backup from it this Stored procedure take 2 paramter
@BackupPath :- this the path of your hard disk
@backupType :- your database backup typy this parmater allow Number Value { 1 = Full backup , 2 = Diff backup , 3 = log Trans backup } any thing not like this 3 value Stored will give you Error.
Now you can Create this proc under your database master then you can call it
Note : if you want to put your database backup for starday in folder and Sunday in folder ……ETC please Check this Post http://wp.me/p1Oidq-1D
Create Stored Procedure TakeDBsBackupDaily
USE MASTER
GO
set ANSI_NULLSON
set QUOTED_IDENTIFIERON
GO
Create PROCEDURE[dbo].[TakeDBsBackupDaily]
@BackupPathnvarchar(500),@backupTypeint
AS
BEGIN
declare @dnvarchar(2)
,@mnvarchar(2)
,@ynvarchar(4)
,@CurDatenvarchar(8)
,@hnvarchar(2)
,@minnvarchar(2)
,@secnvarchar(2)
,@CurTimenvarchar(8)
,@FNamenvarchar(500)
,@DbNamenvarchar(100)
,@BackupTypeNamenvarchar(200)
Set @d=DATEPART(“day”,(GETDATE()))
Set @m=DATEPART(“month”,(GETDATE()))
Set @y=DATEPART(“year”,(GETDATE()))
set @CurDate=replicate(‘0’,2–len(@d))+@d+replicate(‘0’,2–len(@m))+@m+replicate(‘0’,4–len(@y))+@y
Set @h=DATEPART(“hour”,(GETDATE()))
Set @min=DATEPART(“minute”,(GETDATE()))
Set @sec=DATEPART(“second”,(GETDATE()))
set @CurTime=replicate(‘0’,2–len(@h))+@h+replicate(‘0’,2–len(@min))+@min+replicate(‘0’,2–len(@sec))+@sec
DECLARE bK CURSORFOR
SELECT name FROM sys.databases WHERE Database_ID> 4 andstate_desc=‘Online’
OPENbk
FETCH NEXT FROM bk
INTO @DbName
WHILE @@FETCH_STATUS=0
BEGIN
—–FULL backup
IF @backupType= 1
begin
SET @FName= @BackupPath+‘\’+@DbName +‘_D_’+@curdate+‘_FULL’+‘.bak’
print‘Backing up Database : ‘+@DbName+‘ …’
set @BackupTypeName= @DbName+‘-FULL Database Backup’
BACKUP DATABASE @DbNameTO DISK=@FName WITH NOFORMAT,INIT, NAME = @BackupTypeName , SKIP, NOREWIND,NOUNLOAD,STATS= 10
End
—DIFF backup
Else IF @backupType = 2
begin
SET @FName = @BackupPath+‘\’+@DbName +‘_D_’+@curdate+‘_DIFF’+‘.bak’
print‘Backing up Database : ‘+@DbName+‘ …’
set @BackupTypeName = @DbName+‘-DIFF Database Backup’
PRINT@FName
BACKUP DATABASE @DbNameTO DISK = @FName WITH DIFFERENTIAL,NOFORMAT,NOINIT, NAME = @BackupTypeName,SKIP,NOREWIND,NOUNLOAD, STATS= 10
End
—Log backup
Else IF@backupType= 3
begin
SET @FName = @BackupPath+‘\’+@DbName +‘_D_’+@curdate+‘Log’+‘.bak’
print‘Backing up Database : ‘+@DbName+‘ …’
set @BackupTypeName = @DbName+‘-Log_Trans Database Backup’
PRINT@FName
BACKUP LOG @DbNameTO DISK = @FName WITH NOFORMAT,NOINIT, NAME = @BackupTypeName,SKIP,NOREWIND,NOUNLOAD, STATS = 10
End
Else
RAISERROR(‘Invalid Backup Type value.
Must be Write your Database backup type
1 = FULL backup
2 = Diffrential backup
3 = log Transaction backup
Or Contact the Adminstrator Mostafa Elmasry 0593205711′, 16, 1);
FETCH NEXT FROM bk
INTO @DbName
END
CLOSE bk
DEALLOCATE bk
END
####################################################################################
now try to Execute your Stored Procedure
Full backup
exec [TakeDBsBackupDaily] ‘E:\backups\Log’,1
DIFF backup
exec [TakeDBsBackupDaily] ‘E:\backups\Log’,2
Log backup
exec [TakeDBsBackupDaily] ‘E:\backups\Log’,3
DB Cloud TECH 