Live-to-Test, Test-to-Live Data Transfer

It may be desirable to create new databases and create test and QA environments during the implementation or re-implementation processes. In these cases, data transfer is possible.

1- Restore Database.
Use Master;

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE database_name
FROM DISK = 'Directory(For example C):\database-name'
WITH REPLACE, MOVE 'database_name_Data' TO 'Directory (Example E\Folder):\test_database_export_name.mdf',
MOVE 'database_name_Log' TO 'Directory (Example E\Folder):\test_database_export_name.ldf'

ALTER DATABASE database-name SET MULTI_USER

2- Create Schematics
CREATE SCHEMA est-database-name; (Group-Holding Chart)
CREATE SCHEMA test-database-name_1; (Company Chart)
CREATE SCHEMA test-database-name_2020_1; (1. Company's Fiscal Year Chart)
CREATE SCHEMA test-database-name_product; (Product Chart)

Period and company charts can be created as much as the number of companies and periods.

3- Create users. (No need to re-create if the user exists in the system)
CREATE LOGIN test-database-name WITH PASSWORD = 'password' (Complex password is recommended)

4-Authorize Testers for Test Schemes.
Example; CREATE USER [test-database-name_product] FOR LOGIN [test-database-name_product] WITH DEFAULT_SCHEMA=[test-database-name_product]
ALTER ROLE [db_owner(or create the role you specify)] ADD MEMBER [test-database-name_product]

5- Run The Code Below For Each Scheme.
This Code Creates a SQL List. This created list is run by copying and pasting it on a new screen.

SELECT 'ALTER SCHEMA test-database-name TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'

FROM sys.Objects DbObjects

INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id

WHERE SysSchemas.Name = 'test-database-name'

AND (DbObjects.Type IN ('U', 'P', 'V','FN','TF'))

6-Run the period and company views again.