SQL Server Performance

Database refresh in isolated environment

Discussion in 'ALL SQL SERVER QUESTIONS' started by Natasha, Sep 8, 2013.

  1. Natasha New Member

    Dear experts,

    I would greatly appreciate your help on my database "refresh" issue.

    The scenario:

    I have 12 databases on a QA server that were restored there from a Production server approx. 2 years ago.
    Now QAs need to sync those 12 databases with databases on a Prod server; all account logins (and corresponding permissions) have to remain unchanged in QA environment - QAs only need to "refresh" databases so that data is as current as it is in Prod environment.

    For security reasons there's no (and cannot be) connection between two servers (hence it is not possible to import data with append option), so I had to request DBAs on that Prod server to backup databases and to place backup files in a shared folder (already there).

    My question is - what is the best way to "refresh" 12 databases in QA environment - is it to delete/drop old databases and restore them from backup files (then what would happen to the current QA server logins?) or is it to try to restore databases from backups without dropping 12 old databases and is this possible, would be data just appended to existing data and current logins stay unchanged ?

    Thank you in advance for any input.
  2. Luis Martin Moderator

    Welcome to the forums!.
    May be I don't understand your question.
    If you restore without dropping, your problems is to keep the same loggings than production server?
  3. Natasha New Member

    Dear Luis,

    Thank you for instant reply!

    Sorry for not being clear enough.
    In the QA environment I do need to retain same logins as they are currently on the QA server for each of 12 DBs (as they differ from Prod server databases' logins/permissions).
    I only need to "refresh" the data in all 12 DBs so the data is current as it is on Prod server.
    But if I drop QA DBs before restoring them from Prod backup files, then logins/permissions would be restored within all 12 DBs as they exist in Prod environment.
  4. Luis Martin Moderator

    Hi Natasha,
    If you can't have connexions with production servers, then you can use (may be thought production DBA's) the following procedure:
    USE master
    GO
    IF OBJECT_ID('sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
    @binvalue VARBINARY(256) ,
    @hexvalue VARCHAR(514) OUTPUT
    AS
    DECLARE @charvalue VARCHAR(514)
    DECLARE @i INT
    DECLARE @length INT
    DECLARE @hexstring CHAR(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH(@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE ( @i <= @length )
    BEGIN
    DECLARE @tempint INT
    DECLARE @firstint INT
    DECLARE @secondint INT
    SELECT @tempint = CONVERT(INT, SUBSTRING(@binvalue, @i, 1))
    SELECT @firstint = FLOOR(@tempint / 16)
    SELECT @secondint = @tempint - ( @firstint * 16 )
    SELECT @charvalue = @charvalue + SUBSTRING(@hexstring,
    @firstint + 1, 1)
    + SUBSTRING(@hexstring, @secondint + 1, 1)
    SELECT @i = @i + 1
    END
    SELECT @hexvalue = @charvalue
    GO

    IF OBJECT_ID('sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_revlogin
    @login_name SYSNAME = NULL
    AS
    DECLARE @name SYSNAME
    DECLARE @type VARCHAR(1)
    DECLARE @hasaccess INT
    DECLARE @denylogin INT
    DECLARE @is_disabled INT
    DECLARE @PWD_varbinary VARBINARY(256)
    DECLARE @PWD_string VARCHAR(514)
    DECLARE @SID_varbinary VARBINARY(85)
    DECLARE @SID_string VARCHAR(514)
    DECLARE @tmpstr VARCHAR(1024)
    DECLARE @is_policy_checked VARCHAR(3)
    DECLARE @is_expiration_checked VARCHAR(3)
    DECLARE @defaultdb SYSNAME

    IF ( @login_name IS NULL )
    DECLARE login_curs CURSOR
    FOR
    SELECT p.sid ,
    p.name ,
    p.type ,
    p.is_disabled ,
    p.default_database_name ,
    l.hasaccess ,
    l.denylogin
    FROM sys.server_principals p
    LEFT JOIN sys.syslogins l ON ( l.name = p.name )
    WHERE p.type IN ( 'S', 'G', 'U' )
    AND p.name <> 'sa'
    ELSE
    DECLARE login_curs CURSOR
    FOR
    SELECT p.sid ,
    p.name ,
    p.type ,
    p.is_disabled ,
    p.default_database_name ,
    l.hasaccess ,
    l.denylogin
    FROM sys.server_principals p
    LEFT JOIN sys.syslogins l ON ( l.name = p.name )
    WHERE p.type IN ( 'S', 'G', 'U' )
    AND p.name = @login_name
    OPEN login_curs
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled,
    @defaultdb, @hasaccess, @denylogin
    IF ( @@fetch_status = -1 )
    BEGIN
    PRINT 'No login(s) found.'
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN -1
    END
    SET @tmpstr = '/* sp_help_revlogin script '
    PRINT @tmpstr
    SET @tmpstr = '** Generated ' + CONVERT (VARCHAR, GETDATE()) + ' on '
    + @@SERVERNAME + ' */'
    PRINT @tmpstr
    PRINT ''
    WHILE ( @@fetch_status <> -1 )
    BEGIN
    IF ( @@fetch_status <> -2 )
    BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF ( @type IN ( 'G', 'U' ) )
    BEGIN -- NT authenticated account/group
    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME(@name)
    + ' FROM WINDOWS WITH DEFAULT_DATABASE = ['
    + @defaultdb + ']'
    END
    ELSE
    BEGIN -- SQL Server authentication
    -- obtain password and sid
    SET @PWD_varbinary = CAST(LOGINPROPERTY(@name,
    'PasswordHash') AS VARBINARY(256))
    EXEC sp_hexadecimal @PWD_varbinary,
    @PWD_string OUT
    EXEC sp_hexadecimal @SID_varbinary,
    @SID_string OUT

    -- obtain password policy state
    SELECT @is_policy_checked = CASE is_policy_checked
    WHEN 1 THEN 'ON'
    WHEN 0 THEN 'OFF'
    ELSE NULL
    END
    FROM sys.sql_logins
    WHERE name = @name
    SELECT @is_expiration_checked = CASE is_expiration_checked
    WHEN 1 THEN 'ON'
    WHEN 0
    THEN 'OFF'
    ELSE NULL
    END
    FROM sys.sql_logins
    WHERE name = @name

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME(@name)
    + ' WITH PASSWORD = ' + @PWD_string
    + ' HASHED, SID = ' + @SID_string
    + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
    IF ( @is_policy_checked IS NOT NULL )
    BEGIN
    SET @tmpstr = @tmpstr
    + ', CHECK_POLICY = '
    + @is_policy_checked
    END
    IF ( @is_expiration_checked IS NOT NULL )
    BEGIN
    SET @tmpstr = @tmpstr
    + ', CHECK_EXPIRATION = '
    + @is_expiration_checked
    END
    END
    IF ( @denylogin = 1 )
    BEGIN -- login is denied access
    SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO '
    + QUOTENAME(@name)
    END
    ELSE
    IF ( @hasaccess = 0 )
    BEGIN -- login exists but does not have access
    SET @tmpstr = @tmpstr
    + '; REVOKE CONNECT SQL TO '
    + QUOTENAME(@name)
    END
    IF ( @is_disabled = 1 )
    BEGIN -- login is disabled
    SET @tmpstr = @tmpstr + '; ALTER LOGIN '
    + QUOTENAME(@name) + ' DISABLE'
    END
    PRINT @tmpstr
    END
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type,
    @is_disabled, @defaultdb, @hasaccess, @denylogin
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO
    This one is for 2005.
    HTH
  5. Luis Martin Moderator

    If you have 2000 (because QA) then:
    USE master
    GO
    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
    AS
    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
    SELECT @firstint = FLOOR(@tempint/16)
    SELECT @secondint = @tempint - (@firstint*16)
    SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
    SELECT @i = @i + 1
    END
    SELECT @hexvalue = @charvalue
    GO
    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
    DECLARE @name sysname
    DECLARE @xstatus int
    DECLARE @binpwd varbinary (256)
    DECLARE @txtpwd sysname
    DECLARE @tmpstr varchar (256)
    DECLARE @SID_varbinary varbinary(85)
    DECLARE @SID_string varchar(256)
    IF (@login_name IS NULL)
    DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name <> 'sa'
    ELSE
    DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name = @login_name
    OPEN login_curs
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
    IF (@@fetch_status = -1)
    BEGIN
    PRINT 'No login(s) found.'
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN -1
    END
    SET @tmpstr = '/* sp_help_revlogin script '
    PRINT @tmpstr
    SET @tmpstr = '** Generated '
    + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
    PRINT @tmpstr
    PRINT ''
    PRINT 'DECLARE @pwd sysname'
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
    IF (@xstatus & 1) = 1
    BEGIN -- NT login is denied access
    SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
    PRINT @tmpstr
    END
    ELSE BEGIN -- NT login has access
    SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
    PRINT @tmpstr
    END
    END
    ELSE BEGIN -- SQL Server authentication
    IF (@binpwd IS NOT NULL)
    BEGIN -- Non-null password
    EXEC sp_hexadecimal @binpwd, @txtpwd OUT
    IF (@xstatus & 2048) = 2048
    SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
    ELSE
    SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
    PRINT @tmpstr
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
    + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
    END
    ELSE BEGIN
    -- Null password
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
    + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
    END
    IF (@xstatus & 2048) = 2048
    -- login upgraded from 6.5
    SET @tmpstr = @tmpstr + '''skip_encryption_old'''
    ELSE
    SET @tmpstr = @tmpstr + '''skip_encryption'''
    PRINT @tmpstr
    END
    END
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO
    ----- End Script -----
  6. Natasha New Member

    Thank you Luis!

    No, I do not have connection (as described in my very first post) hence I can only restore from backup files provided by a Prod DBA. The SQL Server is 2008R2.

    Would it be a correct assumption that major steps to complete the "refresh" task are:

    1) Script out currently existing on a QA server permissions for 12 databases to be refreshed (leaving server-level permissions intact).
    2) Explicitly delete/drop (what's a preferred way?) existing databases prior to database restore action. Or should they be deleted at all before restore?
    a) Backup existing DBs if delete is necessary.
    3) Restore 12 DBs from Prod backup files.
    4) Apply scripted earlier in Step1 corresponding logins with their permissions to each of 12 DBs.

    This is were I get a bit confused - a) delete OR do-not-delete DBs before restoring from backup files; b) after all databases are restored should I delete logins restored within the databases before executing scripted out in Step1 logins/permissions?

    Thank you in advance and sorry for bugging you with my petty questions.
  7. Luis Martin Moderator

    The idea about scripts I wrote before are:
    1) Scrip loggings with passwords in productions server.
    2) Restore databases without deleting nor dropping in backup server.
    3) Run script results (from step 1). That should work.

    Of course you have to run each step if any changes will occurs in production server.
    Said, there is a new user so you have to run step 1) to 3) again. If there is no changes in users, then only 2) is necessary.

    In any case I don't see why you should drop or delete previous databases before restore operation.

    Anyway, my suggestion is to way (tomorrow, I suppose) to find others members opinions.

    "Thank you in advance and sorry for bugging you with my petty questions."
    Don't worry is a boring Sunday:)!
  8. davidfarr Member

    I agree with Luis; with regard to trying to Append data from the Production DB to the QA DBs is not going to be reliable. 2 years is a long time to determine and append every change in that period.

    The only reliable way to get all the Production DBs data to the QA environment is with a complete RESTORE of the Production backup files onto the QA server instance.
    I am assuming that your QA server has one SQL server instance and that you want to keep the same database names as they currently have. This of course means that you would need to
    either DROP the existing QA databases first or include the "WITH REPLACE" option in your RESTORE syntax to overwrite the existing QA DBs.
    If you want to keep the old QA databases for some reason, then you can either back them up first or rename them so that they do not conflict with the restored DBs from Production.

    As you have said, you need to retain the existing security permissions fom the old QA databases.
    This is probably best done by scripting them before you drop them.
    You can do this from Managment Studio (Right-click database -- Tasks -- Generate Scripts -- Choose Objects -- Users ....)
    This is also where Luis' script above may be useful, both as a backup of your QA users and for applying the same users to the restored Production DBs.

    Once the Production databases have been restored, the QA security permissions need to be applied.
    Depending on what was changed in the last 2 years, this could get tricky. I am hoping, for the sake of simplicity, that all your QA users have db_owner rights on their respective databases.
    The script from Luis above, if I've read it correctly, will not recreate database roles for each user (db_owner, db_datareader, etc.) or grant specific user permissions on specific data objects.

    Therefore; If there are specific database roles for each QA user or specific objects (tables,SPs) within the restored databases that have special access permissions then that may require some manual adjustment after the restore. To my knowledge there is no easy way to generate script permissions at that level (database roles and database object permissions), but if you find yourself needing that then you can try looking here;
    http://www.sql-server-performance.com/sql-server-scripts/
  9. FrankKalis Moderator

    Just to add to the above answers...

    We're in a similar situation here. The DBAs here have routines that script out the existing users in the target database, FTP the backup from production to the QA server, restore it, and then apply the script with the users back again. It's a bit more sophisticated than that, I believe, but these are more or less the steps that we take. All this is part of a job that runs on a certain interval.
    Additionally I have a procedure that is triggered by the job as a second step after the restore, that checks for, say production service accounts and replaces them with QA service accounts, if needed, escalates developers permissions (since we can be trusted :)) and does all the necessary work to prepare the database for QA usage. Works quite nicely.

    EDIT: Hm, should have read David's answer before posting, since I more or less said the same, I guess. :)

Share This Page