Scheduled Restored (with multiple databases) in same job

marmite_dBA · May 22, 2015 9:28AM

Hi all,
I have setup a scheduled restore job within SQL Backup, though would like to know if there is a simpler way of selecting multiple databases within the same job, rather than creating 10 separate jobs for the 10 databases I have.
All test database files are in the same folder and going to the same database the only parameter that is different is the database name.
declare @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE b]<dBName>[/b 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''"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

I have achieved this by copying the above block 10 times and changing the <dBName> to suit, messy I know, is there a way I can set a table variable and pass/iterate the 10 database names into one block or can the GUI have this and I am missing something
Regards
Ian

petey · May 27, 2015 12:08AM

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 &gt; 0
    )
   
    INSERT INTO @Result
    SELECT LTRIM(SUBSTRING(@strString , start, CASE WHEN stop &gt; 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 &lt;= @MAXID)
BEGIN
	SELECT @dbname = dbname FROM #dbnames WHERE ID = @ID

	SET @sqbcmd = '-SQL "RESTORE DATABASE &#91;' + @dbname + '&#93; FROM DISK = &#91;\serverArchive*.sqb&#93; SOURCE = &#91;' + @dbname + '&#93; LATEST_FULL WITH RECOVERY, DISCONNECT_EXISTING, MOVE DATAFILES TO &#91;F:Temp&#93;, MOVE LOGFILES TO &#91;F:Temp&#93;, REPLACE, ORPHAN_CHECK, CHECKDB = &#91;NO_INFOMSGS, ALL_ERRORMSGS&#93;"'

	EXECUTE sqlbackup @sqbcmd, @sqbexitcode OUT, @sqlerrorcode OUT

	IF (@sqbexitcode &gt;= 500) OR (@sqlerrorcode &lt;&gt; 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

marmite_dBA · June 9, 2015 11:15AM

Peter,
Thanks for this works a treat!
Regards
Ian

Scheduled Restored (with multiple databases) in same job

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk