patternsqlMinor
What are the risks of copying MDF/LDF files without detaching the DB first?
Viewed 0 times
mdfwithoutthewhatarerisksfilescopyingldffirst
Problem
Where DB backups are not available, our support team has been trained to restore using the following steps:
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?
- Get a copy of the MDF/LDF from a file system backup.
- Stop the instance.
- Overwrite the existing MDF/LDF files.
- 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?
Solution
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.
Restoring is similarly easy, though if the file structures aren't the same, you may need to use the
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';Code Snippets
BACKUP DATABASE dbname TO DISK = 'C:\whatever\dbname.bak'
WITH COPY_ONLY, INIT, COMPRESSION;RESTORE DATABASE dbname FROM DISK = 'C:\whatever\dbname.bak'
WITH MOVE 'dbname_data' TO 'C:\...\dbname.mdf',
MOVE 'dbname_log' TO 'C:\...\dbname.ldf';Context
StackExchange Database Administrators Q#61810, answer score: 7
Revisions (0)
No revisions yet.