dynamic sql syntax automatic restore

• humbar-a

  SSC Eights!

  Points: 970

  More actions

  August 14, 2008 at 3:52 am

  #122862

  Hi , can anyone help me about dynamic sql. I try to write an automatic restore script. But i don't know the syntax and i get always errors.

  i don't know how to add "@variable" into a Move to command

  Here's the script :

  declare @newBase nvarchar(4000)

  declare @backupDevice nvarchar(4000)

  set @newBase = 'try_restore2'

  set @backupDevice = 'BackupDevice_Name'

  RESTORE DATABASE @newBase FROM @backupDevice

  WITH FILE = 1,

  MOVE @backupDevice + '_data' TO 'e:\MSSQL\data\'+ @newBase + '_Data.MDF''',

  MOVE @backupDevice + '_log' TO 'e:\MSSQL\data\'+ @newBase + '_Log.LDF''',

  STANDBY = N'e:\MSSQL\BACKUP\ROLLBACK_UNDO_' + @backupDevice + '.BAK',

  NOUNLOAD, STATS = 10

  GO

  Thanks for any help.

• TheRedneckDBA

  SSChampion

  Points: 14001

  More actions

  August 14, 2008 at 6:56 am

  #858007

  nailosuper (8/14/2008)

  ---

  Hi , can anyone help me about dynamic sql. I try to write an automatic restore script. But i don't know the syntax and i get always errors.

  i don't know how to add "@variable" into a Move to command

  Here's the script :

  declare @newBase nvarchar(4000)

  declare @backupDevice nvarchar(4000)

  set @newBase = 'try_restore2'

  set @backupDevice = 'BackupDevice_Name'

  RESTORE DATABASE @newBase FROM @backupDevice

  WITH FILE = 1,

  MOVE @backupDevice + '_data' TO 'e:\MSSQL\data\'+ @newBase + '_Data.MDF''',

  MOVE @backupDevice + '_log' TO 'e:\MSSQL\data\'+ @newBase + '_Log.LDF''',

  STANDBY = N'e:\MSSQL\BACKUP\ROLLBACK_UNDO_' + @backupDevice + '.BAK',

  NOUNLOAD, STATS = 10

  GO

  Thanks for any help.

  Try declaring a big VARCHAR variable, setting it equal to the restore script, and then Executing it.

  I.e.

  DECLARE @sql VARCHAR(4000)

  SET @sql =

  '

  RESTORE DATABASE ' + @newBase + ' FROM ' + @backupDevice + '

  WITH FILE = 1,

  MOVE + ''' + @backupDevice + '_data'' TO ''e:\MSSQL\data\' + @newBase + '_Data.MDF'',

  MOVE + ''' + @backupDevice + '_log'' TO ''e:\MSSQL\data\' + @newBase + '_Log.LDF'',

  STANDBY = ''e:\MSSQL\BACKUP\ROLLBACK_UNDO_' + @backupDevice + '.BAK'',

  NOUNLOAD, STATS = 10

  '

  EXECUTE (@SQL)

  (or something like that)

  The Redneck DBA

• humbar-a

  SSC Eights!

  Points: 970

  More actions

  August 14, 2008 at 7:08 am

  #858019

  Thanks for your reply it seemed me logical but when i try it it gives always the syntax error

  third line. The line Move + ... I will try to make the other combinations thanks a lot..

  ' RESTORE DATABASE ' + @newBase + ' FROM ' + @backupDevice + '

  WITH FILE = 1,

  MOVE + ''' + @backupDevice + '_data'' TO ''e:\MSSQL\data\' + @newBase + '_Data.MDF'',

  MOVE + ''' + @backupDevice + '_log'' TO ''e:\MSSQL\data\' + @newBase + '_Log.LDF'',

  STANDBY = ''e:\MSSQL\BACKUP\ROLLBACK_UNDO_' + @backupDevice + '.BAK'',

  NOUNLOAD, STATS = 10

  '

  Msg 170, Level 15, State 1, Line 3

  Line 3: Incorrect syntax near '+'

• TheRedneckDBA

  SSChampion

  Points: 14001

  More actions

  August 14, 2008 at 7:12 am

  #858023

  If you still can't get it, post the new script you are trying to run and the error you are getting. Might help someone troubleshoot and get you some help.

  The Redneck DBA

• humbar-a

  SSC Eights!

  Points: 970

  More actions

  August 14, 2008 at 7:14 am

  #858025

  Thanks really much it worked, just i had to delete the "+" near the command MOVE .

  SET @sql =

  ' RESTORE DATABASE ' + @newBase + ' FROM ' + @backupDevice + '

  WITH FILE = 1,

  MOVE ''' + @backupDevice + '_data'' TO ''e:\MSSQL\data\' + @newBase + '_Data.MDF'',

  MOVE ''' + @backupDevice + '_log'' TO ''e:\MSSQL\data\' + @newBase + '_Log.LDF'',

  STANDBY = ''e:\MSSQL\BACKUP\ROLLBACK_UNDO_' + @backupDevice + '.BAK'',

  NOUNLOAD, STATS = 10

  '

  Thanks.