Backups with SQL Server Management Studio

00:00
  -
  -
  -
  -
00:15
  -
  -
  -
  -
00:30
  -
  -
  -
  -
00:45
  -
  -
  -
  -
01:00
  -
  -
  -
  -
01:15
  -
  -
  -
  -
01:30
  -
  -
  -
  -
01:45
  -
  -
  -
  -
02:00
  -
  -
  -
  -
02:15
  -
  -
  -
  -
02:30
  -
  -
  -
  -
02:45
  -
  -
  -
  -
03:00
  -
  -
  -
  -
03:15
  -
  -
  -
  -
03:30
  -
  -
  -
  -
03:45
  -
  -
  -
  -
04:00
  -
  -
  -
  -
04:15
  -
  -
  -
  -
04:30
  -
  -
  -
  -
04:45
  -
  -
  -
  -
05:00
  -
  -
  -
  -
05:15
  -
  -
  -
  -
05:30
  -
  -
  -
  -
05:45
  -
  -
  -
  -
06:00
  -
  -
  -
  -
06:15
  -
  -
  -
  -
06:30
  -
  -
  -
  -
06:45
  -
  -
  -
  -
07:00
  -
  -
  -
  -
07:15
  -
  -
  -
  -
07:30
  -
  -
  -
  -
07:45
  -
  -
  -
  -
08:00
  -
  -
  -
  -
08:15
  -
  -
  -
  -
08:30
  -
  -
  -
  -
08:45
  -
  -
  -
  -
09:00
  -
  -
  -
  -
09:15
  -
  -
  -
  -
09:30
  -
  -
  -
  -
09:45
  -
  -
  -
  -
09:49

In the video, Understanding Backup Options, we covered how there are multiple ways to execute backups. And one of the ways listed was to manually execute backups from within a GUI or admin tool.

In this video, we’ll therefore look at doing just that from within SQL ServerManagement Studio

But before we proceed, a few words of caution.
First, manually implementing backups is typically only done for ‘ad-hoc’ cases where you need to test something out on a working copy of your database, or for cases where you need to send a copy of your database to someone else, and so on.
In other words, while you CAN backup databases manually, this isn’t the best option if you’re looking to create a disaster recovery plan.

Therefore, if that’s what you’re interested in, make sure to check out some of the other videos in this series to brush up on any concepts you’d like to learn more about, as well as to see how to implement disaster recovery plans using SQL Server Maintenance Plans.

Another word of caution that we need to address before commencing is that you also need to be very careful when making ‘ad-hoc’ backups for two reasons. The first is that backups are pretty complex operations – and it’s easy to shoot yourself in the foot (or worse) if you don’t know what you’re doing.
So always KNOW what you’re doing when you’re working on a production system – and until you know what you’re doing, practice in non-critical environments.

The second concern, stemming from that same complexity, is that well-functioning disaster recovery plans use full backups as a baseline for differential and/or transaction log backups.
Therefore, if you carelessly throw a new full backup into a regular cycle of backups, you can restart that baseline if you’re not careful.

So, with those caveats out of the way, let’s take a look at how to manually backup databases using SQL Server Management Studio.

To launch a manual backup process from within SQL Server Management Studio, you just need to right click on the database you want to backup, and then select Tasks, Backup.
From here you’ve got a single dialogue that lets you manage manual backup options across two different tabs, or pages.

On the General tab, the first option you’ll want to address is which database to backup – which you can select from a dropdown.

Then, in terms of what kind of backup you want to execute, three options are available from the drop-down– as long as your database is set to full recovery mode – otherwise only two options will be listed: full and differential.

Likewise, you also have the option to select partial and filegroup backups as well.
To see an overview of the different kinds of backups, and what they mean, make sure to check out the video Understanding Backup Options – along with the video Managing SQL Server 2005 and 2008 Log files to see how to set recovery models.
Otherwise, most of the time when you’re manually executing a backup you’ll be doing a full backup – so I’ll select that for this demo.

When creating a backup, you’ll also want to specify a name and an optional description. By default SQL Server Management Studio names your backup with the database name and backup type – and that’s usually good enough.
But, if you want, you can also specify a different name and a description if desired.
Otherwise it’s at this point that things start to get interesting, because you now need to specify where you want to direct, or store your backups.

Primary options include tape or disk – but, obviously, you need to have tape hardware attached in order for the option to be viable. Otherwise you can backup databases to a file pretty easily by just clicking on the Add button – which adds a new destination.

From here, you’re able to either select a File location, or a backup device – where a device effectively just represents a named file, or container, that you can load with multiple backups.
For all intents and purposes, devices and individual files are effectively identical from a functional standpoint.

The only real difference between the two is that devices are accessed from SQL Server by means of their logical name, where files are addressed, or accessed, by means of their physical path.
That, and while it’s possible to only ever store a single backup within a device, that’s pretty much unheard of, while it is a fairly common practice to do ‘one-off’ backups into files as we’ll see in this and subsequent videos.

Otherwise, since devices are logical abstractions, you’ll either need to create a new device or use an existing one whenever you want to use one for backup purposes.

And, just like devices, you can also create a backup file, and with that you’re then free to store as many backups as you’d like in that same file.
And those backups can be of all different types and even from different databases.
So, after creating a destination for our sample backup, note that we can also add multiple files if wanted.

To use multiple files for a single backup, you just need to initialize all of the files that you’re using as a single media set – meaning that the files can’t have any data in them already. Then, once initialized, you can treat the collection of files (or devices) that comprise your media set just as you would a single file or device.

As for WHY you’d want to do this, there are two main reasons. The first is that it divides your backups up over multiple locations which can make your backups more portable.
Second, if you’re writing to multiple files on multiple disks you’ll get better performance and throughput – especially if your database has multiple files itself that are spread over multiple disk drives as well – and we’ll look at this in one of our advanced videos on backup performance.

Otherwise, let’s switch to the Options page and look at the overwrite media section, and note that append is the default – meaning that if you select a backup destination that has already been populated with previous backups, that whatever backup operation you select will be appended.

Otherwise, if you select the overwrite option, then your backup operation will overwrite any backups already stored in the destination.
Which, obviously, could be a major problem if you were to accidentally overwrite say… a week’s worth of critical production backups.

As such, SQL Server gives you the option to specify when a backup expires – as a way to help prevent accidental overwrites.

So, in this case, if we specify that our backup set expires in 2 days, and execute a backup… we can wait for it to complete, restart another manual backup and look at the contents of our current backup destination and see that our backup exists – and that has been assigned a retention date.


Then, if we switch to the overwrite option, and check this checkbox, watch what happens when we click OK. Our attempt is blocked.


But that’s ONLY because we have the ‘check media set name and backup set expiration’ option checked.

Therefore, if we UNCHECK that option, which is OFF by default in SQL Server 2005 we’re able to overwrite and any backups we had in the device are now gone.

Likewise, if we also chose the backup to a new media set option … then the understanding is that we’re EXPLICITLY choosing to wipe out anything in the device … and regardless of whether or not the contents have a retention period, they’ll be overwritten with a new backup name and description.

So… to recap: backups will overwrite existing media when you either chose to ignore ‘CHECKS’ or when you explicitly re-initialize your destination. Otherwise, if you try to overwrite and you are checking, then any contents that haven’t yet expired can’t be overwritten.

Which leads us to a major point of confusion for most people when it comes to backups – which is that it’s pretty logical to expect that once a backup expires, that it will just be automatically removed from your backup location the next time backups are written. Sadly, that’s not the case – as retention periods exist ONLY to prevent you from accidentally overwriting critical data. And, in a subsequent video we’ll cover how you go about automating the removal of expired or older backups.

Let’s look at a couple of the remaining options – such as the option to truncate, or remove, inactive entries from the log when executing transaction log backups.

Initially it might seem dumb to even have such an option – as one of the reasons you perform backups is to help keep your log file’s size in check. However, the real reason you perform backups is to keep your data safe – and giving you the option to backup WITHOUT truncating your log gives you the opportunity to create a ‘safe’ backup of your data without jeopardizing your existing backup cycle.

So, in cases where you’re doing ad-hoc backups, you VERY WELL may want to uncheck this option – as it will help keep your disaster recovery plan flowing along perfectly.

Then, in terms of other options, you can also eject tapes if you’re using those, as well as verify your backup once complete – which is always a great option. And, with SQL Server 2005, there’s also a new bit of functionality that helps improve reliability by ensuring checksum validations during the backup process – which is also a great option to use as well.

In this video, we’ve looked at creating one-off, or ad-hoc backups. But if you really care about your data, you’ll want to use maintenance plans instead of relying upon manual backups – as maintenance plans offer a lot of increased versatility when it comes to creating viable disaster recovery scenarios.

Therefore, we’ll take a look at how to create maintenance plans in a subsequent video. Likewise, we’ll also look at the other half of the equation: restoring databases from backups, in a subsequent video as well. So check out those videos, along with the other videos in this series to learn more.