September 28, 2016 at 3:41 am
Hi Guys,
I'm stuck on a little problem... I have a script for restoring a database from a backup file and it works great. I have 3 databases that I'd like to potentially restore from to the same database. Instead of me changing the name of the database in 5 different places in the script I'd like to declare a variable that holds the database name and uses that instead.
This is what I currently have as my script which works fine.
USE master;
GO
ALTER DATABASE CIFT04
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE CIFT04 FROM DISK = 'D:\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\2016-09-27_19-00_CIFT01.bak'
WITH REPLACE,
MOVE 'CIFT01' TO 'D:\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Data\CIFT04.mdf',
MOVE 'CIFT01_log' TO 'D:\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Data\CIFT04_Log.ldf'
GO
ALTER DATABASE CIFT04 MODIFY FILE (NAME=N'CIFT01', NEWNAME=N'CIFT04')
GO
ALTER DATABASE CIFT04 MODIFY FILE (NAME=N'CIFT01_log', NEWNAME=N'CIFT04_log')
GO
EXECUTE CIFT04.dbo.XXX_UpdateContractDetail
GO
ALTER DATABASE CIFT04
SET MULTI_USER;
GO
I'd like to replace every occurrence of 'CIFT01' in the script with a different name such as 'CIFT02' or 'CIFT03'. I was thinking I could declare a variable at the top of the script and simply insert that where I have those occurrences but it doesn't seem to like that.
Any help or pointers would be gratefully received.
Thanks!
September 28, 2016 at 4:12 am
For five databases, I think I'd just write out the RESTORE statement five times and run the whole lot. The database names aren't going to change each time you do this, are they? You could do it with a table of database names and a cursor, but it sounds like a lot of effort to me.
Is there any reason why you're changing the logical names of the files? The physical file names have to be unique across the server, but the logical file names have to be unique only across the database. That means you can keep the logical names from the parent database and dispense with those ALTER DATABASE statements.
John
September 28, 2016 at 4:24 am
Hi John, I didn't realise that I could have just leave the logical name as is, I thought by doing that it would be an identical restore in every respect by doing that.
On the matter of of writing the script 5 times, the backup names are changing all the time with regard to the date and time. And I only ever want one database restored ie. CIFT04 as this is my test database, which is why I'm restoring each time from anyone of the 3 live databases.
Hope that explains it better.
September 28, 2016 at 4:42 am
I've never tried using variables in a RESTORE statement before, but something like this should work:USE master;
DECLARE
@DestDB sysname = 'CIFT01'
,@BakFile varchar(260) = 'D:\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\2016-09-27_19-00_CIFT01.bak'
,@LogicalDataFile sysname
,@LogicalLogFile sysname;
SELECT
@LogicalDataFile = @DestDB
,@LogicalLogFile = @DestDB + '_log';
ALTER DATABASE CIFT04 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE CIFT04 FROM DISK = @BakFile
WITH REPLACE,
MOVE @LogicalDataFile TO 'D:\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Data\CIFT04.mdf',
MOVE @LogicalLogFile TO 'D:\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Data\CIFT04_Log.ldf';
EXECUTE CIFT04.dbo.XXX_UpdateContractDetail;
ALTER DATABASE CIFT04 SET MULTI_USER; -- you only need this line if the source database wasn't multi-user
John
September 28, 2016 at 4:57 am
John,
I've just run some tests with your revised script and it works exactly as I was hoping.
Thank you so much for doing that for me, greatly appreciated!
Regards.
Joe
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply