SQL Server – Update All Data in All Tables from Backup

sql server

I need to update data (all tables) on our QA environment by replacing it with our latest Prod Database data (Make QA more actual). As I understand, by default if I do it with SSMS it will also replace Security options and Roles which I want to avoid:

  • Our Prod and QA DBs are on different servers so service accounts
    regulating activity on servers are also different
  • AD groups are different, so if I just restore DB from Prod on QA, I
    will give access to QA to some users
  • Any other unexpected issue

Is it possible somehow to replace data in all tables (Tables on QA and Prod are identical, difference only in amount of data) from DB backup file and do not touch anything else?

As I understand through Google my only variant is to restore Prod DB on QA server with different name, truncate tables on QA and use SQL MS Wizard but I hope that there are other options.

3rd party compare tools is not an option because server owner is not allow to use it. Only possible options is something free from Microsoft. It's need to be done just one time.

Best Answer

Create a table on your QA environment inside a service database or master. Something like below:

CREATE TABLE [dbo].[SQLAccess](
    [DBName] [sysname] NOT NULL,
    [LoginName] [sysname] NOT NULL,
    [UserName] [sysname] NOT NULL
)

Fill the table with users and logins that have to be remapped:

INSERT SQLAccess (DBName, LoginName, UserName)
VALUES 
('YourRestoredDBName', 'TestServiceLogin', 'ProdServiceLogin'),
('YourRestoredDBName', 'TestDomain\ADUser', 'ProdDomain\ADUser')
-- and so on

After restoring:

USE [YourRestoredDBName];

DECLARE @SqlStatement NVARCHAR(max)
    , @LoginName    sysname
    , @UserName     sysname

DECLARE MyCursor Cursor
FOR 
    SELECT LoginName
        , UserName
    FROM YourServiceDBName.dbo.SQLAccess
    WHERE DBName = 'YourRestoredDBName'

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @LoginName, @UserName

WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @SqlStatement = 'ALTER USER [' + @UserName + '] WITH LOGIN = [' + @LoginName + ']'

    EXEC sp_executesql @SqlStatement

    FETCH NEXT FROM MyCursor INTO @LoginName, @UserName
END

CLOSE MyCursor
DEALLOCATE MyCursor

As a result you will get a database where your test logins have access to test data the same way as prod logins had on the prod environment.

Of course, it is just a concept, feel free to expand the solution with adding logging, error handling, adding additional roles or permissions for some users, etr.

Related Question