5

I'm trying to upgrade from SQL Server 2008 R2 Express to Developer.

My plan is to remove the Express edition, then install the Developer edition. I have one database I need to bring over to the new installation.

To prep, I made physical copies of the data and log files to the desktop, as well as performed a full backup to the desktop. I then detached the database in Management Studio.

To get a restore under my belt before removing Express, I went to physically move the .mdf and log files in windows explorer after detaching, but I don't see the files.

I thought perhaps there was no need, I could restore from the backup (again, still with Express), I get the error that there is insufficient space. And interestingly, I don't see the backup file on the desktop. I am sure I specified it carefully. I tried plan B, to attach the .mdf file that I copied to the desktop, and I get

CREATE FILE encountered operating system error 2 (The system cannot find the file specified).

Any help here would be huge. Thanks.

|improve this question

migrated from stackoverflow.com Aug 9 '12 at 21:22

This question came from our site for professional and enthusiast programmers.

  • 6
    Search the system for files named *.bak or made around the same time. Detaching was a mistake... you should never use physical copies of the mdf/ldf files as "backups"... – Aaron Bertrand♦ Aug 9 '12 at 21:08
  • 1
    I think OP needs to also search for the mdf/ldf, since it sounds like they detached with the intent of uninstalling SQL Server, installing another version and reattaching. And since the mdf/ldf are still there somewhere, OP can't restore due to space constraints. – Eric Higgins Aug 9 '12 at 22:03
  • 3
    Also make sure that when you search for all of these files that you're using an account that has sufficient permissions to access the potential locations for said files (e.g. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL) – swasheck Aug 9 '12 at 22:12
2

If you wanted to make life easier, according to the Version and Edition Upgrades page, there exists an edition upgrade for SQL Server 2008 R2 Express to SQL Server 2008 R2 Developer. You could just perform an edition upgrade and call it a day.

Alternatively, if you wanted to keep with your original plan of installing a new instance and migrating the database...

If you can't find files on your system, try installing Everything. It is an extremely fast indexing application that will run in your notification area.

Once you find the backup file, try running the following command to see if SQL Server can access it:

RESTORE HEADERONLY FROM DISK = 'drive:\path\to\mybackup.bak'

or

RESTORE FILELISTONLY FROM DISK = 'drive:\path\to\mybackup.bak'

If that does not work, then try moving the file to a location that the SQL Server service account has access to. If you don't plan on accessing any network resources with SQL Server, it would be easiest to run SQL Server as local system on your workstation.

Once your Developer instance can see the .bak file, you can either use the Restore Database task in SSMS (Right-click Databases in Object Explorer and select Restore Database) or you can script it. Just remember in either option you should change the destination path to the data and log files so they reside in the new default data path of the SQL Developer instance (or some other directory on your workstation). By default, it'll try putting them back in the original location when the backup was taken.

Here is an example restore script you could modify (use the RESTORE FILELISTONLY details for the logical file names):

RESTORE DATABASE [MyDB] FROM DISK = 'drive:\path\to\mybackup.bak' WITH MOVE 'logicalDataFilename' TO 'drive:\path\to\physicalDataFilename.mdf', MOVE 'logicalLogFilename' TO 'drive:\path\to\physicalLogFilename.ldf'
|improve this answer
0

I have ran into this myself after installing the OS but keeping some of my old drives. What I did to correct this was to create new folders for backup and restoring my databases to. Kind of a pain since I have all of my databases spread out across three drives but at least it works.

I'm sure there is another way but the OS locked the original folders down and this was a quick fix

|improve this answer
0

Did you copy it to your desktop, MyDocs or somewhere in your users folder? If so, the SQL Server Service could be running under a different name. Are you able to see the files yourself on the server? Have you tried just deleting them (assuming you have good tested and ready to go copies elsewhere) and just doing a restore bypassing the need to attach/de-attach? MS is removing support for attach/deattach anyways so it's good practice not to use it anymore IMHO.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy