SQL Server Backup – Quickest Way to Restore a Database

backuprestoresql server

Lately I have been finding myself restoring a DB to my local instance of SQL-server a lot more. I wanted to speed up the process by writing a script and then just executing it in order to restore over some X DB on my local. However, every time that I run this script I get the error saying the DB is still being used. Now this could happen because I have a query open, or because a web application is still using it, but instead of me going to track down what is using my db and stopping it. I would much rather have my script take care of this since this is not going to be affecting anyone but me.

Is there a better way to do this than querying for the processes that are using the DB and killing them? Currently I was thinking of doing the following

 select 
 spid,status,loginame=SUBSTRING(loginame,1,12),
 hostname=SUBSTRING(hostname,1,12),
 dbname = SUBSTRING(db_NAME(dbid),1,10) 
 from sys.sysprocesses where DBid = 17

and then running the results back through a dynamic sql with the KILL command.

Best Answer

You can set the database in SINGLE_USER mode. If you also specify ROLLBACK IMMEDIATE it won't wait for open transactions to finish but will just roll them back.

ALTER DATABASE [yourdatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

do your actions

ALTER DATABASE [yourdatabase] SET MULTI_USER

Related Solutions

Sql-server – SPIDs stuck indefinitely in suspended state

If you've got SPIDs sitting with the wait type of ASYNC_NETWORK_IO it's either row by row processing within the application, the application is calling huge recordsets which is can't process fast enough, or there's a network capacity problem.

PostgreSQL – pg_restore Error: Schema ‘public’ Already Exists

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

The --clean option in pg_restore doesn't look like much but actually raises non-trivial problems.

For versions up to 9.1

The combination of --create and --clean in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):

--clean Clean (drop) database objects before recreating them

and

--create Create the database before restoring into it.

Because what's the point of cleaning inside a brand-new database?

Starting from version 9.2

The combination is now accepted and the doc says this (quoting the 9.3 manpage):

--clean Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)

--create Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

Now having both together leads to this kind of sequence during your restore:

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

There is no DROP for each individual object, only a DROP DATABASE at the beginning. If not using --create this would be the opposite.

Anyway this sequence raises the error of public schema already existing because creating mydb from template0 has imported it already (which is normal, it's the point of a template database).

I'm not sure why this case is not handled automatically by pg_restore. Maybe this would cause undesirable side-effects when an admin decides to customize template0 and/or change the purpose of public, even if we're not supposed to do that.

Best Answer

Related Solutions

Sql-server – SPIDs stuck indefinitely in suspended state

PostgreSQL – pg_restore Error: Schema ‘public’ Already Exists

Related Question