On master4IDR.ckp, Maintenance Plans, Default Backup Directory and SQL Server Configuration

We were going through a process of standardising SQL Server 2000 & 2005 maintenance plans, in this case to put all the backups onto a new drive, out of the way of everything else…

So far so good.  Create MP for backup, point it at the new drive, shift over the existing database backups too, for completeness’s sake.

And then, on the following morning, and on just a few servers (3 out of 80), the following pair of messages:

I/O error on backup or restore restart-checkpoint file ‘C:\Program Files\Microsoft SQL Server\MSSQL\backup\master4IDR.ckp’. Operating system error 3(error not found). The statement is proceeding but is non-restartable

I/O error on backup or restore restart-checkpoint file ‘C:\Program Files\Microsoft SQL Server\MSSQL\backup\model4IDR.ckp’. Operating system error 3(error not found). The statement is proceeding but is non-restartable

Lovely.  “master4IDR.ckp”?  “model4IDR.ckp”?  What?  A quick Google finds that we are not alone with this problem and that, by happy chance, one of the early mentions contains a (now out-of-date-but-fortunately-correctly-redirected) link to a Technote from Veritas / Symantec explaining the problem.  (Don’t be put off by the error description not looking right for this problem – it does get to the point that we’re interested in quite quickly.)

There – at the bottom of the page – a nice short summary of the problem:

“This error message occurs because the backup folder does not exist.”

So, recreate the backup folder mentioned in the error message, and away goes the error.

So far, so good.

However, this path is the default backup path, and is hardwired into each SQL Server instance.  Unlike the default data & log paths, it is not easily accessible through the SQL Server Management Studio.  In order to find (and change) this setting, we need to dive into the registry, to (deep breath):

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

In there, you’ll find a value for each instance defined on your server.  Look at the Data value, and then navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<<instancename>>\MSSQLServer

and there it is.  “BackupDirectory”.  Change this.

So, a simple question:

Why couldn’t Microsoft have put in an option to change this in SQL Server Management Studio, like they did for Data & Log files?

Advertisements
This entry was posted in SQLServerPedia Syndication, Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s