soundripple

Yesterday I posted a script to back up and compress all the user databases on a server.  Today, faced with restoring them one-by-one, I decided it was worth having  a stab at automating the process.

It turns out that SQL Mag posted an article on something pretty much exactly what I needed.  I took their code and tweaked it a bit – mainly to change it from a Stored Procedure into a utility script (I’m not interested in having it live in the DB), and to correct a couple of problems for when it’s used against SQL Server 2005.  I also added a small section to fix the users on each DB as it is restored; I didn’t do anything fancy or rigorous like iterating the users, since I know what mine are in advance (same for all DBs).   I also set it to use the DB name for both the .mdf and .ldf, and as a side-effect it doesn’t support multiple data containers (mdf, ndf, etc).

Here’s the code, anyway; change the section near the top to point to your required directories (@backupDir and @dataDir).

--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 @dbname   sysname
declare @dbshort  sysname
declare @line     varchar(100)
declare @reststmt    varchar(600)
declare @ntstmt   varchar(100)
declare @datafilename sysname
declare @logfilename sysname
declare @i        int
declare @pos1     int
declare @backupDir    varchar(600)
declare @dataDir    varchar(600)

--Setup these two lines to point to appropriate locations.
--BackupDir is the location of your .BAK files, and dataDir is the location you want to store your DBs.
--Both must NOT have trailing backslash, neither should have spaces in path (use 8.3 format if necessary)
select @backupDir = 'c:\database\backup\migrate'
select @dataDir = 'c:\database'

set nocount on
IF OBJECT_ID('tempdb..#listfile') IS NOT NULL
 drop table #listfile
create table #listfile
 (LogicalName nvarchar(128),
 PhysicalName nvarchar(260),
 Type char(1),
 FileGroupName nvarchar(128),
 Size numeric(20,0),
 MaxSize numeric(20,0),
 FileID bigint,
 CreateLSN numeric(25,0),
 DropLSN numeric(25,0) null,
 UniqueID uniqueidentifier,
 ReadOnlyLSN numeric(25,0) null,
 ReadWriteLSN numeric(25,0) null,
 BackupSizeInBytes bigint,
 SourceBlockSize int,
 FileGroupID int,
 LogGroupGUID uniqueidentifier null,
 DifferentialBaseLSN numeric(25,0) null,
 DifferentialBaseGUID uniqueidentifier null,
 IsReadOnly bit,
 IsPresent bit
 )

IF OBJECT_ID('tempdb..#userdbs') IS NOT NULL
 drop table #userdbs
create table #userdbs
 (line varchar(100))

set @ntstmt = 'xp_cmdshell ' + char(39) + 'dir ' + @backupdir+ char(39)
insert into #userdbs exec (@ntstmt)
declare cdb cursor for select line from #userdbs where upper(line) like '%BAK%'

open cdb
fetch cdb into @line
while @@fetch_status = 0
begin
 set @pos1 = CharIndex('.bak' ,@line,1)
 set @i = @pos1
 while substring (@line,@i,1) <> ' ' and @i > 1
 begin
 set @i = @i - 1
 end
 set @dbname = substring (@line,@i+1,@pos1+3-@i)
 set @dbshort = rtrim(ltrim(replace(@dbname,'.bak','')))

 truncate table #listfile
 insert into #listfile    exec ('RESTORE filelistonly from disk=' + '''' + @backupdir + '\' + @dbname  + '''')
--print 'RESTORE filelistonly from disk=' + '''' + @backupdir + '\' + @dbname  + ''''

 select @datafilename = LogicalName from #listfile where type = 'D'
 select @logfilename = LogicalName from #listfile where type = 'L'

 set @reststmt = 'restore Database ' + @dbshort + ' from ' +
 'Disk = ' + char(39) +@backupdir + '\' +
 rtrim(ltrim(@dbname)) + char(39)

 if Isnull (@dataDir,'*') <> '*'
 begin
 set @reststmt = @reststmt + ' with move ' + char(39) + @datafilename + char(39) +
 ' to ' + char(39) + @dataDir + '\' + @dbshort + '.mdf' + char(39) +
 ', move ' + char(39) + @logfilename + char(39) + ' to ' + char(39) +
 @dataDir + '\'  + @dbshort + '.ldf' + char(39)
 end
 print @reststmt
 exec (@reststmt)

 exec ('use ' + @dbshort + ' alter user v6user with login=v6user')
 exec ('use ' + @dbshort + ' alter user v6owner with login=v6owner')
 fetch cdb into @line
end 

close cdb
deallocate cdb
drop table #userdbs
drop table #listfile

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

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