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

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

[See an index of all bad habits / best practices posts]

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
 
BACKUP DATABASE dbname TO DISK = N'E:\wherever\dbname.bak' WITH INIT, COPY_ONLY;
 
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
RESTORE DATABASE dbname FROM DISK = N'E:\wherever\dbname.bak'
  WITH REPLACE, MOVE N'dbname_log' TO N'F:\new_path\dbname_log.ldf';

And here is the modify/offline approach (you should still take a backup before attempting this):

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;

Both of these are much safer, in my opinion. As long as you have a backup that you're comfortable resorting to (e.g. it isn't from yesterday or last Wednesday), then they're all relatively safe approaches, but the detach method is the one most likely to force you to revert to that backup.

SAN "Backups"

Many of the so-called "solutions" pushed by SAN vendors – snapshots in particular – are pipe dreams, and they could leave you in a similar kind of lurch. I'll leave that discussion for another day, but I will suggest you maintain your own native backups in addition to anything you're doing at the SAN level. Do have a read of Denny Cherry's article, "Should I Be Using SAN Snapshots as a Backup Solution?"

Conclusion

As far as I'm concerned, a file system backup of mdf/ndf/ldf files is NOT a backup you can rely on. If you have any hope of recovery, you need to use proper, native SQL Server backups. Anything less exposes you to a lot of risk, could be detrimental to your career, and may even jeopardize the viability of your entire company.

[See an index of all bad habits / best practices posts]

