August 26, 2010 at 8:08 am
I have SQL Servers both 2000 and 2005 and I wanted to perform backups daily, so I want the backups to have Datetime stamp therefore I can distinguish between the different files.
I have 2 questions
1.I'm going to use the following script to run a full backup on the sql 2005 server but I wanted to know if there is a way to perform the same thing in the sql server 2000 server.
declare @CheckDate [nvarchar](50), @Title [nvarchar](100)
set @CheckDate = replace(replace(convert(nvarchar(50),getdate(),120),':',''), ' ', '_')
set @Title = N'E:\DataWarehouse_Microstrategy_'+@CheckDate+''+'.bak'
BACKUP DATABASE [DataWarehouse_Microstrategy]
TO DISK = @Title
WITH NOFORMAT, NOINIT, NAME = N'DataWarehouse_Microstrategy-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,STATS = 10 ;
2. I need to perform a differential backup for both on sql 2000 and sql 2005 and I wanted to know a way of doing the differential backup but delete files that are old e.g any files that are older then 4 weeks.
October 6, 2010 at 7:52 am
Sorry no one has answered your question yet. Hopefully this helps...
As to Item 1) I haven't tested you code, but it would certainly seem that it would work for 2000 as well. I do a very similar thing with my SQL 2000 instances. Did you try to run the backup script on your windows 2000 instance? Did you get nay errors, and if so what were they?
For item 2) For your 2005 instance there is a maintenance plan option you can use that will clean up old backup files. You just give it the appropriate file extension and time frame, schedule it and it will take care of it for you.
For the 2000 instance things aren't quite that simple (unless of course the 2000 instance is on the same SQL box). You need to write a script to clean up the older files. Mine goes something like the following (and it's vbscript) you can use whatever language your prefer but this is what worked for me. I call it from a SQL agent job step and have it echo back the number of files that were deleted to be logged by the SQL agent log. It helped with troubleshooting it when I first set it up. This one is for Transaction logs and zip files, but you should be able to customize it to your needs. The iTRNDaysOld and iZipDaysOld variables tell the script how many days to keep of each file type.
I hope it helps.
-Luke.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iTRNDaysOld
Dim iZipDaysOld
Dim iTrnDeletedCount
Dim iZipDeletedCount
iTRNDaysOld = 3
iZipDaysOld = 7
iTrnDeletedCount = 0
iZipDeletedCount = 0
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "D:\test"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
For each oFile in oFileCollection
If Right(oFile.Name,4) =".trn" Then
If oFile.DateLastModified < (Date() - (iTRNDaysOld)) Then
oFile.Delete(True)
iTrnDeletedCount = iTrnDeletedCount + 1
End If
ElseIf Right(oFile.Name,4) =".zip" Then
If oFile.DateLastModified < (Date() - (iZipDaysOld)) Then
oFile.Delete(True)
iZipDeletedCount = iZipDeletedCount + 1
End If
End if
Next
WScript.StdOut.Write iTrnDeletedCount & " TRN Files Deleted."
WScript.StdOut.Write iZipDeletedCount & " Zip Files Deleted."
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply