ORIGINALLY POSTED BY TALEN FOR THETAZZONE/TAZFORUM HERE
Do not use, republish, in whole or in part, without the consent of the Author. TheTAZZone policy is that Authors retain the rights to the work they submit and/or post…we do not sell, publish, transmit, or have the right to give permission for such…TheTAZZone merely retains the right to use, retain, and publish submitted work within it’s Network.
How many servers do you have running applications that make use of MSDE 2000? You know you should be backing up the database files, but you can’t quite justify the extra cost of a SQL agent for your backup software. I’ve come across the situation more than once. Did you know you can use an OSQL command from the command line to create a backup of the database while it’s mounted and running? This command will create a .bak file that you can backup using a regular backup agent, no SQL agent required. I wrote a script that would run the command, and then simply used the Windows Task Scheduler to run the script once a day. The .bak file is overwritten each time, and our Backup Exec server maintains the archival copies. Here’s the script:
strOSQL = “””C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.EXE”” -U <username> -P <password> -n -Q “”BACKUP DATABASE <database> TO DISK = ‘X:\<filename>’ WITH INIT,SKIP”””
Bear in mind that line 8, the line beginning with “strOSQL”, is one command on one line. If you want your script to wrap for readability, break up the command where you see fit.
You’ll also want to adjust the backup path defined in the OSQL command, ‘X:\<filename>’ in the example, to fit your server/network as well. If you want to back it up to a networked drive, make sure the drive is already mapped locally, I haven’t tested this using a UNC path.