Database Backup with Transact-SQL

I’ve recently installed the Microsoft Small Business Accounting package. I have to say I’m enjoying it and think I’ll keep using it. Unfortunately, I didn’t like the Business Contact Manager for Outlook, as though the two would supposedly talk to one another, I didn’t think it integrated particularly well on either side.

In any case, the application makes use of MSDE for database storage. This is all fine and dandy but it doesn’t really help with backing up data or anything of the like. So I had to go digging through pages to find some decent Transact-SQL references. This one is to back up a database from the command line.

The first challenge I had was getting into the database. It appears that MSDE is configured during installation to use Windows authentication, with the user who is installing the app as the database owner. As such, you cannot specify the user name on the command line. That’s a real pain.

Instead, use the -E switch to establish a trusted connection – just make sure you are logged in as the user in question first! The database is called microsoftsmlbiz, and the computername is just the name of the computer on which you installed the software. Your command should look something like this:

  osql -S <computername>\microsoftsmlbiz -E

If all goes well, you will now have a prompt to enter commands. Once you’re there, you just need to feed the name of your database and your backup location to the processor:

  backup database <databasename> to disk = '<path>'

Where the databasename is the name of the database and path is the path, including disk and filename, to where you’d like to save the backup. If you don’t want to go through the trouble of logging in, say if you’d like to schedule a job, then add the -Q parameter, with your command in quotes, to the end of the command:

  ... -Q "backup database <databasename> to disk = '<path>'"

The process will run your command (hopefully successfully) and exit when it’s done.

2 Replies to “Database Backup with Transact-SQL”

  1. Already have. 🙂

    But I still like the ability to use osql for a quick-and-dirty database backup, which can be scheduled or run manually as needed, instead of needing to set up a job to do it within the database platform.

  2. If you’re a subscriber to the Microsoft Action Pack, load the SQL tools from your SQL Server Developer edition CD. It talks to the MSDE database just fine. Now you can use the Query Analyzer as well as the Enterprise Manager. Some repetitive SQL jobs are a little tricky to do, but other than that you are good to go. Remember, MSDE is SQL Server except they limit you to a 2GB database size. I set up repetitive backups for MSDE in the Enterprise Manager.

    Microsoft Dynamics GP Standard can be run on the MSDE and the MSDE is free. I get the client to buy a copy of the SQL Sever Developer Edition ($50) and load that on the server so I have all my SQL tools to do whatever I need to do.

Comments are closed.