Restoring Multiple databases

Comments

• Options

  Matthew Flatt Redgate › Posts: 255 Bronze 5

  October 29, 2008 2:39PM

  Hi jortizv,
  
  I have the following script which may help, it doesn't exactly meet your requirements though.
  
  The script will go through each database on a server and restore a full backup appending 'Test' to the database name.
  
  If your backup SQL Server doesn't have the databases already created, you could retrieve the database names from a text file or something similar.
  
  The backup part of the script will need editing depending on logical/physical filenames etc
  

  USE master
  DECLARE @dbname NVARCHAR(260)
  DECLARE @newdbname NVARCHAR(260)
  DECLARE cDatabases CURSOR
      FOR SELECT  name
          FROM    sysdatabases
          WHERE   name != 'tempdb'
                  AND name != 'master'
                  AND name != 'msdb'
   --Databases to ignore
  DECLARE @datestamp VARCHAR(30)
  DECLARE @restorepath VARCHAR(500)
  DECLARE @filename VARCHAR(500)
  DECLARE @restorestring VARCHAR(1000)
  DECLARE @exitcode INT
  DECLARE @sqlerrorcode INT
  
  SET @restorepath = 'D:\Backup\'
   --Directory backups are stored
  
  OPEN cDatabases
  FETCH NEXT FROM cDatabases INTO @dbname
  
  WHILE @@FETCH_STATUS = 0
      BEGIN
          SET @newdbname = @dbname + 'Test'
          SET @filename = @restorepath + 'FULL_SQL2005_' + @dbname + '_*.sqb'
          -- FULL_SQL2005 relates to my template for naming backups - Only 1 full backup for each database can be in the Backup location.
          SET @restorestring = '-SQL "RESTORE DATABASE [' + @newdbname
              + '] FROM DISK = ''' + @filename + ''' WITH RECOVERY, MOVE '''
              + @dbname + ''' TO ''D:\Data\' + @dbname + '.mdf'', MOVE '''
              + @dbname + '_log'' TO ''D:\Data\' + @dbname + '.ldf''" -E'
  
          EXEC master..sqlbackup @restorestring, @exitcode OUTPUT,
              @sqlerrorcode OUTPUT
   
          IF ( @exitcode <> 0 )
              OR ( @sqlerrorcode <> 0 ) 
              BEGIN
                  RAISERROR ( 'SQL Backup job failed with exitcode: %d  SQL error code: %d',
                      16, 1, @exitcode, @sqlerrorcode )
              END
  
          FETCH NEXT FROM cDatabases INTO @dbname
      END
  
  CLOSE cDatabases
  DEALLOCATE cDatabases
  

  
  I hope this is useful for you.

  Matthew Flatt
  Redgate Foundry

  0 · Share on Twitter
• Options

  Matthew Flatt Redgate › Posts: 255 Bronze 5

  November 5, 2008 2:15PM

  I have created another script which more suits your needs.
  
  The script will read file names from a directory and overwrite existing databases/create new databases on a server.
  
  If you are interested in this script please e-mail support@red-gate.com and put this forum posting in the subject/content.
  
  It is a little big to paste here.
  
  Thanks,

  Matthew Flatt
  Redgate Foundry

  0 · Share on Twitter
