Restore multiple db's to new server with new file locations

• Steven Webster-494809

  SSC Enthusiast

  Points: 199

  More actions

  September 9, 2013 at 9:50 pm

  #298637

  Comments posted to this topic are about the item Restore multiple db's to new server with new file locations

• Kurt W. Zimmerman

  SSCrazy Eights

  Points: 8676

  More actions

  September 10, 2013 at 8:05 am

  #1648898

  These are the kinds of utilities I tend to create over and over due to ever changing requirements. Case in point, I had to rebuild a mirror server from scratch and create the databases in the same sequence as the principal server. I had about 30 some odd databases to place out there. I was able to create the databases from backups that resided on a separate file server which is used for housing my backups from a variety of servers.

  The job took over 5 hours to run but it did the job. What the job did was simply create a script which I executed on the mirror server. I ran it for a specified database backup date, in this case 9/4. Again, may not be the best solution, but it did the job and all is well.

  Here is a sample of my code:

  set nocount on

  declare @Database_id int

  declare @Database_Name varchar(500)

  declare @FileName_Data varchar(500)

  declare @FileName_Log varchar(500)

  declare @Logical_Data_Name varchar(100)

  declare @Logical_Log_Name varchar(100)

  declare @DB_File_Name varchar(100)

  declare @ID int

  declare @CRLF char(2) = char(13) + char(10)

  declare @BackupPath varchar(max) = '\\<someserver>\<someshare>\<somefolder>'

  declare @FullyQualifiedPath varchar(max)

  declare @DirectoryCmd varchar(8000)

  declare @TodayDate varchar(10)

  declare @ProcessDate datetime = '09/04/2013'

  declare @SQL varchar(max)

  set @TodayDate = cast(YEAR(@ProcessDate) as varchar) + '_' + right('0' + cast(MONTH(@ProcessDate) as varchar),2) + '_' + right('0' + cast(DAY(@ProcessDate) as varchar),2)

  declare @Databases table (ID int primary key identity (1,1)

  ,database_id int

  ,Database_Name varchar(500)

  ,Logical_Data_Name varchar(500)

  ,FileName_Data varchar(500)

  ,Logical_Log_Name varchar(500)

  ,FileName_Log varchar(500)

  ,DB_File_Name varchar(500)

  )

  create table #FileNames (Physical_Name varchar(500)

  ,Logical_Name varchar(500)

  ,type_desc varchar(5))

  declare @DatabaseBackups table (database_id int

  ,BackupFileName varchar(max))

  insert into @Databases (database_id, Database_Name)

  select database_id, name as Database_Name

  from sys.databases

  where database_id > 4

  order by database_id

  declare Database_Cursor cursor for

  select ID

  , database_id

  , Database_name

  from @Databases

  open Database_Cursor

  fetch next from Database_Cursor into @ID, @Database_id, @Database_Name

  while @@FETCH_STATUS = 0

  begin

  set @SQL = 'delete from #FileNames;' + @CRLF

  set @SQL += 'use [' + @Database_Name + ']; ' + @CRLF

  set @SQL += 'insert into #FileNames(Physical_Name, Logical_Name, type_desc)' + @CRLF

  set @SQL += 'select physical_name, name, type_desc from sys.database_files' + @CRLF

  exec (@SQL)

  update @Databases

  set FileName_Data = rows.Physical_Name

  ,Logical_Data_Name = rows.Logical_Name

  ,FileName_Log = logs.Physical_Name

  ,Logical_Log_Name = logs.Logical_Name

  from @Databases db inner join

  #FileNames rows on rows.type_desc = 'ROWS'

  and db.database_id = @Database_id inner join

  #FileNames logs on logs.type_desc = 'LOG'

  and db.Database_id = @Database_id

  set @FullyQualifiedPath = @BackupPath + '\' + @Database_Name

  set @DirectoryCmd = 'dir /b /o:-d ' + @FullyQualifiedPath + '\*.bak'

  delete from @DatabaseBackups

  insert into @DatabaseBackups (BackupFileName)

  exec xp_cmdshell @DirectoryCmd

  delete from @DatabaseBackups

  where BackupFileName is null or

  charindex(@TodayDate, BackupFileName) = 0

  update @DatabaseBackups

  set database_id = @Database_id

  update @Databases

  set DB_File_Name = DBUP.BackupFileName

  from @Databases DB inner join

  @DatabaseBackups DBUP on DB.database_id = DBUP.database_id

  fetch next from Database_Cursor into @ID, @Database_id, @Database_Name

  end

  close Database_Cursor

  deallocate Database_Cursor

  drop table #FileNames

  select * from @Databases

  declare RestoreDatabase_Cursor cursor for

  select Database_Name

  ,Logical_Data_Name

  ,FileName_Data

  ,Logical_Log_Name

  ,FileName_Log

  ,DB_File_Name

  from @Databases

  open RestoreDatabase_Cursor

  fetch next from RestoreDatabase_Cursor into @Database_Name, @Logical_Data_Name, @FileName_Data, @Logical_Log_Name, @FileName_Log, @DB_File_Name

  while @@FETCH_STATUS = 0

  begin

  set @FullyQualifiedPath = @BackupPath + '\' + @Database_Name + '\' + @DB_File_Name

  set @SQL = 'RESTORE DATABASE [' + @Database_Name + ']' + @CRLF

  set @SQL += 'FROM DISK = N''' + @FullyQualifiedPath + '''' + @CRLF

  set @SQL += 'WITH FILE = 1' + @CRLF

  set @SQL += ', MOVE N''' + @Logical_Log_Name + ''' TO N''' + @FileName_Log + '''' + @CRLF

  set @SQL += ', NOUNLOAD' + @CRLF

  set @SQL += ', REPLACE' + @CRLF

  set @SQL += ', STATS = 10' + @CRLF

  print @SQL

  print '-- -- --'

  fetch next from RestoreDatabase_Cursor into @Database_Name, @Logical_Data_Name, @FileName_Data, @Logical_Log_Name, @FileName_Log, @DB_File_Name

  end

  close RestoreDatabase_Cursor

  deallocate RestoreDatabase_Cursor

  Kurt W. Zimmerman
  SR DBA
  Lefrak Organization
  New York, NY

  http://www.linkedin.com/in/kurtwzimmerman

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  April 27, 2016 at 4:50 pm

  #1875104

  Thanks for the script.