0

At my organization, we have a SQL Server (2016) running on an EC2 instance at Amazon on a Windows with SQL Server license. My boss realized we could save a ton of money by standing up a Windows-only EC2 and installing SQL Server on our own license. So I need to move the whole thing to a new location.

It's relatively easy for me to install SQL Server on the new machine and backup/restore the one database we are using. But I will need to re-create all the server objects: users, jobs, linked servers, database mail, etc. I can script most of those things out of the old machine to recreate them, so it's not that big a deal.

I feel like there's probably a much simpler way to do this, but I'm not terribly experienced with DB Admin on the server.

What's the simplest way to do this move? Thanks!

Improve this question
2
  • 1
    The DBAtools PowerShell module makes this pretty simple--it was the original use case the project attempted to solve.
    – AMtwo
    Oct 8, 2021 at 18:23
  • 1
    hey StackOverflow employee 😀 Comments should only be used for asking for clarification, or to leave constructive criticism that guides the author in improving the post, or to add relevant but minor or transient information to a post (e.g. a link to a related question, or an alert to the author that the question has been updated), or to provide site usage guidance. See the help for details.
    – Hannah Vernon ♦
    Oct 8, 2021 at 20:00

1 Answer 1

Reset to default
2

If you have a solid maintenance window to do the move, then you can do the following:

  1. Disable access to the server to anyone except yourself (so new transactions can't occur during the move). You can either do this via the Logins, or setting the server to single user mode, or any other suitable external technique you prefer (such as handling it at the domain controller level).
  2. Take a full backup of any databases in Simple Recovery Model.
  3. Restore the latest backups for all of your databases including the system databases (which you're hopefully already taking backups of) at the new server.
  4. Turn off the old server.

The reason you want to restore the system databases too is because that's where all the information for all of your server objects live, for example Jobs are stored in msdb and Logins are stored in the master database, etc. So it saves you wasting time having to script each individual server object out.

That should encompass pretty much everything, and is a relatively easy way to migrate your database server (when migrating to an instance of the same SQL Server version).

Improve this answer
3
  • 1
    Note that you must be on exactly the same version, edition, and patch level to restore Master. learn.microsoft.com/en-us/sql/relational-databases/…
    – David Browne - Microsoft
    Oct 9, 2021 at 12:48
  • And if you don't have the same folder structure for the system databases, you will have to mess about a bit. (The location for model and tempdb are in master database, and those are required in order for SQL Server to boot.)
    – Tibor Karaszi
    Oct 9, 2021 at 15:09
  • 1
    That's a solid plan and makes sense. I do have a pretty good window of time where I can shut down the database and set up the new one, so that's a benefit for my situation. I know I need to go into single-user mode to do system database restores, and I assume on the new machine I will do RESTORE WITH REPLACE to overwrite the default master database with my previous one. Is that right? Thanks for the helpful answer.
    – jbz
    Oct 11, 2021 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.