June 5, 2009 at 10:08 am
I'm trying to generate a script to restore about 200 databases using dynamic SQL where I pass the database name in, then the script will create the final restore statement after determining the logical file names. However, I cannot get the 2nd variable to be created.
Any help in pointing out what I'm doing wrong will be greatly appreciated!
--Generate a variable from dynamic SQL
USE master;
DECLARE @dbName VARCHAR(30)
SELECT@dbName = 'msdb'
DECLARE@logicalDataName VARCHAR(50),
@logicalLogName VARCHAR(50),
@dir1 VARCHAR(MAX),
@dir2 VARCHAR(MAX),
@SQL1 VARCHAR(MAX),
@SQL2 VARCHAR(MAX),
@SQL3 VARCHAR(MAX)
-- Create Text for directories
SELECT @dir1 = 'r:\rdat1\mssql\data'
SELECT @dir2 = 'r:\rlog1\mssql\log'
-- Create data and log file names
SELECT @SQL1 = 'SELECT name AS @logicalDataName FROM ' +@dbName + '.dbo.sysfiles WHERE groupid = 1'
--PRINT '@SQL1 = ' + @SQL1
EXEC (@SQL1) --generates error on the @logicalDataName
SELECT @SQL2 = 'SELECT name AS @logicalLogName FROM ' +@dbName + '.dbo.sysfiles WHERE groupid = 2'
EXEC (@SQL2) --generates error on the @logicalLogName
--PRINT '@SQL2 = ' + @SQL2
PRINT '@logicalDataName = ' + @logicalDataName --ignores as no value
PRINT '@logicalLogName = ' + @logicalLogName
--Restore DB
PRINT '--Restore database Test Script B'
SELECT @SQL3 = 'RESTORE DATABASE ' +@dbName + ' FROM DISK = ''r:\rdat1\' + @dbName + '.bak''' + '
WITH MOVE ''' +@logicalDataName + ''' TO ''' + @dir1 + '\' + @dbName + '_Data.mdf''' + ',
MOVE ''' +@logicalLogName + ''' TO ''' + @dir2 + '\' + @dbName + '_Log.ldf'''
PRINT '@SQL3 = ' + @SQL3
June 5, 2009 at 10:17 am
Hi Clarie
"EXEC" does not support variables within your sql, you have to work with "sp_executesql". Change your SQL variables from VARCHAR(MAX) to NVARCHAR(MAX) (required for sp_executesql) and try this:
--Generate a variable from dynamic SQL
DECLARE @dbName VARCHAR(30)
SELECT @dbName = 'msdb'
DECLARE @logicalDataName VARCHAR(50),
@logicalLogName VARCHAR(50),
@dir1 VARCHAR(MAX),
@dir2 VARCHAR(MAX),
@SQL1 NVARCHAR(MAX),
@SQL2 NVARCHAR(MAX),
@SQL3 NVARCHAR(MAX)
-- Create Text for directories
SELECT @dir1 = 'r:\rdat1\mssql\data'
SELECT @dir2 = 'r:\rlog1\mssql\log'
-- Create data and log file names
SELECT @SQL1 = 'SELECT @logicalDataName = name FROM ' +@dbName + '.dbo.sysfiles WHERE groupid = 1'
EXECUTE sp_executesql @SQL1, N'@logicalDataName VARCHAR(50) OUTPUT', @logicalDataName = @logicalDataName OUTPUT
PRINT @logicalDataName
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
June 5, 2009 at 11:06 am
Excellent solution! I'll read more on sp_executesql for future reference.
BTW, anyone wanting to use the above script, log files are groupid = 0, not 2.
Many thanks,
Clarie DeWayne
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply