[GUIDE] How To Backup Database Automatically( Every 1 Hour)

[PrinceOfPersia]

For those who are running SQLServer 2005, Here is the easy way to back up your database automatically.
This will ensure that you have a latest copy of your database in case someone hacks your server!
Just make sure that your SQL Server Agent is running...

STEP 1

STEP 2

STEP 3

STEP 4

STEP 5

STEP 6

STEP 7

STEP 8

STEP 9

STEP 10

BELOW IS THE SCRIPT FOR STEP 4


/* BACK UP DATABASE by PrinceOfPersia */
DECLARE @Date NVARCHAR(50), @RanGamePath NVARCHAR(300), @RanShopPath NVARCHAR(300), @RanUserPath NVARCHAR(300)
SET @Date = CAST ( DATEPART(month, GETDATE()) AS NVARCHAR(30)) + CAST( DATEPART(day, GETDATE()) AS NVARCHAR(30))
+ CAST ( DATEPART(year, GETDATE()) AS NVARCHAR(30)) + '_' +CAST ( DATEPART(hour, GETDATE()) AS NVARCHAR(30))
+ CAST ( DATEPART(minute, GETDATE()) AS NVARCHAR(30))

SET @RanGamePath = 'C:\RanGame1_' + @Date + '.bak'
SET @RanShopPath = 'c:\RanShop_' + @Date + '.bak'
SET @RanUserPath = 'c:\RanUser_' + @Date + '.bak'

PRINT @RanGamePath
PRINT @RanShopPath
PRINT @RanUserPath

BACKUP DATABASE RanGame1 TO DISK = @RanGamePath ;
BACKUP DATABASE RanShop TO DISK = @RanShopPath;
BACKUP DATABASE RanUser TO DISK = @RanUserPath
GO


For those who don't want to undergo this process, here is the script to run

USE [msdb]
GO
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DATABASE BACKUP ROUTINE')
EXEC msdb.dbo.sp_delete_job @job_name=N'DATABASE BACKUP ROUTINE', @delete_unused_schedule=1
GO
/****** Object: Job [DATABASE BACKUP ROUTINE] Script Date: 04/16/2009 00:25:06 ******/
/****** by PrinceOfPersia******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 04/16/2009 00:25:06 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DATABASE BACKUP ROUTINE',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [BACKUP DATABASE] Script Date: 04/16/2009 00:25:06 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BACKUP DATABASE',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
/* BACK UP DATABASE by PrinceOfPersia */
DECLARE @Date NVARCHAR(50), @RanGamePath NVARCHAR(300), @RanShopPath NVARCHAR(300), @RanUserPath NVARCHAR(300)
SET @Date = CAST ( DATEPART(month, GETDATE()) AS NVARCHAR(30)) + CAST( DATEPART(day, GETDATE()) AS NVARCHAR(30))
+ CAST ( DATEPART(year, GETDATE()) AS NVARCHAR(30)) + ''_'' +CAST ( DATEPART(hour, GETDATE()) AS NVARCHAR(30))
+ CAST ( DATEPART(minute, GETDATE()) AS NVARCHAR(30))

SET @RanGamePath = ''C:\RanGame1_'' + @Date + ''.bak''
SET @RanShopPath = ''c:\RanShop_'' + @Date + ''.bak''
SET @RanUserPath = ''c:\RanUser_'' + @Date + ''.bak''

PRINT @RanGamePath
PRINT @RanShopPath
PRINT @RanUserPath

BACKUP DATABASE RanGame1 TO DISK = @RanGamePath ;
BACKUP DATABASE RanShop TO DISK = @RanShopPath;
BACKUP DATABASE RanUser TO DISK = @RanUserPath
GO
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'BACKUPDATABASE',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090416,
@active_end_date=99991231,
@active_start_time=90000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

 

[Jolin88]

every hour? That will make the server lag for a few mins every hour (if the DB is big enough then it will lag non stop) and it will also consume tons of harddisk space and RAM

my suggestion : once a day or max twice a day

 

[PrinceOfPersia]

