[magusbuckley]

Hello:

At work, a small hard drive on a backup PC is filling up. If it does fill up, our Mircros database will fail to sync between the main server and the backup server. I found an SQL Backup folder that seems to be taking up most of our drive space. We've been moving that data to another server (temporary fix) on a daily basis. I sat down at my desk today thinking I'd write a batch file to automate the process, but it isn't happening. When the program runs, I get an error message stating - "File Not Found". I'm hoping someone can look at the following code and pin point my problem.

@echo off
TITLE SPACE SAVER
REM
REM THIS PROGRAM MOVES DATA FROM AN SQL BACKUP FOLDER TO ANOTHER SERVER TO FREE UP
REM SPACE ON THIS HARD DRIVE. IF THIS HARD DRIVE IS FULL, THE MICROS DATABASE WILL
REM NOT SYNC BETWEEN THE PRIMARY AND BACKUP SERVERS.
REM
REM WRITTEN 09/01/06
REM
REM
REM WRITTEN BY @@@@@@@@@@@@@@
REM
REM
REM COPY DATA TO THE BACKUP DOMAIN CONTROLLER
CLS
ECHO COPY DATA TO THE BACKUP DOMAIN CONTROLLER - WORKING....
ECHO DELETE ORIGINAL BACKUP FILES TO FREE DISK SPACE - IN QUEUE
COPY D:\PROGRAM FILES\Microsoft SQL Server\MSSQL\Backup\*.* X:\BACKUP\*.* /Y
REM
REM
REM DATA IS COPIED...DELETE THE ORIGINAL FILE
CLS
ECHO COPY DATA TO THE BACKUP DOMAIN CONTROLLER - COMPLETE
ECHO DELETE ORIGINAL BACKUP FILES TO FREE DISK SPACE - WORKING....
DEL D:\PROGRAM FILES\Microsoft SQL Server\MSSQL\Backup\*.*
REM
REM
REM DATA HAS BEEN MOVED TO THE BACKUP DOMAIN CONTROLLER. EXIT PROGRAM.
cls
ECHO COPY DATA TO THE BACKUP DOMAIN CONTROLLER - COMPLETE
ECHO DELETE ORIGINAL BACKUP FILES TO FREE DISK SPACE - COMPLETE
EXIT


The folder is indeed on the "D" drive and the destination folder has been mapped to as an "X" drive. Any and all information will be greatly appreciated.

Thanks,

Magus

[Advertisements]

Hi,

"file not found" means that a file or path does not exist. In your case it is the path. In your script you use the command line
COPY D:\PROGRAM FILES\Microsoft SQL Server\MSSQL\Backup\*.* X:\BACKUP\*.* /Y.

The command prompt cannot work with spaces, as you used it in "PROGRAM FILES\Microsoft SQL Server".
If you remember MS-DOS, you will know that it did not work wirt spaces, too. Longer filenames have been shortened to an eight-signs-name with the ending ~1.

The script should work if you replace lines like the one above with the following one:
COPY D:\PROGRA~1\Micros~1\MSSQL\Backup\*.* X:\BACKUP\*.* /Y.


Folders always have two names: the MS-DOS name and the Windows-Name, this ist even so valid unter Windows XP, 2003, ... . It is possible that you have to change the number at the end of the folder names. If for example i Office is installed it would look like the following:

D:\Program Files\Microsoft Office
D:\Program Files\Microsoft SQL Server

and in DOS:
D:\Progra~1\Micros~1
D:\Progra~1\Micros~2

Hope you understood what I wanted to explain...

Edited by BlueScreen-Bertrand, 04 September 2006 - 05:59 PM.

[BlueScreen-Bertrand]

Hi,

"file not found" means that a file or path does not exist. In your case it is the path. In your script you use the command line
COPY D:\PROGRAM FILES\Microsoft SQL Server\MSSQL\Backup\*.* X:\BACKUP\*.* /Y.

The command prompt cannot work with spaces, as you used it in "PROGRAM FILES\Microsoft SQL Server".
If you remember MS-DOS, you will know that it did not work wirt spaces, too. Longer filenames have been shortened to an eight-signs-name with the ending ~1.

The script should work if you replace lines like the one above with the following one:
COPY D:\PROGRA~1\Micros~1\MSSQL\Backup\*.* X:\BACKUP\*.* /Y.


Folders always have two names: the MS-DOS name and the Windows-Name, this ist even so valid unter Windows XP, 2003, ... . It is possible that you have to change the number at the end of the folder names. If for example i Office is installed it would look like the following:

D:\Program Files\Microsoft Office
D:\Program Files\Microsoft SQL Server

and in DOS:
D:\Progra~1\Micros~1
D:\Progra~1\Micros~2

Hope you understood what I wanted to explain...

Edited by BlueScreen-Bertrand, 04 September 2006 - 05:59 PM.

