Sql-server – What are the risks of copying MDF/LDF files without detaching the DB first

sql server

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?

Best Answer

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.

Related Question