Recovering SQL Server 2005 Databases
-
We have an old 2003 R2 server with SQL Server 2005 that died. Backups are no bueno, but we have a full copy of the data. Pheww. Anyone had any luck mounting SQL Server 2005 MDF/LDF to a new SQL Server version? If not, we got to figure out how to get SQL Server 2005 running on something, somewhere. Not the end of the world and some old OSes and old databases and we can get it up in a VM one way or another. Anyone got any brilliant ideas to make this to some degree easy?
-
What about trying to migrate to SQL Server 2008
https://hub.packtpub.com/moving-database-sql-server-2005-sql-server-2008-three-steps/and then migrate to the latest SQL Server?
-
I feel your pain. I had to move a bunch of stuff from SQL 2000 to 2012 R2 a while back and since it's going to be a multi-step process anyways I'd suggest mounting / restoring the 2005 stuff to another 2005 instance and then go through the process of upgrading.
** Might be easier than I recall, there was an extra step when coming from 2000 because of the way that 2000 handled paged space metadata or something along those lines, I thought it was between 2005 and 2008.
-
I am getting the shakes reading this. I know it's a customer, but fuck I guess their data isn't remotely important to them. I'm surprised it lasted to 2020. Maybe they will learn a valuable lesson from this and not fuck up again this bad.
Good luck this is going to be an annoying task. I would have told the customer good luck as they obviously don't value their data at all! That's not a customer I'd actively choose. This is going to cost them thousands of dollars in support hours. Too bad they didn't migrate 10 years ago when they were supposed to do that.
I guess this brings up another point. At what point do you tell a customer to pound sand? I'm not sure many MSPs or consultants would even take this job.
-
Oh boy, that's bad.
-
@IRJ said in Recovering SQL Server 2005 Databases:
I am getting the shakes reading this. I know it's a customer, but fuck I guess their data isn't remotely important to them. I'm surprised it lasted to 2020. Maybe they will learn a valuable lesson from this and not fuck up again this bad.
Good luck this is going to be an annoying task. I would have told the customer good luck as they obviously don't value their data at all! That's not a customer I'd actively choose. This is going to cost them thousands of dollars in support hours. Too bad they didn't migrate 10 years ago when they were supposed to do that.
I guess this brings up another point. At what point do you tell a customer to pound sand? I'm not sure many MSPs or consultants would even take this job.
Not likely. Unless their costs is 10's of thousands of dollars, they'll likely consider it a full on win. They didn't pay for how many new version? they didn't buy how many new versions of Windows? They didn't pay for the manpower do to those upgrades, and now they pay for just over the cost of one, perhaps two of them at once, way later - time value of money and all, they very likely will consider it a win - unless the DB being down is costing them 10's of thousands of dollars.
-
@Dashrender said in Recovering SQL Server 2005 Databases:
@IRJ said in Recovering SQL Server 2005 Databases:
I am getting the shakes reading this. I know it's a customer, but fuck I guess their data isn't remotely important to them. I'm surprised it lasted to 2020. Maybe they will learn a valuable lesson from this and not fuck up again this bad.
Good luck this is going to be an annoying task. I would have told the customer good luck as they obviously don't value their data at all! That's not a customer I'd actively choose. This is going to cost them thousands of dollars in support hours. Too bad they didn't migrate 10 years ago when they were supposed to do that.
I guess this brings up another point. At what point do you tell a customer to pound sand? I'm not sure many MSPs or consultants would even take this job.
Not likely. Unless their costs is 10's of thousands of dollars, they'll likely consider it a full on win. They didn't pay for how many new version? they didn't buy how many new versions of Windows? They didn't pay for the manpower do to those upgrades, and now they pay for just over the cost of one, perhaps two of them at once, way later - time value of money and all, they very likely will consider it a win - unless the DB being down is costing them 10's of thousands of dollars.
-
I dont know how many MSPs would even take this work. They were sure lucky to find one.
-
A data breach can cost them the entire business. This isnt just a web server, it is a database server. You work in healthcare and if your company did this and got breached you would be fired and the company would likely shutdown or be severely fined in the millions.
-
Not verifying backups on a database server. Extremely poor execution of system adminsitration. Perhaps it was designed by an engineer 15 years ago and never tested again.
-
I bet you money this isn't VLANed in any way. I bet if one of their workstations get breached, they are fucked city. Who knows maybe they are running Windows XP or Windows 98 on their workstations. We have to assume that is a possibility at this point. Maybe its even internet facing :face_screaming_in_fear:
-
-
Kinda Sorta? I had a 2005 one die maybe 6,7 years ago and was able to attach the MDF to a 2008 server I had running in a lab environment to at least get a backup of the data. basic procedure is here:
https://stackoverflow.com/questions/24399664/convert-attach-sql-server-2005-mdf-to-2008-mdf#:~:text=Best practice is to backup,older versions of SQL Server. -
@IRJ said in Recovering SQL Server 2005 Databases:
- A data breach can cost them the entire business. This isnt just a web server, it is a database server. You work in healthcare and if your company did this and got breached you would be fired and the company would likely shutdown or be severely fined in the millions.
You're likely right that I would be fired - but it wouldn't be my fault - because I constantly tell them they need to upgrade, but I don't control the pursestrings. I can't make them spend money.
-
@IRJ said in Recovering SQL Server 2005 Databases:
@Dashrender said in Recovering SQL Server 2005 Databases:
@IRJ said in Recovering SQL Server 2005 Databases:
I am getting the shakes reading this. I know it's a customer, but fuck I guess their data isn't remotely important to them. I'm surprised it lasted to 2020. Maybe they will learn a valuable lesson from this and not fuck up again this bad.
Good luck this is going to be an annoying task. I would have told the customer good luck as they obviously don't value their data at all! That's not a customer I'd actively choose. This is going to cost them thousands of dollars in support hours. Too bad they didn't migrate 10 years ago when they were supposed to do that.
I guess this brings up another point. At what point do you tell a customer to pound sand? I'm not sure many MSPs or consultants would even take this job.
Not likely. Unless their costs is 10's of thousands of dollars, they'll likely consider it a full on win. They didn't pay for how many new version? they didn't buy how many new versions of Windows? They didn't pay for the manpower do to those upgrades, and now they pay for just over the cost of one, perhaps two of them at once, way later - time value of money and all, they very likely will consider it a win - unless the DB being down is costing them 10's of thousands of dollars.
-
I dont know how many MSPs would even take this work. They were sure lucky to find one.
-
A data breach can cost them the entire business. This isnt just a web server, it is a database server. You work in healthcare and if your company did this and got breached you would be fired and the company would likely shutdown or be severely fined in the millions.
-
Not verifying backups on a database server. Extremely poor execution of system adminsitration. Perhaps it was designed by an engineer 15 years ago and never tested again.
-
I bet you money this isn't VLANed in any way. I bet if one of their workstations get breached, they are fucked city. Who knows maybe they are running Windows XP or Windows 98 on their workstations. We have to assume that is a possibility at this point. Maybe its even internet facing :face_screaming_in_fear:
AND - all that is of course true - but none of it became an issue during this time, so short of the, what I assume is a hardware failure they got away with it - soooo someone could very easily have all the thoughts I mentioned above. I never said it was right, I just said it was likely their thinking. Even if not directly thinking - oh man we saved so much money by not following those crazy best practices, etc...
I'm on your side @IRJ. -
-
@Dashrender said in Recovering SQL Server 2005 Databases:
@IRJ said in Recovering SQL Server 2005 Databases:
@Dashrender said in Recovering SQL Server 2005 Databases:
@IRJ said in Recovering SQL Server 2005 Databases:
I am getting the shakes reading this. I know it's a customer, but fuck I guess their data isn't remotely important to them. I'm surprised it lasted to 2020. Maybe they will learn a valuable lesson from this and not fuck up again this bad.
Good luck this is going to be an annoying task. I would have told the customer good luck as they obviously don't value their data at all! That's not a customer I'd actively choose. This is going to cost them thousands of dollars in support hours. Too bad they didn't migrate 10 years ago when they were supposed to do that.
I guess this brings up another point. At what point do you tell a customer to pound sand? I'm not sure many MSPs or consultants would even take this job.
Not likely. Unless their costs is 10's of thousands of dollars, they'll likely consider it a full on win. They didn't pay for how many new version? they didn't buy how many new versions of Windows? They didn't pay for the manpower do to those upgrades, and now they pay for just over the cost of one, perhaps two of them at once, way later - time value of money and all, they very likely will consider it a win - unless the DB being down is costing them 10's of thousands of dollars.
-
I dont know how many MSPs would even take this work. They were sure lucky to find one.
-
A data breach can cost them the entire business. This isnt just a web server, it is a database server. You work in healthcare and if your company did this and got breached you would be fired and the company would likely shutdown or be severely fined in the millions.
-
Not verifying backups on a database server. Extremely poor execution of system adminsitration. Perhaps it was designed by an engineer 15 years ago and never tested again.
-
I bet you money this isn't VLANed in any way. I bet if one of their workstations get breached, they are fucked city. Who knows maybe they are running Windows XP or Windows 98 on their workstations. We have to assume that is a possibility at this point. Maybe its even internet facing :face_screaming_in_fear:
Even if not directly thinking - oh man we saved so much money by not following those crazy best practices, etc...
Yes and there is no change from shitty behavior
-
-
Let's assume no hardware failure. Is there a good path? We have the data, intact. It's just really old and needs to be updated.
-
Why can't you restore the DB to a newer SQL server?
-
@jt1001001 posted a reasonable and simple solution
-
@Obsolesce said in Recovering SQL Server 2005 Databases:
Why can't you restore the DB to a newer SQL server?
Officially, at least, MS says that that doesn't work. Or that it requires doing it many times. Hoping that there is a known, simpler solution
-
@scottalanmiller said in Recovering SQL Server 2005 Databases:
Let's assume no hardware failure. Is there a good path? We have the data, intact. It's just really old and needs to be updated.
FWIW I'd mount on 2005 then backup/restore on the current version of your choice.
https://dba.stackexchange.com/questions/222868/upgrading-sql-server-2005-to-2016
-
@scottalanmiller said in Recovering SQL Server 2005 Databases:
Let's assume no hardware failure. Is there a good path? We have the data, intact. It's just really old and needs to be updated.
There is not good path here. Recovering MDF is painful and slow. I have had to do it with current ProSystems Databases and let me tell you it is not fun.
-
@scottalanmiller said in Recovering SQL Server 2005 Databases:
@Obsolesce said in Recovering SQL Server 2005 Databases:
Why can't you restore the DB to a newer SQL server?
Officially, at least, MS says that that doesn't work. Or that it requires doing it many times. Hoping that there is a known, simpler solution
It does work, you need to mount it and as long as the newer SQL Server is a higher version it should work fine. You cannot go back to the old versions. Now the bigger question is what program used this Database or what was the interaction to this database. That is the biggest consideration here.
-
Assuming here you have Visual Studio/MSDN subscription or similar so you have iso-files for everything old.
This is how I do it:
- Install virtualbox on your desktop
- Install old OS on virtualbox. (OS pick might depend on 2005 express or standard)
- Install old SQL Server on machine
- Copy over old db files
- Backup database to *.bak file
- Install new OS+SQL Server on new server
- Restore backup
If you use shared folders in virtualbox you don't need to worry about setting up any network on the VM. Shared folders let you share files between the host and the VM.
-
@Pete-S said in Recovering SQL Server 2005 Databases:
Assuming here you have Visual Studio/MSDN subscription or similar so you have iso-files for everything old.
This is how I do it:
- Install virtualbox on your desktop
- Install old OS on virtualbox. (OS pick might depend on 2005 express or standard)
- Install old SQL Server on machine
- Copy over old db files
- Backup database to *.bak file
- Install new OS+SQL Server on new server
- Restore backup
If you use shared folders in virtualbox you don't need to worry about setting any network on the VM.
This is how i always deal with things like this. Have not had to do it for 4-5 years. but I last did it was an older SQL2005 MDF file. I spun up a VM with Server 2008R2, installed SQL 2005, reconnected everything, then made a backup within SQL Server Management Studio.
That backup was then restored to SQL Server 2014
