How can we help you today? How can we help you today?

use dynamic string for sqlbackup command

Hi,

I need to run sqlbackup nightly to restore a backup using a dynmically constructed string inserting a date in the <AUTO> name string as below so that I can automate the restore process on a secondary server. The backup is from an EE instance and the restore is to an SE instance. What I have below errors out where I try to insert + @dt + into the command. How do I do this? This command, pulled from the script tab and altered to use the correct paths, succeeds with the date hardcoded, so I'm close, I guess.
DECLARE @dt CHAR&#40;8&#41;
SET @dt = CONVERT&#40;CHAR&#40;8&#41;,GETDATE&#40;&#41;,112&#41;

EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE &#91;CaliforniaSE&#93;  
FROM DISK = ''\\principal\backups\database\FULL_&#40;local&#41;_California_' + @dt + '_030000.sqb'' WITH RECOVERY, 
MOVE ''California'' TO ''F:\FileGroups\DataSE\CaliforniaSE.mdf'', 
MOVE ''California _Data'' TO ''F:\FileGroups\DataSE\CaliforniaSE_Data.ndf'', 
MOVE ''California_Indexes'' TO ''G:\FileGroups\IndexesSE\CaliforniaSE_Indexes.ndf'', 
MOVE ''California_Log'' TO ''H:\FileGroups\LogsSE\CaliforniaSE_Log.ldf''"'
dterrie
0

Comments

4 comments

Sort by
  • petey
    The entire command needs to be in a single string value e.g.
    DECLARE @cmd NVARCHAR&#40;1024&#41;
DECLARE @exitcode INT
DECLARE @errorcode INT

DECLARE @dt CHAR&#40;8&#41; 
SET @dt = CONVERT&#40;CHAR&#40;8&#41;,GETDATE&#40;&#41;,112&#41; 

SET @cmd = N'-SQL "RESTORE DATABASE &#91;CaliforniaSE&#93;  
FROM DISK = ''\\principal\backups\database\FULL_&#40;local&#41;_California_' + @dt + '_030000.sqb'' WITH RECOVERY, 
MOVE ''California'' TO ''F:\FileGroups\DataSE\CaliforniaSE.mdf'', 
MOVE ''California _Data'' TO ''F:\FileGroups\DataSE\CaliforniaSE_Data.ndf'', 
MOVE ''California_Indexes'' TO ''G:\FileGroups\IndexesSE\CaliforniaSE_Indexes.ndf'', 
MOVE ''California_Log'' TO ''H:\FileGroups\LogsSE\CaliforniaSE_Log.ldf''"'

EXEC master..sqlbackup @cmd, @exitcode OUTPUT, @errorcode OUTPUT
    petey
    0
  • dterrie
    Peter,

    Thanks for the help. Just to complete the loop, since I've run the command once and created the target db, I need to replace

    WITH RECOVERY with WITH RECOVERY, REPLACE

    yes?
    dterrie
    0
  • petey
    If using SQL Server 2005, yes.
    petey
    0
  • dterrie
    Fixed syntax errors and took seconds off default <AUT0> name, so we've got this, which works:
    DECLARE @cmd NVARCHAR&#40;1024&#41; 
DECLARE @exitcode INT 
DECLARE @errorcode INT 

DECLARE @dt CHAR&#40;8&#41; 
SET @dt = CONVERT&#40;CHAR&#40;8&#41;,GETDATE&#40;&#41;,112&#41; 

SET @cmd = '''N -SQL "RESTORE DATABASE &#91;CaliforniaSE&#93;  
FROM DISK = ''\\principal\backups\database\FULL_&#40;local&#41;_California_' + @dt + '_0300.sqb'' WITH 
MOVE ''California'' TO ''F:\FileGroups\DataSE\CaliforniaSE.mdf'', 
MOVE ''California _Data'' TO ''F:\FileGroups\DataSE\CaliforniaSE_Data.ndf'', 
MOVE ''California_Indexes'' TO ''G:\FileGroups\IndexesSE\CaliforniaSE_Indexes.ndf'', 
MOVE ''California_Log'' TO ''H:\FileGroups\LogsSE\CaliforniaSE_Log.ldf''" WITH RECOVERY, REPLACE' 
PRINT @cmd

EXEC master..sqlbackup @cmd, @exitcode OUTPUT, @errorcode OUTPUT
    dterrie
    0

Add comment

Please sign in to leave a comment.