3

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?
4

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.

  • Thanks for adding these extra requierements. It seems that the application and DB where left alone with no backup or maintenance whatsoever. I would not be surprised if everything and everyone was using Sa or 1 single account. It will be easy to recreate them :) – Julien Vavasseur Aug 19 '15 at 8:33
  • Backup and Maintenance: ola.hallengren.com ;) – Jens W. Aug 19 '15 at 8:35
3

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.

  • it is impossible to detach a database, server hardware already broken. – Oleg Strutinskii Sep 22 '15 at 7:00
  • @OlegStrutinskii, If you are able to connect SQL server then it possible . – Md Haidar Ali Khan Sep 22 '15 at 7:57

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.