SQL server migration – Server object listing/scripting tool [closed]

We need to quickly migrate multiple SQL Servers. We need a process to script out all server-level objects, e.g. linked servers, logins, jobs, server roles.

We’re looking for a tool to script out all server objects, or at least to list all server objects.

Any suggestion will be very appreciated.

We have servers across different firewalls, powershell sometimes does not work. thanks for the suggestion.

Use the awesome dbatools.

Why ?

dbatools now has Export-DbaInstance to export as below (use -Exclude if you want to exclude certain things) :

All databases.
All logins.
All database mail objects.
All credentials.
All objects within the Job Server (SQL Agent).
All linked servers.
All groups and servers within Central Management Server.
All SQL Server configuration objects (everything in sp_configure).
All user objects in system databases.
All system triggers.
All system backup devices.
All Audits.
All Endpoints.
All Extended Events.
All Policy Management objects.
All Resource Governor objects.
All Server Audit Specifications.
All Custom Errors (User Defined Messages).
All Server Roles.
All Availability Groups.

there is even a blog post detailing on how the above works !

James I think you should really eliminate the “quickly” from this equation if you’re looking for scripts to do it for you.

It seems like you are doing a side-by-side upgrade instead of an in-place upgrade, which is a good start. I’d just take the time to ensure you have everything running smoothly.

Scripting things out like Logins, Jobs, Linked servers, and roles may have you in for a rough time as some of those don’t translate well when placed in another environment. I’ve had issues where even though I “transferred” a component (like a login/linked server) via a script and it deployed, it just didn’t work until I actually rebuilt it (most likely due to unique identifier/mapping issues).

I’d recommend you try your best to:

1. Take inventory of your current server/instance and confirm you ACTUALLY need everything over in the new environment, and you don’t get any stragglers that aren’t in use
2. Revise your layout/architecture to ensure it’s solid and scalable (you may not get this opportunity later)
3. Evaluate opportunities for new backup strategies and redundancy (like AG).

Then, just make a side-by-side install. Implement the new components as if you were building them from scratch.

SysLogins/syscomments is a good start to take inventory, but please make sure you do this the right way, as it seems to be your responsibility.

Good luck, we’re all counting on you.