Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
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

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

1 Answer

up vote 3 down vote accepted

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)

share|improve this answer
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

 
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.