How to Move Database Files in SQL Server: Part 2
In the first part of this article we showed you the best way to move database files. Now we’re going to show you the other way and briefly discuss a third way, though you’ll never use it… at least you’d better not.
There’s a real difference between taking a database offline and detaching it. Setting a database offline merely changes a value in the sys.databases table in the master database. Detaching a database actually removes the entry for that database, so it stands to reason that re-attaching a database can be a bigger ordeal than just bringing it back online. For starters, there are some restrictions; you can’t detach a database if any of the following conditions exist:
- Database is a system database
- Database is being mirrored
- Database is being published through replication
- The database has a snapshot
You should also be aware that there are implications to detaching a database that you may not be expecting. One of them is that any users that have that database as their default will now be given master as their default. This can have consequences for applications as well as for users that are expecting their database to be set when they log in.
Now that we’ve gotten past all the scary stuff, let’s go ahead and look at how to do the same file move we did in part 1, only this time we’ll use the detach/attach method. There are two ways to detach your database: through the SSMS GUI and through T-SQL. We’ll show you T-SQL method.
sp_detach_db @dbname= 'MyDatabase'
,@skipchecks = 'true'
Here’s some explanation of the code.
Sp_detach_db – this is the stored procedure that actually does the detach.
@dbname – this is the name of the database you’re detaching. You don’t really need to use the named parameter like this. I’m doing it only for clarity.
@skipchecks – this parameter defines whether you want SQL to update statistics or not. Acceptable values are ‘true’ or ‘false’. Most of the time you’ll want to use ‘false’ because there’s no need to update statistics just because you detached a database. This is important because the update statistics portion can take quite a long time, so it’s best to leave that for another time.
Once you’ve detached your database, you’ll need to physically move the files in Windows to the new location (using drag-and-drop or cut and paste) and then run the attach command.
EXEC sp_attach_db @dbname = 'MyDB',
@filename1 = 'C: \MyDBData.mdf',
@filename2 = 'C: \MyDBLog.ldf'
By now the syntax should be fairly easy to follow, but we will go ahead and talk about some of the things that you need to know about this command. First of all, you should know that sp_attach_db will be deprecated in future versions of SQL, so you should use the new alternative instead (more on that in a minute). One of the reasons it’s being deprecated is because you can only specify up to 16 files so it’s becoming unwieldy.
Another thing you should know is that if you have a lot of files, you really only have to specify the .mdf, the .ldf and any files that have changed locations. By specifying the first data file (the mdf), it already knows the location of all the other files, so it’s only necessary to specify an .ndf file if it’s changed locations.
We’ll also take this opportunity to warn you against attaching DBs that have a source you’re not certain of. If you attach a database, you’d better know where it came from because it could have malicious code inside that could really mess up your server.
The New Way
We mentioned that there’s a new way to attach a DB. It’s actually a CREATE DATABASE command with the For Attach option. We’re not going to go into that syntax here, but you can find it here: http://msdn.microsoft.com/en-us/library/ms176061.aspx
The reason that we chose to show you sp_attach_db instead of the CREATE DATABASE method is because this method is fully backward compatible with every version of SQL you’ve probably got in your shop.
Never Do This
Remember early on we told you that there was a third method that we’d discuss briefly? Well now’s the time. That third method is to backup and restore the database in the new location. And as we said before, this is just a ridiculous method. Not only is it overkill, but many databases are quite large; to do a full restore could take a very long time. Not to mention there might be some corruption in the backup files and the restore could fail. Even though we’re not going to show you how to do it this way, we did want to discuss it just for completion so you know that theoretically it’s a viable method.
In this short series we’ve discussed the different ways you can move your database files to a new location. We’ve talked about the option that is the least disruptive to your system, and the one that is the most. You have to remember that there are plenty of cautions when using commands like these so do your homework before trying it yourself.