SQL Restore Options

ta5 · 2012-09-12

Hello
We have an automated script that backups the production db and restores it in a staging db on the same server. We use the restore command with the "WITH MOVE" clause.

So far, so good. It would be nice if the logical filename could also be changed, otherwise the logical file name of the staging db would be the same as in the production db, which is a bit confusing.
I know that its possible to use the alter database command after the restore, using "MODIFY FILE" clause. Anyway, I'm looking for an option to have it done in 1 command...

Thx in advance.
Thomas

ta5 · 2012-09-17

Nobody? I guess then, it's a kind of a phantom problem :-k

stryk · 2012-09-17

Shouold work like this (using the NEWNAME clause):

USE [master]
GO
RESTORE DATABASE [test] FROM  DISK = N'E:\Backup\navlive.bak' WITH  FILE = 1,  
MOVE N'NAV_601_1_Data' TO N'E:\Databases\Test_1_Data.ndf',  
MOVE N'NAV_601_Data' TO N'E:\Databases\Test_Data.mdf',  
MOVE N'NAV_601_Log' TO N'E:\Databases\Test_Log.ldf', 
NOUNLOAD,  STATS = 5
GO

USE [test]
GO
ALTER DATABASE [test] MODIFY FILE (NAME=N'NAV_601_Data', NEWNAME=N'NAV_Test_Data')
ALTER DATABASE [test] MODIFY FILE (NAME=N'NAV_601_1_Data', NEWNAME=N'NAV_Test_1_Data')
ALTER DATABASE [test] MODIFY FILE (NAME=N'NAV_601_Log', NEWNAME=N'NAV_Test_Log')
GO

ta5 · 2012-09-18

Hi Jörg
Thanks a lot. And it's not possible to have it as option of the restore command, is it?
Regards
Thomas

stryk · 2012-09-18

ta5 wrote:

Hi Jörg
Thanks a lot. And it's not possible to have it as option of the restore command, is it?
Regards
Thomas

As far as I know it's not possible. But why not just adding those few lines to your script?

ta5 · 2012-09-18

Thanks. Yes I'll add the lines. I was just wondering

SQL Restore Options

Answers

Categories