Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Backup Loop

Posted on 2013-01-09
7
Medium Priority
?
589 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 23

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

715 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