December 19, 2007 at 12:40 pm
When I restore a database using T-SQL (RESTORE DATABASE) to a different database, the restore works but the database stays in a status of 'Restoring'. This is done without being logged onto the server. The same thing happens if I use SSIS, the 'Task/Restore' database option or 'Task/Export' on the source db. However, if I am logged on to the server any of these work just fine.
Questions: Does anyone know why this would happen? If the restore completed successfully, is the database actually okay, having just a bad status? Is the a way to set the status on the database so it is useable?
Thanks for the help
Tim
December 19, 2007 at 2:02 pm
You have to use "With Recovery" to bring the db back online. Make sure you are not using "With No Recovery"
December 19, 2007 at 2:04 pm
Yup, I am using 'With Recovery'.
December 19, 2007 at 2:24 pm
The database should be fine. If you execute
RESTORE DATABASE [DATABASE_NAME] WITH RECOVERY
It will make the database come back online. As to why it is doing this to you, I am not exactly sure. Let me poke around for a bit for an answer.
December 19, 2007 at 2:30 pm
Can you post the script you are using to perform the restore?
December 19, 2007 at 2:45 pm
Sure:
restore database ds2
from disk = 'ds1.bak'
with recovery,
move 'ds1_data' to 'c:\ds2_data.mdf',
move 'ds1_log' to 'c:\ds2_log.ldf'
December 19, 2007 at 3:09 pm
Tim,
from disk = 'ds1.bak'
You need to specifiy the location of the .bak file.
e.g from disk = 'c:\ds1.bak'
Let me know if this works.
December 19, 2007 at 3:13 pm
Sorry, I forgot that. I do have the path for the backup file fully defined.
December 19, 2007 at 4:08 pm
I have used your script to successfully restore a database, with recovery. I am not sure why it is not working for you.
I would perform the backup in SSMS and click the generate script button. Try to use the script SSMS creates.
December 20, 2007 at 4:53 am
Thanks.
Tim
December 20, 2007 at 5:31 am
How big is this database? Could recovery just be taking a while?
December 20, 2007 at 5:34 am
No, the recovery finishes. It's just that afterward the blasted thing stays in a 'Restoring' state.
December 21, 2007 at 7:52 am
Just want to make sure you are refreshing your EM or MS screen. Right click on the database after the restore says it finished and select refresh. If this was done this is rather a curious problem. Your command seems right. You aren't seeing any messages in the SQL logs are you?
Francis
December 21, 2007 at 8:47 am
Yup, did a 'refresh', shut down SSMS and re-started, no joy. I am running this over a VPN and one of network techs said this could cause an issue; anyone have an idea?
Tim
December 21, 2007 at 12:37 pm
I VPN all the time without issues. However, I VPN then RDC and run the processes on the server. Running things over a network (especially backup-related things) have caused many a headache for me.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply