Solved

SQL Backup Loop

Posted on 2013-01-09
7
583 Views
Last Modified: 2013-01-11
Currently, I make a FULL backup and the log files prior that just completed full backup are deleted.  Log files backups are then created and accumulate until the next full backup is completed and then they are erased.  The loop begins again with a FULL backup and zero log backups.  I would like to alter this loop to be:

1.  Full Backup = run Monday morning
2.  All log file created  = run all throughout Monday
3.  Full Backup = run Tuesday morning
4.  All Log files created = run all throughout Tuesday

I would like step 3 and 4 to run while leaving the results of step 1 and  2 intact.  When I loop back through 1 and 2, steps 3 and 4 are left intact.  This would resolve the risk of my currently having a single BAK file as my 1 and only backup.  If 1 is corrupted, at least i have 3.  If 3 is corrupted, at lease I have 1.

I am not clear how to create such a LOOP?  Everything is done in T-SQL.  I do not use the maintenance wizards or rely upon expiration dates of backup files.
0
Comment
Question by:dastaub
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38761955
My first question would be why not use the Maintenance Plans ?  This is exactly what they are for - and make this a whole lot easier.

However that aside.

You would need to be able to name your backup files with a date (or date and time) to identify them.  Create a SQL Agent job, using T-SQL, that runs daily and executes something along the lines of the following:

(The database in these examples is called demo).

declare @date char(8);
declare @bakfile char(100);
set @date = convert(char(8),getdate(),112)
set @bakfile = rtrim('D:\Backups\demo\demo_'+@date+'.bak');

BACKUP DATABASE [demo] 
TO  DISK = @bakfile 
WITH NOFORMAT, NOINIT,  NAME = N'demo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Open in new window


Similarly, you'd need to set up a similar job for your transaction log backups, running at whatever interval you deem appropriate.

Something like this makes your filename demo_yyyymmdd_hhmmss.trn - you could also apply the time to the above.

declare @date char(8);
declare @time char(8);
declare @bakfile char(100);
set @date = convert(char(8),getdate(),112)
set @time = replace(convert(char(8), getdate(), 108),':','')
set @bakfile = rtrim('D:\Backup\demo\demo_'+@date+'_'+@time'.trn');

BACKUP LOG [demo] 
TO  DISK = @bakfile 
WITH NOFORMAT, NOINIT,  NAME = N'demo-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Open in new window


This then accumlates your transaction log backups.

The tricky part then is deleting them after x days.

When you use a Maintenance Plan to clean up old files, it calls an extended stored procedure to do the dirty work.  No reason you can't do the same thing.

(I recently went into xp_delete_file in great detail in answer to another question).

However, summarizing that:

xp_delete_file is undocumented.  So the following is what I learned through trial and error and playing with the settings.  I do not guarantee it's accuracy.

You could then setup a third Agent job to call xp_delete_file to try to delete the jobs.

It would look something like this:

(The following code used from Andy Leonard's blog post,

If you want to keep files 48 hours old:

declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -48, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
EXECUTE master.dbo.xp_delete_file 0,N'D:\Backup',N'bak',@DeleteDate,1
EXECUTE master.dbo.xp_delete_file 0,N'D:\Backup',N'trn',@DeleteDate,1

Open in new window


This will delete all .bak files and .trn files from d:\Backup and first level subfolders older than 48 hours.

Hope this helps.
0
 
LVL 1

Expert Comment

by:tissier
ID: 38762464
Hi,

I'm not sure I well understood your need.
But this is how I'd do:

1.  Full Backup = run Monday morning in FULL1.BAK
2.  All log file created  = run all throughout Monday IN LOG1-x.TRN
3.  On tuesday morning, we delete FULL2.BAK and LOG2-x.TRN
4.  Full Backup = run Tuesday morning in FULL2.BAK
5.  All Log files created = run all throughout Tuesday in LOG2-x.TRN
6.  On monday morning, we delete FULL1.BAK and LOG1-x.TRN
0
 

Author Comment

by:dastaub
ID: 38763096
sjwales, Your answer described what I am currently doing.  I still have the question of having 2 sets rather than 1?

tissier, what do you do the other 5 days of the week?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38763164
You're using xp_delete_file to clean up your files at the moment ?

Then just increase the time you keep the files for.

The code I showed you allowed you to keep files for 48 hours.  If you want to keep for longer, change to 72 hours or some other value that suits your needs

declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
EXECUTE master.dbo.xp_delete_file 0,N'D:\Backup',N'bak',@DeleteDate,1
EXECUTE master.dbo.xp_delete_file 0,N'D:\Backup',N'trn',@DeleteDate,1

Open in new window


The way to keep multiple copies of your files is to make sure that each file name is unique (so you're not overwriting a previous backup) and then keep as many as you need - adjusting the parameter of the time passed to xp_delete_file so that you keep as many days as you want on disk.

As long as your full backup is set to run daily (as per your example) and your TLOG backups set to run daily at whatever interval (hourly or whatever) and the file names are unique with date and time stamps you will accumulate multiple copies over multiple days and then can let the xp_delete_file proc delete files older than whatever date you specify.
0
 

Author Closing Comment

by:dastaub
ID: 38765044
A complete Answer. Thank You
0
 
LVL 1

Expert Comment

by:tissier
ID: 38766342
Well, you do the same the other 5 days of the week. With 1 full backup file per day, and 1 trn backup file per day, you create 7 different jobs (one per day).
That's right, you have to maintain 7 jobs for full backup + 7 jobs for TRN backup

NB : when you do log backup, you must do an append to the file. If not, you won't be able to restore with transaction log...

Anyway, sjwales solution is smarter ;)
0
 

Author Comment

by:dastaub
ID: 38768585
tissier, thank you for your follow up.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question