All of us have daily routine to create back up of databases on daily or weekly basis. I am here presenting a simple script that can create backup all databases except the system databases. Backup files will be saved in the format NameOfDatabase_YYYYMMDDHHMMSS.bak, where name of database will be appended with date at which back up is created in format NameOfDatabase_YYYYMMDDHHMMSS where YYYY is Year, MM is month in numeric, DD Date, HHMMSS is hours, minutes & seconds.
—
— 1. Backup all databases except the system databases.
— 2. Backup files will be saved in the format NameOfDatabase_YYYYMMDDHHMMSS.bak.
—
— Name of database
DECLARE @DatabaseName VARCHAR(50)
— Path of backup folder.
DECLARE @BackupPath VARCHAR(256)
— Name of Backup file
DECLARE @BackUpFileName VARCHAR(256)
— Get current date used for suffixing with file name.
DECLARE @Date VARCHAR(20)
— Specify path for database backup directory. Make sure directory exists before executing script, else script will give error.
SET @BackupPath = ‘C:\Backup\’
— Get current date used for suffixing with file name.
SELECT @Date = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),‘:’,”)
–Declaring cursor for storing Database Names
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
— Excluding system databases
WHERE name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’)
–For Specific Database, replace TestDB with required database name in below line & uncomment it
–AND name IN (‘TestDB’)
–Fetching database names from cursor to local variable
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName
–Loop for all databases
WHILE @@FETCH_STATUS = 0
BEGIN
–Setting file name in format NameOfDatabase_YYYYMMDDHHMMSS
SET @BackUpFileName = @BackupPath + @DatabaseName + ‘_’ + @Date + ‘.bak’
–Creating back up
BACKUP DATABASE @DatabaseName TO DISK = @BackUpFileName
–Fetching Next Database Name
FETCH NEXT FROM db_cursor INTO @DatabaseName
END
–Close & deallocate cursor
CLOSE db_cursor
DEALLOCATE db_cursor
Connoisseur 