0

I'm using SQL Server 2005. One of my backup drives is filling up, so I want to move backups older than 2 days.

I was thinking of creating a job to run everyday, but got stuck on the scripts moving the .bak files.

Do you know a script to move files from drive A and move it to a shared location? My .bak files doesn't have a timestamp.

  • This will help : dba.stackexchange.com/a/78702/8783 – Kin Shah Dec 14 '16 at 14:41
  • you can check backupset table for information on your backup and then move files with xp_cmdshell – Stijn Wynants Dec 14 '16 at 14:42
  • 1
    Just note that it may make sense to build in logic such that you're not just moving files that are two days old, but you are keeping log files that belong to the oldest full backup from two days ago, that sort of thing. If you have to restore and your full backup and the first chunk of log files have been moved to some other location and you still have a bunch of newer log files locally, that's going to be a pain. – Aaron Bertrand Dec 14 '16 at 14:54
  • Of course, if you're in simple recovery and only taking full backups, your problem here is much simpler (but your overall problem is a little harder, e.g. if you have to restore, you can't restore to a point in time). – Aaron Bertrand Dec 14 '16 at 14:57
  • @ Aaron. thanks.. Those are only full backups. You are right, if it had log files. I had a situation long time ago, and have to match a numerous number of LSNs to load logs. was not fun at all (: – reshad Dec 14 '16 at 15:01
5

This Powershell example will find all .bak files older than two days in the c:\backup folder and move them to \shared\archive shared drive.

Get-ChildItem c:\backup\*.bak |where {$_.LastWritetime -lt (get-date).adddays(-2)} |move-item -Destination \\shared\archive

Note you cannot run PowerShell commands directly in SSMS. Try it in a SQL Server Agent job step, or just in a PowerShell command window. Should work fine in either.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.