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,
#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,
#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.
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.
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.
Very Informative
ReplyDeleteI prefer not to use cursors. If I need to back up a SQL express instance using code, I like powershell.
ReplyDeleteYou could also do the same for the undocumented SP - sp_msforeachdb.
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).
ReplyDeleteThe .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/
Yes Adam,
ReplyDeleteWe can use sp_msforeachdb if we need all the databases backup without any filter.
Excellent information, thanks
ReplyDelete