Lock Down a Database Before Restoring

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Lock Down a Database Before Restoring

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-08-27 : 18:38:04

Hi all,

Nightly, I have an automated process that refreshes a test database by restoring from production db backup.

At the top of the process, I kill all active connections to that test database.

... But sometimes someone comes in just after that, causing the database restore to fail.

So I want to design a more fail-proof technique.

Is setting the test database to 'Single-user mode' the best option?

(I dont want to restrict the entire instance, only the Test database)

Thanks, John

Kristen
Test

22859 Posts

Posted - 2011-08-27 : 18:46:25

I do:

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

or

ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Some syntax examples here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-27 : 19:50:36

I prefer the second option - ALTER DATABASE {database} SET OFFLINE WITH ROLLBACK IMMEDIATE;

I have had issues with the setting to single user, especially when using third-party backup utilities (e.g. Litespeed). Because of the way these utilities work, you need 2 connections - one for the statement executing the command and another for running the extended procedure.

Jeff

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-08-28 : 21:52:40

Thank you Kristen and Jeff.

This week, I'm going to add

ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE

to my job to see how it goes.

John - Memphis TN USA

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-08-30 : 13:49:52

Last night's refresh of the TEST database went really well, after I put this in place to run just before the RESTORE DATABASE:

ALTER DATABASE AVNAPPDB SET OFFLINE WITH ROLLBACK IMMEDIATE;

To anyone else needing "lock down" a database before restoring, I recommend this technique.

Thanks Kristen and Jeff!

Kristen I'm very impressed that you could spin back to a post from so long ago (2006) for the syntax link!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-08-30 : 17:56:10

I prefer this:

use master
ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDatabaseName SET ONLINE  WITH ROLLBACK IMMEDIATE
DROP DATABASE MyDatabaseName

CODO ERGO SUM

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-30 : 21:29:01

quote:
Originally posted by Michael Valentine Jones

I prefer this:
use master
ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDatabaseName SET ONLINE WITH ROLLBACK IMMEDIATE
DROP DATABASE MyDatabaseName

CODO ERGO SUM

That's exactly what I do in my automated restore jobs.

Replaced many of them with NetApp flex-cloning though. So far, flex-cloning is 4x - 6x faster easily. Much more so in some cases.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-30 : 22:31:25

quote:
Originally posted by Michael Valentine Jones

I prefer this:
use master
ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDatabaseName SET ONLINE WITH ROLLBACK IMMEDIATE
DROP DATABASE MyDatabaseName

CODO ERGO SUM

Why set the database ONLINE then DROP it? The restore will bring the database back online when you issue RECOVERY so is there really any benefit to doing so?

As for dropping the database, there could be a performance hit when restoring the database when SQL Server builds the data file. If you don't have instant initialization enabled - the file will have to be zero initialized, and if you do have it enabled the OS still has to build the file. In some cases, SQL Server will reuse the existing file for the restore and skip those steps.

Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-30 : 22:43:32

Yeah I was confused by that too. Plus my apps connect very fast, so the drop could fail after it's brought back online. Imagine 3000 connections from many app servers slamming a high performance server, one or two will get in before the drop. So I'd have to set it to restricted mode too, but I'm just not seeing the point of all of that for the reasons Jeff mentioned.

MVJ and russell, do elaborate on this please!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-08-30 : 23:36:43

Like russell, this is how I do it for automated restore jobs. I have a lot of cases where we restore databases from production into a testing environment on demand, and we have jobs setup for these. There are also cases where we have scheduled jobs to restore a production OLTP database to another server to be able to offload reporting.

The reason I like to drop the database is because Litespeed complains if the files are not exactly the same as when the backup was made, and that is usually the case if I am doing a restore to a new server. When there is no database it doesn't complain.

The reason I set it offline and back online to to clear all the connections before dropping the database. The reason I set it back online is because SQL Server does not delete the data files if you drop a database while it is offline. If SQL Server had an option to delete the files when you drop an offline database I would do that.

I would set it to restricted mode in situations where an app might reconnect, but too often the app service accounts are db_owner anyway, so it is still hard to prevent. If that was happening, I might remove the login from the db_owner role before setting it offline, and set it back online in restricted mode. Or I could disable the login, but that can be more trouble. As I mentioned before, this is usually for automated restore jobs to different servers, so having the app reconnect is less of a concern for me.

CODO ERGO SUM

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-30 : 23:48:13

^^ What he said. I only automate restores for (a) testing environment and (b) analyst sandbox.

And the not deleting files is a deal-breaker in some cases.

Unlike Jeff, instant file initialization is never an issue for me, because I'm a pretty firm believer in making the SQL account local admin -- I know he disagrees, but we lock it down pretty good, as far as who can access, and no one can escalate.

Recovery is pretty much a non-issue usually. Our "anylists" run some mighty ugly queries, but the automated restores are in the middle of the night, so it seldom comes into play.

EDIT: by the way, single_user (instead of offline) I've experienced exactly what Jeff is talking about earlier in the thread (needing 2 connections).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-31 : 13:02:15

quote:

Unlike Jeff, instant file initialization is never an issue for me, because I'm a pretty firm believer in making the SQL account local admin -- I know he disagrees, but we lock it down pretty good, as far as who can access, and no one can escalate.

I'm in the same boat as you. We are quite possibly switching things here though and will need to grant the access for instant file initialization if we do go down that non-local admin route.

quote:

The reason I like to drop the database is because Litespeed complains if the files are not exactly the same as when the backup was made, and that is usually the case if I am doing a restore to a new server. When there is no database it doesn't complain.

We are using LS here, and the restores never have a problem. Maybe you aren't using REPLACE and MOVE when you run into the issue? I don't know, just know that we don't experience this issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-31 : 19:44:18

I also use Litespeed on my automated restores and haven't had that issue myself yet (knock on wood). I have had some other strange things happen with Litespeed - but not that one.

And to Russell, yes - I do disagree about putting the service account in as a local administrator. You know, best practices and all that :) We can agree to disagree on that point...

If you can lock down the systems very tightly, the risk can be mitigated. However, in a lot of cases the service account is put into the administrators group because someone once had a problem, on SQL Server 7/2000 where the only way they could get it to work was to do that and that became the 'standard'. Oh, and getting the privileges right in SQL Server 2000 was just a nightmare...

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources