Last Updated on September 3, 2013 by John Morehouse
Recently I’ve been assigned to help out with database migrations from SQL Server 2005 up to SQL Server 2008. The migration process goes like this:
- Put the database into single user mode
- Backup & Restore it to it’s new home
- Take the original database offline
Now, while taking the database offline isn’t all that exciting, I think that it’s a neat feature of SQL Server that you might not be aware of. So, let’s take a look!
How to Offline a Database
Basically, there are two ways that I know of for taking a database offline; 1) via the GUI and 2) via code.
In the GUI, you take a database off line by simply right clicking the database within SSMS, select Tasks –> Take Offline.
In code, you execute this:
USE tempdb GO ALTER DATABASE [Database_Name_Goes_Here] SET OFFLINE; GO
Yes, it’s that simple. I’m a fan of using code versus the GUI but you can use what method you’re comfortable with.
One thing that I’m also a fan of, is setting the database into single user mode BEFORE taking it offline. This is because in either method, script or via the GUI, SQL Server has to obtain an exclusive lock on the entire database so if there are any connections or transactions in flight, the process of setting the database to an offline state will fail. You can help to get around this by setting it SINGLE_USER mode. Just a side note, you’ll have to have ALTER DATABASE rights in order to set it to single user mode. You can read more about this here.
Ok, back to bringing the database offline!
The database is still there
Sorta. Setting it into offline mode just simply makes it unavailable to end users. Well, to all users actually, including you. The physical files are still present on the server, but you can’t read from it nor can you make any data modifications. You won’t be able to even view the properties of the database through the SSMS interface.
Here you can see that the ‘Test’ database is set to Offline mode. The dialog box is what is returned when I attempted to look at the properties of the database.
You can also see that the physical database files are still present on the operating system.
Whoops! I dropped the Database!
Setting the database offline has an additional feature. If you drop the database and it’s in offline mode, the physical files won’t be deleted. This can be an invaluable save if you mistakenly drop the wrong database. Hey, mistakes happen, we know. You are backing up your databases right?
Take a look. Here the Test database was set offline and subsequently dropped.
You can see that the Test database doesn’t live in the database tree on the left (should be right under the Scratch database) but yet the files are still present on the disk.
By knowing that the files are still present, this would allow you to simply re-attach the files back to the instance if needed! Voila! The database will then be back online and all is well! This is of course assuming that there wasn’t any corruption to the physical files before attaching them.
Yet another disclaimer, you should always backup your databases before doing operations like this. You’ll notice that I backup the database PRIOR to bringing it offline.
Last Ditch Effort
If things go south down the road shortly after the migration, you can point your services/connections back to the old server, bring the database back online and presto! Everything is back up and running. Keep in mind however, if you do have to bring the database back online and you put it single_user mode before taking it offline, it’ll come back online in single_user mode. You’ll need to set it back to multi_user mode before any connections (other than yours) will be accepted.
However, keep in mind that any transactions that might have occurred on the NEW server are not going to be present if you were to do this. You would have to find a way to export/import/re-key the data into the original database, if there are any.
This is, in my opinion, would be one of those last ditch efforts to get things working. I tend to do things like this just as a fail safe. I will leave the database in it’s offline state until the new configuration has a chance to bake in for a week or so. As long as the utilized disk space isn’t in need, you won’t harm anything by doing this.
I would recommend that you communicate this to the powers at be, whether it’s your management or your business owners, especially as to when you expect to archive the database off and actually drop it.
If you are using the backup/restore method to migrate your databases, I think that using this neat feature of SQL Server helps provide a little more safety in your migrations. I’ve found that most business owners rest a little easier if they know that the original database is still available in the event they wanted to rollback.
If you accidentally drop an offline database, have no fear. Your database files should still be present on the operating system and available, just as I mentioned above.
Of course, as with everything in SQL Server, with great power comes great responsibility! So I’d recommend you test these commands & your process before doing anything in production.
© 2013, John Morehouse. All rights reserved.