SQL database Backup every 12 hours

[[GR]SiLvER]

The following query will make backup of the databases muonline and ranking every 12 hours , using jobs system

this gives you security so if someone hacks you will be able to restore database


EXECUTE IN QUERY ANALYZER : [start->all programs->Microsoft SQL server->QUery analyzer]

-- Script generated on 28/9/2005 10:18 pm
-- By: localhost / silver_private_
-- Server: (local)

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:

the backup files will be saved three times if some directorys don't exist (like D:/muserver/ )
first in D:/muserver/...
second : C:/WINDOWS/...
and third : C:/...

*NOTE* : the new backups will always overwrite the old
*NOTE2* : SQL SERVER AGENT MUST RUN

[MentaL]

[boulika]

nice release mate.Ill give it a try

[Forcystos]

gr8 *guide* man. this is not a release tho, so if its not much trubble could u put in guides?

[[GR]SiLvER]

umm,i thinks it's ok in both sections :P

if you find any bugs report it
i will try to make it to send the backups with e-mail
or upload it with FTP

[Forcystos]

hmmm i cant test it now, but from what i see, i think its pretty good...if i run it in query analyzer, does it tell if backup was successful or unsuccessful? and also, if i want to i can make it any time i want rite?

[Dios]

Excellent work! I have something like this, but never setted it for 3 places, nice idea!

Gratz!

[4pLay]

Nice Work Bro...

Hope You Can Make An SQL Query Triger For Making Each And Every Create

A Certain Accounts Having A Jewels Or Some Kind Of Items On Each And Very

Character You Have Created...

TaNx Again Bro...

[vantherX]

nice guide oops release i forgot lolz :D

[_ZLOI_]

guys i hawe error
Server: Msg 208, Level 16, State 1, Procedure sp_delete_job, Line 95
Invalid object name '#temp_jobs_to_delete'.
what it ????????

[HackingSkills]

*NOTE* : the new backups will always overwrite the old
*NOTE2* : SQL SERVER AGENT MUST RUN

hhhmmmmm overwrite? you can setup the backup at much closer interval using also the JOBS of the SQL agent. not to mention it can be FULLY backed-up without overwriting the earlier backup thus giving you the option to restore the whole DB at your preferred date and time.

[Mu-Classic]

The following trigger will make backup of the databases muonline and ranking every 12 hours , using jobs system

this gives you security so if someone hacks you will be able to restore database


EXECUTE IN QUERY ANALYZER : [start->all programs->Microsoft SQL server->QUery analyzer]

-- Script generated on 28/9/2005 10:18 pm
-- By: localhost/SiLvER
-- Server: (local)

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', @owner_login_name = N'S-8883B8A1735A4\{S**iLvER', @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:

the backup files will be saved three times for more security
first in D:/muserver/...
second : C:/WINDOWS/...
and third : C:/...

*NOTE* : the new backups will always overwrite the old
*NOTE2* : SQL SERVER AGENT MUST RUN

See After all that explanation i can't do anithing od that and i cannot download the info either :) add me mess_molotov@hotmail.com

[Camus_anth]

It work nice thanks you for this guide :)

[RealSlasher]

Is this normal I Got the message after I Run it in Query Analyser

Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).

[norrin]

nice guide silver

btw, i think there is another easy way to automate your database backup using SQL without coding. I may check it first and give a try.

[pipoulo]

i suppose you can use sql time & date functions to format the filename with datetime so it will not overwrite the old backup.

[[GR]SiLvER]

Is this normal I Got the message after I Run it in Query Analyser

Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).

heh , mistake :lamo:
corrected code :

-- Script generated on 28/9/2005 10:18 pm
-- By: localhost / silver_private_
-- Server: (local)

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:

[IAmMe]

how to turn sql server agent on.. i can't find it >.>.

[Fraps]

Dear Silver,
The question is if after someone hacking your database, and deleteing (for example) all the accounts, and then the backup time comes up and making a fresh new backup, but this backup is without any account, true ?

[blekk]

It should make more backups, basically a new backup will not overwrite the old backup, not sure tho :p.

[laeryT]

Dear Silver,
The question is if after someone hacking your database, and deleteing (for example) all the accounts, and then the backup time comes up and making a fresh new backup, but this backup is without any account, true ?

I think so ;)
thise script is noo good cuz if you dont know when you were hacked
your server will be reseted :tooth:
if the backup is everytime with the date before MuOnline.bck for example
in 00:00 today to be 5.6.1.MuOnline.bck
in 12 : 00 to be 5.6.2.MuOnline.bck and for every 12 hours to have different backups
thise will be the best firewall for the hackers :prop:

[Fraps]

Just need to generate a new script that WILL NOT OVERWRITE the old backup

[Camus_anth]

Cool silver its very safely :D thanks

EDIT

I found a little error on your script:

BACKUP DATABASE [ranking] TO DISK = N''C:\WINDOWS\Muonline\db_muonline.bak''

BACKUP DATABASE [ranking] TO DISK = N''C:\WINDOWS\Muonline\db_ranking.bak''

:D

[Suffer]

well...bump.. :P

tell me which database i have to open in query?
mu online or ranking?

[jrroger]

Does it shrink the DB or does any truncate job?