How to backup a SQL Server database

45 pts.
SQL Server 2005
SQL Server 2005 backup
Veritas Backup Exec 9.1
Hello, I wonder if you can help. We are just about to start implemetation of SQL 2005 Server. Can anybody, let me know how we go about backing up the database? We have veritas 9.1 but I guess we need to back-up live if possible. What is our best option?

Answer Wiki

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

Typically you set up a maintenance plan in SQL to do a backup “dump” to disk then you tell the Veritas software to back up that backup folder on the SQL server. That way work is not interrupted while SQL does its backup and then when veritas backs up that folder. Here is one article which may help you.

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; the complete script can be downloaded here.

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. For this example, I chose Northwind and Pubs as Figure 1 shows:

Figure 1: 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.
  • Garrybreed
    Thanks Technochic. This is great. Just what I was looking for, I clicked on the wrong Answer rating, if I could, I would have give you 100 out of 100,
    45 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: