Auto Database Backup every hour in new file

[BlackEye]

Hello.
I hawe searched in all forum and found manny SQL Auto Backup Scripts, but cant find script what i need.

All scripts (auto backup) working on one principe, that is, save database in same file with overwrite data, but if think, if server database was hacked and Auto Backup save data, what we get?! Saved DB with 0 Characters or 0 Accounts?

So,what i try to find is Auto Backup witch every hour create NEW FILE!

Example:
MuOnline_30.11.2009_12:00
MuOnline_30.11.2009_13:00
MuOnline_30.11.2009_14:00
ETC.

Hawe anyone script who make this job or can someone write it?!
Thanks.

 

[magicswe]

Gotta learn to search harder man, this is my script i use. It's also found on RZ guide section.

DECLARE @file varchar(70)
SET @file = N'D:\MuOnline-BACKUP\BACKUP'+REPLACE(REPLACE(CAST(getdate() AS varchar), ' ', '_'), ':', '_')
SELECT @file
BACKUP DATABASE [MuOnline] TO DISK = @file WITH INIT , NOUNLOAD , NAME = N'MuOnline backup', NOSKIP , STATS = 10, NOFORMAT

This baby put a backup in D:/MuOnline-BACKUP, it names them
BACKUPNov_27_2009__1_15PM
BACKUPNov_27_2009__1_15PM
BACKUPNov_27_2009__1_15PM

Change "D:\MuOnline-BACKUP" Wo whatever path you want to use. For example:
C:\Backup etc.

etc. It doesn't overwrite, it makes a new file for each time it's executed.

 

[commandcom]

heres mine for 3 database it need to make directories manually
i did make this for shatter's latest s4 server
it does make files like:
MuEvent_2009-12-1-10.53.bak
MuOnline_2009-12-1-10.53.bak
MuRanking_2009-12-1-10.53.bak
also it does shrink database
and i use it as sql job

BACKUP LOG MuOnline WITH NO_LOG

USE [MuOnline]
GO
DBCC SHRINKDATABASE(N'MuOnline')

Declare @dateBackup Varchar(100)
Set @dateBackup = 'c:\SQL\Backup\shatter\MuOnline_' +
Convert(varchar, datepart( year , Getdate() )) + '-'+ 
Convert(varchar, datepart( month , Getdate() ) ) + '-'+
Convert(varchar, datepart( day , Getdate() ) ) + '-'+
Convert(varchar, datepart( hour , Getdate() ) ) + '.'+   
Convert(varchar, datepart( minute , Getdate() ) ) + '.bak' 
BACKUP DATABASE [MuOnline] TO DISK = @dateBackup WITH NOFORMAT, 
NOINIT, NAME = N'MuOnline-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

BACKUP LOG MuRanking WITH NO_LOG

USE [MuRanking]
GO

DBCC SHRINKDATABASE(N'MuRanking')

Declare @dateBackup Varchar(100)
Set @dateBackup = 'c:\SQL\Backup\shatter\MuRanking_' +
Convert(varchar, datepart( year , Getdate() )) + '-'+ 
Convert(varchar, datepart( month , Getdate() ) ) + '-'+
Convert(varchar, datepart( day , Getdate() ) ) + '-'+
Convert(varchar, datepart( hour , Getdate() ) ) + '.'+   
Convert(varchar, datepart( minute , Getdate() ) ) + '.bak' 
BACKUP DATABASE [MuRanking] TO DISK = @dateBackup WITH NOFORMAT, 
NOINIT, NAME = N'MuRanking-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

BACKUP LOG MuEvent WITH NO_LOG

USE [MuEvent]
GO

DBCC SHRINKDATABASE(N'MuEvent')

Declare @dateBackup Varchar(100)
Set @dateBackup = 'c:\SQL\Backup\shatter\MuEvent_' +
Convert(varchar, datepart( year , Getdate() )) + '-'+ 
Convert(varchar, datepart( month , Getdate() ) ) + '-'+
Convert(varchar, datepart( day , Getdate() ) ) + '-'+
Convert(varchar, datepart( hour , Getdate() ) ) + '.'+   
Convert(varchar, datepart( minute , Getdate() ) ) + '.bak' 
BACKUP DATABASE [MuEvent] TO DISK = @dateBackup WITH NOFORMAT, 
NOINIT, NAME = N'MuEvent-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

maybe its not best and easyest solution but works for me

 

[genoxide]

Backup every hour in a new file may seem like a good idea on the short run, but if you have a large database you disk will be full in a matter of a few days. Then you need every end of the day (example) to find what backup is OK then delete the rest blabla..
A better idea for long run is to secure your web/server.

 

[magicswe]

The files are created in a alphabetic order (My script) Since it use the exact date and time. It's not hard to select the last 50 backups then hit delete. Heck i bet there's even purge programs on the net if you're lazy.

 

[Dios]

If you are using sql 2005 it already has a built in system, much better than a simple trigger.

Its called Maintenance Plans and you can do a lot more than a simple backup

You must be registered to see links