10: Disaster Recovery in VS .NET

Printer QR in VS .NET 10: Disaster Recovery

When using a tape backup device, the tape must be physically connected to a computer running SQL Server when using SQL Server s backup and restore capabilities We can t back up to a remote tape device, but we can back up to a remote disk device using the UNC path If using third-party tools (and many good ones are available), we don t have this limitation However, the test will focus on SQL Server 2005 capabilities

SQL Server identifies backup devices using either a physical or logical device name The physical name is the name of the path and the file used by the operating system to identify the backup device (such as d:\SQLBackups\AdventureWorksbak) The logical name is the alias used within SQL Server to identify the physical backup device As an example, we could create a backup device with a logical name of AdventureWorksBackupDevice that points to the physical name of the file at d:\SQLBackups\ AdventureWorksbak Either the physical or logical names can be used in backup and restore statements

Backup devices can be created in SSMS or via T-SQL statements A Backup Devices container exists within the Server Objects container in SSMS By right-clicking the Backup Devices container, selecting New Backup Device, and following the wizard, you can create a backup device To create backup devices in T-SQL, the sp_addumpdevice system stored procedure is used In the following exercise, we ll create a backup device that will be used in other exercises in this chapter Exercise 102: Create a Backup Device 1 Press WINDOWS+E to open Windows Explorer 2 Browse to the root of C:, and create a new folder named SQLBackups Close Windows Explorer 3 Open SSMS and connect to the default instance of SQL Server 4 Double-click the Server Objects container to open it Right-click Backup Devices and select New Backup Device 5 In the Backup Device window, enter AW_SSMS_BackupDevice as the Device Name 6 Click the ellipsis button next to the filename In the Locate Database Files dialog box, browse to c:\SQLBackups In the File Name box, enter AW_SSMS and click OK Your display should look similar to Figure 10-12 Click OK NOTE SQL Server automatically senses tape devices If a tape device is not available on your system, the selection for a tape device as the destination will be dimmed

7 Open a new query window in SSMS 8 Enter the following script to create another backup device in the same folder:

EXEC sp_addumpdevice 'disk', 'AW_TSQL_BackupDevice', 'C:\SQLBackups\AW_TSQLBak'

9 Right-click the Backup Devices container and select Refresh You should see both backup devices in this container

SQL Server 2005 introduces a new concept to SQL Server 2005 known as mirrored media sets In this section, we ll discover what a mirrored media set is and define some other relevant terms

The backups on a set of one or more backup media make up the media set If multiple backups are spread across multiple tape drives, all the tapes make up the media set Since tape drives are slow, it is possible to add multiple tape drives and write to them in parallel The media set is the ordered collection of backup media where we write a single backup If three tapes were in the media set, the backup would actually be contained on three different tapes Media sets can be on tapes or disks, but not both within the same media set Take a look at Figure 10-13 It shows a media set of four tapes: two tapes created on tape drive 1 and two tapes created on tape drive 2

New to SQL Server 2005 is the capability to mirror backup media If you re familiar with RAID-1 (mirroring) with disks, this works pretty much the same way Whatever is written to one tape in the mirrored media set is written to the other tape If one tape fails, we still have a copy on the other tape within the mirrored media set