Solved

SQL 2008, scheduled database restore fails

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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