Sql-server – Migrating to a new (upgraded) SQL server

migrationsql server

We have a single SQL server with about 60 databases running on Win2012r2 with SQL 2014.

I am in the process of planning a hardware refresh and plan to migrate to WIN2016 with SQL 2017, I understand the basics of migrating to a new server (copy files over, re-attach, test etc).

My question is, we have specific restricted logins setup for each database. The logins are windows ad groups and have specific permissions for their allowed databases. In addition to this we have about 20 SQL logins with the same setup against their databases.

My question is, how can i migrate these logins and databases while maintaining the permissions for specific databases?

Best Answer

I would use Copy-DbaLogin from the dbatools powershell module.

This command migrates the logins from server A to server B.

From their website the example:

Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Force

Copies the logins from sqlserver2014a to sqlcluster and overwrites them if they exist by using the -Force parameter

What does it migrate?

Migrates logins with SIDs, passwords, defaultdb, server roles & securables, database permissions & securables, login attributes (enforce password policy, expiration, etc.)

Same source as the one mentioned above

Related Solutions

Sql-server – Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box

For logins, this is better IMHO:

http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

And for jobs, you don't need SSIS. Just highlight the Jobs node in Management Studio, click on Object Explorer Details (F7), in the right pane select all, right-click and script.

enter image description here

I'd do the logins first, in case any of the jobs involve any of the logins.

Sql-server – SQL Server 2008R2 Database refresh in isolated environment

Just restore them over top.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY;

You may have to use the MOVE option if the folder paths for data/log files don't match, e.g.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'D:\SQLData\file.mdf',
  MOVE 'log_file_name'  TO 'E:\SQLLogs\file.ldf';

You can get the file names (and anticipate their sizes, too) from:

RESTORE FILELISTONLY FROM DISK = '\\share\folder\file.bak';

Best Answer

Related Solutions

Sql-server – Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box

Sql-server – SQL Server 2008R2 Database refresh in isolated environment

Related Question