• Options

  RickyR Posts: 67

  February 22, 2016 4:47PM

  For future reference, here is the script that Matt is referring to
  
  /*
  Creation Data : 05/11/2008
  
  This script will look in a directory for SQL Backup .sqb files and restore the newest backup over current databases.
  
  The script will also look in a directory for any SQL Backup .sqb files not associated with a current database.
  
  The database name will then be extracted from the header, checked against the current system databases.
  
  If this is a new database the script will read the file list and restore to a new database, moving 1 data and 1 log file.
  
  For more data/log files adjustments will need to be made.
  
  This script is provided "AS IS".
  */
  
  USE master
  DECLARE @dbname NVARCHAR(260)
  DECLARE cDatabases CURSOR
  FOR SELECT name
  FROM sysdatabases
  WHERE name != 'tempdb'
  AND name != 'master'
  AND name != 'msdb'
  --Databases to ignore
  DECLARE @restorepath VARCHAR(500)
  DECLARE @filename VARCHAR(500)
  DECLARE @restorestring VARCHAR(1000)
  DECLARE @exitcode INT
  DECLARE @sqlerrorcode INT
  DECLARE @searchstring VARCHAR(500)
  DECLARE @flagg INT
  DECLARE @ldata VARCHAR(200)
  DECLARE @llog VARCHAR(200)
  SET @restorepath = 'C:Backup'
  --Directory backups are stored
  
  -- Tempory tables to store list of all .sqb files in the @resorepath directory
  CREATE TABLE #sqbdir
  (
  subdirectory VARCHAR(500),
  Files BIT,
  size INT,
  datemodified datetime
  )
  CREATE TABLE #Files
  (
  subdirectory VARCHAR(500)
  )
  -- Get All files/Folder names in @restorepath
  INSERT INTO #sqbdir
  EXEC MASTER..SQBDIR @restorepath
  -- Filter only backup files with .sqb extension
  INSERT INTO #Files
  SELECT subdirectory
  FROM #sqbdir
  WHERE subdirectory LIKE '%.sqb'
  -- Get first database in sysdatabase
  OPEN cDatabases
  FETCH NEXT FROM cDatabases INTO @dbname
  -- While there are more databases
  @FETCH_STATUS = 0
  BEGIN
  SET @filename = NULL
  -- Search for .sqb files in the format of FULL_SQL2005_databasename_2date.sqb
  SET @searchstring = 'Full_SQL2005_' + @dbname + '_2%'
  -- Get the most recent backup file for the current database, only files in above format will be retrieved. Datastamp in filename used to retreive newest
  SELECT @filename = subdirectory
  FROM #Files
  WHERE subdirectory = ( SELECT TOP ( 1 )
  subdirectory
  FROM #Files
  WHERE subdirectory LIKE @searchstring
  ORDER BY subdirectory DESC
  )
  -- Set the backup command for the restore, overwrites current database with backup and moves file to Restored Folder
  SET @restorestring = '-SQL "RESTORE DATABASE [' + @dbname
  + '] FROM DISK = ''' + @restorepath + @filename
  + ''' WITH RECOVERY, REPLACE, MOVETO = ''C:BackupRestored''" -E'
  -- If a backup file exists begin the restore
  IF ( @filename != '' )
  BEGIN
  EXEC master..sqlbackup @restorestring, @exitcode OUTPUT,
  @sqlerrorcode OUTPUT
  -- If an error occurred raise it
  IF ( @exitcode <> 0 )
  OR ( @sqlerrorcode <> 0 )
  BEGIN
  RAISERROR ( 'SQL Backup job failed with exitcode: %d SQL error code: %d',
  16, 1, @exitcode, @sqlerrorcode )
  END
  END
  
  
  -- Get next database
  FETCH NEXT FROM cDatabases INTO @dbname
  END
  -- Close cursor and drop temporary tables
  CLOSE cDatabases
  DEALLOCATE cDatabases
  DROP TABLE #Files
  DROP TABLE #sqbdir
  -- Create new tempory tables to store file name information
  CREATE TABLE #sqbdir2
  (
  subdirectory VARCHAR(500),
  Files BIT,
  size INT,
  datemodified datetime
  )
  CREATE TABLE #Files2
  (
  subdirectory VARCHAR(500)
  )
  
  -- Get any .sqb files still in the @restorepath folder
  INSERT INTO #sqbdir2
  EXEC MASTER..SQBDIR @restorepath
  INSERT INTO #Files2
  SELECT subdirectory
  FROM #sqbdir2
  WHERE subdirectory LIKE '%.sqb'
  
  -- Create a cursor with list of .sqb files remaining and get first filename
  DECLARE cDatabases CURSOR
  FOR SELECT subdirectory
  FROM #Files2
  OPEN cDatabases
  FETCH NEXT FROM cDatabases INTO @filename
  -- While there are files remaining
  @FETCH_STATUS = 0
  BEGIN
  -- Create a tempory table and read the current backup files header information, inserting it into the table
  CREATE TABLE #header
  (
  id INT IDENTITY,
  header VARCHAR(MAX)
  )
  SET @restorestring = '-SQL "RESTORE SQBHEADERONLY FROM DISK = '''
  + @restorepath + @filename + ''' WITH SINGLERESULTSET" -E'
  INSERT INTO #header
  EXEC MASTER..sqlbackup @restorestring
  -- Extract the database name from the header file
  SELECT @dbname = SUBSTRING(header, 23, 260)
  FROM #HEADER
  WHERE header LIKE 'Database name%'
  -- Check the database name doesn't currently exist on the Server, if it does not restore the file
  SELECT @flagg = COUNT(*)
  FROM sysdatabases
  WHERE name = @dbname
  IF ( @flagg = 0 )
  BEGIN
  -- Create a temporary table to store filelist information and insert the data
  CREATE TABLE #FileList
  (
  ID INT IDENTITY,
  LogicalName VARCHAR(200),
  PhysicalName VARCHAR(200),
  Type CHAR,
  FileGroupName VARCHAR(100),
  Size INT,
  MaxSize BIGINT,
  FileID INT,
  CreateLSN BIGINT,
  DropLSN BIGINT,
  UniqueID VARCHAR(36),
  ReadOnlyLSN INT,
  ReadWriteLSN INT,
  BackupSizeInBytes INT,
  SourceBlockSize INT,
  FileGroupID INT,
  LogGroupGUID VARCHAR(36),
  DifferentialBaseLSN BIGINT,
  DifferentialBaseGUID VARCHAR(36),
  IsReadOnly BIT,
  IsPresent BIT
  )
  
  SET @restorestring = '-SQL "RESTORE FILELISTONLY FROM DISK = '''
  + @restorepath + @filename + '''" -E'
  INSERT INTO #FileList
  EXEC master..sqlbackup @restorestring
  -- Get the Logical Names for the Data and Log files
  SELECT @ldata = LogicalName
  FROM #FileList
  WHERE TYPE = 'D'
  SELECT @llog = LogicalName
  FROM #FileList
  WHERE TYPE = 'L'
  -- Set the SQL Backup Command, this will create a new database and move the data/log files to D:Data
  -- If your databases generally have more data/log files script will need to be ammended for each extra file
  SET @restorestring = '-SQL "RESTORE DATABASE [' + @dbname
  + '] FROM DISK = ''' + @restorepath + @filename
  + ''' WITH RECOVERY, MOVE ''' + @ldata
  + ''' TO ''D:Data' + @dbname + '_Data.mdf'', MOVE '''
  + @llog + ''' TO ''D:Data' + @dbname
  + '_Log.ldf'', MOVETO = ''C:BackupRestored''" -E'
  -- run the backup command and catch any errors
  EXEC master..sqlbackup @restorestring, @exitcode OUTPUT,
  @sqlerrorcode OUTPUT
  IF ( @exitcode <> 0 )
  OR ( @sqlerrorcode <> 0 )
  BEGIN
  RAISERROR ( 'SQL Backup job failed with exitcode: %d SQL error code: %d',
  16, 1, @exitcode, @sqlerrorcode )
  END
  DROP TABLE #FileList
  END
  
  DROP TABLE #header
  
  
  FETCH NEXT FROM cDatabases INTO @filename
  END
  
  CLOSE cDatabases
  DEALLOCATE cDatabases
  
  DROP TABLE #Files2
  DROP TABLE #sqbdir2

  Ricky Ram
  Technical Support
  Red Gate Software Ltd.

  0 · Share on Twitter