Bad Habits: Using MDF/LDF Files as "Backups"

I come across an alarming number of people who need to take a backup of their database, or restore a copy of it somewhere, or move the data or log file(s) to a different drive, and do so by detaching the database (or shutting down the entire SQL Server service), and then copying or moving the file(s).

Before I go any further, let me cut to the chase:

Both of these approaches are very dangerous.

Having the service stopped removes the very protection SQL Server provides by locking the files from file system access in the first place (which is why you can't copy the files while they are attached and the SQL Server service is running). Relying on the service to detach your files cleanly or to shut down gracefully is risky. I have seen multiple cases where the latter resulted in corrupted files that could not be used elsewhere, and which failed to come back online even when starting back up the source instance.

I also often see people struggling to attach a database when all they kept was the .mdf file - if only I had a nickel for every time someone told me, "but the log file isn't necessary." The log file IS necessary; if you are detaching to try and recover from an out-of-control log file, you are asking for trouble, and there are better ways to deal with that scenario. Please read this dba.stackexchange thread in its entirety.

Even if the files detach cleanly, and even if you do include all of the files, moving (rather than copying) them after detaching poses another huge risk - if they get damaged in or after transit, what are you going to fall back to? It's possible you now have zero copies of your database. Trust me; this is not a lesson you want to learn the hard way.

USE A NATIVE BACKUP

I always ask these people if they are taking backups.

• If they say no, I ask them why not? I don't get many good reasons for this, mostly excuses, and I respond that it is never too soon to start. The most common excuse is disk space. Often from the same people who justify using GUIDs for keys or indexing every view with the reason that "disk space is cheap." Well, time to put your money where your mouth is, I guess. Anyway if you are copying an mdf and ldf file, you are getting the entire files, no matter how much free space is inside of them. A backup will be smaller because it only backs up pages with data on them, and on top of that you can back up with compression in modern versions and certain editions.
• If they say yes, I tell them to copy the backup file, and restore that instead of relying on the data/log files. If the backup isn't recent enough, create a new one (with COPY_ONLY, perhaps, to avoid disrupting the log chain - all depends on the nature of the move). This is a much safer approach because your original database is still intact. If something goes wrong with the file during transit, or there are issues trying to restore it on the destination server, you can always start over - having lost nothing but time.

If you are stopping the service or detaching the database so that users can't modify the schema or data after your "backup" or copy process has started, there is a much safer way to do that: take the database offline. (Obviously, having the source database offline temporarily is acceptable, if your current approach is detaching it or shutting SQL Server down.)

USE [master];
GO
ALTER DATABASE db_name SET OFFLINE WITH ROLLBACK IMMEDIATE;
BACKUP DATABASE db_name TO DISK = N'<file spec>' WITH INIT, COPY_ONLY;

Now, if you restore the backup and it doesn't go well, you can always bring the source database back online.

You need to make sure you're in the right recovery model and that you are protecting yourself adequately in the event of a disaster. Again, this thread on Database Administrators is a worthy read.

Moving a file by detaching and re-attaching

I frequently see detach/attach as the suggested approach for moving either the log or data file(s) to a different drive. For all of the reasons above, this is the last approach I would ever use. A safer way is to use backup/restore, or to simply modify the database's file configuration, take it offline, then copy (not move) the file manually, and finally bring the database back online.

Here is the backup approach - let's say I want to move the log file from the E: drive to the F: drive:

USE [master];
GO
 
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
ALTER DATABASE dbname MODIFY FILE 
  (name = N'dbname_log', [filename] = N'F:\new_path\dbname_log.ldf');
 
ALTER DATABASE dbname SET OFFLINE;
 
/*
  Now go to Windows Explorer, copy the ldf file to the new location, 
  rename the original (and move it or delete it after this process is
  complete). Make sure the SQL Server service account has adequate 
  permissions on the new folder. Then:
*/
 
ALTER DATABASE dbname SET ONLINE;