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?
Best Answer
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