I’m moving to a new laptop, and one of the more tedious tasks is backing up, moving, and restoring all my SQL Server databases (it’s quite a collection).

I figured it must be easy enough to automate, so that the entire backup process can happen overnight. I don’t have enough space to back everything up and leave it uncompressed, so figured a T-SQL script was in order; something that will back up all user databases, compressing each and deleting the backup file. A bit of searching turned up an example of how to use xp_cmdshell to run OS commands, and another script to do the actual backup, so combining them yields a beast that will backup each DB, compress it (using 7-Zip), then delete the backup file.

A small note: according to the docs, a T-SQL xp_cmdshell command “cannot contain more than one set of double quotation marks. A single pair of quotation marks is required if any spaces are present in the file paths or program names referenced in command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.”
Although this is a gigantic pain in the arse, you can of course work around it by either using paths that don’t include spaces, or by using the old 8.3 format as suggested.

Just change the two locations near the top of the script to point to your backup directory and your 7-Zip exe, and you should just be able to run the script. To test it on just the first database, comment out the ‘while’ line. Oh, and make sure you install 7-Zip of course.

I was going to write a similar script to do the reverse, based on iterating the directory contents and uncompressing / restoring each DB, but decided it wasn’t worth the time. For those who are interested, there’s an example here of how to iterate a directory, and you could easily reverse-engineer the below script to uncompress and restore instead.

Update: I gave in and set up a script for the restore too, see the post here.

--turn on the ability to run commands from T-SQL, only needed if we're in version 9 (SQL Server 2005) or later
if (select substring(@@version, charindex(' - ', @@version) + 3, charindex('.', @@version) - charindex(' - ', @@version) - 3)) > 8
begin
	exec sp_configure 'show advanced options', 1
	reconfigure
	exec sp_configure 'xp_cmdshell', 1
	reconfigure
end

declare @backupDir varchar(800)
declare @7zipExe varchar(800)
declare @backupName varchar(800)
declare @zipName varchar(800)
declare @cmd varchar(800)
declare @dbName varchar(200)

--Setup these two lines to point to appropriate locations.
--Dir must have trailing backslash, neither should have spaces in path (or use 8.3 format)
set @backupDir = 'C:\Database\Backup\migrate\'
set @7zipExe = 'C:\Progra~1\7-Zip\7z.exe'

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 @dbName  

while @@FETCH_STATUS = 0
begin
	set @backupName = @backupDir + @dbName + '.BAK'
	set @zipName = @backupDir + @dbName + '.7z'
	print 'Backing up ' + @dbName
	backup database @dbName to disk = @backupName

	--In the following command-line: 'a' is add, '-t7z' is 7-zip native format, '-mx7' is compression level (9 is max), '-mmt' is use multithreading
	select @cmd = '"' + @7zipExe + ' a -t7z -mx7 -mmt ' + @zipName + ' ' + @backupName + '"'
	print 'Compressing ' + @dbName
	EXEC  master..xp_cmdshell @cmd, no_output

	select @cmd = 'del "' + @backupName + '"'
	print 'Deleting .bak file for ' + @dbName
	EXEC  master..xp_cmdshell @cmd, no_output

	fetch next from db_cursor into @dbName

end

close db_cursor
deallocate db_cursor

if (select substring(@@version, charindex(' - ', @@version) + 3, charindex('.', @@version) - charindex(' - ', @@version) - 3)) > 8
begin
	exec sp_configure 'xp_cmdshell', 0
	reconfigure
	exec sp_configure 'show advanced options', 0
	reconfigure
end