May 28, 2014 at 10:31 pm
I can't understand why this won't work. I do this all the time.
I created a dummy database (TomsTest) and then I try to restore my other database "DEV" to it.
Normally, you have to use the Move for both the mdf and ldf files to tell it to restore to another database.
But the message I am getting is:
The file 'D:\SQL\Data\TomsTest.mdf' cannot be overwritten. It is being used by database 'TomsTest'.
USE [master]
RESTORE DATABASE [DEV] FROM DISK = N'D:\SQL\Backup\DEV_backup_2014_05_28.bak' WITH FILE = 1,
MOVE N'Dev' TO N'D:\SQL\Data\TomsTest.mdf',
MOVE N'DEV_log' TO N'D:\SQL\Data\TomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Of course it is in use, that's always the case. Even if I put it in single use mode, it still doesn't work.
I tried it on another machine and had the same problem. Not sure why. If I call it the same name, it works fine but I don't want to overwrite the original database, I just need to get some data from the backup.
What would cause this not to work?
Thanks,
Tom
May 28, 2014 at 10:38 pm
tshad (5/28/2014)
I can't understand why this won't work. I do this all the time.I created a dummy database (TomsTest) and then I try to restore my other database "DEV" to it.
Normally, you have to use the Move for both the mdf and ldf files to tell it to restore to another database.
But the message I am getting is:
The file 'D:\SQL\Data\TomsTest.mdf' cannot be overwritten. It is being used by database 'TomsTest'.
USE [master]
RESTORE DATABASE [DEV] FROM DISK = N'D:\SQL\Backup\DEV_backup_2014_05_28.bak' WITH FILE = 1,
MOVE N'Dev' TO N'D:\SQL\Data\TomsTest.mdf',
MOVE N'DEV_log' TO N'D:\SQL\Data\TomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Of course it is in use, that's always the case. Even if I put it in single use mode, it still doesn't work.
I tried it on another machine and had the same problem. Not sure why. If I call it the same name, it works fine but I don't want to overwrite the original database, I just need to get some data from the backup.
What would cause this not to work?
Thanks,
Tom
What is happening is that you have TomsTest and the restore command you have created is to restore the backup as DEV instead of TomsTest. This is telling SQL Server that you are trying to force TomsTest and Dev to use the same files which is not allowed.
You need to restore the database as TomsTest instead of Dev so the following needs to be done instead.
USE [master]
RESTORE DATABASE [TomsTest] FROM DISK = N'D:\SQL\Backup\DEV_backup_2014_05_28.bak' WITH FILE = 1,
MOVE N'Dev' TO N'D:\SQL\Data\TomsTest.mdf',
MOVE N'DEV_log' TO N'D:\SQL\Data\TomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 28, 2014 at 11:31 pm
That was it.
Thanks
May 29, 2014 at 2:26 am
Aside, you don't need to create a dummy database to restore over. Just restore the backup and specify the name you want the DB to have in the restore statement.
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
May 29, 2014 at 7:04 am
tshad (5/28/2014)
That was it.Thanks
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply