Good morning. Today I am going to talk about the second most important task a DBA is responsible for….backups. What is the most important? That would be restores and will be next week’s topic. If you cannot restore your database, you will be very sorry. Seriously, if you cannot recover the data that your company needs, you may need to look new employment since your company might go out of business.
This will be a longish post, since there is much to cover and it is an important topic.
The first thing you need to do is find out when the last time your databases have been backed up. Running the below query, you can see that the WideWorldImporters has never been backed up.
SELECT d.NAME AS databasename ,max(b.backup_finish_date) last_backup FROM sysdatabases d LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = d.NAME GROUP BY d.NAME
Uh oh….that is our companies main database that stores all of their customers and their sales and order data. If that data was lost, it would be very difficult to recreate and to continue on as a business. Your customers would also lose confidence in your company; if you cannot protect your important data, how can you protect theirs (credit card info, emails, passwords, etc.)?
There are many ways to backup your databases, you can use the Maintenance Plan wizard (which we will do here), you can download scripts off of reputable sites like Ola Hallengren’s great site: https://ola.hallengren.com/sql-server-backup.html which also has other tools that are very useful, or you can create your own.
Before you backup your database, you need to know your companies tolerance for data loss. You will need to have a talk with management to see what kind of data loss is acceptable. Some companies are fine with losing 24 hours of work, but most would prefer to lose a minimum or none. Our WorldWideImporters say they are comfortable with losing up to 30 minutes of work. The lower the amount of time they want to lose, the exponentially the costs go up.
Since they only want to lose up to 30 minutes of data, the database will need to be in the Full recovery model. This means you need to take a full backup daily and then you will also need to take transaction log backups every 30 minutes. This way if a disaster strikes, you would restore the full backup and then restore the transaction logs since this backup to get your database up to the point of the last transaction log backup.
There is also the Simple recovery model. This does not require transaction log backups, but you can only recover up to your last backup, so this model does not work for managements requirement of only losing 30 minutes of work. Bulk-Logged is the other recovery model, but will not be discussed here.
Lets confirm our database is in the correct recovery model: right click on your database -> click properties -> and then click Options (see below). This database is currently set to Full.
Lets create a new Maintenance Plan
In object explorer, right click on Maintenance Plans and click on Maintenance Plan Wizard.
That brings up the following wizard, click next.
This brings up the next step. Put a name in for what you want to name your job. I put: Backup Job. Click next
Select what kind of task you want to perform. We are going to pick: Backup Database (full), click next.
We are going to keep the task order default, so just click next.
On the General tab: Click the database dropdown and pick the WorldWideImporters database. Leave the “backup up to” set to disk.
On the Destination tab, for the folder option, put in the directory that you want to save your backups to. This directory must exist, or the wizard will not let you continue to the next step. I usually leave the backup file extension the default bak.
On the Options tab for this exercise, we will leave everything the defaults and click next.
The next screen will write the results of the job to a file. You can also have the report emailed to you. Click Next.
This shows all of your options that you selected during the creation process so you can verify them. Click Finish.
This runs the creation scripts and shows the results of them. This process just creates the plan and does not schedule the job. That will be created below.
Time to schedule the backup job
The job has been created, but now we need to setup a schedule for it to run. Double click the Backup Job.Subplan_1 item under Jobs.
Click the schedules option on the left side and then click new on the bottom of the screen.
You can put in whatever you want for the name, I put in schedule 1 below. If you create more elaborate plans, you will probably put step1, step2, or what the process is doing so you can keep track. This is setup to run every night at 11:00pm.
In real life you would want to be emailed if the job fails, but we are going to leave it blank for now. Click OK, and your job is now scheduled and will kick off at 11pm.
We can test the job by right clicking the Backup job and clicking: start job at step.
This runs the job, and will alert you if any errors occur.
Below, the job finishes and you can see the file gets created in the backup directory.
We are not done yet. Since we are running the full recovery model, we need to backup the transaction logs now. These record all of the changes made to the database and will be used to reconstruct the database if we need to restore. Since WorldWideImporters said they can live with 30 minutes of data loss, we will now create and schedule a transaction log backup. This is similar to what we have done above, just a couple different options you need to select.
If you ever wonder why your transaction log ever grows bigger than your database file size, it is more than likely an issue where you have a database in full recovery mode, but a transaction log backup has never been taken. The log will continue to grow and grow until you run out of disk space if you never take a back up. Another posting will show you how to recover from that issue if it ever happens to you.
Create a transaction log backup job
Run the maintenance plan wizard again, this time select: Back up Database (Transaction Log).
Call it transaction log backup, so you can differentiate it when you look at the jobs under object explorer.
On the General tab, pick the database you want to create a transaction log backup for. Only databases running in the full recovery model will show up in the listing now.
Pick which directory you want the transaction logs to go to. I am picking the same directory as the main backups and using the default extension of trn.
On the options tab, I just let everything default like we did for the full backup.
You can have the job results emailed or saved to a file like before.
Finishing the job creation process shows success. Now we need to schedule the job to run.
Scheduling the Transaction Log Backups
Double click the Transaction Log Backup.Subplan_1 item below jobs in the object explorer.
Since WorldWideImporters agreed to 30 minutes of data loss, we schedule the job for every 30 minutes like below.
We can test the job the same way we tested the full backup. Right click the Transaction Log Backup.Subplan_1 item under jobs and start job at step.
This will run the job and alert you of any errors. You can see below, the file with the bak extension is our full backup and the file with the trn extension is our transaction log backup. This file is large because I had the database running in the full recovery model for a few weeks before making the first transaction log backup. As seen in the next screenshot, I ran the job again, and the transaction log backup is much smaller. The transaction log backup sizes will vary in size depending on how busy your database is during that 30 minute window between transaction log backups.
You have now successfully backed up your database! You would want to move these files to another non local drive to keep them safe in case the machine blows up or back them up to tape or disk. These are your life preserver, take care of them.
The next step is to restore it so you know you can protect the companies most prized asset….its data.
Next weeks post will show you the most important task, how to restore your data.
Thanks for reading.
Jim Salasek's SQL Server Blog 