patternsqlMinor
Database restoring taking too long
Viewed 0 times
longtoorestoringdatabasetaking
Problem
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.
This is how it looks like. When I try to access it gives this window. username/passwords were different on each machine.
From googling I found from microsoft msdn: sp_change_users_login (Transact-SQL)
What I tried is:
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.
myDatabase(Restoring...)This is how it looks like. When I try to access it gives this window. 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';
GOIn 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.
Solution
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
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)
This is done in SQL using
WITH RECOVERYIn 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)
Context
StackExchange Database Administrators Q#12998, answer score: 4
Revisions (0)
No revisions yet.