Copy files to another location

[Chuckster]

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?

[OutToLunch]

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

 

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

[Chuckster]

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

 

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?

[Jamo]

enclose the path in " " speech marks as it will be looking at C:\Program at the moment for the path due to the spaces

[OutToLunch]

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.

[Chuckster]

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?

[OutToLunch]

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

 

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.

[Ric_]

Why not just set up a scheduled job using SQL Server Management Studio to do this?