Couple of quick questions: I'm programming a Macro to automate DB backups offsite to an FTP server. Is it sufficient to just perform an xcopy off the ..mdf and .ldf files for the particular DB? Can this be done without shutting down any of the DB applications that are running?
Thanks in advance!
-=Rich=-
Don --
That makes sense, thanx!
Rich
"D> It cannot be done without stopping the mssqlserver service. It would be
I use just such a script myself. I use a batch file and here is the code: osql -Uusername -Ppassword -Q "BACKUP DATABASE SRCNEW TO DISK 'c:\db_backup.bck'" cd "program files\7-zip"
7z a "c:\db_backup.7z" "c:\db_backup.bck" cd \ ftp -v -s:ftp_script_file.ftp ftpserver_name del db_backup.7z del db_backup.bckosql is a command line tool built into windows for running queries on mssql db's. 7-zip is an application which can be found at
I'm sure you can schedule the backup through Windows scheduler but I choose to use System Scheduler from
Lance
"hitman" wrote:
I'm using task scheduler & a batch file to perform automated backups. Is there a reason you cannot simpy map the ftp site as a network drive and use the OSQL command in a fashion similar to Lance's methed above.
For example, where ftp site = drive X: OSQL -U sa -P password -S serverName -Q "BACKUP DATABASE dbName TO DISK 'X:\backupDir\backupfile.bck 'WITH FORMAT, STATS"
I'm even using a 3-day auto-archive system that looks like this: CD X:\backupDir
IF EXIST DailyBackup3.bck DEL DailyBackup3.bck IF EXIST DailyBackup2.bck REN DailyBackup2.bck Dailybackup3.bck IF EXIST DailyBackup1.bck REN DailyBackup1.bck Dailybackup2.bck
I got the idea for this sytem from an older topic on this group.
You might have to add a NETUSE command to your batch file just to make sure tha the X: drive is connected.
Best of luck, Tom
Have something to add? Share your thoughts — no account required.
Ask the community — no account required