Solved

SQL 2008, scheduled database restore fails

Posted on 2014-03-12
1
1,183 Views
Last Modified: 2014-03-13
I have a database which is backed up and restored to another machine over night.
The problem is, whenever SQL management studio is left open, it fails. I know I have to close it when I 'm done working with it, but sometimes I forget and it affects everyone next day.


I don't know how setting single user will bring down the database, even the process which had opened management studio. I think I tried this before, it didn't work either.

Next I

Error message on restoring database:

Executed as user: NT AUTHORITY\SYSTEM. Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101)  RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

The scheduled database restore does following;


--Do Actual Restore
RESTORE DATABASE db1
FROM DISK = '\\db-srv\ScheduledBackups\db1.bak'
WITH MOVE 'db1_dat' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1.mdf',
MOVE 'db1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1.ldf',
REPLACE

use db1
GO
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'username', 'username'
GO


I tried to use SQL statements to  kill all process, but it didn't work. I thought SQL management studio doesn't show as active process in process list.

Options what I found online was;

Solution 1:

USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

ref. http://blog.sqlauthority.com/2009/02/06/sql-server-quickest-way-to-kill-all-threads-kill-all-user-session-kill-all-processes/

Solution 2
The other solution I found online is, it specifically set the database offline and it seems to be more promising to close all connections, but still does it close process management studio uses?

ALTER DATABASE <Put Your Database Here> SET OFFLINE WITH ROLLBACK IMMEDIATE

-- And to bring the database online again
ALTER DATABASE <Put Your Database Here> SET ONLINE

http://www.dbatodba.com/sql-server/how-tos/how-to-kill-all-users-in-specific-database-on-sql-server/


I need to test each of solution, but I want to get any additional information before I do it because I won't have much time to do the experiment when I actually do this.
0
Comment
Question by:crcsupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39923697
I would go with a SET OFFLINE, and then directly do the restore, which should bring it back online implicitly. you can try that on a test db restored to even another name, connecting to it, and trying to run the restore there ...
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question