Team BlueWater Game Online Tutorial's
Bine ati venit la Team-BlueWater!!Tutorial's 4 all Games!

Script pentru Beckup la DB la fiecare 12 ore!

View previous topic View next topic Go down

Script pentru Beckup la DB la fiecare 12 ore!

Post  m4s4cru on Tue Aug 02, 2011 10:59 am

Deschideti Query Analyzer ;
Dupa acesta DataBase se vor salva in 3 locuri :
D:/muserver/...
C:/WINDOWS/...
C:/...

Cele noi le vor inlocui pe acele vechi!
SQL Server Agent trebuie sa fie pornit.

Code:

BEGIN TRANSACTION     
  DECLARE @JobID BINARY(16)
  DECLARE @ReturnCode INT 
  SELECT @ReturnCode = 0 
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id 
  FROM  msdb.dbo.sysjobs 
  WHERE (name = N'Database BackUP')   
  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 ''Database BackUP'' 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'Database BackUP'
    SELECT @JobID = NULL
  END

BEGIN

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Database BackUP', @description = N'// makes automatically a backup from // database MUonline and Ranking // newer version with more fuctions coming // soon // SiLvER', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 0, @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'backup and save in D:/', @command = N'BACKUP DATABASE [muonline] TO DISK = N''D:\MuServer\db_muonline.bak'' WITH INIT , NOUNLOAD , NAME = N''muonline backup'', NOSKIP , STATS = 10, NOFORMAT
BACKUP DATABASE [ranking] TO DISK = N''D:\MuServer\db_ranking.bak'' WITH INIT , NOUNLOAD , NAME = N''muonline backup'', NOSKIP , STATS = 10, NOFORMAT', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'baclup and save in WINDOWS', @command = N'BACKUP DATABASE [muonline] TO DISK = N''C:\WINDOWS\Muonline\db_muonline.bak'' WITH INIT , NOUNLOAD , NAME = N''muonline backup'', NOSKIP , STATS = 10, NOFORMAT
BACKUP DATABASE [ranking] TO DISK = N''C:\WINDOWS\Muonline\db_muonline.bak'' WITH INIT , NOUNLOAD , NAME = N''muonline backup'', NOSKIP , STATS = 10, NOFORMAT', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'backup and save in C:/', @command = N'BACKUP DATABASE [muonline] TO DISK = N''C:\db_muonline.bak'' WITH INIT , NOUNLOAD , NAME = N''muonline backup'', NOSKIP , STATS = 10, NOFORMAT
BACKUP DATABASE [ranking] TO DISK = N''C:\db_ranking.bak'' WITH INIT , NOUNLOAD , NAME = N''muonline backup'', NOSKIP , STATS = 10, NOFORMAT', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @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'backup database every12 hours', @enabled = 1, @freq_type = 4, @active_start_date = 20050928, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 12, @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:

Informatia a fost luata de pe MuForum!
avatar
m4s4cru
Admin
Admin

Mesaje : 172
Data de inscriere : 2011-07-29
Varsta : 26

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum