Do you test your backups?

By Greg No comments

So you’ve worked out how often to take your backups, what sort to take, and where to store them. You’ve created your jobs and have them running regularly, now what? Now you need to test that they’re working, and not just now, but forever into the future. There is only 1 way to know that […]

Where to store your backups

By Greg No comments

When you do a backup, whether it be full, differential or log, SQL creates a backup file that you have to store somewhere. You’ll need to decide for yourself where to store your backups based on your requirements for restoring. To work out where to store a backup, we need to work out what we’re […]

How much data can you lose?

By Greg No comments

If you manage data for a business, the biggest risk you should always consider is how much of this data am I going to lose if a disaster strikes? Your manager is always going to want to have 0 data loss, that everything saved is kept no matter how big the disaster. However, they are […]

Types of SQL Backups

By Greg No comments

SQL Server has 3 different types of built-in backups: Full, Differential and Log. When working out how you’re going to backup your database, you need to understand what the 3 different types are intended for, so you can work out what you need to do. Full A full backup is the simplest type to understand […]

Why Backup SQL Server?

By Greg No comments

I’m going to start a new series on backing up SQL Server. This was inspired by @Kendra_Little in one of her Dear SQL DBA podcasts In this series I want to work through why we need backups, how to do backups, and the different types of backups that you could do and why you would […]

Cleanup Backup History

By Greg No comments

While looking at long running queries today I noticed that one of our worst performing queries was a monitoring tool that checked when the last backup ran. SELECT database_name = DB_NAME(DB_ID([database_name])) ,         type = [type] ,         backup_start_date = MAX([backup_start_date]) ,         backup_finish_date = MAX([backup_finish_date]) FROM    msdb..backupset WHERE   DB_ID([database_name]) IS NOT NULL GROUP BY DB_NAME(DB_ID([database_name])) ,[type] I had […]

CDC Jobs missing after Database Restore

By Greg No comments

We use Change Data Capture (cdc) in our production environment on a few tables so we can do some asynchronous processing of data. Our developers have a pre-deployment environment which is a nightly restore of our nightly backups (this has an added bonus of testing our nightly backups). Usually the testers don’t care about testing the CDC […]

Failing Mirror Backups

By Greg No comments

Failing Backups! Is there anything more worrying for a DBA? Recently we have been having multiple failures a week for our full and differential backups. We use Ola Hallengren’s backup scripts, and unless you are using a 3rd party product, you should be using Ola’s scripts as they are amazing and so simple to use. We […]