Is is possible to recover a database from the 'raw' files (.mdf and .ldf) of the old server to a new SQL Server instance on a new server?

Question

I have a client that has POS software called Restaurant Pro Express (RPE) from www.pcamerica.com. RPE uses a SQL Server database.

Their old POS server had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data. However, my client doesn't seem to make backups very often - the last one is dated January 5, 2015.

I was able to copy the C:\Program Files\Microsoft SQL Server\ folder which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1.

I installed the RPE software on the new computer and a SQL Server instance called MSSQL10_50.PCAMERICA. The new computer is using SQL Server 2008 R2, while I believe the old computer would have been using SQL Server 2005.

I am not a DBA, especially when it comes to SQL Server.

• Is is possible to 'recover' the database from the 'raw' .mdf and .ldf files from the old computer to the new computer / SQL Server instance?
• If so, how should I proceed?

Answer 1

Please pay attention. The answer from Julien is correct. BUT if you attach the database to an SQL Server 2008 instance it will be upgraded! If the application can't handle SQL Server 2008 and needs SQL Server 2005 you're in trouble. Take an extra step and make a backup.

Furthermore you have to create the needed logins on the new server. You might want to attach the old master under a new name for looking up the details.

Answer 2

You first have to detach it on your old server:

USE Master
GO

-- drop all active connections and roll back open transactions
ALTER DATABASE YourDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- detach it
EXEC sp_detach_db 'YourDB'
GO

If the old server is not available, you can proceed with ataching it on a new server. On your new server, once the files have been move, you can now attach it:

USE master;
GO
CREATE DATABASE YourDB 
    ON (FILENAME = 'C:\...\YourDB_data.mdf'),
    (FILENAME = 'C:\...\YourDB_log.ldf')
    FOR ATTACH;
GO

MSDN: Move a Database Using Detach and Attach (Transact-SQL)

As mentioned by Jens W., SQL Server 2005 and 2008R2 does not have the same compatibility level (90 vs 100). You will have to make sure it does not crash your application if it is upgraded to compatibility level 100.

Another option could be:

• attach it on a temporary server with SQL Server 2005
• backup the new db
• restore it on the new SQL Server 2008 R2 without changing the compatibility level
• test you application

You should probably get in touch with the company developping this software and try to know which version SQL Server they support.