Ok here is the situation, one which many of you may have experienced.  I have a live SQL DB and my development SQL DB; I've got the nod from the client for a new site which will be essentially a re-skin of an existing site, including its DB.
So I have downloaded the latest version of it's backup file (lets call it databaseSkin1.BAK) from the live server and I'm going to do a restore onto my new SQL 2005 express, I’ve broken the steps required to complete the process.

  1. Create a new Database with my development SQL Express DB, lets call it databaseSkin2
  2. Right click my new database and select TASKS > RESTORE > Database
  3. Specify from device and select the folder in which you placed you databaseSkin1.BAK file

This is where i ran into issues, SQL told me that the file was not accessible, which is very strange as it was reading it straight from the drive.  This initially led me to the following article http://support.microsoft.com/kb/827452 which i thought was a little heavy handed and to be honest i could not be bothered going through the whole process of downloading the fix and waiting for an email from Microsoft etc etc.

So i kept searching where i found this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=169145&SiteID=1, now this did not work for me like it did for "wilsonjan" but it got me thinking that it must be permission based issue.  So in order to get this to work I had to find the databaseSkin1.BAK file on the hard drive right click and select SECURITY then add the Network service user and give that user full access rights to the databaseSkin1.BAK file. 

To satisfy my curiosity and to see how this process would behave in SQL 2000 i also attempted to restore this same databaseSkin1.BAK file to my SQL 2000 instance. In this scenario  I already had a database called databaseSkin1 on the sever and when i attempted to create a new DB called databaseSkin2 by restoring from the backup file databaseSkin1.BAK we will always get the error that the file already exists even if you have selected the "force restore over existing database". The only way to get to do this is to rename the following during the restore process, in the options tab:

  • databaseSkin1_Data to databaseSkin2_Data
  • databaseSkin1_Log to databaseSkin2_Log
By default these will be named as per the BAK file you are using.

Hope all of the above made sense?!