Sql ошибка 3101

Generally, you come across ‘SQL error 3101’ when you try to access the database when there is already a restore process going on.

Here at Bobcares, we have seen several such SQL related issues as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the cause for this error and see how to fix it.

What causes SQL error 3101 to occur

This error is thrown by SQL when a database is interrupted when it is being restored.

Here are the different reasons for the same.

• A query is being executed when there is an ongoing database restoration process.
• Any other application is using the database.
• A user is using the database that we are already trying to restore.
• Setting the database that we are trying to restore is been set as a default database for login.

For instance, the error appears as below.

How we fix SQL error 3101

Now, let’s see how our Support Engineers fix this error to our customers.

1. The database system should have an Exclusive Lock when we are restoring the database. Also, we should kill the processes that are using the database. The management studio is good enough to support all the features, you can try all these commands (Restore/Backup/Kill/Single_user Mode), etc from the Query Analyser.

2. Another way to get rid of this error would be to drop the database. For that, run the below commands

use Master ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

3. At last, try another restore if the error persists. You can rename the *.mdf and *.ldf files corresponding to the backup job and do another restore. Finally, this should run fine.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, this SQL error occurs when you try to access the database when there is already a restore process going on. Today, we saw how our Support Engineers fix this SQL error.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

• Remove From My Forums

• Question

• Hi, I can’t restore a db which is in restoring mode and  it before was altered to SINGLE_USER.

  I’m using the next command:
  «RESTORE LOG [db] FROM  DISK = N’J:db1.trn’   FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10″

  It retuns «Msg 3101, Level 16, State 1…»  and tells us that db is in use and it is with  exclusive access…

  db is not referenced when I run «sp_Who2» and I cannot use command «db ALTER DATABASE SET SINGLE_USER/MULTI_USER»  because
  db is in restore mode…

  Please, any ideas?
  Thanks 

• Remove From My Forums

• Question

• hi

  i try to backup and restore database using sql-server 2008 and got error.

  to backup i done this: `BACKUP DATABASE MyDB TO DISK=’d:MyDB.BAK’` (and its work fine)

  to restore i done this: `USE MASTER RESTORE DATABASE MyDB FROM DISK=’d:MyDB.BAK`

  and got this error:

      Msg 3159, Level 16, State 1, Line 7

      The tail of the log for the database «MyDB » 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 7

      RESTORE DATABASE is terminating abnormally.

  Where I’m wrong  ? what is missing ?

  thank’s in advance

  • Edited by

    Sunday, March 2, 2014 7:30 AM
    Spelling