August 3, 2009 at 1:03 pm
Currently we keep one week's worth of backup files on the main storage drive on each server. We manually move files older than one week to a separate mapped drive on the server specifically for storing old backups for an additional two weeks.
We would like to automate the process of moving the files older than one week from one drive to another by running a scheduled job that executes a T-SQL move script. Has anyone created such a process? We could add it as a T-SQL step to our current maintenance plan weekly full backup job, or as a separate archival job that we would also run weekly.
I've searched the forums but found no solution. Any suggestions? This is not a time-critical request so I have some time to find the best option (how often does that happen?)
Thanks for any suggestions,
Kay
August 3, 2009 at 1:56 pm
Research xp_cmdshell
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 3, 2009 at 2:13 pm
I say you should look into an SSIS package for all your backup moving, and schedule it within SQL Agent.
Cheers,
J-F
August 3, 2009 at 2:47 pm
Thanks for replying, Pablo.
Is there a way to extract the file creation date so I can condition the move based on the date the file was created? We don't want to move any files until they are over a week old.
August 3, 2009 at 3:28 pm
You can get the backup details from the below SQL punt into a table and then use the date fields and file paths to create a dos copy via xp_cmdshell, just create the cmd dynamically and base the copy on the date.
select * from master..sysdatabasess (nolock)
LEFT OUTER JOINmsdb..backupset b (nolock)
ON s.name = b.database_name
August 7, 2009 at 8:06 am
Thanks for the xp_cmdshell tip. I've looked at this and believe this command could provide a good solution.
I am finding quite a lot of information related to deleting files, but what i need to do is move files based on the file creation date. Any .bak or .trn files residing on the c: drive that are older than 7 days will be moved to an archive drive. A separate maintenance plan will monitor the archive drive and delete any files older than 14 days.
I've found the xp_cmdshell script that I believe will work for the move portion. Does this seem reasonable? I believe the /d switch identifies the files older than 7 days.
exec master..xp_cmdshell 'move c:\folder\filename.bak /d -7 c:\archive_folder\filename.bak'
August 7, 2009 at 8:42 am
You could use robocopy (in the win2003 resource kit tools).
It has options to move files, sync folders, only copy files older than (or newer than) x days, auto restart, resume .. all kinds of junk.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply