Migrating SQL Server Databases (Part II)

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