3

I have backuped sql database from my server and tried restore on my machine. Restoring window disappeared by saying successfully restored. In the database list my database is not accessible.

myDatabase(Restoring...)  

This is how it looks like. When I try to access it gives this window.enter image description here username/passwords were different on each machine. From googling I found from microsoft msdn: sp_change_users_login (Transact-SQL) What I tried is:

USE myDatabase;
GO
EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, '12';
GO

In the messages panel:

Msg 927, Level 14, State 2, Line 2
Database 'myDatabase' cannot be opened. It is in the middle of a restore.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0.

How can I fix it? UPDATE I restored the database on another machine it worked properly.

migrated from stackoverflow.com Feb 14 '12 at 12:05

This question came from our site for professional and enthusiast programmers.

  • How was the backup taken? What commands are used for the restore? Could it be it is waiting for another backup on top (deltas)? You can tell when restoring that you rae "not finished, more will come". – TomTom Feb 14 '12 at 7:27
  • It was backuped by clicking right click on database-->tasks-->backup – Jamshid Asatillayev Feb 14 '12 at 7:41
4

When you restore (either via SQL or GUI), you have to be sure that you don't leave the database expecting more restores (of differentials or logs).

This is done in SQL using WITH RECOVERY

In the GUI, it is the "Recovery state" panel (point 12):

Leave the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)

  • thanks, "Recovery state" panel link helped me, i deleted my db and attached another and again tried with another options that explained above link, and it started working... – Jamshid Asatillayev Feb 14 '12 at 10:19
  • 1
    GBN has the right of it. I just thought I'd add try as hard as possible not to use the GUI for actual execution. If nothing else, hit the "script action" button that's near the top of nearly every task window in SSMS. This will not only give you better understanding of how things work, but you can be sure that it's doing what you think it's doing. Then, after a while, you won't need to use the GUI and that makes you a better admin, imo. – Ben Thul Feb 14 '12 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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