Developing Matt

My Technical Journal

Restoration

leave a comment »

Pretty basic stuff…but here are a few things I use when restoring a database including determining who is connected to the database.  I always lose this information between the times that I’m not actively restoring a database.  As a precursor, make sure it’s not you (close all sessions and apps and windows that might be connected to it)

1.  If users are in the database, determined by calling everyone in  your company or  …

declare @DBName varchar(15) = ‘yourdatabase’
SELECT spid, nt_username, * 
FROM master..sysprocesses 
WHERE dbid = DB_ID(@DBName)  and  spid != @@SPID 
— @@spid is you
— you can use sp_who but it lists all connections to the database server

2.  You can ask them politely to get out or you can violently kill their sessions with…

USE MASTER
DECLARE @DBName varchar(15) = ‘yourdatabase’
DECLARE @spid INT,
    @cnt INT,
    @sql VARCHAR(255)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
    FROM master..sysprocesses
    WHERE dbid = DB_ID(@dbname)
    AND spid != @@SPID
PRINT ‘Starting to KILL ‘+RTRIM(@cnt)+’ processes.’
WHILE @spid IS NOT NULL
BEGIN
    PRINT ‘About to KILL ‘+RTRIM(@spid) 
    SET @sql = ‘KILL ‘+RTRIM(@spid)
    EXEC(@sql) 
    SELECT @spid = MIN(spid), @cnt = COUNT(*)
        FROM master..sysprocesses
        WHERE dbid = DB_ID(@dbname)
        AND spid != @@SPID 
    PRINT RTRIM(@cnt)+’ processes remain.’
END

3.  If that doesn’t work or they continue to connect then you can always set it to single-user database mode.

Use Master
DECLARE @dbName varchar(15) = ‘yourdatabase’
Alter Database @dbName
SET SINGLE_USER With ROLLBACK IMMEDIATE
–this rolls back any transactions.

4.  I still like to use the GUI to restore but you can use this of course.  ‘they’ say it is better this way. 

RESTORE DATABASE @dbName
FROM DISK = ‘c:\yourdirectory\yourfile.bak’

Advertisements

Written by matt

September 9, 2009 at 10:17 am

Posted in Sql Server

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: