Sql-server – Difficulty in moving a SQL Server database

sql serversql-server-2008

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.

Best Answer

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'
Related Question