Comments ( 9 )

        • Omar I says:

          Aaron,
          Its been awhile since this post has been updated but I thought I give it a try.

          We are facing a scenario where we are getting a new server for the DB but not new or extra storage. We need to use the existing storage only. New server will be pointed to the old SAN storage with all the database files.
          There is a debate to have the old DB instance offline and point the new server DB instance to the old database files (attach). Do you think this is a sound approach since we are not moving/copying any of the mdf, ldf or ndf files or will you still suggest to still take a backup from old server and restore on new server?

        • Aaron Bertrand says:

          Hi Omar,

          If you are installing a new instance of SQL Server, is it exactly the same @@version and will it have exactly the same folder structure?

          Are you not already taking backups?

          My objection is normally about *moving* the files, sure, but also because using a backup shouldn't be intrusive or extra work. There's a grey area when you are just moving storage but there's always a risk when you detach.

        • Jeff says:

          Aaron,

          Here is a scenario that I'm hoping you will comment on to explain how it should be handled differently:

          I'm a relatively new DBA and we just did a disaster recovery test (that was my first time being involved in). It was up to me to make sure the SQL Server databases got up and running. Our network guys did a full restore of the database server, so all the SQL instances were restored from tape. But the big surprise to me was our tape backup system is not backing up any of the .mdf or .ldf files (because it doesn't back up "in-use" or "on-line" files). I have nightly jobs that create backups of all my databases, including the system dbs, but they are all .bak files, and those were restored from tape. But without the master.mdf (.ldf) I was not even able to open SSMS, so the .bak files were useless. So to get around this I "cheated" and copied the master, msdb, model, and temp .mdf and .ldf files from the production server to the DR server and put them in the instance path on the DR server. I know that this is not a valid DR method but it worked and it was certainly a learning experience. So now I'm wondering what would be the expected steps to recovery if I didn't have those .mdf and .ldf files to allow me to open SSMS?

          My idea is to create a script that I schedule nightly to shut down the SQL Server service, copy the system database .mdf and .ldf files to a backup folder – this way the tape backup system will back those up, and then restart the service. Then if there is a true failure, I can get everything restored from tape, copy the .mdf and .ldf files to the instance path, open SSMS, do a restore of my user databases from .bak files and then be off and running.

          How would you do a recovery in this scenario?

          Thanks.

        • Aaron Bertrand says:

          Lack of .mdf files do not prevent you from opening SSMS. They can prevent the instance from starting, which means SSMS can't *connect* to that instance.

          Personally, I do not like the idea of your nightly "shut down the server and copy all the mdf/ldf files" job at all. You got lucky this time that the copies of those files were valid; this is not guaranteed. At all. I would much rather take proper backups and, in the worst possible case, have to install a new instance of SQL Server in order to restore them. If you're relying on just mdf/ldf files, and they get damaged, guess what? They're no good to you on the existing instance or a new one.

          What you should probably consider doing is having a warm, secondary stand-by. You can maintain changes to jobs and logins, and keep your user databases up to date using log shipping, which effectively tests your full and log backups constantly. Should anything go wrong on the primary you can always switch over to the secondary, and it will involve not much more than changing the connection strings of your apps.

          Trust me, copies of mdf/ldf files are not part of a sane DR plan.

        • Andrew says:

          Nicely written article, trying to clear in short. After a long search I found an article here you can know what are the reasons that affect the MDF and LDF files and how to overcome to affected files in SQL Server database. http://sqltechtips.blogspot.in/2015/11/introduction-mdf-ldf.html

        • bogdan says:

          I appreciate the intention here but you should've stated more clearly that all this FUD strongly applies when MDFs/LDFs are snatched as the MAIN backup procedure.

          The following statement's generality is unsupported by the hundreds of years of DBA experience I've had access and contributed to: "Relying on the service to detach your files cleanly or to shut down gracefully is risky. …". We all have horror stories (hello engine bugs) but they're far from casting a shadow on SQL Server's general ability to restart or detach/attach dbs (nobody would install & restart for Windows Updates and everybody would freak out after a power failure).

          Here is a scenario where snatching MDFs/LDFs is preferred: Temporary SAN snapshots of VM disks to get data from production to QA/staging environments.

          In this case, the content of MDFs/LDFs would be as if a power failure occurred, which SQL Server is specifically designed to handle. It will perform db recovery and then you have a valid point in time snapshot of the production db. The efficiency with which multi-TB dbs can be taken from production with this method is orders of magnitude greater than through native backups, not to mention that as a DEV you don't need to deal with the DBA just with the infrastructure admin ;).

          Of course there are pitfalls (desync with meta-data in system dbs) but the resource usage efficiency is much better (it's FAST and you need a LOT less disk space) and the simplicity can be tremendously higher for special cases like trying to capture point in time snapshots across multiple large dbs used simultaneously by enterprise apps which don't support marked transactions.

          In conclusion, I think you're mostly right when saying that snatching MDFs/LDFs is not a valid MAIN backup but it has its uses and so your introductory statement "or restore a copy of it somewhere" is too broad, as is the implication that SQL Server's restart or detach/attach operations are risky.

        • Aaron Bertrand says:

          Sorry, but I stand by my statement, and it is still risky – not because of engine bugs but because of the things that humans will do. And since SQL Server *does* support marked transactions, I think backup and restore to a point in time is *far* safer than shutting down the service and copying all of the files for a set of databases. You're still going to have to cross your fingers and hope they all attach ok, whereas with proper backups (which you should already be taking), you won't. I also disagree about speed and disk space – if you're already taking backups, there's no speed to consider, since the files you need already exist. Also backups only contain the data, not all of the empty space, and on top of that, they can be compressed, whereas detached and copied MDF/LDF files cannot.

        • Anonymous says:

          Here's a relevant story.

          Picture a small company with 6 DBAs (2 professionals who had just quit, 1 remaining professional, 1 barely functional human hired through nepotism, and 2 seat warmers on six figure salaries with impressive resumes but somehow completely incapable of doing anything in their entire year-long stint allegedly because their micromanager never let them do anything).

          One night despite all of the above resources the IT manager decides to be a lone cowboy and shut down the instance to move the files of ~50 databases to another disk.

          By the next day, ~50 customers were completely dead in the water and unable to run their business, impacting an approximate 5,000 employees. It's at this point that the IT manager goes running to one of the 6-figure seat warmers, who instead of attempting to do an ALTER DATABASE MODIFY FILE decides that the only safe thing to do is to restore every single customer from backup.

          Because they don't believe in the power of scripting (if it isn't .NET it isn't real) this all had to be done by hand. It took the rest of the day and night to get everyone back online, with a bit of missing data and all.

          What I love about this story is that it has everything; arrogant managers, incompetent staff, highly paid DBA frauds, inexplicable policies, and the customers who somehow entrust their million dollar businesses to them and suffer for it at the end of the day. Plus of course I don't work there so that's the best bit.

        • Chris Wood says:

          Aaron,

          Very timely for me. We are moving our database log files to a different drive letter on availability group servers. Until I read this I was thinking of using drop from availability group, detach, copy log file and attach and then backup and restore on the other node and sync.
          Changed this to backup, drop from availability group, restore, backup, restore on other ndoe and sync.

          Chris

        Leave A Comment

        Your email address will not be published.

        This site uses Akismet to reduce spam. Learn how your comment data is processed.