I have published my posts related to database backup history, restore history and also cleanup for the same recently. For the database backup we are performing using the maintenance plans or wizard. We cal also performing this database activity by scripts automatically same as maintenance plans. Visit
full database backup from SSMS for reference.
How can?
I have created a scripts 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 predefined the backup folder and the it will be taken in DBName_FullBackup_Date_time format for the user databases. You can see here i have written a script for database 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\'
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
-- 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 predefined the backup folder and the it will be taken in DBName_DiffBackup_Date_time format for 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\'
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
-- 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 predefined the backup folder and the it will be taken in DBName_TrnBackup_Date_Time.trn format for 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 databases are in FULL recovery mode otherwise it will be failed.
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 time stamps extent for each backup file. You just need to schedule these script only. We can also use MSFOREACHDB if we don't need to filter any databases to backup.