Backing up MSDE installation

[Pat Wilson]

I remember seeing something some time ago with a script to backup the MSDE 'Insight_v1_0' database. I believe the default name (from the documentation) was 'myBackup.sql'. For the life of me I can't find it any more. Does this ring any bells with anyone else? Anyone have a copy of this script?

Thanks.

[Paul Timmerman_1 · Accepted solution]

I do not know if there is a way to do this directly through insight manager but you can easily back the database up to disk through the use of some SQL tools like Query Analyzer or the command line version OSQL.

Just issue the following command:

BACKUP DATABASE xxx TO DISK = 'yyy\xxx.bak'

xxx = database name
yyy = disk path

Paul

[Pat Wilson]

Thank you Paul for the info. I'll try your script as well. (it's much simpler than the one I was thinking of).

I found the 'mybackup.sql' about half-way through the "HP Systems Insight Manager Help Guide" (502 pages!) located at:

http://h18000.www1.hp.com/products/servers/management/hpsim/infolibrary.html

The reason I hoped someone else had a copy is that I had previously spent about half a day getting it to work in my environment. (I'm definitely NOT a Transact SQL guru)

:)

PS. I have attached a text copy of the .sql procedure for your perusal.

[Paul Timmerman_1]

Well as luck might have it, I am a SQL DBA by training so this is right up my alley.

The difference between what I gave you and what the script provided by HP shows is the following:

My code will execute a one time backup. The script provided by HP actually creates a job that can be executed at scheduled intervals utilizing the SQL Server Agent. Keep in mind that sans a few minor differences, MSDE is SQL Server.

The script provided by HP also provides a step for transaction log backups. This is a much larger issue than what I will go into here but lends to what type of recovery capabilities you need for the database. If you need to be able to recovery to a specific point in time (lets say you can only stand to lose 30 minutes of data is the server blows up) then you should be doing transaction log backups at least every 30 minutes. Your DB should also be in full recovery model. If you do not need point in time recoverability and going back to your last DB backup is fine, then you can skip the TLOG backups (make sure to put the DB in simple recovery mode so the log does not balloon) and routinely backup the database as per your needs.

Paul

[Pat Wilson]

So, if I run this script once, it will create a permanent job that I then only need to use the "EXEC sp_start_job @job_name = 'mydbBackupJob'" command to execute?

[Paul Timmerman_1]

True, you could do it that way. the easier way though is to schedule the job through the agent so it runs on a consistent basis. it does not appear that the script establishes a schedule so you would need to that yourself.

Paul