Scheduled Restored (with multiple databases) in same job

• 
  Here is one option. It requires that you first create a function that takes a comma delimited string of database names, and returns a table containing one row for each value.
  

  CREATE FUNCTION dbo.csv2table (@strString varchar(4000))
  RETURNS  @Result TABLE(ID int IDENTITY, dbname varchar(256))
  AS
  BEGIN
      WITH StrCTE(start, stop) AS
      (
        SELECT  1, CHARINDEX(',' , @strString )
        UNION ALL
        SELECT  stop + 1, CHARINDEX(',' ,@strString  , stop + 1)
        FROM StrCTE
        WHERE stop > 0
      )
     
      INSERT INTO @Result
      SELECT LTRIM(SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END)) AS stringValue
      FROM StrCTE
     
      RETURN
  END
  

  
  Then, in your job step, you could do this:
  

  SELECT * INTO #dbnames FROM csv2table('dbrestore001,  dbrestore002') --  change the string value to those of the databases you want to restore
  
  DECLARE @ID int = 1
  DECLARE @maxID int
  
  SELECT @maxID = MAX(ID) FROM #dbnames
  
  DECLARE @dbname varchar(128)
  DECLARE @sqbcmd varchar(1024)
  DECLARE @sqbexitcode int
  DECLARE @sqlerrorcode int
  
  WHILE (@ID <= @MAXID)
  BEGIN
  	SELECT @dbname = dbname FROM #dbnames WHERE ID = @ID
  
  	SET @sqbcmd = '-SQL "RESTORE DATABASE [' + @dbname + '] FROM DISK = [\serverArchive*.sqb] SOURCE = [' + @dbname + '] LATEST_FULL WITH RECOVERY, DISCONNECT_EXISTING, MOVE DATAFILES TO [F:Temp], MOVE LOGFILES TO [F:Temp], REPLACE, ORPHAN_CHECK, CHECKDB = [NO_INFOMSGS, ALL_ERRORMSGS]"'
  
  	EXECUTE sqlbackup @sqbcmd, @sqbexitcode OUT, @sqlerrorcode OUT
  
  	IF (@sqbexitcode >= 500) OR (@sqlerrorcode <> 0)
  	BEGIN
  		RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @sqbexitcode, @sqlerrorcode)
  	END
  
  	SET @ID = @ID + 1
  END
  
  DROP TABLE #dbnames
  

  petey May 27, 2015 00:08

  - 0 +
• 
  Peter,
  Thanks for this works a treat!
  Regards
  Ian

  marmite_dBA June 09, 2015 11:15

  - 0 +

Add comment

Please sign in to leave a comment.