March 19, 2008 at 7:48 am
hi ,
will u plz give me query for full and diifferential with a scheduled jobs which run automatically every week
March 19, 2008 at 7:53 am
You can find this out easily for yourself by reading about database maintenance plans. If you don't want to use one of those, look up the syntax of BACKUP DATABASE and schedule it in a job.
John
March 19, 2008 at 8:11 am
Hi,
Thaxx for reply me but i already know this .will u plz give me scripts for full and differential database backup for job. i need a scripts for job .
regards
jagpal singh
March 19, 2008 at 8:22 am
This is as easy as I'm going to make it for you. If you're not willing to help yourself then you won't find many people here willing to help you. Maybe there's one thing in particular you don't understand... if that's the case then say what that is and we'll try to explain. But asking us to do your job for you won't get you very far.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/89a4658a-62f1-4289-8982-f072229720a1.htm
John
March 19, 2008 at 8:36 am
I agree with John, use the resources, learn a little and if you have specific issues, post them. Many of us will gladly help...but try and help yourself first. Having said that (let me get off the proverbial soapbox!), in addition to BOL, there are volumes of scripts readily available, both on this site and elsewhere. Google has a lot of hits and many (if not all) the various SQL web boards have them. It's amazing what you can find, and do, with a little effort.
-- You can't be late until you show up.
March 19, 2008 at 9:00 am
hi,
actually u feel wrong i already have a script for job as well but its not
working .
March 19, 2008 at 9:58 am
Post the script and we'll absolutely help.
-- You can't be late until you show up.
March 19, 2008 at 10:09 am
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'COLT User Database Backup -jazz01 ')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''COLT User Database Backup - jazz01' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'COLT User Database Backup -jazz01 '
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'COLT User Database Backup - BOXI', @owner_login_name = N'UK-EIRE\SQLAdmin', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Full BOXI Backup', @command = N'declare
@x1int,
@x2varchar(255)
begin
execmaster.dbo.colt_sp_sqlmanager''FULLDBBACKUP'',
''jazz01'',
null,
null,
@x1 output,
@x2 output
if @x1 != 0
begin
PRINT''Backup Job for jazz01 Failed, returned error code:''+ltrim(str(@x1))
execmaster.dbo.colt_sp_sqlmanager''FAILMAIL'',
''jazz01'',
3,
''Full Database Backup Failure'',
@x1 output,
@x2 output
if @x1 != 0
PRINT ''Error occurred and unnable to email failure notification''
end
end', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 2, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'G:\COLT SQL Logs\COLT User Database Backups.log', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'BOXI database schedule', @enabled = 1, @freq_type = 4, @active_start_date = 20070331, @active_start_time = 190000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
March 19, 2008 at 10:20 am
OK, you say this isn't working. Does that mean that it gives an error message, or it runs but doesn't do what you expect, or something else? Please make it easy for us to help you.
John
March 20, 2008 at 2:34 pm
I see you are using a third party tool to conduct your backups:
master.dbo.colt_sp_sqlmanager ''FULLDBBACKUP''
From reading your posting, it should have emailed an error code or posted an error stating the email failed in the error log.
What was the error code received in the email or did you get the message saying the email failed? Also, what is the third party tool that you are using to conduct your backups? You may have to check the help files for this tool to see what the error code means.
If you want to see the SQL version of the backup script, right click on your database that you want to backup. Goto to tasks and then to backup database. set up the backup in the window that pops up and at the bottom of this window is the option to schedule it. Schedule the backup and then goto scheduled jobs and view the job that was just created. There is your script.
Dave
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply