6

I want to create a database snapshot and restore database to it every time a unit test is run. I am able to create snapshot but while restoring it, I encounter the error below while doing it.

Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'ImportData'

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The SQL queries for creating and reverting database to snapshot are listed below.

Create snapshot:

CREATE DATABASE Data_SShot
ON (NAME=Data,
    FILENAME='C:\Snapshot\DataSnapshot.ss'),
   (NAME=Data_Data1,
    FILENAME='C:\Snapshot\Data1Snapshot.ss'),
   (NAME=Data_Index1,
    FILENAME='C:\Snapshot\DataIndexSnapshot.ss')
AS SNAPSHOT OF Data

Restore to snapshot

use master
go

RESTORE DATABASE Data 
FROM DATABASE_SNAPSHOT = 'Data_SShot'

It states that

Database state cannot be changed while other users are using the database

How to overcome this problem? I am using .NET (C#) to do this. How to close connection to run RESTORE DATABASE successfully?

I want this entire process of creating snapshot and reverting DB to it later to happen for each test in test suite.

Madhur Maurya
  • 1,016
  • 4
  • 19
  • 42
  • 2
    [This SO post](http://stackoverflow.com/questions/7197574/script-to-kill-all-connections-to-a-database-more-than-restricted-user-rollback) going to help, I think. – DHN Apr 22 '16 at 09:23
  • you need to take the database offline first. – user1666620 Apr 22 '16 at 09:48

2 Answers2

7

There are few things which need to be done before restoring the DB.

  1. Get the number of active users for the DB and kill the sessions.
  2. Take the DB offline and online which will terminate all hidden connections.
  3. Now restore the DB. If it fails, then take the DB offline and try to restore.
  4. Once restore is done, bring the DB online.

These are the steps which I have been using for DB restore and it's worked for me for a long time.

SharpC
  • 6,974
  • 4
  • 45
  • 40
Sasidharan
  • 3,676
  • 3
  • 19
  • 37
1

Do the following:

ALTER DATABASE [Data] SET SINGLE_USER

RESTORE DATABASE Data 
FROM DATABASE_SNAPSHOT = 'Data_SShot'

ALTER DATABASE [Data] SET MULTI_USER WITH NO_WAIT
PepiX
  • 479
  • 5
  • 4