Category: backups

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’

PRINT @FName

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