Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers

up vote 6 down vote accepted

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

share|improve this answer
    
"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. –  Mark Storey-Smith Jul 26 '11 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 Jul 26 '11 at 14:38
2  
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. –  Mark Storey-Smith Jul 26 '11 at 15:49
    
@Mark Storey-Smith: fair enough, will add something to my answer –  gbn Jul 26 '11 at 16:27
add comment

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.

share|improve this answer
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. –  Jack Douglas Oct 21 '11 at 19:35
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Why would "people from the Oracle world think this way"? Hot backups are the only kind I remember ever doing. –  Jack Douglas Oct 21 '11 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 Oct 21 '11 at 22:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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