The slides for the SQLServerCentral webinar #13 are available here on the blog. Download them from the link below
There aren’t notes in there, but if you have questions, let me know. The recording of the webinar will be up in the Training section of SQLServerCentral next week.
I saw a post recently where someone was asking about the restore sequence for a series of backups. The scenario was this:
What do you restore?
The short answer is that it doesn’t matter. If you use NORECOVERY (Always use NORECOVERY) and restore the logs in order, SQL Server will sort things out. If the transactions from backup 2 (the log backup from 3:05) are in the full backup, they won’t be applied twice and the system will let you know.
The same thing occurs for the second log backup. This is why SQL Server uses the Log Sequence Numbers. They ensure that SQL Server can track which transactions occurred when and in which order.
If we are wondering when the full backup is complete, or at what point during your system’s life is the full backup going to return you to, it’s easy.
It is consistent as of the time when the data reading portion of the full backup is complete. We don’t necessarily know when that is, but at that point, the full backup will copy enough log records to get consistent to that point in time. If it’s a lot of log records, it’s possible that this is quite a bit of time before the backup completes and the timestamp goes on the backup file.
This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.
By default databases in SQL Server are created with the Full Recovery Model. That means that without log backups, the log will continue to grow and grow until it hits the growth limit you’ve set, or it fills the disk. I see questions on this constantly at SQLServerCentral from people who have a 10MB database and a 653GB log file.
A log backup will mark the transactions in the log as backed up, and that space can be re-used. The log backup aids in space management and also provides recovery to points in time in between full backups. With that in mind, you need to do a few things.
As soon as you create a database, and you create a full backup schedule, schedule log backups as well. It’s easy to schedule a single daily log backup along with your full backup. They don’t block each other, and for most non-production systems, this works fine.
Note that the total space used for the log backups is the same whether you schedule 1 a day or 86,400 a day. There is a slight overhead to each file, but essentially the space used is the same. However backups scheduled more often result in a smaller log files size for the LDF.
This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.
Most people might answer this with “every day”, or “as often as needed”, but those are a little nebulous, and not necessarily correct.
I do recommend that you backup as often as you can, which is every day for most people. Those with large databases might go once a week, or even once a month, but you want to get a full backup periodically. If you have a really large database, say 50TB or greater (in 2011), you might opt for a yearly SAN snapshot of some kind, but you need some full backup as a base.
So when do you backup your database? Here’s a list of some times you might think about:
That last one might throw people, but I’ve seen more than a couple situations where someone recovered a database in a DR situation and never setup backups. And there was another failure.
Whenever you create a database, either from scratch or after a restore, make a backup.
* Note that if you have Enterprise Edition or above, you can use Database Snapshots to substitute for a full backup in some of these situations.
