Copy SQL BAK FIles to an External drive or NAS

We have SQL Server 2008 SP1 that we need to copy a BAK Files

I wanted to copy all the files on this folder\\amssql\D$\MSSQL$AMS2008\MSSQL10.AMS2008\MSSQL\Backup

to \\ExternalDrive\CopyFolder

Then i want anything older than five days on [/]CopyFolderto deleted

We prefer  the script to be powershell
tomfontanillaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tomfontanillaAuthor Commented:
In addition, we would like to create a Log files on each job that run.
0
arnoldCommented:
One option to achieve this is to use sql jobs to create a mirror backup where it does and on the new device that you create that points to \\...

Presumably your sql backup is already setup to maintain only five versions of the backup files, the simple rule, is you get the listing from the source directory and put the files into array/hash. Another array will include the files from the destination location. First you can delete from the destination files that are not present at the source.
Then you copy the file that is exists at the source but is missing from the destination.

A simpler option is to use robocopy with the /MIR option

Robocopy source \\destination /MIR
It will only copy files that are new and delete from the destination files that do not exist at the source.

You can schedule its runs using schtasks and /LOG is an option to robocopy that will maintain a log of the job.
0
yo_beeDirector of Information TechnologyCommented:
You can also use robocopy cli.
Robocopy "source" "destination" /e /xo *.bak /log:\\path\sqlrobocopy.log /np /tee

Save it as a bat file and schedule it to run.
http://technet.microsoft.com/en-us/magazine/ee851678.aspx
0
Redefine Your Security with AI & Machine Learning

The implications of AI and machine learning in cyber security are massive and constantly growing, creating both efficiencies and new challenges across the board. Check out our on-demand webinar to learn more about how AI can help your organization!

David ToddSenior DBACommented:
Hi,

 ... or use xcopy.

Regards
  David
0
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
To add a little piece... the remove files older than five days can be done in powershell as:
get-childitem | ? {$_.creationtime -lt (Get-Date).addDays(-5)} | remove-item -verbose

Open in new window


-verbose tells the script to output each line.
You can replace it with -whatif, while testing.
0
yo_beeDirector of Information TechnologyCommented:
I would like to apologize for restating a suggestions that  arnold mentioned.

Here is an additional switch I left out that will help with the files age  /MINAGE:n

robocopy  "Source" "Dest" /E /XO *.BAK  /MINAGE:5 /Log:"Path\Filename" /NP /TEE
0
tomfontanillaAuthor Commented:
Guys,
We prefer Powershell.

 Razmus,

Good start, can provide the exact solution?
0
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Okay, I'm not a powershell guru, but the basic outline of a script in what I hope to be an easy to follow and customize format:

# Setting directories - End with backslashes, and ensure the directories exist
$Source = 'd:\temp\x\'
$Des = 'd:\temp\w\'
$logdir = 'd:\temp\log\'
#----
$LogFile = $logdir+"Cleanup"+[DateTime]::Now.ToString("yyyyMMdd")+".log"

#First we're going to check to make certain the directories exist.  This doesn't make certain they end in backslash

if ((test-path $Source) -and (test-path $Des) -and (test-path $logdir))
  { #our paths exist! 
  }
  else
  {write-host "I'm expecting the source and destination paths to already exist!"
   Break
  }
  
#Okay, copying files  
  
add-content $LogFile -Value "*** Copying files ***"
foreach ($f in Get-ChildItem $Source)
{
   if (test-path $Des$f) 
      {
      #uncomment if you want to log files NOT copied
      #add-content $LogFile -value "Not Copied [$f]"
      } 
   else 
      {
      copy-item $Source$f $Des$f #-whatif
      add-content $logfile -Value "Copied [$f]"
      }
}


#now removing files.

add-content $LogFile -Value "** Removing old files **"
foreach ($f in Get-ChildItem $Des)
{
     if ($f.creationtime -lt (Get-Date).addDays(-5) )
        {
        remove-item $Des$f 
        Add-content $LogFile -Value "Deleted [$f]"
        }
}   

Open in new window


My test directories are still in there, you can modify with your own.
Please, please, please test in a safe place before you put in anywhere near a production environment.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Of course, there's a thousand ways it could be improved...  more error checking, not hard coding the number of days old you want old files deleted, and using a variable instead.  I think this gives a starting point though.

Eep... and change that 'Break' in the first block to 'Return'!
0
yo_beeDirector of Information TechnologyCommented:
Can I ask a question.
Why try an reinvent something that already does the job with very little code.
The PS just seems a bit much IMO.
0
tomfontanillaAuthor Commented:
Razmus,

I will modify your code, and run some test on my test environment. I will update you tomorrow, by noon time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.