SQL SERVER – 2005 Take Off Line or Detach Database

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 (http://blog.SQLAuthority.com) , BOL

About these ads

28 thoughts on “SQL SERVER – 2005 Take Off Line or Detach Database

  1. Pingback: SQL SERVER - 2005 - T-SQL Script to Attach and Detach Database Journey to SQL Authority with Pinal Dave

  2. 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

  3. 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*

  4. 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.

  5. 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

  6. Pingback: SQL SERVER – T-SQL Script to Take Database Offline – Take Database Online Journey to SQL Authority with Pinal Dave

  7. 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

  8. 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 http://msdn.microsoft.com/en-us/library/ms143393.aspx).

    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.

  9. 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.

  10. 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????

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

  12. Pingback: SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068 | Journey to SQL Authority with Pinal Dave

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