July 5, 2012 at 12:48 pm
Hi,
I am trying to write a script that will generate restore commands for several DBs at once with move options.
I am using a If statement inside the cursor for adding the with move option for .ndf files since i have some databases with .ndf files and some don't. But My output is generated something like this:
RESTORE DATABASE LearningPlatform_marketing_lists
FROM DISK='N:\BIEDWUATACQ4\Backup\MSSQL\Bak\TK5EDWUATACQ04\LearningPlatform_marketing_lists.bak'
with
move 'master' to 'N:\BIEDWUATACQ4\Data\MSSQL\Data\master.mdf' ,
move 'mastlog' to 'N:\BIEDWUATACQ4\Data\MSSQL\Log\mastlog.ldf',
keep_cdc
My cursor remains inside of the MAster Database and does not take the other database names as it should being inside the cursor loop.
Please help me figure out what is wrong in my code here.
Below is my code:
DECLARE @Database VARCHAR(255)
DECLARE @Restore nvarchar(1024)
DECLARE @dest_file_path_mdf nvarchar(1024)
DECLARE @dest_file_path_ldf nvarchar(1024)
DECLARE @dest_file_path_ndf nvarchar(1024)
DECLARE @physical_name_mdf nvarchar(1024)
DECLARE @physical_name_ldf nvarchar(1024)
DECLARE @physical_name_ndf nvarchar(1024)
SET @dest_file_path_mdf = 'N:\BIEDWUATACQ4\Data\MSSQL\Data\'
SET @dest_file_path_ldf = 'N:\BIEDWUATACQ4\Data\MSSQL\Log\'
SET @dest_file_path_ndf = 'N:\BIEDWUATACQ4\Data\MSSQL\Data\'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
--ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @Database
set @physical_name_mdf = (select name from sys.database_files where physical_name like '%.mdf')
set @physical_name_ldf = (select name from sys.database_files where physical_name like '%.ldf')
set @physical_name_ndf = (select name from sys.database_files where physical_name like '%.ndf')
If (select count(*) from sys.database_files) = 2
BEGIN
set @restore =
'RESTORE DATABASE ' + @database +
' FROM DISK=''N:\BIEDWUATACQ4\Backup\MSSQL\Bak\TK5EDWUATACQ04\'+ @database + '.bak'' with move '
+ ''''+@physical_name_mdf + ''''+' to '+ ''''+ @dest_file_path_mdf + @physical_name_mdf +'.mdf'+
'''' +' ,move ' + '''' + @physical_name_ldf + ''''+ ' to '+ ''''+ @dest_file_path_ldf
+ @physical_name_ldf +'.ldf'+ '''' + ',keep_cdc'
print @restore
End
IF (select count(*) from sys.database_files) = 3
BEGIN
set @restore =
'RESTORE DATABASE ' + @database +
' FROM DISK=''N:\BIEDWUATACQ4\Backup\MSSQL\Bak\TK5EDWUATACQ04\'+ @database + '.bak'' with move '
+ ''''+@physical_name_mdf + ''''+' to '+ ''''+ @dest_file_path_mdf + @physical_name_mdf +'.mdf'+
'''' +' ,move ' + '''' + @physical_name_ldf + ''''+ ' to '+ ''''+ @dest_file_path_ldf
+ @physical_name_ldf +'.ldf'+ ''''
+' ,move ' + '''' + @physical_name_ndf + ''''+
' to '+ ''''+ @dest_file_path_ndf + @physical_name_ndf + '.ndf' +' ,keep_cdc'
print @restore
End
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
July 5, 2012 at 12:51 pm
There's nothing in your code that could change a database context (a USE statement) or point the catalog views at other databases (three-part naming)
But then, you don't ever need to change databases. Just use the sys.master_files catalog view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2012 at 1:06 pm
Hi GilaMonster,
Thanks so much for replying. I tried to add the USE <Database> statement inside the loop, like this:
USE @database
But it doesn't seem to like that. I am very new at coding, can you please show how to add this in my original code?
Much much thanks for looking into this.
July 5, 2012 at 1:51 pm
GilaMonster (7/5/2012)
But then, you don't ever need to change databases. Just use the sys.master_files catalog view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply