July 20, 2009 at 7:35 am
Hi,
I have a the following SQL code run daily from a .bat file to do daily backups..
The .bat file has...
Sqlcmd -S MyDB -U MASTER -P CONTROL -i C:\MyDB\MyDB.sql
The MyDB.sql file has...
BACKUP DATABASE MyDB TO DISK = "C:\MyDB\MyDB.bak" WITH INIT
It works fine - but each time it overwrites the last backup - (i want to keep last 3 or 4 backups)
An ideas how I can save the file name like yyyymmdd_MyDB.bak
where yyyymmdd is the year-month-day of the backup so we can save copies each day.
many thanks
Mike
July 20, 2009 at 7:54 am
Hi Mike,
Try using the following in the .sql file:
declare @BackupDest varchar(100)
set @BackupDest = 'C:\MyDB\' + cast(year(getdate()) as char(4)) + right('0' + month(getdate()),2) + right('0' + day(getdate()),2) + '_MyDB.bak'
BACKUP DATABASE MyDB
TO DISK = @BackupDest WITH INIT
Silly question, did you alter the username and password to protect the innocent? 😀
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
July 20, 2009 at 10:43 am
Many thanks Rob, will try tonight, Mike
July 21, 2009 at 8:33 am
Thanks, worked perfect!! Mike
July 21, 2009 at 9:24 am
declare @BackupDest varchar(100)
set @BackupDest = 'C:\MyDB\MyDB_' + convert(varchar,getdate(),112)+'.BAK'
BACKUP DATABASE MyDB
TO DISK = @BackupDest WITH INIT
A trimmed version of the same code 😉
March 19, 2012 at 11:28 am
Thank you for that, it was difficult to explain my need and finally this was right on target.:-D
May 10, 2012 at 9:38 am
I dont want to hyjack this thread, but, I'm gona.:-)
So I want to do the same thing, back up and keep several backup files. This being siad, I have one more thing I want to do, but I'm not sure how to do it.
I want to remove backups older then a few days fromthe folder where I am storing the backups.
I want this to be a part of the backup process. Do the backup, remove backup older then 2 days
Drive space is a premium for us so the attempt here is to maintain 2 days of near online backups, while the server is backed up daily as well with our backup solution.
I'm admittedly not a DBA so I'm rather unskilled with SQL server.
Thank you in advance for any help
May 10, 2012 at 9:44 am
Comment removed - should have paid more attention to which forum this was posted in. Sorry!
Bex
May 10, 2012 at 3:51 pm
Hi,
I use this type of logic to remove old files (WScript)..
Option Explicit
Dim FSO, WshShell, Then_Date, WSHShellENV, TempFolder, Recycled
On Error Resume Next
Set FSO = CreateObject("Scripting.FileSystemObject") ' creating a new object to a computer's file system
Set WshShell = WScript.CreateObject("WScript.Shell") ' creates an object in type specified
Set WSHShellENV = WSHShell.Environment("PROCESS")
set TempFolder = FSO.GetFolder(WSHShellENV("TEMP")) ' creates a Folder object to the path you specify
Set Recycled = FSO.GetFolder("C:\Myfolder\Backup\") ' creates a Folder object to the path youspecify and name it
DoDir FSO.GetFolder("C:\Myfolder\Backup") 'set starting directory A MUST
Then_Date = DateAdd("d", -2, Date)
Sub DoDir(Folder)
Dim i, File, SubFolder, fstr, pos, last_mod ' creating variables needed for function
Dim Now_Date, Then_Date ' creates the variable used for today's date
Now_Date = Date ' loads today's date into variable Now_Date
Then_Date = DateAdd("d",-2, Now_Date) ' subtracts two days from the current date
'WScript.Echo(Then_Date) ' TEST FOR THEN DATE
'WScript.Echo(Now_Date) ' TEST FOR TODAY'S DATE
'WScript.Echo(Folder) ' TEST FOR CORRECT FOLDER
For Each File In Folder.Files ' For each file in folder.files
if ((File.DateLastModified <= Then_Date)) then ' check whether the file IS
' older than keep date
File.delete ' if older than keep date, simply delete it
End if
Next
For Each SubFolder in Folder.SubFolders
DoDir SubFolder
Next
End Sub
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply