One of our collagues told me that they are doing "cold" backups for SQL Server so that they are shutting down services and then taking a backup somehow. I think this is all wrong! Am I right? I think that they should take full bakcups, differential backups and log backups, while their databases are on FULL mode.

They are also having a problem when they start SQL Server services after a backup, they cannot find .mdf files. Do you have any idea why this is happening to them?

4 Answers 4


Never heard such rubbish.

FULL or BULK LOGGED mode means you require log backups. SIMPLE means you don't. You always require full backups.

Copying the MDF and LDF files isn't a backup and can potentially lose** you data if you are in a transaction as you shut SQL Server down.

If the mdf files are missing it means they aren't there because of finger trouble or this "backup" method. You can't lose mdfs unless someone removes the files or removes a drive when SQL Server is shut down

** Edit:

By losing data I mean "open transactions won't be committed". Typically, client code won't handle a retry and, say, log the error but not the data to be written.

Any data in committed transactions, no matter how old or new, will be safe

  • "can potentially lose you data if you are in a transaction as you shut SQL Server down" is not true. Even killing the SQL service would not result in data loss. Commented Jul 26, 2011 at 14:34
  • @Mark Storey-Smith: do you mean "all transactions are committed on shutdown" or "not committed so it isn't lost". If the latter, then data being written will be lost if the client can't handle it. If you say the former, you're wrong. COMMIT is determined by the client or the executing SQL code: not the server itself.
    – gbn
    Commented Jul 26, 2011 at 14:38
  • 4
    Even in the context of this bonkers back up approach, one assumes they are at least cutting off clients before shutting down, so of course I meant the latter. My suggestion is that to the non-dba your comment on losing data implies that shutting down the server risks data loss, which conjures up all manner of scary implications that are not applicable. Commented Jul 26, 2011 at 15:49
  • @Mark Storey-Smith: fair enough, will add something to my answer
    – gbn
    Commented Jul 26, 2011 at 16:27

Although less than ideal, cold backups are a perfectly legitimate way to backup a database. The assumption is that the database has been shut down, hence the "cold" nomenclature versus a "hot" or "warm"(1) backup where the database is online.

The down side of a cold backup is you're copying the entire file structure of the database regardless of how full the files are. So if you have a 1TB database that only has 10MB of data in it, you're going to be copying 1TB worth of data files. The hot backup approach works a different way in that only those pages in the database that actually have data are backed up. There's also some level of compression that hot backups support. Generally speaking, hot backups are faster and smaller than cold backups.

In either case, you're going to end up with a consistent backup.

(1) Warm backups are kind of special case depending on the platform. Some database platforms support "quiescing" of the database while it is online, meaning write activity to the data files is halted. You can still query the database, and at the same time you can effectively do a cold backup of the database files. Why would one follow this approach? Some storage subsystems (like EMC) support snapshot-ing a volume over to another volume, effectively mirroring the first volume onto the second almost instantaneously. You can use this to mirror your production data over to a development environment or to a standby system. You need to put the database in a consistent state for the duration of this snapshot process, which is usually only a few seconds. So a "hot" system plus a "cold" backup equals a "warm" backup.

  • 1
    the "down side of a cold backup" is that you have to shut your database down at all, and that that you lose your cache. Commented Oct 21, 2011 at 19:35

If you have the downtime then stopping SQL Server and copying the mdf and ldf files would be one way to backup your database. Being that SQL Server has stopped it has "cleanly" shutdown the database so it is a good state to be reattached. Any active transactions can be rolled back or rolled forward when SQL Server reads the transaction log.

Most folks will copy the MDF and LDF files when they are doing some type of maintenance on their server (a server move, reallocating storage space, or hardware is being serviced). I have also done this as an option to get a replica of production into my test environment. I had the time to copy the files when we had the services stopped for server maintenance.

If they are "moving" the files instead of "copying" the files that could be the issue. When SQL Server starts back up it remembers the last location the files were located in, so if they are not there.

You can read numerous articles on better backup solutions. There are benefits in doing SQL backups versus copying the physical files each time. One being full backups will aid in controlling the database size, if the database is in FULL recovery mode. Then the main one being it does not require downtime.


Some people go this route, specifically people from the Oracle world think this way. In SQL Server you shouldn't ever need to stop SQL to do a backup. Simply use the BACKUP DATABASE command to backup the database doing FULL, DIFFERENTIAL and LOG backups as needed depending on the recovery mode and backup requirements.

  • Why would "people from the Oracle world think this way"? Hot backups are the only kind I remember ever doing. Commented Oct 21, 2011 at 19:31
  • I've heard from a lot of Oracle people that cold backups are the only way they take backups. Never heard people using other platforms going with this method. Didn't mean that all Oracle people do this, just some. Those people would of course be totally wrong.
    – mrdenny
    Commented Oct 21, 2011 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.