Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.
A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.
Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.
For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.
For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.
Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.
Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)
As with all things in SQL Server, it depends.
First thing you need to do is make sure you understand what each type of backup does.
Books Online has all the gooey details, but here's my summary.
A FULL backup contains everything within the database. A DIFFERENTIAL backup is cumlative NOT incremental. In your example, if your database failed on the 12th, then you'd only need to restore the full backup from the 1st and then the most recent differential on the 12th, then followed by all the transaction log backups upto the failure. A TRANSACTION LOG backup is only needed for databases using the full or bulk-logged recovery model. If you're using the simple recovery model then transaction log backups are not needed.
Now that we've cleared that up...Designing a backup schedule really depends on how much data you need to recovery and how fast you need to recover it in the event of a diaster. I would recommend starting with a full backup each day. You can always reduce the frequency later. Remember the differential backup is cumlative since the last full, so depending on the amount change going on in your database the differential could be larger than the full backup after a few days. If you do a full backup each day, then you may not need to use differentials at all; however you could still do it once a day and schedule it at 12 noon. The transaction log backup only backs up the log. The frequency of the log backup will determine how much data you're willing to lose in the event of a failure. If you run your log backup every 15 minutes, then you would expect to lose up to the last 15 minutes of data that changed. 15 minutes is a good frequency, but every 30 minutes works perfectly for my environment.
As I said earlier, it all depends on your environment. After you've designed and setup your backup schedule, remember to test it on an alternate server. Practice restoring your full, diff, and log backups so that you know everything works like you designed it.
Books Online has some good info if you plan use Maintenance Plans, but if you really want flexibility then check out Ola Hallengren's backup scripts.
Best Answer
Backing up a database (
BACKUP DATABASE ...) backs up the pages from all of the database files (data and log) and upon restore those files will be recreated as a result.A full database backup does just that: It backs up the database in its entirety. There is no way to get around not backing up the transaction log file. If you are in full recovery mode, then you should ensure you are doing the proper transaction log backups so the log doesn't grow out of control. Outside of that, you are constrained with a full backup. A database consists of data file(s) and log file(s).
A workaround to this could be just to generate scripts from you database. When you right-click your database to generate scripts, you can script out the entire database. There is an option to script out both Schema and Data. Then on your other machine/instance you can run this script. It's not a traditional "backup" by any means, but it should get you what you want and you'll just have a script file as a result. Mind you, when the
CREATE DATABASEportion of the script runs, a log file will be created. You can't have an RDBMS database without a log file (or at least not on SQL Server that I know of).