0

I took a full backup of a SQL Server 2005 database, then restored it onto SQL Server 2008. I'm curious, was it a 2008 database at that point?

Anyways, I since did a full backup of it using SQL Server 2008 and now I have a machine with only SQL Server 2012 Express on it. When I try to restore from the backup file I get the following error:

TITLE: Microsoft SQL Server Management Studio

Restore of database 'CodeLib' failed. >(Microsoft.SqlServer.Management.RelationalEngineTasks)

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\SQLdata\MSSQL10_50.SQLBESI\MSSQL\DATA\CodeLib.mdf'. (Microsoft.SqlServer.SmoExtended)

I ran SSMS as an Admin when I tried to restore it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chad
  • 23,658
  • 51
  • 191
  • 321
  • 1
    Looks like the SQL Server service account (find it in `services.msc`) does not have sufficient access to that path. (this is different to the account you log into to SMSS with). When you restore a prior version of a database, it maintains the _compatability level_ and it's up to you to change it to suit the upgraded level, usually when your application testing is complete – Nick.Mc Dec 08 '15 at 03:55
  • Or it could be that the file already exists and is in use by SQL Server. Does it already exist? – Nick.Mc Dec 08 '15 at 03:56
  • 1
    @Nick: I ran it under my local admin account and it worked. Thank you. – Chad Dec 08 '15 at 04:17
  • OK so you went into services.msc and changed the logon account to be a local admin (just being verbose for anyone who finds this afterwards from a searche) – Nick.Mc Dec 08 '15 at 05:59
  • 1
    Possible duplicate of [SQL Server Operating system error 5: "5(Access is denied.)"](http://stackoverflow.com/questions/18286765/sql-server-operating-system-error-5-5access-is-denied) – Nick.Mc Dec 08 '15 at 06:00
  • *I'm curious, was it a 2008 database at that point* - yes, as soon as you restore a database onto a given version, SQL Server updates the internal database structures of that `.mdf` file, and you can never go back down to an earlier version from that point on – marc_s Dec 08 '15 at 06:13

0 Answers0