Restoring Database - passing parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Restoring Database - passing parameters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-30 : 13:57:32

Stardot718 writes "I am trying to automate a process using DTS Package.
One of the steps in the process is to Backup a database daily, then restore it to another Database which will be created daily based on the business date.
The following syntax fails in the restore statement and I cannot figure out why. Any ideas !??

DECLARE @databasename varchar(100)
DECLARE @dbPrefix char (2)
DECLARE @WorkDate char(8)
DECLARE @dbSuffix char (8)

SET @dbPrefix = 'XX'
SET @WorkDate = convert(char(8),getdate(), 112)
SET @dbSuffix = 'DBSAMPLE'
SET @databasename = @dbPrefix + @WorkDate + @dbSuffix

---- Set parameter values
-- Data file path where .mdf file will reside
DECLARE @datafilepath NVARCHAR(260)
-- Log file path where .ldf file will reside
DECLARE @logfilepath NVARCHAR(260)

-- Customized path for data file path
-- data file path. make sure directory exists
set @datafilepath = 'C:\TEST\DATA'

-- Customized path for log file path
-- log file path make sure directory exists
set @logfilepath = 'C:\TEST\LOG'

declare @stmt nvarchar(2000)
select @stmt =
('RESTORE DATABASE [' + @databasename + '] FROM DBTEST
WITH REPLACE,
MOVE ''DBTEST'' TO '''@datafilepath'' + ''@databasename'' + '.mdf'',
MOVE ''DBTEST_log'' TO '''@logfilepath'' + ''@databasename'' + '.ldf')
print @stmt
-- EXEC (@t)
GO

No matter what I do, I just cannot seem to get it right ??
Is this possible at all ??

I get the following error with the above statement:
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near '@datafilepath'.

Which leads me to believe that it IS possible but I just can't get it right.... HELP !!!"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-30 : 14:37:37

maybe

MOVE ''DBTEST'' TO ''' + @datafilepath + @databasename + '.mdf'',
MOVE ''DBTEST_log'' TO ''' + @logfilepath + @databasename + '.ldf''')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources