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 only takes a minute to sign up.
Sign up to join this community
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?
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
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.
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.
