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?