using variables in restore command

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 2012 Forums

Transact-SQL (2012)

using variables in restore command

Author

Topic

ppatel112
Starting Member

35 Posts

Posted - 2014-11-28 : 00:57:27

Hi Guys,

I have a script below:

DECLARE @databasename VARCHAR(50) -- database name
DECLARE @SUPPdatabasename VARCHAR(50) -- Support database name
DECLARE @VTdatabasename VARCHAR(50) -- Vendor Testing database name
DECLARE @disk VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @mdffile VARCHAR(256) -- mdffilename for backup
DECLARE @ldffile VARCHAR(256) -- ldffilename for backup

SET @disk = 'C:\temp\'
SET @databasename = 'DOMINOSQL'
SET @SUPPdatabasename = 'DOMINOSQL_SUPP'
SET @VTdatabasename = 'DOMINOSQL_VT'
SET @fileName = @disk + @databasename + '.BAK'
SET @mdffile = 'DOMINOSQL'
SET @ldffile = 'DOMINOSQL_LOG'

RESTORE DATABASE @databasename
FROM DISK = @fileName
WITH REPLACE,
STATS = 10,
MOVE @mdffile TO @disk + @mdffile + '.mdf'
MOVE @ldffile TO @disk + @ldffile + '.ldf'

RESTORE DATABASE @SUPPdatabasename
FROM DISK = @fileName
WITH REPLACE,
STATS = 10,
MOVE @mdffile TO @disk + @mdffile + '_SUPP' +'.mdf'
MOVE @ldffile TO @disk + @ldffile + '_SUPP' +'.ldf'

RESTORE DATABASE @VTdatabasename
FROM DISK = @fileName
WITH REPLACE,
STATS = 10,
MOVE @mdffile TO @disk + @mdffile + '_VT' +'.mdf'
MOVE @ldffile TO @disk + @ldffile + '_VT' +'.ldf'
GO

below thing is not working? if i create a variable it will work but i dont want to create 10 new variables for a small stuff and make the script longer and confusing to read.

MOVE @mdffile TO @disk + @mdffile + '.mdf'
MOVE @ldffile TO @disk + @ldffile + '.ldf'

MOVE @mdffile TO @disk + @mdffile + '_SUPP' +'.mdf'
MOVE @ldffile TO @disk + @ldffile + '_SUPP' +'.ldf'

MOVE @mdffile TO @disk + @mdffile + '_VT' +'.mdf'
MOVE @ldffile TO @disk + @ldffile + '_VT' +'.ldf'

is there a way around it to make above working?

thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-28 : 09:05:55

You could build up the restore command as dynamic sql then execute the result with sp_executesql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources