SQL Server Database stuck in 'Restoring' state

Question

Hi,

I posted a question about an hour ago about how to create a duplicate copy of a database. It appears now that my initial attempt at doing this has had an adverse side effect.

What I did is the following:

(1) I used the SSMS interface (as opposed to SQL script) to back up my database named 'ProductionDatabase' to a .bak file

(2) I created a new database named 'TestDatabase'

(3) Using the SSMS interface, I attempted to restore 'TestDatabase' from the .bak file. When I tried this, I got the following error message:

"Restore of database 'TestDatabase' failed. However, the Tail-log backup operation completed successfully."

Not only did my backup not get restored / copied to TestDatabase, but now the status of 'ProductionDatabase' shows as "Restoring". In this state, the front end client application we use is not able to connect to it.

If anyone can suggest how I might be able to solve this issue, I would greatly appreciate it.

I'd still like to know the proper way to duplicate a database (as I asked in my previous post), but this issue is now a higher priority for me.

Thanks in advance,
Paul

Answer 1

I'm somewhat hesitant whether to answer here, since it is not clear what you did. But it seems like you did not perform the operations you think you performed. It sounds like rather than restoring the backup to TestDatabase, you overwrote the existing production database. I hope that was a fresh backup!

The reason that the database is now in the restoring state is presumably because you ran RESTORE with the NORECOVERY option. You need this option when you want to apply log backups to the full backup.

And since there is talk of a tail-of-the-log backup, I think that this is what you want. You need to locate that file and apply it with this command:

RESTORE LOG ProductionDatabase FROM DISK = 'C:\temp\logbackup.bak' WITH RECOVERY

SQL Server will give you an error message if the log file does not fit with the current state of the database.

Generally, if you want a test version of a production database, you would never restore that copy on the production server for multiple reasons. One is that your playing around in the test database should steal load from the production workload. But also very important is that this invites to accidents, as happened here. It is also bigger risk that you think that you are running something in the test database when you in fact are in production.

Answer 2

Hi Erland,

Thank you for your response. I agree with your suspicion. The way I initiated the operation in which I hoped to restore the copy of ProductionDatabase into a new database named TestDatabase most likely resulted in a restore being initiated over ProductionDatabase. Luckily, I had made a Full backup and I was certain that there was no activity while I was doing this, so I was able to successfully restore ProductionDatabase from my backup.

Your points about the danger of doing this on our production server are well-taken, as my mistake could have caused us some problems. I am happy it didn't (other than costing me a little time). I've learned my lesson.

Thank you for your help.

Best regards,
Paul