Recovering SQL Server 2005 Databases
-
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
-
We got a 2008 R2 that we tracked down. And they were able to get backup files rather than the original database files only, so looking like it will be much easier.
-
@dbeato said in Recovering SQL Server 2005 Databases:
@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.
The application supports at least SQL Server 2017, which we have.
-
@scottalanmiller said in Recovering SQL Server 2005 Databases:
We got a 2008 R2 that we tracked down. And they were able to get backup files rather than the original database files only, so looking like it will be much easier.
Forget about the old OS and try restoring the old backup files directly into SQL Server 2017 then. It should work.
-
@Pete-S said in Recovering SQL Server 2005 Databases:
@scottalanmiller said in Recovering SQL Server 2005 Databases:
We got a 2008 R2 that we tracked down. And they were able to get backup files rather than the original database files only, so looking like it will be much easier.
Forget about the old OS and try restoring the old backup files directly into SQL Server 2017 then. It should work.
To restore the old backup I believe you need SQL Server Management Studio. I don't think you can do it with the command line tools. I haven't tried though.
-
I was thinking there's some kind of per-database compatibility mode that allows the backward compatibility with older applications that need to access the database. But it's also been a while since I've done much with SQL Server.
-
@NetworkNerd said in Recovering SQL Server 2005 Databases:
I was thinking there's some kind of per-database compatibility mode that allows the backward compatibility with older applications that need to access the database. But it's also been a while since I've done much with SQL Server.
There is. But that would have nothing to do with restoring.
It may prevent the legacy application from talking to the restored database though.
-
@scottalanmiller said in Recovering SQL Server 2005 Databases:
We got a 2008 R2 that we tracked down. And they were able to get backup files rather than the original database files only, so looking like it will be much easier.
Good news, much less pain than having to deal with mounting on 2005 and then backup/restore to current.
