SQL Backup Script Help

[ekirk4]

Ok, I'm very new to using the SQL language so I need some help. Basically, I am attempting to do a backup of some SQL files where I am using the MSDE version. The name of the database I am backing up is "avdata". Here is my code that I got from Microsoft KB article ID : 241397 :

--This Transact-SQL script creates a backup job and calls sp_start_job to run the job.

-- Create job.
-- You may specify an e-mail address, commented below, and/or pager, etc.
-- For more details about this option or others, see SQL Server Books Online.
USE msdb
EXEC sp_add_job @job_name = 'myTestBackupJob3',
@enabled = 1,
@description = 'myTestBackupJob3',
@owner_login_name = 'sa',
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend =2,
@notify_level_page = 2
-- @notify_email_operator_name = 'email name'
go

-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob3',
@step_name = 'Backup avdata Data',
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE avdata TO DISK = ''c:\msdb.dat_bak''',
@on_success_action = 3,
@retry_attempts = 5,
@retry_interval = 5
go

-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob3',
@step_name = 'Backup avdata Log',
@subsystem = 'TSQL',
@command = 'BACKUP LOG avdata TO DISK = ''c:\msdb.log_bak''',
@on_success_action = 1,
@retry_attempts = 5,
@retry_interval = 5
go

-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @job_name = 'myTestBackupJob3', @server_name = N'(local)'

-- Run job. Starts the job immediately.
USE msdb
EXEC sp_start_job @job_name = 'myTestBackupJob3'


What seems to be happening, is it seems to remember the previous SQL database I backed up and it saves it to the dat file, so it keeps getting bigger. Then it will error out if I don't change the job name. What is wrong in my code, to make it keep amending the previous databases I have backuped in place of this "avdata" one, and how to I make it so I don't have to keep chaninging the job name? Thanks,

Eric

 

[mrdenny]

ok, this is just way more than you need. If you are just trying to backup the database once, you don't need to create a job.

Simply use the backup database and backup log commands.

BACKUP DATABASE avdata to disk='c:\somefolder\file.bak'
WITH FORMAT
go

This will backup the database.

Jobs are used to schedule recurring tasks. For example you could setup a job to backup the database daily. But if you just want to backup the database on command, just use the BACKUP DATABASE command.

You'll also want to delete all the jobs you've added to the system.

If you want to overwrite the data in the backup file so that it only contains one backup you'll want the WITH FORMAT that I included above. Otherwise it will add each backup to the same file.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ekirk4]

Thanks Denny, I had a feeling it was much easier. I actually do want to set it up as a daily task for backup. Would you happen to have some good code for doing that? Thanks for your help, it is very much appreciated.

Eric

 

[mrdenny]

For a daily task, you'll want to use a job. In addition to the code above to create the daily job, you'll also want to use sp_add_jobschedule to add a schedule. See sp_add_jobschedule in BOL for the full syntax.

I'd recommend picking up a copy of the Developer edition of Microsoft SQL Server. It comes with SQL Enterprise Manager, which is the GUI for SQL Server, and makes creating and managing jobs much easier. It will cost you $50 from Fry's, Microsoft, or just about any retailer on the net.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)