TAKING BACKUPS SCRIPT

àBackup all systemdb (Masster, model, msdb):

DECLARE @DB VARCHAR(20)

DECLARE @BkpFName VARCHAR(100)

DECLARE @BkpFDate VARCHAR(50)

 

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

 

DECLARE Cursor_DBs CURSOR FOR

 

SELECT name

FROM master.sys.DATABASES

WHERE name IN ('master','model','msdb')

 

OPEN Cursor_DBs

FETCH NEXT FROM Cursor_DBs INTO @DB

 

WHILE @@FETCH_STATUS = 0

 

BEGIN

SET @BkpFName = 'C:\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'

BACKUP DATABASE @DB TO DISK = @BkpFName WITH COMPRESSION

FETCH NEXT FROM Cursor_DBs INTO @DB

END

 

CLOSE Cursor_DBs

DEALLOCATE Cursor_DBs

================================================================================================================================

àBackup all Userdb :

DECLARE @DB VARCHAR(20)

DECLARE @BkpFName VARCHAR(100)

DECLARE @BkpFDate VARCHAR(50)

 

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

 

DECLARE Cursor_DBs CURSOR FOR

 

SELECT name

FROM master.sys.DATABASES

WHERE name NOT IN ('master','model','msdb', 'tempdb')

 

OPEN Cursor_DBs 

FETCH NEXT FROM Cursor_DBs INTO @DB 

 

WHILE @@FETCH_STATUS = 0 

 

BEGIN 

SET @BkpFName = 'C:\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'

BACKUP DATABASE @DB TO DISK =  @BkpFName  WITH COMPRESSION

FETCH NEXT FROM Cursor_DBs INTO @DB 

END 

 

CLOSE Cursor_DBs 

DEALLOCATE Cursor_DBs

 ===================================================================================

àBackup all systemdb & Userdb's:

DECLARE @DB VARCHAR(20)

DECLARE @BkpFName VARCHAR(100)

DECLARE @BkpFDate VARCHAR(50)

 

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

 

DECLARE Cursor_DBs CURSOR FOR

 

SELECT name

FROM master.sys.DATABASES

WHERE name != 'tempdb'

 

OPEN Cursor_DBs 

FETCH NEXT FROM Cursor_DBs INTO @DB 

 

WHILE @@FETCH_STATUS = 0 

 

BEGIN 

SET @BkpFName = 'C:\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'

BACKUP DATABASE @DB TO DISK =  @BkpFName  WITH COMPRESSION

FETCH NEXT FROM Cursor_DBs INTO @DB 

END 

 

CLOSE Cursor_DBs 

DEALLOCATE Cursor_DBs

Thanks & Regards,

ChanBasha Patan

+91-7411823445

   Think before you print. Go Green.