May 7, 2009 at 3:19 pm
I have an issue with disk space on my backup disk array. I can only keep the last/latest bak file there, along with the translogs since that backup.
Back in the day (SQL 7) I built a long and complex script to backup all the different databases based on the database growth since the last backup and delete backup and trans log files older than a week, but make sure there was at least one backup file and subsequent trans logs in the folder. This was helpful for psuedo-static databases that only were backed up every 2 weeks or once a month.
Boy I wish I had that script now. But, on we march into the modern age. Time to embrace SSIS.
I did some quick and admittedly cursory research and could not find anything to help me get to where I wanted to be, so I put together a little bit of code. I'll be modifying it over time to improve it, but I wanted to share the initial concept and code to solicit feedback and see if anyone else is doing something today like I use to do in SQL 7 days.
I almost put this in the SSIS forum, but this is about a specific strategy for backups and I want feedback from the folks interested in commenting on this backup strategy. Are you guys doing something like this some other way?
'Delete all backup sets but the last in a folder
Dim oDirInfo As System.IO.DirectoryInfo = My.Computer.FileSystem.GetDirectoryInfo("C:\JATO\testfolder\")
Dim oBUList As System.IO.FileInfo() = oDirInfo.GetFiles("*.bak")
Dim oBUFile As System.IO.FileInfo
Dim oTRNList As System.IO.FileInfo() = oDirInfo.GetFiles("*.trn")
Dim oTRNFile As System.IO.FileInfo
Dim oDIFList As System.IO.FileInfo() = oDirInfo.GetFiles("*.dif")
Dim oDIFFile As System.IO.FileInfo
Dim sBUFileToSave As String
Dim dtBUMostRecent As DateTime = DateAdd(DateInterval.Month, -1, Now())
For Each oBUFile In oBUList
If oBUFile.CreationTime > dtBUMostRecent Then
dtBUMostRecent = oBUFile.CreationTime
sBUFileToSave = oBUFile.Name
End If
Next
If sBUFileToSave <> "" Then
For Each oDIFFile In oDIFList
If oDIFFile.CreationTime < dtBUMostRecent Then
oDIFFile.Delete()
End If
Next
For Each oTRNFile In oTRNList
If oTRNFile.CreationTime < dtBUMostRecent Then
oTRNFile.Delete()
End If
Next
For Each oBUFile In oBUList
If oBUFile.Name <> sBUFileToSave Then
oBUFile.Delete()
End If
Next
End If
May 7, 2009 at 7:05 pm
Simple add more disk space on your system. And configure backups of DB's as required.
Regards
Swayambhu
MCAD, MCDBA, MCTS
May 7, 2009 at 7:42 pm
I am surprised at the "throw more disk at it" answer.
Why add disk space when it is not needed? Having the last backup locally (the other backups go off-site) is a great convenience but not worth a couple TB of data, cumulative.
Edit, I forgot the most important part. Even with all the space in the world, I won't retain a backup file from a job that is run once a quarter with an across the board, 1 month backup retention. Unless, I do something like above. No?
May 21, 2009 at 9:02 am
Ignore what the other person said, not cleaning up backups is a waste of space. I stole part of your scirpt because cleaning up backups is important. Thanks! 🙂
May 21, 2009 at 11:32 pm
Hi Neil,
Your backup strategy seems straightforward to me, that is, keeping on disk only the last backup files. Only thing you have to consider if you take also differential backups, you need both the full and the diff (obviously, you did :). As for transaction log retention: if you want to be able to restore the most recent state, you don't have to keep the TRN files since the full backup, only the last differential. Other than that, nice script.
Btw, how about powershell? I suggest you moving to that - in a few weeks you can get the basics and then it will boost your scripting performance (ok, boosted mine:).
-- Erik http://blog.rollback.hu
May 22, 2009 at 8:30 am
Thanks Erik,
I appreciate the feedback. I will look into PowerShell. I hadn't thought about it. The trans logs the script is keeping are those logs since the last backup. The ones before it get deleted.
May 26, 2009 at 12:33 pm
I use a similar strategy to move IIS log files to another drive.
This is VBA code in Excel example.
Note 1 difference - I order them and can choose how many files I wish to leave.
Date is not important.
For I = 1 To .FoundFiles.Count - 1
Sub dirSearch_myServerName()
Dim myPath As String
Dim myDest As String
Dim myFullDest As String
Dim myFullDest2 As String
Dim I As Integer
Set myFile = CreateObject("Scripting.FileSystemObject")
myPath = "\\myServerName\C$\WINDOWS\system32\LogFiles\W3SVC1"
myDest = "\\myServerName\D$\Old IIS Logs"
Set fs = Application.FileSearch
With fs
.LookIn = myPath
.Filename = "*.log"
If .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderAscending) > 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
'" file(s) found."
For I = 1 To .FoundFiles.Count - 1
'MsgBox .FoundFiles(I)
myFullDest = .FoundFiles(I)
x = Len(myFullDest)
y = Right(myFullDest, 13)
myFullDest2 = myDest & y
Z = Right(myFullDest, 12)
myFile.MoveFile myFullDest, myFullDest2
Next I
Else
MsgBox "There were no files found."
End If
End With
End Sub
Thanks, Greg E
May 27, 2009 at 7:59 am
At the risk of over simplifying this (I live by the KISS principle)
set backups up via maintenance plan and place backups for each database in separate sub directories of the main backup directory, then setup different retention periods for each sub directory via the cleanup task.
If you don't even have space to temporarily store 2 copies of the backups write a stored proc to backup all databases with init, then use the maintenance plan with cleanup tasks just for log backups
---------------------------------------------------------------------
May 27, 2009 at 8:55 am
Adding more disk space is one better option. If you want to keep dialy one day backup then better option is 'With copy_only' so that it will overwrite dialy to same bak file.
May 27, 2009 at 9:07 am
Thank you for your feedback. I appreciate the knowledge share. It has helped me understand the options.
May 28, 2009 at 10:17 am
Pradyothana Shastry (5/27/2009)
Adding more disk space is one better option. If you want to keep dialy one day backup then better option is 'With copy_only' so that it will overwrite dialy to same bak file.
COPY_ONLY backups cannot be used for point in time recovery nor can they be used as a base for restoring differential backups and do not make sense for regularly scheduled backups.
To advise using COPY_ONLY backups in this manner is inexcusable.
May 28, 2009 at 10:21 am
Pradyothana Shastry (5/27/2009)
Adding more disk space is one better option. If you want to keep dialy one day backup then better option is 'With copy_only' so that it will overwrite dialy to same bak file.
Why are you repeating what other people have said? What's the point?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply