Backup script for msde databases with tsql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

MSDE (2000)

Backup script for msde databases with tsql

Author

Topic

denise
Starting Member

14 Posts

Posted - 2006-08-03 : 12:10:11

I'm trying to run this script as a scheduled job to back up the msde database daily. It runs ok if started immediately is uncommented, but won't run as scheduled job. Can anyone help me figure out what I'm doing wrong? Thanks for any ideas.

USE msdb
EXEC sp_add_job @job_name = 'mybackupjob',
@enabled = 1,
@description = 'mybackupjob',
@owner_login_name = 'sa',
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
-- @notify_email_operator_name = 'dmcmillan@mgh.org'
go

--add job step to backup BESMgmt
USE msdb
EXEC sp_add_jobstep @job_name = 'mybackupjob',
@step_name = 'Backup BESMgmt data',
@subsystem = 'TSQL',
@command = 'backup database BESMgmt to disk = "c:\Program Files\Microsoft SQL Server\MSSQL\Backups\BESMgmt_db.BAK" WITH INIT, RETAINDAYS = 0',
@on_success_action = 3,
@retry_attempts = 5,
@retry_interval = 5
go

--add job step to backup master
USE msdb
EXEC sp_add_jobstep @job_name = 'mybackupjob',
@step_name = 'Backup master data',
@subsystem = 'TSQL',
@command = 'backup database master to disk = "c:\Program Files\Microsoft SQL Server\MSSQL\Backups\master_db.BAK" WITH INIT, RETAINDAYS = 0',
@on_success_action = 3,
@retry_attempts = 5,
@retry_interval = 5
go

--add job step to backup model
USE msdb
EXEC sp_add_jobstep @job_name = 'mybackupjob',
@step_name = 'Backup model data',
@subsystem = 'TSQL',
@command = 'backup database model to disk = "c:\Program Files\Microsoft SQL Server\MSSQL\Backups\model_db.BAK" WITH INIT, RETAINDAYS = 0',
@on_success_action = 3,
@retry_attempts = 5,
@retry_interval = 5
go

--add job step to backup msdb
USE msdb
EXEC sp_add_jobstep @job_name = 'mybackupjob',
@step_name = 'Backup msdb data',
@subsystem = 'TSQL',
@command = 'backup database msdb to disk = "c:\Program Files\Microsoft SQL Server\MSSQL\Backups\msdb_db.BAK" WITH INIT, RETAINDAYS = 0',
@on_success_action = 3,
@retry_attempts = 5,
@retry_interval = 5
go

--add the target server
USE msdb
EXEC sp_add_jobserver @job_name = 'mybackupjob', @server_name = N'BB2NWM'

--Run job. Starts immediately
--USE msdb
--EXEC sp_start_job @job_name = 'mybackupjob'
--go

--Schedule job
USE msdb
EXEC sp_add_jobschedule @job_name = 'mybackupjob',
@name = 'ScheduledBackup_msdb',
@freq_type = 4, --daily
@freq_interval = 1, --once
@active_start_time = '171500' --PM 24 hr HHMMSS
go

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-08-04 : 05:40:43

When run interactively 2 things happen.
a) the code is run under local security rights.
b) the paths involved are relative to the local account used.

When run off a schedule, the "local" bit changes to "the account of SQL Agent/SQL Server"
Put some "trace" code in place to see how far it gets when executing.

denise
Starting Member

14 Posts

Posted - 2006-08-09 : 10:22:18

So do I need to check permission for the SQL Agent/SQL Server account? How should I put in traces? Do you mean to print something to the screen throughout the script? If so, can you help withthe syntax to use? Thanks very much.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-08-10 : 05:56:08

Can you see if the job exists in MSBD fully-defined?...before you schedule it.
Can you see it scheduled?
Is your scheduler agent 'active'?

Re-tracing...I'm talking about putting in very basic "print 'I am here'" type statements to see how, in the procedures being called, the code actually gets to. You could direct this output to a 'text' file on the SQL Server....and then review using notepad. Items run off the scheduler can't output to a screen. Search here for syntax in doing this....I think others have gone down this route....but I personally haven't had to.

denise
Starting Member

14 Posts

Posted - 2006-08-15 : 17:31:25

This is running now but only every other day. Where can I find a complete list of the choices for the parameters? For example, @freq_type would be what for daily? I used 4 and that doesn't seem to be correct. Thanks!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-08-16 : 05:08:12

Can you try an inspect/print out the code behind "sp_add_jobschedule"?...most of these SP's (in regular SQL) reside in a system database and are viewable. Alternatively is there info in BOL for the MSDE...or do a internet-search for it.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-16 : 14:53:03

Denise,

quote:

This is running now but only every other day. Where can I find a complete list of the choices for the parameters? For example, @freq_type would be what for daily? I used 4 and that doesn't seem to be correct. Thanks!

@freq_type is documented in BOL (in a pretty little table, no less). :-) 4 is the correct value for daily, according to that table.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources