Creating a Backup Device
by William R. Stanek
Microsoft® SQL Server™ 2005 Administrator's Pocket Consultant
- Microsoft® SQL Server™ 2005 Administrator’s Pocket Consultant
- Acknowledgments
- Introduction
- I. Microsoft SQL Server 2005 Administration Fundamentals
- 1. Microsoft SQL Server 2005 Administration Overview
- 2. Deploying Microsoft SQL Server 2005
- 3. Managing the Surface Security, Access, and Network Configuration
- Getting Started with the Configuration Tools
- Using SQL Server 2005 Surface Area Configuration
- Configuring SQL Server Services
- Managing the Network and SQL Native Client Configuration
- Configuring the Shared Memory Network Configuration
- Configuring the Named Pipes Network Configuration
- Configuring the TCP/IP Network Configuration
- Configuring the Native Client Protocol Order
- Configuring the Shared Memory Native Client Configuration
- Configuring the TCP/IP Native Client Configuration
- Configuring the Named Pipes Native Client Configuration
- 4. Configuring and Tuning Microsoft SQL Server
- II. Microsoft SQL Server 2005 Administration
- 5. Managing the Enterprise
- Managing SQL Server Startup
- Using SQL Server Management Studio
- Managing SQL Server Groups
- Managing Servers
- Registering a Connected Server
- Registering a New Server in Registered Servers View
- Registering Previously Registered SQL Server 2000 Servers
- Updating Registration for Local Servers
- Copying Server Groups and Registration Details from One Computer to Another
- Editing Registration Properties
- Connecting to a Server
- Disconnecting from a Server
- Moving a Server to a New Group
- Deleting a Server Registration
- Starting, Stopping, and Configuring SQL Server Agent
- Starting, Stopping, and Configuring the Microsoft Distributed Transaction Coordinator
- Starting, Stopping, and Configuring the Microsoft Search Service
- Working with Full-Text Search
- Managing Full-Text Catalogs
- Managing Server Activity
- 6. Configuring SQL Server with SQL Server Management Studio
- Managing the Configuration with SQL Server Management Studio
- Determining System and Server Information
- Configuring Authentication and Auditing
- Tuning Memory Usage
- Configuring Processors and Parallel Processing
- Configuring Threading, Priority, and Fibers
- Configuring User and Remote Connections
- Managing Server Settings
- Managing Database Settings
- Adding and Removing Active Directory Information
- Troubleshooting Configuration Problems
- 7. Core Database Administration
- Database Files and Logs
- Database Administration Basics
- Creating Databases
- Altering Databases and Their Options
- Setting Database Options in SQL Server Management Studio
- Modifying Databases Using ALTER DATABASE
- Configuring Automatic Options
- Controlling ANSI Compliance at the Database Level
- Configuring Cursor Options
- Controlling User Access and Database State
- Setting Online, Offline, or Emergency Mode
- Managing Cross-Database Chaining and External Access Options
- Configuring Recovery, Logging, and Disk I/O Error-Checking Options
- Viewing, Changing, and Overriding Database Options
- Managing Database and Log Size
- Manipulating Databases
- Tips and Techniques
- 8. Managing SQL Server 2005 Security
- 5. Managing the Enterprise
- III. Microsoft SQL Server 2005 Data Administration
- 9. Manipulating Schemas, Tables, Indexes, and Views
- Working with Schemas
- Getting Started with Tables
- Table Essentials
- Working with Tables
- Managing Table Values
- Using Views
- Creating and Managing Indexes
- Column Constraints and Rules
- 10. Importing, Exporting, and Transforming Data
- 11. Linked Servers and Distributed Transactions
- 12. Implementing Snapshot, Merge, and Transactional Replication
- 9. Manipulating Schemas, Tables, Indexes, and Views
- IV. Microsoft SQL Server 2005 Optimization and Maintenance
- 13. Profiling and Monitoring Microsoft SQL Server 2005
- 14. Backing Up and Recovering SQL Server 2005
- Creating a Backup and Recovery Plan
- Selecting Backup Devices and Media
- Using Backup Strategies
- Creating a Backup Device
- Performing Backups
- Restoring a Database
- Restoring the Master Database
- Mirroring Databases
- 15. Database Automation and Maintenance
- Overview of Database Automation and Maintenance
- Using Database Mail
- Using SQL Server Agent
- Managing Alerts
- Managing Operators
- Scheduling Jobs
- Automating Routine Server-to-Server Administration Tasks
- Multiserver Administration
- Database Maintenance
- Managing Log Shipping
- Log Shipping: How It Works
- Preparing for Log Shipping
- Upgrading SQL Server 2000 Log Shipping to SQL Server 2005 Log Shipping
- Enabling Log Shipping on the Primary Database
- Adding Log Shipping Secondaries
- Changing the Transaction Log Backup Interval
- Changing the Copy and Restore Intervals
- Failing Over to a Secondary Database
- Using Mirroring and Log Shipping
- William R. Stanek
- Index
Early versions of SQL Server required you to configure backup devices before you could back up databases. With SQL Server 2005, you do not need to explicitly define backup devices. Nevertheless, backup devices do provide an easy way to ensure that you create backups that have the same file name and location time after time. By using consistent names and locations, you can more easily manage the backup and recovery process.
To create a backup device using SQL Server Management Studio, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Expand the server’s Server Objects folder.
Right-click Backup Devices, and then choose New Backup Device to open the dialog box shown in Figure 14-1.
In the Device Name box, type the name of the logical backup device. Use a short but descriptive name, such as Customer Device or Master Device.
If you have installed a tape drive and want to back up to the tape drive, select the Tape option button, and then use the related drop-down list box to select the target drive.
If you are backing up to a file, select the File option button, and then type the full path to the backup file you want to associate with this device, such as E:MSSQLBACKUPPERSONNEL.BAK.
Click OK. SQL Server will attempt to verify the backup file location. If there is a problem, you will see a prompt notifying you of any issues.
With Transact-SQL, you create backup devices using sp_addumpdevice. Example 14-1 shows the syntax and usage for this command, which uses many different arguments, including device_type, logical_name, physical_name, controller_type, and device_status. The device_type is the type of device you are using—disk or tape. The logical_name is the name of the backup device. The physical_name is the full path to the backup file. The controller_type is 2 for a disk or 5 for a tape. The device_status is either noskip, to read ANSI tape headers, or skip, to skip ANSI tape headers.
Example 14-1. sp_addumpdevice Syntax and Usage
sp_addumpdevice [@devtype =]'device_type',
[@logicalname =] 'logical_name',
[@physicalname =] 'physical_name'
[, {
[@cntrltype =] controller_type |
[@devstatus =] 'device_status'
}
]Usage
EXEC sp_addumpdevice 'disk', 'Customer', 'c:mssqlackupcust.bak' EXEC sp_addumpdevice 'disk', 'Customer on Backup Server', '\omegaackupscust.bak' EXEC sp_addumpdevice 'tape', 'Customer on Tape', '\. ape0'
-
No Comment