Have your SQL Database Restoration get failed with a message saying the 'database is in use'. This error occurs when any other users are connected to the database then the restore get failed & shows following error message:
Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
To resolve this issue, you need to drop all other connections or the database that user are using should to be changed so that they will not use that database that you are going to restore. You can use sp_who2 or SSMS to see what connections are using the database.
To resolve this issue:
1. Put the database in single-user mode
In SQL Server 2005 you do this by right clicking on the database - > properties and changing the relative attribute from multiuser to single user. After performing restore operation set again it to multiuser.
Or you can also use this TSQL command:
use master
alter database xyz set single_user with rollback immediate
restore database xyz ...
alter database xyz set multi_user
2. Set your database offline:
If it is Still giving same error message after keeping database in single_user mode then you should just set it offline before running the restore option. This is the easiest way to kill all connections to the SQL Server database:
use master
alter database MyDatabase set offline with rollback immediate
After that you can successfully restore your SQL Server Data base & all authenticated SQL Server database users can connect to the database again.
Courtesy : http://bnjho.blogspot.in
0 comments:
Post a Comment