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.
-
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.
-
@scottalanmiller said in Recovering SQL Server 2005 Databases:
Anyone had any luck mounting SQL Server 2005 MDF/LDF to a new SQL Server version
You can move SQLServer 2005 to a new Server version by following this guide: https://solutioncenter.apexsql.com/restore-sql-server-backup-to-a-newer-version-of-sql-server/
