Error Code 870 Parameters?
essamughal
Posts: 35
Hi;
I am trying to parameterise all the values in the command for sqlbackup stored procedure.
DECLARE @exitcode int
DECLARE @sqlerrorcode int
Declare @L_RESTORE_DATABASE_COMMAND nvarchar(4000)
Declare @L_DATABASENAME varchar(100)
Declare @L_DISK_PATH varchar(500)
Declare @L_MOVETO_PATH varchar(500)
Declare @L_STANDBY_UNDO_PATH varchar(500)
Declare @L_DATAFILE_NAME varchar(100)
Declare @L_MOVE_DATA_PATH varchar(500)
Declare @L_LOGFILE_NAME varchar(100)
Declare @L_MOVE_LOG_PATH varchar(500)
Declare @L_PASSWORD varchar(100)
SET @L_DATABASENAME = '[abc]'
SET @L_DISK_PATH = '''''\\abc\E$\Dump\*.sqb'''''
SET @L_MOVETO_PATH = '''''\\abc\E$\Dump\Processed\'''''
SET @L_STANDBY_UNDO_PATH = '''''E:\UNDO_abc.DAT'''''
SET @L_DATAFILE_NAME = '''''abc_Data'''''
SET @L_MOVE_DATA_PATH = '''''E:\SecondarySiteDatabase\abc.mdf'''''
SET @L_LOGFILE_NAME = '''''abc_Log'''''
SET @L_MOVE_LOG_PATH = '''''E:\SecondarySitedatabase\abc_log.LDF'''''
SET @L_PASSWORD ='''''abc'''''
SET @L_RESTORE_DATABASE_COMMAND = '''-SQL "RESTORE DATABASE ' + @L_DatabaseName +
' FROM DISK = ' + @L_DISK_PATH +
' WITH MOVETO = ' + @L_MOVETO_PATH + ' , ' +
' STANDBY = ' + @L_STANDBY_UNDO_PATH + ' , ' +
'MOVE ' + @L_DATAFILE_NAME + ' TO ' + @L_MOVE_DATA_PATH + ' , ' +
'MOVE ' + @L_LOGFILE_NAME + ' TO ' + @L_MOVE_LOG_PATH + ' , ' +
' REPLACE, PASSWORD = ' + @L_PASSWORD + '" -E -I '''
Print @L_RESTORE_DATABASE_COMMAND
Exec master..sqlbackup @L_RESTORE_DATABASE_COMMAND , @exitcode OUTPUT, @sqlerrorcode OUTPUT
The print statement is giving me the exact command as I have without parameters. And If copy the statement which is created by Print statement it is running as well but, when I run
Exec master..sqlbackup @L_RESTORE_DATABASE_COMMAND , @exitcode OUTPUT, @sqlerrorcode OUTPUT
it is giving me this error:
SQL Backup (DLL v4.0.0.113)
login parameters:
[-U login id] [-P password] [-E] use trusted connection
[-I instance name]
common parameters:
[-SQL command]
[-USE setting description]
(7 row(s) affected)
name value
exitcode 870
sqlerrorcode 0
(2 row(s) affected)
Any Idea?
:?:
I am trying to parameterise all the values in the command for sqlbackup stored procedure.
DECLARE @exitcode int
DECLARE @sqlerrorcode int
Declare @L_RESTORE_DATABASE_COMMAND nvarchar(4000)
Declare @L_DATABASENAME varchar(100)
Declare @L_DISK_PATH varchar(500)
Declare @L_MOVETO_PATH varchar(500)
Declare @L_STANDBY_UNDO_PATH varchar(500)
Declare @L_DATAFILE_NAME varchar(100)
Declare @L_MOVE_DATA_PATH varchar(500)
Declare @L_LOGFILE_NAME varchar(100)
Declare @L_MOVE_LOG_PATH varchar(500)
Declare @L_PASSWORD varchar(100)
SET @L_DATABASENAME = '[abc]'
SET @L_DISK_PATH = '''''\\abc\E$\Dump\*.sqb'''''
SET @L_MOVETO_PATH = '''''\\abc\E$\Dump\Processed\'''''
SET @L_STANDBY_UNDO_PATH = '''''E:\UNDO_abc.DAT'''''
SET @L_DATAFILE_NAME = '''''abc_Data'''''
SET @L_MOVE_DATA_PATH = '''''E:\SecondarySiteDatabase\abc.mdf'''''
SET @L_LOGFILE_NAME = '''''abc_Log'''''
SET @L_MOVE_LOG_PATH = '''''E:\SecondarySitedatabase\abc_log.LDF'''''
SET @L_PASSWORD ='''''abc'''''
SET @L_RESTORE_DATABASE_COMMAND = '''-SQL "RESTORE DATABASE ' + @L_DatabaseName +
' FROM DISK = ' + @L_DISK_PATH +
' WITH MOVETO = ' + @L_MOVETO_PATH + ' , ' +
' STANDBY = ' + @L_STANDBY_UNDO_PATH + ' , ' +
'MOVE ' + @L_DATAFILE_NAME + ' TO ' + @L_MOVE_DATA_PATH + ' , ' +
'MOVE ' + @L_LOGFILE_NAME + ' TO ' + @L_MOVE_LOG_PATH + ' , ' +
' REPLACE, PASSWORD = ' + @L_PASSWORD + '" -E -I '''
Print @L_RESTORE_DATABASE_COMMAND
Exec master..sqlbackup @L_RESTORE_DATABASE_COMMAND , @exitcode OUTPUT, @sqlerrorcode OUTPUT
The print statement is giving me the exact command as I have without parameters. And If copy the statement which is created by Print statement it is running as well but, when I run
Exec master..sqlbackup @L_RESTORE_DATABASE_COMMAND , @exitcode OUTPUT, @sqlerrorcode OUTPUT
it is giving me this error:
SQL Backup (DLL v4.0.0.113)
login parameters:
[-U login id] [-P password] [-E] use trusted connection
[-I instance name]
common parameters:
[-SQL command]
[-USE setting description]
(7 row(s) affected)
name value
exitcode 870
sqlerrorcode 0
(2 row(s) affected)
Any Idea?
:?:
Comments
SET @L_RESTORE_DATABASE_COMMAND = '''-SQL "RESTORE ... -E -I '''
use
SET @L_RESTORE_DATABASE_COMMAND = '-SQL "RESTORE ... -E -I'
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Thanks for your reply. I had to remove quotes from each variable as well.
Here is correct syntax for others who want to do the same task.
DECLARE @exitcode int
DECLARE @sqlerrorcode int
Declare @L_RESTORE_DATABASE_COMMAND nvarchar(4000)
Declare @L_DATABASENAME varchar(100)
Declare @L_DISK_PATH varchar(500)
Declare @L_MOVETO_PATH varchar(500)
Declare @L_STANDBY_UNDO_PATH varchar(500)
Declare @L_DATAFILE_NAME varchar(100)
Declare @L_MOVE_DATA_PATH varchar(500)
Declare @L_LOGFILE_NAME varchar(100)
Declare @L_MOVE_LOG_PATH varchar(500)
Declare @L_PASSWORD varchar(100)
SET @L_DATABASENAME = '[abc]'
SET @L_DISK_PATH = '''\\abc\E$\Dump\*.sqb'''
SET @L_MOVETO_PATH = '''\\abc\E$\Dump\Processed\'''
SET @L_STANDBY_UNDO_PATH = '''E:\UNDO_abc.DAT'''
SET @L_DATAFILE_NAME = '''abc_Data'''
SET @L_MOVE_DATA_PATH = '''E:\SecondarySiteDatabase\abc.mdf'''
SET @L_LOGFILE_NAME = '''abc_Log'''
SET @L_MOVE_LOG_PATH = '''E:\SecondarySitedatabase\abc_log.LDF'''
SET @L_PASSWORD = '''abc'''
SET @L_RESTORE_DATABASE_COMMAND = '-SQL "RESTORE DATABASE ' + @L_DatabaseName +
' FROM DISK = ' + @L_DISK_PATH +
' WITH MOVETO = ' + @L_MOVETO_PATH + ' , ' +
' STANDBY = ' + @L_STANDBY_UNDO_PATH + ' , ' +
'MOVE ' + @L_DATAFILE_NAME + ' TO ' + @L_MOVE_DATA_PATH + ' , ' +
'MOVE ' + @L_LOGFILE_NAME + ' TO ' + @L_MOVE_LOG_PATH + ' , ' +
' REPLACE, PASSWORD = ' + @L_PASSWORD + '" -E -I '
Print @L_RESTORE_DATABASE_COMMAND
Exec master..sqlbackup @L_RESTORE_DATABASE_COMMAND , @exitcode OUTPUT, @sqlerrorcode OUTPUT
Thanks