+ Post New Thread
Results 1 to 8 of 8
How do you do....it? Thread, Copy files to another location in Technical; I need to backup and copy two .mdf (SQL) files everyday to another location, and have it appended to folders ...
  1. #1

    Join Date
    Jun 2008
    Posts
    718
    Thank Post
    118
    Thanked 64 Times in 52 Posts
    Rep Power
    31

    Copy files to another location

    I need to backup and copy two .mdf (SQL) files everyday to another location, and have it appended to folders with their current date.

    For example, today's date with the copied files. Tomorrow's date with the copied files etc.

    How would I go about doing this?

  2. #2

    Join Date
    Feb 2006
    Location
    Derbyshire
    Posts
    1,381
    Thank Post
    181
    Thanked 211 Times in 171 Posts
    Rep Power
    65
    Quote Originally Posted by jinnantonnix View Post
    Not as easy as it sounds, because the date /t returns slashes in the date, which cannot form part of a filename. So I've used a feature of the set command to replace the slashes with a hyphen.
    Alternative to this is using

    Code:
    filename-%date:~6,4%-%date:~3,2%-%date:~0,2%
    in the batch which will give you filename-YYYY-MM-DD or can be rearranged into DD-MM-YYYY

  3. Thanks to OutToLunch from:

    Chuckster (23rd October 2009)

  4. #3

    Join Date
    Jun 2008
    Posts
    718
    Thank Post
    118
    Thanked 64 Times in 52 Posts
    Rep Power
    31
    Sorry, the files need to go in like this:

    C:\Backup\23-10-2009\name_of_file.pdf
    C:\Backup\24-10-2009\name_of_file.pdf
    etc

    The location it's copying from is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

    This is how I have it

    Code:
    for /f "tokens=1" %%a in ('date /t') do set Today=%%a
    
    set Today=%Today:/=-%
    
    copy C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pr2.mdf C:\Backup\%Today%
    
    copy C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pr2_log.ldf C:\Backup\%Today%
    But it says "the system cannot find the file specified."

    Any ideas?

  5. #4
    Jamo's Avatar
    Join Date
    Jan 2009
    Posts
    1,349
    Thank Post
    66
    Thanked 175 Times in 147 Posts
    Rep Power
    60
    enclose the path in " " speech marks as it will be looking at C:\Program at the moment for the path due to the spaces

  6. #5

    Join Date
    Feb 2006
    Location
    Derbyshire
    Posts
    1,381
    Thank Post
    181
    Thanked 211 Times in 171 Posts
    Rep Power
    65
    Not sure, but aren't the mdf files locked when in use? If you still get errors after adding the spaces you may find you cannot just do a simple backup like this while the database is online.

  7. #6

    Join Date
    Jun 2008
    Posts
    718
    Thank Post
    118
    Thanked 64 Times in 52 Posts
    Rep Power
    31
    Quote Originally Posted by OutToLunch View Post
    Not sure, but aren't the mdf files locked when in use? If you still get errors after adding the spaces you may find you cannot just do a simple backup like this while the database is online.
    I'm stopping and starting their services as a way round it.

    How do I get the script to not rename the filename to the current date?

  8. #7

    Join Date
    Feb 2006
    Location
    Derbyshire
    Posts
    1,381
    Thank Post
    181
    Thanked 211 Times in 171 Posts
    Rep Power
    65
    Quote Originally Posted by Chuckster View Post
    I'm stopping and starting their services as a way round it.

    How do I get the script to not rename the filename to the current date?
    Your script is doing a copy operation on the file and assuming as it's copying a single file, %today% is what you want to rename the file to. If you want to stick with the same script you have, you'd change it to

    Code:
    for /f "tokens=1" %%a in ('date /t') do set Today=%%a
    
    set Today=%Today:/=-%
    
    xcopy C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pr2.mdf C:\Backup\%Today%\
    
    xcopy C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pr2_log.ldf C:\Backup\%Today%\
    Note that I have switched over to xcopy as copy on it's own will not automatically create a folder when copying, also the \ after the directory name (%today%) which tells it it is copying into a directory not changing the filename.

  9. #8

    Ric_'s Avatar
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,590
    Thank Post
    109
    Thanked 762 Times in 593 Posts
    Rep Power
    180
    Why not just set up a scheduled job using SQL Server Management Studio to do this?

SHARE:
+ Post New Thread

Similar Threads

  1. Software to copy files by extension
    By cookie_monster in forum Windows
    Replies: 6
    Last Post: 29th June 2009, 12:57 PM
  2. Weird can't copy files error
    By mark in forum Windows
    Replies: 6
    Last Post: 16th June 2008, 04:19 PM
  3. How to copy files from two domains
    By timbo343 in forum Scripts
    Replies: 5
    Last Post: 18th February 2008, 09:43 PM
  4. Failed to copy files from CD
    By Dos_Box in forum Learning Network Manager
    Replies: 13
    Last Post: 12th April 2007, 10:42 AM
  5. Replies: 3
    Last Post: 1st March 2006, 12:08 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •