Only Full Backup of SQL Master Database? (v 6.5)

[CCC]

After using Retrospect for many years on my own peer-to-peer network and on the domain networks of various clients, I recently upgraded my system to SBS 2003 server and a domain. Consequently, I upgraded from Retrospect 5.6 to 6.5 in order to backup SQL and Exchange on SBS.

 

My preferred backup mode is to do a full backup once a week and an incremental ("normal" in Retrospect parlance) backup the other six days, changing the tape once a week. I understand this is not as safe as doing a full backup every day with complicated tape rotation schemes, but it meets my needs for simplicity while providing a reasonably long history. (It also meets the needs of many of my small business clients who have only five to ten workstations and a small staff).

 

I now find that I can not do an incremental backup (Differential or Log) on the SQL Master database. Only a Full backup can be done. But the option for SQL backup style for scripts is set for the whole backup script, not on a per database basis.

 

It would appear then that I must further split my normal backup script so that the Master SQL database is backed up by its own script. This means I must now run a separate script on the first night to do a full backup on all databases, then two separate scripts on the other nights, one to backup the Master database Full and the other to backup the other databases Differential or Log.

 

I searched the documentation, Help, FAQs, Forums and Google news groups and found no information about backing up the SQL Master database in Retrospect.

 

Is this correct: I have to use three spearate scripts to backup of the SQL database incrementally? Or am I missing something?

 

[natew]

Hi

 

You are correct. This was actually a major point of debate at Dantz when the 6.5 Exchange and SQL agents were being built.

 

Unlike regular backups Retrospect does not (cannot) perform matching operations on the databases. The SQL and Exchange agents rely on the Microsoft APIs to determine what needs to be backed up for full/incremental/log etc. backups. As a result database backups don't respond to weekly recycle backups and backup set rotations the way regular file backups do.

 

A bit of a scheduling pain for sure but there is just no better way to back up a live database than the Microsoft APIs.

 

Nate

 

 

 

 

 

 

[CCC]

Thanks for the reply. I was hoping I overlooked something.

 

 

 

However, if I understand the way scripts work, it is more than just a pain to set up; it is also wearing on the tapes and drive. I believe the tape is repositioned at the start of each backup script. If this is the case, having to run multiple scripts means multiple tape passes to position at the end of the last set written by the previous script.

 

 

 

Multiple scripts also means having to look at multiple log files to verify everything worked OK. Most of my clients are small businesses with only a few staff people. They want a really simple backup scheme that requires a minimum amount of interaction and a minimum amount of supervision on my part. Few of my clients have anybody that can make sense of the Retrospect log. They just want a simple answer to "Did it run OK?" Multiple scripts running every night just makes things worse.

 

 

 

I would rather run one script per night. As it stands now, for SBS I have to run three scripts: one for the server system, Exchange, all databases except Master, and most desktop systems that are connected and on 24x7; another for the Master database; and a third for laptops and desktops that may or may not be connected and turned on.

 

 

 

It would be nice if we could just set the backup type for each database and run one script.

[dzenizo]

Hey, I'm confused. I admit I'm not a SQL expert, so bear with me.

 

When backing up a SQL database, there are options to do Full Backup, Differential Backup, Log Backup and Log Backup No-Truncate. Until now, I was only doing Normal and Recycle backups of my servers (some of them with licensed SQL agents), and the log reported I was backing up my SQL databases with Full Backup.

 

Now, what should I expect when restoring. Do I have only the latest database image, and NOT previous sessions databases, like last monday database image? That is, if I need to restore previous images, I cannot do it since I only have the latest image?

 

So, what would be a best practice to script in order to have images for each day of the last two weeks? (kind of normal and recycle backups for regular files).

 

Also, in the manual there's a small note about restrictions (page 265) that says I cannot archive SQL data, so does that mean I cannot directly do an SQL archive (like I'd do for a regular file), or that I can indeed archive a Backup Set and later restore whatever it's in the archive, including SQL backups?

 

Your comments will be very much appreciated.

 

Diego Zenizo

[natew]

Hi

 

FWIW - In my experience failed tapes are pretty rare, even with a lot of use.

 

I don't have a tape drive here so I'm not 100% sure but I thought the tape doesn't rewind until you eject it or quit Retrospect. As an option you can type CTRL ALT P P ad any Retrospect screen and turn off the rewind at quit setting.

 

Thanks

Nate

[natew]

Hi

 

Retrospect keeps snapshots of the databases much like it does with regular volumes. As long as you take incremental backups and have all the tapes required to restore those backups you will be able to restore to any point in time. This is true even if the logs are truncated.

 

Thanks

nate