SQL Backup Job

Newbie Spellweaver
Joined
Aug 18, 2004
Messages
77
Reaction score
0
This SQL job will create a backup when you schedule it to run. E.g. every hour or every day.

I highly recommend you do not set it to anything lower than an hour otherwise it will overwrite other backups within that hour!

Code:
DECLARE @DATE NVARCHAR(250)
DECLARE @BACKUPNAME NVARCHAR(250)

SET @DATE = CONVERT(NVARCHAR(13), GETDATE(), 120)
SET @DATE = REPLACE(@DATE,':', '-')
SET @DATE = REPLACE(@DATE,' ', '_')

SET @BACKUPNAME = '[COLOR="Red"]D:\MuDB_backup\MuOnline\[/COLOR]' + @DATE + '.BAK'

BACKUP DATABASE [MuOnline] TO DISK = @BACKUPNAME
WITH NOFORMAT, NOINIT, NAME = N'MuOnline', SKIP,
NOREWIND, NOUNLOAD, STATS = 10

Simply make an SQL job and change the red part, set a schedule time and it will do its own thing :w00t:

Once it has backed up, the file name will be in the format:
YYYY-MM-DD_HH.BAK
E.g:
2008-11-01_17.BAK

Would indicate the 1st of November 2008 at 17:00 hours :)
 
Re: [Guide] SQL Backup Job

its better and easier to configure by right-clicking on the DB and start the maintenance plan.. it will guide you through the setup of the back up jobs... ;)
 
Re: [Guide] SQL Backup Job

Explain how it's "better".

The ability to change the filenames and get more indepth isn't better?
 
Re: [Guide] SQL Backup Job

its better and easier to configure by right-clicking on the DB and start the maintenance plan.. it will guide you through the setup of the back up jobs... ;)
I don't think so

DECLARE @DATE NVARCHAR(250)
DECLARE @BACKUPNAME NVARCHAR(250)

SET @DATE = CONVERT(NVARCHAR(13), GETDATE(), 120)
SET @DATE = REPLACE(@DATE,':', '-')
SET @DATE = REPLACE(@DATE,' ', '_')

SET @BACKUPNAME = 'D:\MuDB_backup\MuOnline\' + @DATE + '.BAK'

BACKUP DATABASE [MuOnline] TO DISK = @BACKUPNAME
WITH NOFORMAT, NOINIT, NAME = N'MuOnline', SKIP,
NOREWIND, NOUNLOAD, STATS = 10


is more simply, fast and can be customized. :thumbup:
 
Back