Introduction
All of us have a daily routine to create a back up of databases on a daily or weekly basis. I am here presenting a simple script that can create backup of all databases except the system databases. Backup files will be saved in the format NameOfDatabase_YYYYMMDDHHMMSS.bak, where the 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 and seconds.
- Backup all databases except the system databases
- 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 the below line and 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 and deallocate cursor
CLOSE db_cursor
DEALLOCATE db_cursor
