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 
Some how in code above comments using — are converted to some other character ‘–’and same goes to single quotes ‘ which are converted to ‘ or ’ while publishing, which renders code non executable. I am here pasting actual script removing comments so that it can work.
If you have error “Incorrect syntax near ‘‘’.”, please replace ‘‘’ & ‘’’ by single quotes and ” by double single quotes.
DECLARE @DatabaseName VARCHAR(50)
DECLARE @BackupPath VARCHAR(256)
DECLARE @BackUpFileName VARCHAR(256)
DECLARE @Date VARCHAR(20)
SET @BackupPath = ‘C:\Backup\’
SELECT @Date = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),’:’,”)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BackUpFileName = @BackupPath + @DatabaseName + ‘_’ + @Date + ‘.bak’
BACKUP DATABASE @DatabaseName TO DISK = @BackUpFileName
FETCH NEXT FROM db_cursor INTO @DatabaseName
END
CLOSE db_cursor
DEALLOCATE db_cursor
Thanks, it’s exact what I was looking for.
that is a fantastic post! congratulations on walking through those doors of opportunity!
I came to your “Back up all databases using T-SQL | Connoisseur” page and noticed you could have a lot more traffic.
this is a really interesting article. thanks.
Back up all databases using T-SQL | Connoisseur
Thanks for some other informative web site. The place else
may just I am getting that type of information written in
such a perfect manner? I’ve a undertaking that I’m simply now operating on, and I have been at the look out for
such information.
Great web site you’ve got here.. It’s hard to find high quality writing like yours these days. I truly appreciate people like you! Take care!!