Skip to main content

Automated All Databases Backups Script - SQL Server

I have published my posts related to database backup history, restore history and also cleanup for the same. For the database backup we are performing using the maintenance plans or a wizard. We could also perform this database activity by scripts automatically same as maintenance plans. Visit full database backup from SSMS for reference.

How can?
I have created a script to perform FULL, Differential and Transactional backups of user databases. I would like to share the scrips with you.

#1. Perform Full Backups:
Here I have predefine the backup folder and then it will be taken in DBName_FullBackup_Date_time format of the user databases. You can see here I have written the script for backups which are in Online state and have database_id > 4 means it will not consider system databases,
DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DEclare @BackupDate varchar(100)

-- Setting value of  backup date and folder of the backup
SET @BackupDate =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551
SET @BackupFolder = 'D:\DBBackups\'

-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR  
SELECT NAME FROM SYS.DATABASES 
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND database_id > 4  -- Exluding system databases


-- Opening and fetching next values from sursor
OPEN c_bakup 
FETCH NEXT FROM c_bakup INTO @BackupFile 

WHILE @@FETCH_STATUS = 0
BEGIN

SET @BAK_PATH = @BackupFolder + @BackupFile

-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_FullBackup_'+@BackupDate+'.bak'' WITH INIT;'

-- Executing dynamic query
PRINT (@Baksql)
EXEC(@Baksql)

-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile 
END 

-- Closing and Deallocating cursor
CLOSE c_bakup
DEALLOCATE c_bakup


#2. Perform Differential Backups:
Here I have predefine the backup folder and then it will be taken in DBName_DiffBackup_Date_time format of the user databases. You can see here I have written the script for backups which are in Online state and have database_id > 4 means it will not consider system databases,
DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DEclare @BackupDate varchar(100)

-- Setting value of  backup date and folder of the backup
SET @BackupDate =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551
SET @BackupFolder = 'D:\DBBackups\'

-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR  
SELECT NAME FROM SYS.DATABASES 
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND database_id > 4  -- Exluding system databases

-- Opening and fetching next values from sursor
OPEN c_bakup 
FETCH NEXT FROM c_bakup INTO @BackupFile 

WHILE @@FETCH_STATUS = 0
BEGIN

SET @BAK_PATH = @BackupFolder + @BackupFile

-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_DiffBackup_'+@BackupDate+'.bak'' WITH DIFFERENTIAL;'

-- Executing dynamic query
PRINT (@Baksql)
EXEC(@Baksql)

-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile 
END 

-- Closing and Deallocating cursor
CLOSE c_bakup
DEALLOCATE c_bakup


#3. Perform Transactional Backups:
Here I have predefine the backup folder and then it will be taken in DBName_TrnBackup_Date_time. trn format of the user databases. You can see here I have written the script for backups which are in Online state and have database_id > 4 means it will not consider system databases Even Database has, in FULL recovery mode other it will be filled.
DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DEclare @BackupDate varchar(100)

-- Setting value of  backup date and folder of the backup
SET @BackupDate =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551
SET @BackupFolder = 'D:\DBBackups\'
SET @Baksql = ''

-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR  
SELECT NAME FROM SYS.DATABASES 
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND database_id > 4  -- Exluding system databases
AND recovery_model_desc = 'FULL'  -- Including database which are only have FULL recovery mode

-- Opening and fetching next values from sursor
OPEN c_bakup 
FETCH NEXT FROM c_bakup INTO @BackupFile 

WHILE @@FETCH_STATUS = 0
BEGIN

SET @BAK_PATH = @BackupFolder + @BackupFile

-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP Log ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_TrnBackup_'+@BackupDate+'.trn'' ;'

-- Executing dynamic query
PRINT (@Baksql)
EXEC(@Baksql)

-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile 
END 

-- Closing and Deallocating cursor
CLOSE c_bakup
DEALLOCATE c_bakup


These are the scripts which will perform full, differential and transaction log backup with timestamps extent for each backup file. You just need to schedule this script only. I hope you liked this script for automated backups. You can share the way you are using for the backup plans here.

Comments

  1. I prefer not to use cursors. If I need to back up a SQL express instance using code, I like powershell.

    You could also do the same for the undocumented SP - sp_msforeachdb.

    ReplyDelete
  2. I do a bit more in my backup script. I do a single .BAK file per database where the first backup is a full backup, and subsequent backups are either a transaction log backup (for FULL or BULK LOGGED recovery databases), a differential backup (for SIMPLE recovery databases) or a full database backup (for the master database).

    The .BAK files are created in a directory specified by the DBA. If a file doesn't contain a FULL backup, one is taken automagically. If the BAK file doesn't exist, one is created and a full backup taken.

    This also allows a cycle (defined as the start of a FULL + incremental backup) to start at any hour. This defaults to midnight (@start_of_cycle = 0), but can be any hour.

    The error trapping could probably be improved... feel free to make recommendations.

    http://marcjellinek.wordpress.com/2011/08/09/backing-up-all-databases-on-sql-server/

    ReplyDelete
  3. Yes Adam,

    We can use sp_msforeachdb if we need all the databases backup without any filter.

    ReplyDelete

Post a Comment