Jump to content

MSDE Backup ?


realized

Recommended Posts

My office uses propriety software that utilizes a MSDE database and we are looking for a solution for automatic backups. I have no experience with msde.

Please recommend any commercial or non commercial products which you use.

Thanks guys!

Link to comment
Share on other sites


I'm sure there are tons of commercial apps that can do the job (I bet searching for "MSDE backup" finds LOTS of them), some being ungodly expensive, and some cheap but often unreliable ones, but why bother?

OSQL is all one needs - along with the proper TSQL statement e.g.

BACKUP DATABASE SomeDataBase TO DISK = 'c:\backups\something'

or such; replace DATABASE for LOG if you want to backup the logs

If you need help on how to write such queries, just consult BOL (Books OnLine), a free download from Microsoft (google it if you can't find it). I bet technet and such places have articles too, and google can most likely find lots more.

Write a little script/batchfile to do it for you, and schedule that task or whatever you want. Free, quick, reliable, you can tweak/modify it as much as you want as you have the "source code" of the script. I like to append backup date in YYYYMMDD ISO date format in the backup name personally. If you want to be fancy, wrap up all your backup TSQL in a sproc (which will even generate & append the date and even email if you want), and just call that sproc from OSQL (schedule that). You can even schedule it (using a sproc - sp_add_jobschedule) to run too, bypassing OSQL altogether. It's actually pretty straightforward, and if you look around, I'm sure you can find basically "pre-made" statements you can reuse with very little changes.

BTW, I'd look into support for SQL Server 2005 Express. MSDE is a bit dated, and not very good by today's standards.

Whatever you do, DON'T pick something that just copies the files as-is! Databases aren't backed up like that.

Link to comment
Share on other sites

Yes, but then the app can't be used. Considering it's just as much trouble to stop and restart the database than to just back it up the standard way, I see no reason to create unecessary "downtime" every time you want to back it up. I mean, it comes with standard and very good backup mechanisms - it can take care of it by itself all using sprocs (self-contained), without scheduling anything if you want. There's no reason to stop it (in most cases, people can't afford totally unecessary downtime like that - much less for every single backup)

Link to comment
Share on other sites

If you want to post MS articles about backing up MSDE, why not post relevant ones? This mentions NOTHING of how to back up a DB.

Try

http://support.microsoft.com/kb/325003

or

http://support.microsoft.com/kb/q241397/

Short answer: use the OSQL utility or sprocs (with a TSQL "BACKUP DATABASE" statement)

Thanks guys, i'll try the osql commands, should be good to go =)

I'll keep you posted

Link to comment
Share on other sites

This artivle explains how you should tackle this problem, it is a bit scetchy on the actual software used but does have some sql statements to verity the integrety of the database for instance.

http://support.microsoft.com/kb/328747/en-us

Hope it helps,

Lilbit hostile ey?

Try reading the article someday, might surprise you how relevant it could be. 90% of a backup scheme is planning what and how to recover when itr fails otherwise the backup is moot.

The friggin title says : Recovery Planning for Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Databases, excuse me for not going in blind and planning to actualy restore a db when it fails ..

Link to comment
Share on other sites

Lilbit hostile ey?

Try reading the article someday, might surprise you how relevant it could be. 90% of a backup scheme is planning what and how to recover when itr fails otherwise the backup is moot.

The friggin title says : Recovery Planning for Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Databases, excuse me for not going in blind and planning to actualy restore a db when it fails ..

No idea what you're seeing as hostile.

I have read the irrelevant article, and it says nothing of HOW to do backups, which is exactly what he's asking for (NOT how to do planning and such).

Title or not, that's NOT what he asked for, so I pointed to relevant ones (which say EXACTLY how to do it). And obviously he's already planned the most part, as he's asking how to back them up - nobody's going in blind here, you're just assuming other people are.

Besides, I totally disagree on the 90% is planning part. Planning a simple DB backup is more or less trivial. Implementing (install hardware, backup software, configure it all, write sprocs and schedule stuff, etc), testing and monitoring is the tedious part. I've seen many times backups gone wrong (even if planned right), because of bad credentials, ACL issues, insufficient disk space and such, hence the testing (ensuring it actually runs when scheduled and works), and monitoring (like disk space).

Edited by crahak
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...