EXEC sp_dboption N'mydb', N'offline', N'true'
OR
ALTER DATABASE [mydb] SET OFFLINE WITH
ROLLBACK AFTER 30 SECONDS
OR
ALTER DATABASE [mydb] SET OFFLINE WITH
ROLLBACK IMMEDIATE
Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds. The default is to wait for all currently running transactions to complete and for the sessions to be terminated. Use the rollback immediate clause to rollback transactions immediately.
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
25 Comments. Leave new
Nice post, with some useful samples.
And SQL sERVER 6.5? wATH
Hi Dave!
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE
Works just fine but if I want to take many DBs (354) offline with a script at the same time. Can that be done. I also what to exclude some DBs from beeing taken offline.
/Magnus
Magnus, sure it can. A quick way to do this is simply to use a cursor and a query similar to –
select name from sysdatabases where name not in (‘master’, ‘msdb’, ‘model’, ‘tempdb’, ‘donttakeoffline’)
*note ‘donttakeoffline’ is the name of a db you might want to exclude, the others you shot NOT take offline)
Have the cursor dynamically build the command and exec such as this –
set @cmd = ‘Alter database ‘ + @dbname + ‘ set offline with rollback immediate’
exec (@cmd)
If you want to exclude them you can exclude from the original query for the cursor or use an IF statement example
If @dbname != ‘dontoffline’
begin
exec @cmd
else
end
Mind you, doing this can be dangerous and is not 100% certain. Most things like this should be done one at a time and/or monitored very closely.
If I were going to do this, I would have it record into a table somewhere of the status of taking it offline and when it was taken offline for each DB.
*not responsible for people breaking their DB using this information*
Hi dave
i want to lock the record and send the message to user who try to access the same record for certain time
can u help me out .
how to shrink transaction loh in sql 2005.
Hi Pinal,
1.Can we shrink the log file after taking database into offline?
Hello,
does a offline database uses less memory ?
hi i want to know how i can create an offline server to play for example Lineage-i own SQL and navicat
i want to know how i can create an offline server to play for example Lineage-i own sql and navicat
Carlos,
for SQL Server 6.5, you can use sp_dboption dbname, offline, true
I tried to Drop database from web page, by executing SQL Command, then show the result, done or error. And I got this message : Cannot drop database “detach” because it is currently in use.
Thanks for your help.
I assume using the command –
ALTER DATABASE [mydb] SET OFFLINE WITH
ROLLBACK AFTER 30 SECONDS
will close the existing connections as I want to restore my DB and restore command fails with
“Exclusive access could not be obtained because the database is in use.”
Any help will be appreciated.
Thanks
How to take database online. this is urgent. I can not find in your blog
Hi..
I have an issue, Actually my all tables have schema user. and all tables are looks like user.tablname. Now if i restore this backup into new server then I am unable to create database user “user” because it shows error user already exits. And if I create another user then I unable to access table by just its name (I can access by schemaname.tablename) , I want to change schema of all tables at once . Please help me regarding this.
Himanshu Kumar Pant
for eWebGuru.com
Thanks for the information. That was a great post.
Can you help me to attach databases in bulk, i.e. if there are 100 databases and I’m needed to migrate in bulk to other server through script?
Currently I hosted databases on SQL Server 2005 SP2 x64 bit. I’m planning to upgrade my database server to SQL Server 2008 R2 x64, and it’s not supported to upgrade SQL Server 2008 R2 X64 from SQL Server 2005 x64 SP2 (according to .
what is your suggestion?
I am planning to install new version of SQL Server 2008 Rs x64 and migrate all databases to new instance, that is why I am looking for script for attaching SQL Server in bulk.
Thank you.
Hi,
I have a problem. There is a table in my database called Corporates. It has a Field which contains the Domain name.
When i open the site the URL of the Site is fetched from the Domain Name field of the Table. I need to update the Domain Name field for testing purpose with Localhost. After testing when i again Update the Domain Name field with the Original Domain Name of the Site it automatically get updated to LocalHost.
I checked the code to see if it has been coded to update but to no avail. I dont understand why is this happening. I need to solve this problem urgently. Any help is greatly appreciated.
Post the update code that you used
If I take a SQL Server 2000 db offline with the ALTER cmd, can I then put it back online with another ALTER cmd? Or since it’s already offline… how can I access it, to put it back online????
Good One. Thanks for this. It helped to turn off the junk databases.