every hour? That will make the server lag for a few mins every hour (if the DB is big enough then it will lag non stop) and it will also consume tons of harddisk space and RAM

my suggestion : once a day or max twice a day

You can set it up depends on what you want. I just give this an example. Make every 3 hour, 5 hours, 1D or etc.
You can actually delete the old back up if you didn't want it. It just your making sure that you have a recent updated copy of your database. Normally, private servers don't have BIG DATABASE. Back up of database just takes just a few minutes and even seconds depending on the database size. Backup up of db don't consume too much RAM. It's the server that consumes.
FYI: It's up to you if you want to give them 1D, 2D ROLLBACK for your players which I think it hurts.:8:

 

[shins012]

cOoL....i try this one
tnx prince

 

[•Zentrix•]

This is a Nice share mr.. s**i**h**r ^^

 

[icedbox2002]

@PrinceOfPersia
ask only
we already have a script in auto backup (different in your script)
the question is... is this possible that when backuping RanGame1,RanUser,RanShop the file name is the date?
so that the backup file for tomorrow will not overwrite the file!

 

[KenWeiLL]

but, you wont know when the server is hacked. and when to restore.

also, like icebox said, it might ovewrite the previous backup file. in that case, the hacked database will be backed up as well then might ovewrite the other backup that is supposed to be the clean database, which was not hacked. and ended up, restoring the hacked database also.

anyway, thanx for the guide. this is still usefull in some situations.

 

[PrinceOfPersia]

@PrinceOfPersia
ask only
we already have a script in auto backup (different in your script)
the question is... is this possible that when backuping RanGame1,RanUser,RanShop file name is the date?
so that the backup file for tomorrow will not overwrite the file!

The filename i used is actually of the format DatabaseName + _ + currentdate + _ + currenttime(hour and mins)
You can changed it to what format you want sir. This wont actually overwrite the existing back up files

 

[icedbox2002]

@PrinceOfPersia
Thank you for the fast reply i will going study your script!

 

[PrinceOfPersia]

but, you wont know when the server is hacked. and when to restore.

also, like icebox said, it might ovewrite the previous backup file. in that case, the hacked database will be backed up as well then might ovewrite the other backup that is supposed to be the clean database, which was not hacked. and ended up, restoring the hacked database also.

anyway, thanx for the guide. this is still usefull in some situations.

Don't worry sir it won't overwrite! :
I already anticipated that scenario.

 

[iWareZ]

wow nice share.. 2 thumbs up bro.. ^^

 

[KenWeiLL]

@PrinceOfPersia

is it possible from that script to shrink database first before backup? or, its already included in the script.

and btw, under your Object Explorer, there is "SQL Server Agent". I dont have that. How did you create that?

 

[jerwin]

@PrinceOfPersia
you using Sql 2005 Enterprise Edition?Can u share the link were to download it?
im looking for that kind of sql for a long time ago

 

[lourence8]

the guide is good ehhehehe Very GOod..

thank u

 

[volter]

another good job from you ser this is really helpful...
additional security for server running Online. ^_^

 

[albertmsanchez]

nice share ^^


btw...can you share for trigger code that insert certain skill for every corresponding number of reborn?

 

[PrinceOfPersia]

@PrinceOfPersia

is it possible from that script to shrink database first before backup? or, its already included in the script.

and btw, under your Object Explorer, there is "SQL Server Agent". I dont have that. How did you create that?

Shrinking is not implemented sir, It's just a normal back up.
Try to check sir your installation sir.

 

[PrinceOfPersia]

@PrinceOfPersia
you using Sql 2005 Enterprise Edition?Can u share the link were to download it?
im looking for that kind of sql for a long time ago

You can buy a CD sir you know what I mean.

 

[PrinceOfPersia]

nice share ^^


btw...can you share for trigger code that insert certain skill for every corresponding number of reborn?

I'll try to study about this one. If I'm successful, I will share it here. I have already an idea how to do this, I just need to test it on my part.

 

[YouYube]

i need a guide for sql2000