August 18, 2015 at 11:16 pm
I wasn't sure which forum to post this under, so the 'Getting Started' seemed the most applicable to my situation as I have never really dealt with MSSQL before.
I have a client that has POS software called Restaurant Pro Express (RPE) from http://www.pcamerica.com
Their old POS computer had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data. RPE uses a MSSQL database system. 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 over 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 their new computer, and it too now has an instance called MSSQL10_50.PCAMERICA. The new computer is using MSSQL 2008 R2, while I believe the old computer would have been using MSSQL 2005.
I am no DBA expert, especially when it comes to MSSQL. Is is possible to 'restore' the database from the 'raw' .mdf and .ldf files of the old computer to the new computer / database instance? If so, how should I proceed?
August 18, 2015 at 11:51 pm
It's not possible to restore a database from the actual data files.
There's only one option I can suggest without a good understanding of how everything is currently set up. I suggest you copy the data and log file to the new SQL installation (making sure to put them in the same location as the other databases) and run through the Attach Database document on MSDN (https://msdn.microsoft.com/en-us/library/ms190209.aspx), making sure to use a different name for the database you're attaching.
From there, you could either rename the databases so the POS software points to the database you just attached, or you could backup the old database and restore it over the top of the new install. I don't know what impact that would have on the operation of the software.
I would also suggest running DBCC CHECKDB against the database you attach to make sure there's no errors in those data files.
Whatever you do, set up a backup plan (even if you just use a SQL Server Maintenance Plan to do it) that at least does a full backup of the database after the daily sales have been reconciled. Better still would be to have full backups done nightly, and then transaction log backups every 15 minutes (which you can do through the maintenance plan wizard) starting an hour before the doors open and finishing an hour after the doors close. This makes sure the staff have time to perform daily reconciliation tasks and have all those transactions logged.
August 19, 2015 at 3:03 am
It's possible to recreate the DB from the files.
It's not a restore though, you don't have a backup. You can attach the files as a new database, providing they are undamaged (and with a hardware failure, that's a fairly big if) and that you have all of them.
Copy the mdf, ldf and any ndf files which you find over to the new instance's data directory and then use the Attach Database wizard. Specify the same name as the old DB had.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2015 at 4:09 am
Here's the documentation on how to use ATTACH.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 19, 2015 at 10:11 am
SQL expert or not, I'd advise you to use a CREATE DATABASE ... FOR ATTACH command rather than using a gui. That way you have a record of exactly what was specified for the attach. And, if you have to do this again, say, 6 months from now, you simply pull up the command, adjust the file names as needed, and run, rather than having to re-learn how to use the gui.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply