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.