Restore Database Failed

Question

I think this post belongs to the SQL Database Engine Forum since we are focusing on what the Restore Command is doing.   Here is the situation I am in.

I have a ssis package which restores database from a backup file.  the command is:

 USE [master]
    RESTORE DATABASE [HMX_TEST]
    FROM DISK = N'L:\dwbi\STAGE\unzipped\Main_agk.BAK'
    WITH FILE = 1
        ,MOVE N'Main_Data' TO N'J:\dwbi\data\HMX_TEST.mdf'
        ,MOVE N'Main_Log'  TO N'K:\dwbi\logs\HMX_TEST.ldf'
        ,NOUNLOAD
        ,REPLACE
        ,STATS = 10
    GO

two days ago the job was run successfully.  but for the past two days, we are getting error messages below, and the jobs failed:

Message
Executed as user: JHA\sqlsrvc. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:10:15 AM  Error: 2013-07-18 10:10:36.14     Code: 0x00000000     Source: Restore HMX_TEST      Description: Exclusive access could not be obtained because the database is in use.  End Error  Error: 2013-07-18 10:10:36.14     Code: 0xC002F210     Source: Restore HMX_TEST Execute SQL Task     Description: Executing the query "    USE [master]       RESTORE DATABASE [HMX_TEST]  ..." failed with the following error: "RESTORE DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  10:10:15 AM  Finished: 10:10:36 AM  Elapsed:  20.672 seconds.  The package execution failed.  The step failed.

Nothing was changed.  Can someone help me identify what the issue might be?  thanks !

---

Thank You Warmest Fanny Pied

Answer 1

Description: Exclusive access could not be obtained because the database is in use. 

Hello Fanny,

Seems you try to restore the backup to an existing database, which is still in use. Either restore it as a new database or drop (kill) all connection, which are establish to that database; take care that also you close your connection. For a restore you need exclusive access to the DB.

And add a GO after "user master" in your script to ensure you are connected to master database.

---

Olaf Helper

Blog Xing

Answer 2

Hi,

The error message is quite clear "the database is in use", so someone or something is connected to this

database while you're trying to restore it.

I recommend you to set the database offline before launching your restore

ALTER DATABASE HMX_TEST SET OFFLINE WITH ROLLBACK IMMEDIATE

(ROLLBACK IMMEDIATE will kill all connections prior to setting the database offline)

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 3

You can use below query,make sure all query windows are closed.

alter database [HMX_TEST] set single_user with rollback immediate
go
---after this close all existing query window and refrsh ur server.Then run below

USE [master] 
    RESTORE DATABASE [HMX_TEST]
    FROM DISK = N'L:\dwbi\STAGE\unzipped\Main_agk.BAK' 
    WITH FILE = 1
        ,MOVE N'Main_Data' TO N'J:\dwbi\data\HMX_TEST.mdf'
        ,MOVE N'Main_Log'  TO N'K:\dwbi\logs\HMX_TEST.ldf'
        ,NOUNLOAD
        ,REPLACE
        ,STATS = 10 
    GO

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 4

The database it is in use hence it is unable to perform ther operations-

USE [master]
GO
ALTER DATABASE [HMX_TEST] SET  RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [HMX_TEST]
    FROM DISK = N'L:\dwbi\STAGE\unzipped\Main_agk.BAK' 
    WITH FILE = 1
        ,MOVE N'Main_Data' TO N'J:\dwbi\data\HMX_TEST.mdf'
        ,MOVE N'Main_Log'  TO N'K:\dwbi\logs\HMX_TEST.ldf'
        ,NOUNLOAD
        ,REPLACE
        ,STATS = 10 
    GO
ALTER DATABASE [HMX_TEST] SET  MULTI_USER WITH ROLLBACK IMMEDIATE
GO

but ensure No open transaction performs during this time other wise all the transactions will get rollbacks.

Hope you aware that you are using replace option (as it will overwrite)

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.