monicai
asked on
Restore Database With MOVE
Hi guys!
I'm stumped with this one and I am hoping you could help me.
I am trying to restore a database into another server. I ran a RESTORE FILELISTONLY and this is what I got.
RESTORE FILELISTONLY FROM DISK = '\\SomeSharedDrive\Backup$ \SomeFolde r\DummyDB_ LIVE.bak'
DB_data D:\SQLServer\MSSQL\Data\Du mmyDB_LIVE _data.MDF D PRIMARY 292749312 35184372080640
DB_log E:\SQLServer\MSSQL\Data\Du mmyDB_LIVE _Log.LDF L NULL 104595456 35184372080640
I ran the following command:
Restore database DummyDB_LIVE
from disk = '\\SomeSharedDrive\Backup$ \SomeFolde r\DummyDB_ LIVE.bak'
with move 'DB_data' to 'F:\SQLServer\MSSQL\data\D ummyDB_LIV E_data.mdf ',
move 'DB_log' to 'G:\SQLServer\MSSQL\data\D ummyDB_LIV E_Log.ldf' ,
replace
I got this error:
Msg 3156, Level 16, State 1, Line 3
File 'DB_log' cannot be restored to 'G:\SQLServer\MSSQL\data\D ummyDB_LIV E_Log.ldf' . Use WITH MOVE to identify a valid location for the file.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
I can confirm that the paths are correct. The user account used by SQL Server service is permissioned with read/write access to its respective folders.
SQL server version is SQL Server 2000.
What else am I missing?
I'm stumped with this one and I am hoping you could help me.
I am trying to restore a database into another server. I ran a RESTORE FILELISTONLY and this is what I got.
RESTORE FILELISTONLY FROM DISK = '\\SomeSharedDrive\Backup$
DB_data D:\SQLServer\MSSQL\Data\Du
DB_log E:\SQLServer\MSSQL\Data\Du
I ran the following command:
Restore database DummyDB_LIVE
from disk = '\\SomeSharedDrive\Backup$
with move 'DB_data' to 'F:\SQLServer\MSSQL\data\D
move 'DB_log' to 'G:\SQLServer\MSSQL\data\D
replace
I got this error:
Msg 3156, Level 16, State 1, Line 3
File 'DB_log' cannot be restored to 'G:\SQLServer\MSSQL\data\D
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
I can confirm that the paths are correct. The user account used by SQL Server service is permissioned with read/write access to its respective folders.
SQL server version is SQL Server 2000.
What else am I missing?
ASKER
Btw, this restore is basically overwriting an existing DummyDB_LIVE database (on a different server) which has exactly the same mdf and ldf paths specified in the restore statement.
I did try doing the restore on a new database with the same directory path but slightly different filenames and it works.
For some reason the RESTORE fails to overwrite the LDF file.
I did try doing the restore on a new database with the same directory path but slightly different filenames and it works.
For some reason the RESTORE fails to overwrite the LDF file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From what you are saying, a different SQL instance is using those files in the location you are trying to restore to.
This will not work. SQL Server will not let you have the files. They are in use. You will have to restore to a different path or a different file name.
What exactly are you trying to do? Are you trying to restore over the database on the other instance?
-G
This will not work. SQL Server will not let you have the files. They are in use. You will have to restore to a different path or a different file name.
What exactly are you trying to do? Are you trying to restore over the database on the other instance?
-G
ASKER
I have resolved this issue by detaching and attaching the database then re-run the same restore query.
ASKER
Btw thanks to all who has answered this question. Points has been awarded.
-G