Wednesday, August 15, 2012

MSSQL 2008: Database Backup

A couple of days ago it became necessary to set up automatic backups of the database using nothing, but mssql server and t-sql. The backup scenario was simple: make full backup every twenty-four hours at 00:05 and differential backup - every hour. With the following backup file name format: "DatabaseName_yyyy_dd_mm_hh_mm_[full|diff].bak" Daily backups should be stored in a separate folder, with specific name ('yyyy_mm_dd'). So this is how it could be done.
The following script will do full backup (dont forget to change database name):
declare @rootDir nvarchar(512)
declare @backupSubDir nvarchar(512)
declare @backupPath nvarchar (1024)
declare @dirList table (dir nvarchar(255), depth int)
declare @backupName nvarchar(1024)
declare @timestamp nvarchar(5)

set @rootDir = 'd:\test\Backup\' /* root backup folder*/
set @timestamp = replace(left(convert(varchar, getdate(), 108), 5), ':','_') /* 5 left numbers of hh:mm:ss */
set @backupSubDir = replace(convert(varchar, getdate(), 102), '.', '_') /* subfolder name for the daily backup */
set @backupPath = @rootDir + @backupSubDir
set @backupName = 'dms_'+@backupSubDir+'_'+@timestamp+'_full.bak'

/* get the list of folders inside root backup folder */
insert into @dirList (dir, depth)
exec master.sys.xp_dirtree @rootDir

/* create subfolder for the daily backup if needed */
if not exists (select 1 from @dirList _dl where _dl.dir = @backupSubDir)
exec master.dbo.xp_create_subdir @backupPath

/* make the full backup */
set @backupPath = @backupPath+'\'+@backupName
BACKUP DATABASE [dms] TO  DISK = @backupPath WITH NOFORMAT, NOINIT,  NAME = 'dms_full_backup', SKIP 
/* to make differential backup use the same script but with ‘DIFFERENTIAL’ parameter */
Read about backup params here:
Some information about datetime format:

Now we got the script and we need to run it automatically. Navigate to "Maintenance Plans" in mssql object explorer, right click "Maintenance Plans" and hit "New Maintenance Plan...":

Select an "Execute T-SQL Statement Task" from the toolbox and drag it to the subplan designer:

Right click the appeared plan task, click "Edit" and insert our backup sql statement. Now we need to configure the plan schedule. Hit the calendar button and set up appropriate schedule:

In my case i need recurring task which occurs every day every 1 hour between 0:05:00 and 23:59:59. When the plan is saved a new job will appear in Jobs folder of SQL Server Agent:

That's all. Now the task will automatically backup the database, specified in the backup script. It is also possible to run the task manually.

No comments :

Post a Comment