What are the risks of copying MDF/LDF files without detaching the DB first?

Question

Where DB backups are not available, our support team has been trained to restore using the following steps:

1. Get a copy of the MDF/LDF from a file system backup.
2. Stop the instance.
3. Overwrite the existing MDF/LDF files.
4. Restart the instance.

This is without a detach. The MDF and LDF files are simply overwritten and the instance restarted in the hope that the new files can be initialized correctly.

I'm currently trying to write a document describing the appropriate use of detach/attach in this scenario. While I know this is the correct approach, I can't find any resources which describe the risks of the other approach.

Can anyone provide any advice?

Answer 1

It should be OK. after all, that's what the entire goal of recovery is, to survive any abrupt interrupt. Instance shutdown is actually quite a graceful shutdown, will properly close all DBs (should be no pending xact to rollback on recovery).

But the gist of it is that you're using a sledgehammer to break an egg shell. Copying using BACKUP FULL WITH COPY ONLY/RESTORE is faster and safer. Even file copy can be achieved more efficient, just set the DB offline and the files will be accessible for copy, then bring the DBs back online.

About overwriting the destination, there are some potential problems. It obviously does work, after all your team was doing to all along... But you can and up with discrepancies what the instance knows about the database from data in the master catalog and what the database itself claims in its boot page (things like service_broker_id or compatibility level for instance). These can be resolved, but you will have problems just understanding what is wrong. Basically, why the DB behaves one way in production and another way on support. Again, there is simply no reason to go into this gray area. Use BACKUP/RESTORE, is safer and faster.

Answer 2

This is a terrible way to take a backup. Why aren't they using, uh, backups? Not just because they don't require that you take down the entire instance.

If you just stop the instance, there isn't really a guarantee that the MDF/LDF files will be detached gracefully. So just because you are able to copy them, does not mean they are good. Having the service stopped removes the very protection SQL Server provides by locking the files from file system access in the first place (what is preventing users from accidentally deleting one of these files? They are human, after all). And for all you know the files could be corrupt before you even copy them, or could become corrupt while you copy/move them. If the user accidentally moves them instead of copies them, and something happens to them during that process, you now have zero copies of your database.

Backups are very easy, and protect you from all of the above.

BACKUP DATABASE dbname TO DISK = 'C:\whatever\dbname.bak'
  WITH COPY_ONLY, INIT, COMPRESSION;

Restoring is similarly easy, though if the file structures aren't the same, you may need to use the WITH MOVE arguments. This effort is worth it.

RESTORE DATABASE dbname FROM DISK = 'C:\whatever\dbname.bak'
  WITH MOVE 'dbname_data' TO 'C:\...\dbname.mdf',
       MOVE 'dbname_log'  TO 'C:\...\dbname.ldf';

Answer 3

I would advice explaining the purpose of these files. As in:

• .mdf (and also .ndf) are the data files of the database, where all of the tables, indexes (other than full-text catalogs), procedures, etc. are stored. The .mdf is the primary data file and the .ndf only exists if a new file group is created.

• .ldf is the transaction log. The transaction log is where all transactions are written to. Upon a checkpoint the contents of the transaction log (.ldf) is written to the appropriate .mdf and/or .ndf.

When you are copying these files without detaching the database first, you are risking to corrupt your backup in the event that .ldf will be synchronized during the copy procedure thus rendering the backup broken.