Solved

SQL Backup Loop

Posted on 2013-01-09
7
571 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
Comment Utility
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
Comment Utility
A complete Answer. Thank You
0
 
LVL 1

Expert Comment

by:tissier
Comment Utility
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
Comment Utility
tissier, thank you for your follow up.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now