Feeds:
Posts
Comments

Posts Tagged ‘Line 1 Exclusive access could not be obtained because the database is in use. Msg 3013’

For this scenerio I have considered Foodmart database as an
example  

 

1.Assuming database is backed up earlier either through command
line or SSMS

BACKUP
DATABASE Foodmart TO
DISK=‘C:\Foodmart.bak’

 

2.While trying to restore using below

RESTORE
DATABASE Foodmart FROM
DISK=‘C:\Foodmart.bak’

 

If you are getting below error

 

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.

 

3. To get rid of above error

Set the database in SINGLE_USER mode and forcibly terminate
other connections

ALTER
DATABASE Foodmart

SET
SINGLE_USER With
ROLLBACK IMMEDIATE

 

4.Then try to restore again

RESTORE
DATABASE Foodmart FROM
DISK=‘C:\Foodmart.bak’

 

If restore is Successful then you are done!

 

But if you are getting below error  go to Step 5  else Step 6

 

Msg 3159, Level 16, State 1, Line 1

The tail of the log for the database "Foodmart" has not been backed up.
Use BACKUP LOG
WITH NORECOVERY
to backup the log if it contains work you do not
want to lose.
Use the WITH REPLACE
or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE
is terminating abnormally.

 

 

5.Then take log backup using below command

BACKUP
LOG Foodmart TO
DISK=‘C:\Foodmart.trn’  WITH
NORECOVERY

 

Try to restore again

RESTORE
DATABASE Foodmart FROM
DISK=‘C:\Foodmart.bak’

 

Processed
240 pages for database
‘Foodmart’, file ‘Foodmart’ on file 1.

Processed
2 pages for database
‘Foodmart’, file ‘Foodmart_log’ on file 1.

RESTORE
DATABASE successfully processed 242 pages in 0.290 seconds (6.811
MB/sec).

 

Restore Successful

 

6.
Finally, Set the database back
to MULTI_USER mode

USE [master]

ALTER
DATABASE Foodmart

SET MULTI_USER;

Advertisements

Read Full Post »

%d bloggers like this: