Each night I perform a full backup of our single database, with the understanding that the database is not in use at 5am. So I do the backup of the DB, I don't back up the log, and then I copy the bak file to my local box/environment and load.
But lately I've had trouble with loading because of messages concerning transaction logs. The short term solution has been to include "WITH REPLACE" in the restore, but I'm struggling to learn how to make a backup at 5am that doesn't care about the state of uncommitted transactions - I just need a full "shapshot."
Here's my current backup instruction, and I'm sure it's not entirely accurate:
BACKUP DATABASE [MyDB] TO DISK = N'F:\Backups\MyDB.bak' WITH NOFORMAT, INIT,
NAME = N'MyDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
How can I do better? Again, I'm hoping to create a full backup such that I can easily load it to my local box each morning without worrying about logs or uncommitted transactions.
Thank you very much in advance - I'd appreciate any advice.
Without the exact error message it's difficult to diagnose the issue but here are some observations...
A "full" backup is a self contained complete version of your database. It is all you need to restore to another location.
The "WITH REPLACE" isn't a log backup related option; it is used to indicate that the existing destination database should be overwritten when you restore. If the database exists, you must use the REPLACE option. If it doesn't you can't use the REPLACE option.
The NORECOVERY option of the restore is used to indicate that additional restore files (differential or logs) still need to be applied. The RECOVERY option is used to indicate that this is the last restore file to be applied. For a simple Full database restore, use the with RECOVERY option.
The need (ability) to perform Log backups depends on the Recovery mode of the database; not to be confused with the RECOVERY option of the Restore statement. Databases in the Full or Bulk logged mode need to have their Log files backed up or they keep on growing. Databases in Simple mode can't have their log files backed up but they don't need it.
If your database is in full recovery model (sounds like it is) - then you MUST perform frequent transaction log backups. If the recovery model is Simple - then you cannot perform transaction log backups.
The first thing you need to do is figure out whether or not you need to include transaction log backups. Do not automatically switch to simple recovery model without determining whether or not the business can afford to lose a days worth of data in the event of a disaster. Note: depending on how manage the actual backup files - this loss could be catastrophic for the organization. If you are not moving the backup files to offline storage - and sending those to another site - anything happens to that data center/location and your company could lose everything.
The second thing you need to do is modify your daily backup process so it creates date stamped files - instead of overwriting the existing backup file. As soon as your backups start - you have lost the previous backup file - and if that is your only copy available when the backup job fails you no longer have a way of recovering your system.
Note: do not every use the above syntax for transaction log backups. The INIT statement will initialize the file and cause you to lose any possibility of recovering your system.
And finally - any issues you are having with restoring are probably due to the fact that the transaction log file continues to grow and you have a space issue. I would also question why you are copying the database to your system every day - that is not a normal process.
If FULL : you need to backup the log, I suggest every 10 minutes; in a disaster you will be able to recover to the time of the most recent Log backup, so 10 minutes loss maximum. That said, in many "disasters" (e.g. disk controller / memory / hardware fault) the database is trashed (and that will corrupt any FULL backups too), but not the logs, so you actually have the opportunity to take a "Tail" Log backup, before you restore, and you then have zero data loss Storing the physical Log files on a different disk spindle / controller to the Data files obviously helps in this regard (and boosts performance too)
If SIMPLE : you can only restore to the last 5AM backup. If you have a hardware fault that is corrupting your database it may be several days / weeks before you realise and you will have to go back to the last known good backup and repeat the work from then. (Of course if your database just pulls data from some external source you will be able to recreated the data easily, so this is not necessarily a bad thing - but if you are, manually, entering data at 100 PCs in the company all day long ... then your database should be in FULL Recovery Model and have Log backups every few minutes - and those backups to be copied to A.N.Another location as soon as the file has been created)
Couple of other thoughts:
Consider setting the Page Verification on all your databases to CHECKSUM (earlier versions only offered TORN PAGE, yours might even be set to NULL ) This will cause warning messages, and maybe application failure, as soon as database corruption starts. Might be annoying, but at least you'll get to know about it sooner!
Schedule a DBCC CHECKDB regularly (e.g. once a week) That will check database consistency - if your database is small you could use all the CHECKDB checking options. Note that checking a restored backup is equally valid, so you can do this on your restored Test DB, rather than using up CPU cycles on the Production server. Again, if the DB is small I doubt you will have a problem doing this on Production.
Can't remember what version it was introduces - maybe SQL 2008? - but there is a single [config] setting which will default all backups to be compressed. There is no good reason NOT to do this. You will save disk space on the backup files, of course, but also reduce I/O making backups (and restoring) plus probably copying to your Test PC too. Maybe [tape backup cannot further compress a compressed backup, Natch!] also reduce space on your backup media too (or bandwidth if you are backing up to the cloud)
I agree [very strongly] with @jeffw8713 advice that every backup file should have a unique name - e.g. including Date and Time.
I suggest keep "several" backup files online. We keep all the 10-minute transaction logs for 3 days, all the daily backups for 7 days, and all the Sunday backups for 4 weeks. So we can restore (to a TEMP DB for "a look see") without having to first find the tape, wait for the tape machine to be free, wait for the tape restore and THEN find that wasn't an old enough backup and we have to Rinse and Repeat!
Maybe? have a look at Minion Backup. They have a great drop-in backup solution, it will do all the 10-minute logs, daily-fulls, and a bunch of other stuff for you. My suggestion of Minion is because I think it has the easiest install-and-config of all the ones out there, so least-time-to-install and best-chance-of-default-setting-being-good-enough