Today as part of a migration, a client asked me to backup all thirty three SQL databases on a specific server to a local drive.  Normally they only ask for a handful, and I use the following code:

USE <em>databaseName</em>;
GO
BACKUP DATABASE <em>databaseName</em>
TO DISK = 'I:\backups\<em>currentDate</em>_<em>currentTime</em>_<em>serverName</em>_<em>databaseName</em>.bak'
WITH FORMAT,13
MEDIANAME = 'I:\backups',
NAME = <em>'currentDate</em>_<em>currentTime</em>_<em>serverName</em>_<em>databaseName</em>.bak';
GO

However with thirty three databases to do, I needed something a little smarter.

The store procedure sp_databases gives me a list of all databases, but this also includes the temp, model and master databases which I don’t need.  What I needed was a script to iterate through all the databases, and back them up.

The first attempt went like this:

DECLARE @backupCommand varchar(1000)
SELECT @backupCommand = 'IF ''?'' IN(''dbaUtils'') BEGIN USE ? BACKUP DATABASE ? TO DISK = ''I:\backups\20150526_<em>serverName</em>_?.bak'' WITH FORMAT, MEDIANAME = ''I:\backups', NAME = ''20150526_<em>serverName</em>_?.bak'' END'
EXEC sp_MSforeachdb @backupCommand

Whilst that was great, it didn’t give me the current date and time for each database as it backed up.  After a lot of debugging, this came about:

DECLARE @backupCommand varchar(2000)
SELECT @backupCommand = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?; DECLARE @filename varchar(100) SET @filename = ''I:\backups\'' + left(replace(replace(replace(convert(varchar(25), getdate(), 120),''-'',''''),'' '',''_''),'':'',''''),13) + ''_'' + @@SERVERNAME + ''_?.bak''; BACKUP DATABASE ? TO DISK=@filename END'
EXEC sp_MSforeachdb @backupCommand

A big thanks to my friend and resident SQL Jedi Master Chris for his help (read: he did it all) on this.