CODEWITHSUNDEEP
sqlsql-serversql-server-2016
NFAL
NFAL

Reputation: 195

Duplicate instances on sql server

How can I duplicate my instance in sql server !?

I want to have a test instance on my same server just for testing purposes instead of applying all tests on the real data.

is it possible to create an new instance and copy all the same data and users with permissions on a new instance?

Or is there any other way other than VM, because my DB is in running and in use from other user but I want to do my test environment without disturbing other users. And all that on the same server.

Upvotes: 0

Views: 3542

Answers (4)

user847990
user847990

Reputation:

without disturbing other users

This requirement alone means you will have to run your instance on another machine or VM. You cannot expect to maintain an instance on a server without certain things affecting the server as a whole, and any other instance running on it. (e.g. restarts for patching or troubleshooting)

There is no reason if you have the resources to not just put it on another VM, but that all depends on what you want to test (e.g. unit, integration or performance testing).

With regards to duplicating your server, you can utilize dbatools. The Start-SqlMigration would perform the work to bring over the major parts. To make it the easiest process it helps to make sure your new SQL Server instance has the same drive configuration.

Upvotes: 1

Thomas Rushton
Thomas Rushton

Reputation: 5816

You can automate this work using DBATools's Start-SqlMigration powershell commandlet.

However, I would warn against running both the production & the test instances on the same physical hardware, as you will be starving the production instance of resources.

Upvotes: 0

Marek Vitek
Marek Vitek

Reputation: 1633

Yes, you can do it. Just create new instance, and then restore your prod. database on that instance. You might need to create users there.

Following might help with creating users and mapping them to users in DB.

USE [master]
GO
CREATE LOGIN [myDBUser] WITH PASSWORD=N'myPassword' MUST_CHANGE, DEFAULT_DATABASE=[myDB], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE mdb
exec sp_change_users_login @Action = 'Update_One',@UserNamePattern = 'myDBUser', @LoginName = 'myDBUser'

Upvotes: 0

sle1306
sle1306

Reputation: 134

You could either install a new instance by starting the installer again or simply use the same instance and restore a backup of your prod database to a test database.

Upvotes: 2

Related Questions