Personally, I would avoid the detach/attach mechanisms. Especially in SQL Server 2000, I just don't trust that you will always bring the server back up and be able to attach those files. I've heard plenty of stories where this didn't happen cleanly - just because you have a Plan B doesn't automatically make Plan A sensible.
With backup / restore, you don't risk having to go to Plan B. If the backup fails, your database is still up. If restore fails, your old database is still up. In both cases you can restore the operation of the original database and revisit the plan later. In addition to the extra security here over stopping SQL Server and/or detaching, this also means you can test the hoo-has out of the backup/restore methodology (assuming you currently have the space to perform the backups and another instance to test the restore). You can't really test the detach approach without detaching the databases or stopping SQL Server, and that's tough to do outside of a proper maintenance window. And finally, with the other approaches you can't even start copying the files until you've detached or brought SQL Server down. With backup/restore you can have the .bak file waiting on the new storage long before you take the last log backup and start your maintenance window.
One other benefit over the pull-the-drive-out-from-under-SQL-Server method: with backup/restore you can move various files to different drive letters than they were before. For example when we migrated to a new SAN, we were able to have more volumes, so we could move tempdb to T:\ (which didn't exist before), some of the data and log files to new drive letters, etc. to better utilize all the new I/O capacity we had. If you simply shut down SQL Server and then swap out the disks, you need to have the same drive letters and the same number of volumes.
What Is Detach or Attach and How do They Work?
We'll start with detach. When you detach a database in SQL Server, you are taking the database offline and removing it from the SQL Server instance from which you are detaching it. The databases data and log files remain in tact and are left in a consistent state so you can then attach the database at a later point or to another SQL Server instance. Attach connects the data and log files from a database that has been properly detached (or that were copied from a cleanly shut down instance of SQL Server) to an instance of SQL Server and brings the database online.
How Do I Detach a Database?
You can do this in T-SQL or from the SQL Server Management Studio GUI.
In the GUI, you right click on the database you wish to detach, select All Tasks
and click on Detach
. From there you'll get the detach dialog. You can choose to drop connections first to forcibly disconnect any active connections and rollback work they were in the middle of executing. You can also choose to update statistics before the detach.
In T-SQL:
-- You don't want to be in the database you are trying to detach
USE Master
GO
-- Optional step to drop all active connections and roll back their work
ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
-- Perform the detach
EXEC sp_detach_db 'DatabaseName'
GO
For the system stored procedure sp_detach_db there are two paramaters that you can pass in optionally:
@skipchecks
- acceptable input is 'True'
or 'False'
if 'True'
, SQL Server will update statistics before detach. If 'False'
, it won't. If you don't specify anything here the statistics will be updated in SQL Server 2005 or later.
@keepfulltextindexfile
- The default here is 'True'
- if this is set to true, the full text index metadata will not be dropped during the detach.
To see a lot more about detach and some more details on the risks I highlight below, the Books Online article for sp_detach_db
is a good place to start.
How Do I Attach a Database?
You can also do this in T-SQL or from the SQL Server Management Studio GUI.
(NOTE: If you have the data and log files from a database that was not properly detached, your attach may not work. When detach occurs, the database is brought offline and the log and data files are put into a consistent state. This also happens when a service is cleanly shut down.)
In the GUI, you right click on the top level Databases
folder for your instance and select Attach
. In the next dialog you would then select the primary data file (.MDF) of the database you wish to attach and ensure you have the other files selected and their appropriate locations specified, and click OK, attaching your database.
In T-SQL the best way to do this in SQL Server 2005 and forward is through the CREATE DATABASE
command. This is the method that is supported beyond SQL Server 2012. If you want to see how to use sp_attach_db
, you can see that in the books online articles for [sp_attach_db][3]
or [sp_attach_single_file_db][4]
When you have your log file and data files available and they are consistent this is the T-SQL approach:
-- Using Create Database and the FOR ATTACH clause to attach
CREATE DATABASE DatabaseName
ON (FILENAME = 'FilePath\FileName.mdf'), -- Main Data File .mdf
(FILENAME = 'FilePath\LogFileName.ldf'), -- Log file .ldf
(FILENAME = 'FilePath\SecondaryDataFile.ndf) -- Optional - any secondary data files
FOR ATTACH
GO
You can see more about the Create Database statement in books online as well.
How Do I Detach/Attach in SQL Server Express?
It's actually the same. If you are using SQL Server Management Studio Express you can use the detach/attach dialog in the GUI described above or the T-SQL steps through SSMS Express described above as well. No difference with Express there.
If you don't have SSMS Express, you can download it (Here is the SQL Server 2012 Express version).
Of you can enter into a SQLCMD
session and use the same T-SQL constructs described above.
When Should I Consider Doing a Detach or Attach?
First a word on what detach and attach is not meant to be used for: Backup and Recovery Detach and Attach is not a way to backup your database for routine recovery purposes. There are no transaction log backups this way, it puts your database into a state where the database files can be deleted accidentally and is not a good way at all for this purpose.
That said, detach and attach are good for a few use cases (not exhaustive, feel free to edit to add or create a new answer with more):
- Sometimes for migrations (although I prefer backup/restore for those as discussed in my answer here)
- When you want to remove a database that is no longer actively used but have the ability to attach later as needed.
- In certain troubleshooting situations, this may be called upon
- Don't have the space to backup or to restore both a data and log files to another environment (you shouldn't ever be here but I've used it to move dev databases around environments at times.. Didn't want or need the log so did an attach/rebuild of the log file)
Risks and Warnings
Again, books online is a good resource here, but I'll call out some specific considerations to have in mind with detaching or attaching a database -
Detach
- You are taking your database offline. It won't be accessible anymore. This should be obvious, but worth calling out. This is why it isn't a great backup option.
- When your database is online, SQL Server locks the files. I wouldn't recommend trying this to prove me wrong, because there could be some other situation at play, but you typically can't delete a database file (data, secondary data or log file) while SQL Server is online. This is a good thing. When you detach, you have no such protection - this can be a bad thing.
- If you are dealing with database corruption and you find some article someplace that has a first step of Detach - it's wrong - if you detach a corrupt database, that may be it. You may not be attaching that database again.
- Cutting and pasting your production database files throughout your network is a way to potentially introduce file level corruption.. Another reason I prefer backup/restore when doing migrations.
- It might cause a maintenance plan to fail. The situation is that you have, as I did, set up a maintenance plan to carry out regular backups of all databases without checking best practice. This works fine so you stop thinking about it. Someone else then decides to take a database they're not using offline. The maintenance plan will fail from that point forwards until you modify the maintenance plan by checking the "ignore databases whose state is not online" option in the "Database(s)" dialog. Note that it won't just fail for the offline database - the maintenance plan will fail with an error at the point when it tries to backup the offline database so some online databases might not be backed up. (different author for this point so treat with suspicion)
Attach
- Just like you shouldn't run scripts from the internet or accept packages from strangers at the airport, you shouldn't attach a database you got from someone else without some steps to verify it. This database could have code inside of it in triggers, stored procedures, etc. that could compromise your environment. You should review a database you want to attach in a safe, and firewalled environment, not your production system.
What About Different Versions or Editions of SQL Server?
These are no different than the rules around restoring databases between versions. You can generally restore up to the next version for 3 versions (SQL Server 2008 to SQL Server 2012, for example will work. SQL Server 2000 to SQL Server 2012 will not). You cannot go backwards at all via backup/restore or detach/attach - you'd have to script out objects and script out the inserts and do it manually or with a tool that does this. For editions, you can generally move between the main SKUs of SQL Server - for instance you can move a database from Standard to Enterprise with no extra work. If you are using Enterprise features (Say, compression or partitioning), you'll need to disable those features before you make the move, though. You can get an idea of the features you'd need to consider disable by looking here.
Best Answer
Attaching/Detaching could be quicker than backup/restore depending on the situation.
A detach is instantaneous and you can start copying the files right away, and the attach is near instantaneous too. Backup takes a while, and restore takes a while too.
This technique was a lot quicker before we had backup compression because the size of the files that needed to be copied over were more or less the same size depending on the situation.
Backup compression has an influence on that, since the backups are now a lot smaller than the data files.
It all depends on your IO speed and the source and destination of the copy.
The math is fairly easy, If the time of taking a backup, copying over the files, and restoring the backup is shorter than copying over the mdf+ldf files it's faster to backup/restore, otherwise it's faster to detach/attach.
The detach/attach method obviously only works if your database can go offline.
If your IO subsystem is fast, and the network link over which you have to transfer the database is slow it might be faster to transfer a compressed backup (if that's available in your version/edition). If your IO subsystem is slow and so is the process of backing up and restoring but you can copy the files over quickly it might make sense to detach, copy over the relatively larger .mdf files and reattach.
In my opinion, this used to be more of an issue with large databases before we had backup compression available. If I recall correctly this was a possible question in Microsoft Certification exams before we had compression but I haven't seen it in the material for more recent versions.
The end result is basically the same, an exact copy of the database.