February 18, 2009 at 10:40 am
Hello All,
I have a backup that I testing to restore. I do not want to overwrite the existing database and I don't have enough space on the server (it is a test server) So I am trying to restore the database using the following command
RESTORE filelistonly
FROM DISK='\\web-server6\FreeAgent\Backups\SQLtest\Test_COR_Online\Test_COR_Online_backup_20090213.bak'
RESTORE DATABASE test_restore
FROM DISK='\\web-server6\FreeAgent\Backups\SQLtest\Test_COR_Online\Test_COR_Online_backup_20090213.bak'
WITH
MOVE 'cor_online_data' TO '\\web-server6\FreeAgent\Backups\SQLtest\Data\Test_COR_Online.mdf',
MOVE 'cor_online_log' TO '\\web-server6\FreeAgent\Backups\SQLtest\Logs\Test_COR_Online_1.LDF',
STATS=1,
NORECOVERY
GO
RESTORE DATABASE test_restore
FROM DISK='\\web-server6\FreeAgent\Backups\SQLtest\Test_COR_Online\Test_COR_Online_differential_20090217.dif'
WITH
STATS=1,
RECOVERY
GO
I get the following error
Msg 5110, Level 16, State 2, Line 3
The file "\\web-server6\FreeAgent\Backups\SQLtest\Data\Test_COR_Online.mdf" is on a network path that is not supported for database files.
Msg 3156, Level 16, State 3, Line 3
File 'COR_Online_Data' cannot be restored to '\\web-server6\FreeAgent\Backups\SQLtest\Data\Test_COR_Online.mdf'. Use WITH MOVE to identify a valid location for the file.
I need to copy the database on the network share since I do not have space. I want to make sure my backup is working on my test server before I test it on the production server
Any help is greatly appreciated
Thank You
Shri
February 18, 2009 at 10:47 am
shri_sastry (2/18/2009)
Hello All,I have a backup that I testing to restore. I do not want to overwrite the existing database and I don't have enough space on the server (it is a test server) So I am trying to restore the database using the following command
RESTORE filelistonly
FROM DISK='\\web-server6\FreeAgent\Backups\SQLtest\Test_COR_Online\Test_COR_Online_backup_20090213.bak'
RESTORE DATABASE test_restore
FROM DISK='\\web-server6\FreeAgent\Backups\SQLtest\Test_COR_Online\Test_COR_Online_backup_20090213.bak'
WITH
MOVE 'cor_online_data' TO '\\web-server6\FreeAgent\Backups\SQLtest\Data\Test_COR_Online.mdf',
MOVE 'cor_online_log' TO '\\web-server6\FreeAgent\Backups\SQLtest\Logs\Test_COR_Online_1.LDF',
STATS=1,
NORECOVERY
GO
RESTORE DATABASE test_restore
FROM DISK='\\web-server6\FreeAgent\Backups\SQLtest\Test_COR_Online\Test_COR_Online_differential_20090217.dif'
WITH
STATS=1,
RECOVERY
GO
I get the following error
Msg 5110, Level 16, State 2, Line 3
The file "\\web-server6\FreeAgent\Backups\SQLtest\Data\Test_COR_Online.mdf" is on a network path that is not supported for database files.
Msg 3156, Level 16, State 3, Line 3
File 'COR_Online_Data' cannot be restored to '\\web-server6\FreeAgent\Backups\SQLtest\Data\Test_COR_Online.mdf'. Use WITH MOVE to identify a valid location for the file.
I need to copy the database on the network share since I do not have space. I want to make sure my backup is working on my test server before I test it on the production server
Any help is greatly appreciated
Thank You
Shri
SQL Server doesn't support using network shares for the database files. If you really need to do this, you will need to shutdown SQL Server and restart it with the appropriate trace flag (you'll need to google for it as I don't know it, I just know it exists).
February 18, 2009 at 10:59 am
Trace flag 1807
Use it at your own risk!
* Noel
February 18, 2009 at 11:09 am
noeld (2/18/2009)
Trace flag 1807Use it at your own risk!
First, noel, thank you for finding it.
Second, I second your warning, use at your own risk.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply