Create a Logical Backup Device to Store Your Backups in SQL 2005
Description:
Probably the most convenient method to perform database backups in SQL Server is through the use of a backup device. A backup device has a logical component (which is a device name that you refer to when doing the backup) and a physical component (the physical path associated with the device). You can create backup devices to backup to disk, named pipe, or to tape. The examples in this section back up to a local drive on disk. Keep in mind that these examples are for illustrative purposes only. Your backups should not go to the same disk that your data or transaction log files reside on.
(back to top)Requirements:
- As with all of the exercises on this site, do not perform this exercise on a production server - perform this exercise in a closed development environment
Setup:
None required
(back to top)Video:
We have a video of this exercise which shows step by step how to create a backup device in SQL Server 2005 using the GUI in SQL Server Management Studio, as well as using T-SQL.
(back to top)GUI Steps:
1. Within SQL Server Management Studio, go into Object Explorer. Expand Server Objects, right click on "Backup Devices", and select "New Backup Device" as shown below.
2. The "Backup Device" window pops up. Enter a logical name for your backup device, and optionally a file path, then click ok. As you can see below, we selected testbackupgui as our backup device name and accepted the default filepath. You can choose a filepath that is either local to your computer (for development or test purposes) - or you can select a backup device located on a network share. You can even select tape backup if you have access to a tape backup device.
3. As you can see below - our backup device has been created with the name that we provided in the previous step. Our backup device has been created and can be used for backups.
T-SQL Steps:
1. You can create a backup device through T-SQL by using the system stored procedure sp_addumpdevice. There are only 3 parameters that you need to worry about - the device type (tape or disk), the logical name of your device (whatever you want it to be), and the physical path to the location of your backup device. Your backup device can be located on your local computer (for local testing or development purposes), on your network, or a tape drive could also be your backup device. Below, we have chosen to backup to our local disk (don't do this in production) and we have provided the name testDeviceTSQL as our backup device name.
2. As you can see below, the new backup device can be seen in object explorer along with the device that we created during the previous gui exercise.
