November 7, 2013 at 4:24 am
Hi, I have been a DBA for good few years now and have always had trouble explaining the best way to go about the following issue.
Lets say I need to restore a database using SSMS or a third party backup tool. One of the common problems is where users (application or individual users) are still connected to the database and the restore job fails. Most of the time I can ask users directly to close any connections, but some application users take time to disconnect, even if the application is stopped. Sometimes this disconnections are not quick enough for the need of the restore and so I have performed any number of the following to bring the database to a state where it can be restored:
- Setting the database to offline with immediate rollback, bringing it online then quickly kicking off the restore before any users can connect.
- Using a filtered SP_Who2 query to generate a kill script for all the users, killing them off, then again quickly kicking off the backup.
- Set the application users to deny connect to server, then kick off the backup and then change the login back to allow connections.
- Setting the database to restricted_user mode, but this doesn't always work since the application user is usually a member of the db_owner database role.
A number of our developers may execute changes within applications and then need to restore a database back to a starting point multiple times on a test system, and they always hit the issue with users still connected. I find it difficult to explain what the proper way to enable the database to be in a state of where it can be restored and what they "should" be doing to fix the issue, since the options I do above always seem messy!
Thanks for any clarification.
November 7, 2013 at 4:40 am
I have a script that I run based on the sysprocesses table that kill all SPID's in a given database.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
November 7, 2013 at 5:33 am
You don't need to bring the database back online before doing the restore.
I do this quite a lot:
ALTER DATABASE myDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [myDatabase] FROM DISK =..... (etc) GO
The database restore brings the database back online, and users can ony reconnect once the restore is complete.
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
November 7, 2013 at 7:31 am
You could also set the database to single user mode (with immediate rollback).
This will kick everyone but you off of the database, and keep other people out until you set it back to multi user.
November 7, 2013 at 7:33 am
sqlmunkee (11/7/2013)
You don't need to bring the database back online before doing the restore.I do this quite a lot:
ALTER DATABASE myDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;RESTORE DATABASE [myDatabase] FROM DISK =..... (etc) GO
The database restore brings the database back online, and users can ony reconnect once the restore is complete.
That's interesting! I never realised this!
Thanks all for the responses. Glad I have been doing things in pretty much the proper way.
November 7, 2013 at 10:15 am
I'd set it to single user or restricted user, with immediate rollback and then restore. This is what sestell1 suggested as well.
That way I can Check things before/after the restore without users causing issues.
November 7, 2013 at 10:29 am
If you are planning on running it as a scheduled job, I would recommend checking to make sure the database is online before you take it offline. Something similar to the following:
if exists (select d.name FROM sys.databases AS d
WHERE d.source_database_id IS NULL -- snapshot databases are not null
AND d.state_desc = 'ONLINE'
AND d.name = 'AdventureWorks')
BEGIN
alter database AdventureWorks set offline with rollback immediate
END
November 8, 2013 at 3:36 am
Maddave (11/7/2013)
A number of our developers may execute changes within applications and then need to restore a database back to a starting point multiple times on a test system, and they always hit the issue with users still connected.
Can you elaborate this more ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 8, 2013 at 4:23 am
Bhuvnesh (11/8/2013)
Maddave (11/7/2013)
A number of our developers may execute changes within applications and then need to restore a database back to a starting point multiple times on a test system, and they always hit the issue with users still connected.Can you elaborate this more ?
This only really happens in our test\dev environment where developers have full sysadmin rights to the sql server. These servers are on a separate domain and no live data is held here. It allows them to quickly backup, make DDL changes or run the application in a test mode, then rollback the database by doing a restore to quickly get it back to the point before the changes. Because they are devs and not DBAs they do sometimes run into issues like above, where they can't restore the database and leave their applications running, blocking the restore. Some of them have also ventured into using single user mode, but again, they end up locking themselves out of the database and not being able to reconnect to set it to multi user! This exact scenario happened yesterday in fact!
This thread is good in that it allows me to say with confidence now what is the best way to do this particular task.
Thanks all for your responses!
November 8, 2013 at 4:25 am
sestell1 (11/7/2013)
You could also set the database to single user mode (with immediate rollback).This will kick everyone but you off of the database, and keep other people out until you set it back to multi user.
I do this too, but I'm always looking for more inventive methods.
November 8, 2013 at 5:40 am
With some of the automated programs using the database, they will see their SPID die and reconnect again...you can go thru all the database users and DENY CONNECT. Then when you kill all the user SPIDs for that database they cannot start another... the restore has the GRANT CONNECT permissions included so the users can connect again after the restore is complete.
November 8, 2013 at 7:00 am
Maddave (11/8/2013)
Some of them have also ventured into using single user mode, but again, they end up locking themselves out of the database and not being able to reconnect to set it to multi user! This exact scenario happened yesterday in fact!
the single user or rollback-immediate approaches will work but i think you should have a proper session with dev team so make them understand that what is best and safe approach to roll back the db and what kind of issues dev or dba might face you they dont follow the standards/rules.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 8, 2013 at 7:23 am
Bhuvnesh (11/8/2013)
Maddave (11/8/2013)
Some of them have also ventured into using single user mode, but again, they end up locking themselves out of the database and not being able to reconnect to set it to multi user! This exact scenario happened yesterday in fact!the single user or rollback-immediate approaches will work but i think you should have a proper session with dev team so make them understand that what is best and safe approach to roll back the db and what kind of issues dev or dba might face you they dont follow the standards/rules.
I Agree and I did this exact thing yesterday and went through the different options, what was best and some of the pit falls etc. It was purely coicidence I hard started this thread as I was never sure what was considered best practice.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply