SQL database backup question

My wife's business uses a piece of client/server software called "Encompass", by the company "Ellie Mae".

I just got off of their tech support line. I asked them how to back up the server's data, and they said they don't support that. WHAT?

What they mean is, they can tell us what needs to be backed up, but not HOW to do it. Because they don't want to be held responsible for our data going south.

Anyhoo, one of the things that needs to get backed up is the SQL database. Their software installs MSDE (Microsoft Database Engine, which is essentially SQL Lite), and their database within MSDE is called "EMMSDE" (Ellie Mae MSDE). Their installation doesn't include tools such as the SQL Enterprise Manager, which is what I'd normally use to back up the data.

There are a lot of cryptic script commands that I could use with MSDE to back it up, but I'm wondering if it's much simpler than that.

There is a folder...

\Program Files\Microsoft SQL Server\MSSQL$EMMSDE\Data

...which looks like it contains the data I want. If I stop the MSSQL$EMMSDE service, back up that folder, then restart the service, does that mean I've successfully backed it up? And if so, is restoration a similar procedure?

As long as SQL is completely down, you should be able to backup and restore the folders and have it work.

A good / quick way to test is to shut it down, rename the existing folder and copy it back to the original name. See if it starts cleanly. If it does, you have just created your first successful backup; otherwise, shut it down again, rename the original back and restart.

Excellent. Thanks.

Incidentally, that works with just about any database engine. I do that with mysql all the time.

Here is a little administrative tool which does most of the stuff that Enterprise Manager does, certainly everything I've ever needed to do on machines that didn't have it installed.

Gareth

Oo, nifty, thanks!

NB: You can't backup a live DB the way Paul has suggested (he does mention that).

If there is no problem taking the DB down for a few minutes after hours then no problem.

If you want to backup to tape, that's going to get a bit more complicated unless there is no problem taking the DB down before the backup and bringing it back up after the tape finishes (i.e. a longish time).

Quote:
If you want to backup to tape, that's going to get a bit more complicated unless there is no problem taking the DB down before the backup and bringing it back up after the tape finishes (i.e. a longish time).

Or you could shut the database down, copy the files, start the database back up, then backup the copies.