3

I just got a new job as an analyst but I’m being tasked with an administrator task and just looking for insight.

We have a Windows server 2003 (32bit) that has Microsoft SQL Server 2005. I have been tasked with upgrading it to the latest possible windows & SQL server.

Since both the sql server and host OS have been unsupported for years now I figured it might be easier to create a new server and restore a backup of the databases on the new server. My questions are:

  1. What would be the latest OS and SQL server versions that would restore a 32 bit sql server backup? (New OS and SQL server will be 64 bit)
  2. If the OS and SQL server are both 64 bit, would I still be limited to 4gb ram when interacting with the database? Or is the version of a the OS what dictates ram?
  3. Can I backup the entire instance (preferably with local user information) or do I backup each database individually?
  4. If you have had a similar experience, what is the #1 thing you suggest I look out for?

Relevant information

  • Both the old and new servers are VM’s that I create through VMware
  • Licensing is not a factor so if I need multiple steps that is ok
  • the sql server is interacted with by a couple dozen users who use windows 10&11 and Ubuntu 16, 18, 20, & 22
Improve this question

1 Answer 1

Reset to default
5
  1. The bitness of the backup does not affect anything. What does affect things is the version of the backup. Backups before 2005 cannot be restored without an intermediate step, but 2005 is fine to upgrade even to 2022.
  2. As I said, the bitness of the database is irrelevant. New versions of SQL Server can use as much RAM as you have.
  3. Each database needs a separate backup. Do not backup master, tempdb or msdb, just do a manual migration of logins, permissions, Agent jobs etc.
  4. Is opinion-based and out of scope for this site.

Further considerations:

  • The minimum compatibility level for new versions of SQL Server is 100. I would advise you to upgrade to the latest one (160), and review all breaking changes to ensure you are not affected.
  • Other new Database Scoped Configuration should ideally be set. Check the model database on the new installation for default settings.
  • Make sure the PAGE_VERIFY setting is CHECKSUM.
  • Run CHECKDB when done.
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.