File move

Question

File move

igloo21

Mr or Mrs. 500

Points: 534
More actions
December 9, 2009 at 1:36 pm

#217556

Hello-
I’m attempting to create a SQL job that will move some files around and needing some advice. I’m trying to create a table that will look into a directory d:\backups and move anything that has a created on date on the 4th of each month and move them to d:\backups\monthly_backups
I have no problems extracting the file names, but can figure out how to retrieve the created on date for the files, and lastly, move everything created on the 4th.
Code I have so far:
create table #output (FileName varchar(200))
insert into #output exec master..xp_cmdshell 'move /y "D:\backups\*.bak" "D:\backups\Monthly_Backups"'
Would a datepart be what I’m needing to achieve this?
Any input welcome and THANKS!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

try using xcopy with a command to delete

or you can do a dir to parse the filename and date info into a temp table and then do a move based on your selection criteria from the table.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736468 More actions · Answer 2

I would not use T-SQL to do this. User VBScript or the Powershell scripting languages to do this. They are better suited to work with files.

The FileSystemObject can help you easily and access file properties.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Have you considered using a maintenance plan, or an SSIS package to do this?

Both seem like viable alternatives as well (in addition to Steve's suggestion).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

Steve Jones - Editor (12/9/2009)
I would not use T-SQL to do this. User VBScript or the Powershell scripting languages to do this. They are better suited to work with files.
The FileSystemObject can help you easily and access file properties.

I've respectfully but firmly have to disagree with that... XCopy makes it super simple to move files by date. And, you don't even need to know the file names.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 5

CirquedeSQLeil (12/9/2009)
Have you considered using a maintenance plan, or an SSIS package to do this?
Both seem like viable alternatives as well (in addition to Steve's suggestion).

Yes I have but my experience with SSIS is minimal at best and end up at the same stopping point when trying to move by created date.

Meh I'll keep working on it and eventually get ti to do what I want it to do. Thanks for the advice!

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

igloo21 (12/10/2009)
CirquedeSQLeil (12/9/2009)
Have you considered using a maintenance plan, or an SSIS package to do this?
Both seem like viable alternatives as well (in addition to Steve's suggestion).
Yes I have but my experience with SSIS is minimal at best and end up at the same stopping point when trying to move by created date.
Meh I'll keep working on it and eventually get ti to do what I want it to do. Thanks for the advice!

The XCOPY method Jason brought up is very powerful. You're already using xp_CmdShell...

To find all of the switches and operands that XCOPY can take, just go to a CMD window (ie: DOS prompt) type XCOPY /? and press ENTER.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Jeff Moden (12/10/2009)
igloo21 (12/10/2009)
CirquedeSQLeil (12/9/2009)
Have you considered using a maintenance plan, or an SSIS package to do this?
Both seem like viable alternatives as well (in addition to Steve's suggestion).
Yes I have but my experience with SSIS is minimal at best and end up at the same stopping point when trying to move by created date.
Meh I'll keep working on it and eventually get ti to do what I want it to do. Thanks for the advice!
The XCOPY method Jason brought up is very powerful. You're already using xp_CmdShell...
To find all of the switches and operands that XCOPY can take, just go to a CMD window (ie: DOS prompt) type XCOPY /? and press ENTER.

Had xp_cmdshell not already been in use, I probably would not have suggested xcopy. I have seen other cmd line utils used for the same process (such as robocopy) but xcopy is there ready to use.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 8

I figured it out with a little VBS.

oI’m moving all files with *20????02*.bak to E:\Backups\Monthly

oI’m leaving all other bak files in the root of Backups

oI’m purging all files in E:\Backups\ that are older then 8 days

oI’m purging all monthly files that were captured on the 2nd of each month that are older the 100 days. So we’ll have 3 months worth of month end backups kept around

Code:

Set Shell = CreateObject("WScript.Shell")

Shell.Run "cmd.exe /c robocopy E:\Backups E:\Backups\Monthly 20????02*.bak /MOV /copyall"

Shell.Run "cmd.exe /c robocopy E:\Backups *.bak /PURGE /MINAGE:8"

Dim fso, f, f1, fc

Set fso = CreateObject("Scripting.FileSystemObject")

Set f = fso.GetFolder("E:\Backups")

Set fc = f.Files

For Each f1 in fc

If DateDiff("d", f1.DateLastModified, Now) > 8 Then f1.Delete

Set fso = CreateObject("Scripting.FileSystemObject")

Set f = fso.GetFolder("E:\Backups\Monthly")

Set fc = f.Files

For Each f1 in fc

If DateDiff("d", f1.DateLastModified, Now) > 100 Then f1.Delete

Set Shell = Nothing

Thanks for all the advise!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Congrats and you're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events