Backing up SQL Server 2005 databases to a tape drive

Backup to Tape
SQL Server 2005
SQL Server database
I am currently running SQL Server 2005 and I’ve set up a maintenance plan that backs up my databases to the disk. I'd like to change that and backup my databases to a backup tape drive that's attached to a backup server. Can this done, and if so, how?

Answer Wiki

Thanks. We'll let you know when a new response is added.

I am sure you know how to change and modify the backup jobs already but the information below will help others that do not. Your issue to get your backup files on tape is a easy one. Create the Maint. Plan that backs up your databases to a central location on your network. Then point your backup job to grab that directory. Those files will be .bak files and once they are on tape you can restore them.

The SQL Server 2005 Maintenance Plan feature has been significantly modified in comparison with SQL 2000: now it utilizes new Integration Services. Also, creating database and transaction log backups is not as clear as it was in SQL 2000. This article does not describe all available SQL Server 2005 backup features or provide some tricks dealing with them; instead, it offers solutions for the most commonly used backup jobs.

Using SQL Server 2000 Backup Job Scripts

If you created backup maintenance plans in SQL 2000, you probably noticed that the key element of the backup job was the xp_sqlmaint extended stored procedure, which used the sqlmaint utility. Despite the fact that Microsoft has deprecated both sqlmaint and xp_sqlmaint, and is planning to remove them from future versions of SQL Server, they are still here and they work well. So, you can take your existing SQL 2000 backup jobs, modify the server and database names, the backup files folders, the output and report files, etc., and run those scripts on your SQL 2005 server.

In case you do not have those scripts, here is an example of a database backup job that uses the xp_sqlmaint procedure. It runs a full database backup of the AdventureWorks database on the dba02\sql2005 instance to the shared dbbackup folder on the server02 server, deletes backup files older than 4 days, and stores a report into the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder on the local database server. Below is the code fragment that utilizes the xp_sqlmaint procedure;

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = N'EXECUTE master.dbo.xp_sqlmaint N"-S "dba02\sql2005" -D "AdventureWorks" -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG backup_aw.txt" -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "\\server02\dbbackup\sql2005" -CrBkSubDir -DelBkUps 4days -BkExt "BAK""',
@database_name = N'master',
@server = N",
@database_user_name = N",
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 4, --Overwrite output file
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\aw_backup.log',
@on_success_step_id = 0,
@on_success_action = 1, -- (default) Quit with success
@on_fail_step_id = 0,
@on_fail_action = 2 -- (default) Quit with failure

Creating a SQL Server 2005 Maintenance Plan

If you are going to use new Maintenance Plan features, I strongly recommend installing SQL Server 2005 Service Pack 1 first. Among the new features and improvements that are included in SQL Server 2005 Service Pack 1, there is a fix for the previously existing discrepancy between Back up database task, which allowed storing database backups in separate folders, and Maintenance Cleanup Task, which could not delete backup files from those subfolders.

In order to create a new maintenance plan in Management Studio, first connect to the target server using Windows Authentication, then right-click on the Maintenance Plan folder in Object Explorer, select New Maintenance Plan, and enter the plan name. As a result, a Maintenance Plan design panel will appear on the right, and a toolbox with available Maintenance Plan Tasks will be displayed on the left. Click on the Connection button to verify that the current connection uses Windows Authentication (recommended by Microsoft). Currently connected Windows user will become the owner of the job created by this maintenance plan.

The first step in creating a database or transaction log backup is to drag and drop Back up database task from the toolbox to the design panel. Then double-click on that item to set the following necessary properties:

* Databases: Click on the dropdown field to bring up the database selection window.

* Backup type: choose Full.
* Destination parameters:
o Back up to — choose Disk.

Make sure that the Create a backup file for every database option is selected and the Create a sub-directory for each database box is checked.
o You can use the default destination folder or specify your own. For this example, the network folder \\server02\dbbackup\sql2005 has been selected.
o Backup file extension — make sure that its value is bak without a leading dot.
o Check the Verify backup integrity box.

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • OpenSystemsStorageGuy
    I would like to point out that while this is an excellent answer and is very helpful, I believe that the original question mentioned a direct attached tape drive. If he has a network backup program that can catch his database backup, this will work, but if not, he needs to know how to set up SQL to use a direct attached tape drive.
    545 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: