Answered by:
Restore Database Failed

-
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
GOtwo 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
Question
Answers
-
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.
- Marked as answer by Fanny Pied Thursday, July 18, 2013 10:58 PM
- Edited by Rama Udaya Friday, July 19, 2013 2:49 PM updatedDB
All replies
-
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- Edited by Olaf HelperMVP, Moderator Thursday, July 18, 2013 5:56 PM Addition
-
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
-
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
- Proposed as answer by Olaf HelperMVP, Moderator Thursday, July 18, 2013 6:27 PM
-
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.
- Marked as answer by Fanny Pied Thursday, July 18, 2013 10:58 PM
- Edited by Rama Udaya Friday, July 19, 2013 2:49 PM updatedDB