Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

I ran a backup of my SQL Server database using the backup utility in the Server GUI. The backup works fine and I can restore on that machine without issue. I cannot, however, restore the backup on another machine. It starts to load and everything looks ok, but the 'restore' process just sits idle forever (I say forever as I let it run for 6 hours one day with no sign of completing. This is only a 1GB database so the restore time should be quick.)

This was on a backup database that had the exact same version of SQL Server and even the same schema. Is there something I am missing? Is there a better way to do a recovery on another server?

share|improve this question
or dba.stackexchange – Filip De Vos Jan 30 '12 at 23:51
Does that "other machine" also have SQL Server 2005 installed? – marc_s Jan 31 '12 at 6:20

migrated from stackoverflow.com Feb 1 '12 at 3:36

1 Answer

up vote 0 down vote

Do both SQL servers have the same service pack? If you do the restore, did you check the "overwrite" option on the database restore?

It should work. The only type of backup that I believe might (in some cases) normally fail on the GUI is a "copy only backup", which it doesn't seem like you made.

share|improve this answer
The servers have the same service pack as they're VM's built from the same image. I do not see a "copy only backup" option as I make the backup. I did try the 'overwrite' option this time but I get the following error with/without that at this point: "Restore Failed for Server 'MyServer' Additional Information: System.Data.SqlClient.SqlError: RESTORE cannot process database 'MyDB' because it is in use by this session. – Wild Bill Feb 8 '12 at 23:49
Then, you should verify that you can restore the backup into a brand new database with a different name. This is easy to do with Management Studio. If you cannot do that, then you wont be able to restore onto an existing database name either. – djangofan Feb 13 '12 at 20:18
The database name is the exact same. The server has a different host name obviously but the name of the database is identical to the primary one. – Wild Bill Feb 13 '12 at 22:27
If everything else fails, then install SQL 2008 R2 and use the SQL Server Studio Managment tool to export the data using either Export or a SSIS transaction, bypassing the backup-restore method. Unfortunately, SQL 2005 Express doesn't have the export feature. In my experience with 2005, backup/restore has worked flawlessly, and so I am not sure what else to suggest. – djangofan Feb 14 '12 at 18:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.