Solved

SQL 2008, scheduled database restore fails

Posted on 2014-03-12
1
1,177 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
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

713 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