[magusbuckley]

Bertrand:

I understand what you are saying completely. I'll try the new code tomorrow when I'm back at work. I have a funny feeling you've just solved my problem though. Thanks for the reply. I'll post back and let you know what happens.

Thanks,

Magus

[magusbuckley]

Bertrand:

You fixed my problem. The new code worked fine. There was an unexpected naming scheme of the files though so I had to rewrite the code. In my original code, I was telling the computer to overwrite the existing files on the backup domain controller. The problem, however, is that the file names are always preceded with the system date. As such, nothing was being overwritten. We would have a huge file named backup09062006 and then the same huge file named backup09072006 and so on and so forth. To combat this issue, I have the computer delete the backup folder from the Backup Domain Controller, Copy the Backup folder from our Micros server to the Backup Domain Controller, and finally delete the backup folder on the Micros server. In the end, the code looked like this:

@echo off
TITLE SPACE SAVER
REM
REM THIS PROGRAM MOVES DATA FROM AN SQL BACKUP FOLDER TO ANOTHER SERVER TO FREE UP
REM SPACE ON THIS HARD DRIVE. IF THIS HARD DRIVE IS FULL, THE MICROS DATABASE WILL
REM NOT SYNC BETWEEN THE PRIMARY AND BACKUP SERVERS.
REM
REM WRITTEN 09/01/06
REM
REM
REM WRITTEN BY @@@@@@@@@@@@@
REM
REM
REM DELETE OLD BACKUP DATA FROM BACKUP DOMAIN CONTROLLER
CLS
ECHO DELETE OLD BACKUP DATA FROM BACKUP DOMAIN CONTROLLER - WORKING....
ECHO COPY DATA TO THE BACKUP DOMAIN CONTROLLER - IN QUEUE
ECHO DELETE ORIGINAL BACKUP FILES TO FREE DISK SPACE - IN QUEUE
DEL X:\BACKUP\COREDB\*.* /Q
DEL X:\Backup\locati~1\*.* /Q
DEL X:\Backup\portaldb\*.* /Q
DEL X:\Backup\rta\*.* /Q
REM
REM
REM COPY NEW BACKUP DATA TO THE BACKUP DOMAIN CONTROLLER
CLS
ECHO DELETE OLD BACKUP DATA FROM BACKUP DOMAIN CONTROLLER - COMPLETE
ECHO COPY DATA TO THE BACKUP DOMAIN CONTROLLER - WORKING....
ECHO DELETE ORIGINAL BACKUP FILES TO FREE DISK SPACE - IN QUEUE
COPY D:\PROGRA~1\Micros~1\MSSQL\Backup\coredb\*.* X:\BACKUP\coredb\*.* /Y
COPY D:\PROGRA~1\Micros~1\MSSQL\Backup\locati~1\*.* X:\BACKUP\locati~1\*.* /Y
COPY D:\PROGRA~1\Micros~1\MSSQL\Backup\portaldb\*.* X:\BACKUP\portaldb\*.* /Y
COPY D:\PROGRA~1\Micros~1\MSSQL\Backup\rta\*.* X:\BACKUP\rta\*.* /Y
REM
REM
REM DATA IS COPIED...DELETE THE ORIGINAL FILE FROM NETVIEWPOINT SERVER
CLS
ECHO DELETE OLD BACKUP DATA FROM BACKUP DOMAIN CONTROLLER - COMPLETE
ECHO COPY DATA TO THE BACKUP DOMAIN CONTROLLER - COMPLETE
ECHO DELETE ORIGINAL BACKUP FILES TO FREE DISK SPACE - WORKING
DEL D:\PROGRA~1\Micros~1\MSSQL\Backup\coredb\*.* /Q
DEL D:\PROGRA~1\Micros~1\MSSQL\Backup\locati~1\*.* /Q
DEL D:\PROGRA~1\Micros~1\MSSQL\Backup\portaldb\*.* /Q
DEL D:\PROGRA~1\Micros~1\MSSQL\Backup\rta\*.* /Q
REM
REM
REM DATA HAS BEEN MOVED TO THE BACKUP DOMAIN CONTROLLER. EXIT PROGRAM.
cls
ECHO DELETE OLD BACKUP DATA FROM BACKUP DOMAIN CONTROLLER - COMPLETE
ECHO COPY DATA TO THE BACKUP DOMAIN CONTROLLER - COMPLETE
ECHO DELETE ORIGINAL BACKUP FILES TO FREE DISK SPACE - COMPLETE
EXIT

Thanks for your help.

Magus

[BlueScreen-Bertrand]

Hi, thank you for your reponse.

I found something that might be interesting for you:
http://en.wikipedia.org/wiki/Rsync
http://www.antidis.c.../windows-rsync/

But you can be more proud on something that is written by yourself