TheTAZZone - Internet Chaos

create a .bak file that you can backup using a regular backup agent

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

Please refer to the original post as not all the script may be visible here.

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:

Code: Select all
Option Explicit

Dim objShell
Dim strOSQL
'-------------------------------------------------------------------------------
Set objShell = CreateObject("WScript.Shell")

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"""
'-------------------------------------------------------------------------------
objShell.Run strOSQL, 1, True

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.

Enjoy.

Leave a Reply

Your email address will not be published. Required fields are marked *

Advertise

If you'd like to advertise on The Mutt ( aka TheTAZZone.com ) feel free to contact us at: administration[at]thetazzone.com

TheTAZZone is a non-commercial entity. We do not sell any products or services ourselves. Our revenue comes from advertising and donations only.

We appreciate your support! Your advertising revenue ( or donations ) helps us to continue to upgrade, improve, and offset the costs of maintaining this site.

Donations can be made through the page ' Donate '.