SQL 2005 – Restoring a backup […error ‘5(error not found)’…]


You may have adopted MS-SQL for one of your IT solutions at one point and maybe tried restoring a backup among other operations. Sometime, when attempting to restore a backup you may get a message similar to the below one:

Error message:
System.Data.SqlClient.SqlError: The operating system returned the error ‘5(error not found)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Database1.mdf’. (Microsoft.SqlServer.Express.Smo)

The account name of the user that actually performs the backup is saved as metadata inside the backup (eg: DOMAIN\backupuser).

My guess is that when you are trying to restore a backup, that very user : DOMAIN\backupuser is expected to have modify permissions in the folder(s) where the database files (emerging from the backup) will have to be created/placed. In the Restore dialog there’s a second page called Options where the name of these folders can be edited. There are usually 2 files: the data file of the primary group (*.mdf) and a transaction log file (*.ldf) and they can be placed in different folders.

As the backup may have been created on a different domain or workgroup the above mentioned user(security account) will be (possibly) completely unknown on the machine you attempt the restore operation. So, to actually stand a chance of restoring it you have to adjust the permissions on the previously mentioned folders. Your best bet is to temporarily set modify permissions for the Everyone group and then attempt the restore.

If the restore is successful you can safely go back and remove the Everyone’s modify permissions on that folder as the database owner is automatically set to the user that performed the restore and any reference to the user stored in the backup is relevant no more.

You might have the folder used to restore the database files set up like a temporary location only for restoring process and you want to keep the Everyone modify permissions on it. In that case you may want to move the database files to a more secure location and you can do that by detaching the database, move the files and attaching the database again. Just make sure you pick all the database files when you move them to the new location.

P.S.
There’s a utility that can successfully replace a backup procedure by helping you bringing over a database completely with all its data.

It’s a tool that creates a script for everything in the Database except the database itself so when you will run the script you need to run it from an already created empty database.

SQL2005 Database Publishing Wizard

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Advertisements

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