April 30, 2019 at 2:35 pm
I have a requirement to send a backup of SQL databases to an electronic records management system then remove them. At my organisation we do not yet have a policy on the best way to do this. We are not clear even about how long we need to retain the data. My questions are initial forays into the art of the possible. I would appreciate your experience and thoughts.
My questions are:
If I backup a SQL Server 2008 database now. Can I restore it in 10 years / 20 years time? Will SQL Server 2038 be able to restore my old backup? What is the Microsoft dev policy on this? I believe you can restore a 7.0 backup to 2008 for example so it may be that Microsoft will continue to develop in a way that will allow restores of much older versions.
If not a backup to a standard SQL backup, is there a better backup strategy? A way I don't know about to backup to a flat file for example or some system independent method of storage.
I understand this depends on the size of the database. I could script out all the DDL, relevant login metadata etc. & bcp all the tables out for example on a small databases, but let's say they are fairly large.
Are there any other issues / constraints I should think about here?
Thanks for reading.
April 30, 2019 at 7:13 pm
It's difficult to be future-proof. Today, you can restore an SQL 2008 backup on any later version of SQL Server, including SQL 2019. But there are of course no guarantee that you will be able to restore it on SQL 2038. Or for that matter there will be any product by the name SQL Server available in 2038. Or any company called Microsoft.
About any format you pick - JSON, XML, flat files - have the same potential problem. Add to this that the backup media itself may not be readable in AD 2038.
But the more formats and media types you use, the better the chances that data will be retrievable.
It may also be a good idea to retrieve and test the backups yearly, and possibly also move the backup to a newer format. For instance, you upgrade the database to SQL 2017 and store an SQL 2017 backup, rather than an SQL 2008 backup already today.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 30, 2019 at 8:49 pm
Thanks to virtualization, your chances of running an instance of SQL Server 2008 in the future 2038 is very good. Going forward, you and your successor DBA(s) will need to test your recovery process annually, and perhaps every five years or so consolidate legacy database backups into new formats and media. Just to put this into perspective, where I work now, there are legacy apps running on VMs loaded with MS-DOS and FoxPro; the same old bits running on the latest hardware and host operating system.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 1, 2019 at 9:45 am
Erland / Eric,
Thank you!
You've raised some good and eloquent points I hadn't thought of. I feel much more confident going back to my client with a plan now.
It can be lonely as the sole DBA so I really do appreciate your responses.
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply