Maintenance Plan Question SQLA 12

[former_SQLA_member1694900]

I have defined a Mainenance Plan using the wizard in Sybase Central (SQLA 12) to execute a daily full image online backup (while de db is running).

1. I have "instructed" the plan to execute command "CHECKPOINT;" before it starts. Is that necessary? (or is the plan implicitly doing this before starting)

2. I have included a validation of database pages (Checksum check) at which point I realized that I had better check both "Disconnect all users when maintenance plans runs" and "Disallow logins while the maintenance plan is running" in order not to get false-positives about corruption.

Now, in a case of a failure during the execution of the plan, will that leave the db engine in an unusable state (meaning no client can connect to it anymore because of having both previously mentioned checks on)?

In such a case, will the engine have to be restarted in order to become operational?

I know this is more than one question but please bear with me since I really need some help here. Thank you.

[VolkerBarth]

Just to answer point 1:

The maintenance plan itself creates an event, and you can easily check that event's handler code (and from your posting, I think you have already done that...). That event will here contain a BACKUP DATABASE STATEMENT, which will itself force a CHECKPOINT, as the docs tell. So IMHO, an explicit checkpoint statement is not necessary.

[reimer_pods]

A thought on point 2:

I wouldn't recommend running a validation on a database that's in use. The validation could be executed on the backup database created in step 1. That way you could make sure you got a valid backup. While the maintenance plan won't enable you to do so directly, it should be possible to write some SQL statements to run then validation on the backup database (or a copy of it so the backup would stay untouched).

[Breck_Carter]

To expand on (repeat?) what Volker said: The idea behind running dbvalid on the backup copy is that (a) it's not being used by any other connections and (b) a successful validation of a copy implies the live database is OK since "making a copy" will never remove errors (it might introduce them, but that's another problem). Point (a) makes it unnecessary to disconnect anyone from the live database. It is also a good idea to run dbvalid on a copy of the backup copy since the database being validated must be started, and starting implies recovery which applies the backup transaction log, and recovery implies no future transaction-log-only backups can be applied to the database that has just been validated... which is why it's a good idea to leave the original backup database and log alone. That's a subtle point... I am not familiar with the wizard so I don't know if it makes that distinction... you're using the wizard so I'm posting this as a comment rather than an answer 🙂

[Breck_Carter]

This article doesn't use a maintenance plan, but it might be of interest nonetheless: Demonstrating Backup, Validation and Restore

[VolkerBarth]

I am not familiar with the wizard so I don't know if it makes that distinction... you're using the wizard so I'm posting this as a comment rather than an answer 🙂

Though I usually do not use the Wizard for such tasks, in v12.0.1.3942 the Wizard does not offer to validate the backup or a copy of the running database. It simply offers to validate the running database (and lets you opt to disconnect users before). You can additionally add custom SQL statements that are executed unconditionally

• before the first step (validation or backup)
• or after the last step (validation or backup)

but as they will be executed on the local database, it would require some "xp_cmdshell effort" to make them validate a copy of the backup...

---

And if you are able to do that, you might as well ignore the Wizard completely (or just use the generated events as a starting point) and write your own events to backup and validate the backup copy...

[former_SQLA_member1694900]

Ok, following the general consensus here, I have decided to ditch the Wizard in favor of my own script (actually Breck's) to backup an validate.

I have also created a scheduled task (running Windows Server 2008 here) to run the batch and send me a status email.

The most interesting part by far is the script. Here are the main steps (disregarding the boilerplate work to create working directories and send results to a log file):

---

First make the backup (while DB is running):

dbbackup -c "connstring" -o "log" C:\\backup

---

Then, copy the backup file to another folder. Then, apply the tran log because while backing up, stuff might have happened to the DB:

dbeng12 -o "log" C:\\PathToDBFile -ad "C:\\PathToOriginalTranLog"

(So -ad "Path" specifies where to go get the tran logs to be applied to the DB)

---

Then, start a read-only copy of the DB backup because we want to validate it:

dbspawn -f dbeng12 -n readonlycopy -o "log" -r -gf "C:\\PathToCopyOfDBFile"

( -f force start of DB even if one is already running, how cool is that? -r means open DB in readonly, -gf means to disable firing of triggers, if you need it)

---

Then validate:

dbvalid -c "connstring-to-readonlycopy" -o "log" -q

(-q don't log to client output, 'cause we already do that to a log file)

---

Then stop the read-only copy

dbstop -c "connstring-to-readonlycopy" -y

(-y stop even with active connections to the server)

[VolkerBarth]

Why would you apply the current translog to the backup database? Changes during the backup will be contained in the backup translog automatically.

I would think it would be better to copy both the backup database file and the backup log file to the other folder, and then to simply start the database there via DBVALID (so any required recovery will be done automatically). As it is a copy of the backup, you would not need to start it in read-only mode... I would think that this would really simplify the script, i.e. you would just have 3 (or 4 steps):

1. Do the backup
2. Copy the backup files to a second folder
3. Run DBVALID against the backup copy (and ensure the database name - and possibly the engine name - is different from the production database!)
4. Remove the backup copy if it could be validated successfully

Just my 2 cents, apparently:)