locked
How do I copy a database from a production to a training environment RRS feed

  • Question

  • Our DBA is out for about 6 weeks. One of his regular jobs is to take a Database that is in our Prod environment and copy it to our Training environment.

    I have backed up and restored to different servers before, however the Database name in Prod is different than in the Train environment in this case.

    In other words I have a PROD database named DATABASE-XX that I want to copy to another SQL server and restore it to DATABASE-XX-TRAIN.

    Is there anything special I need to do, other than backup on PROD, copy to TRAIN and restore to the corresponding DB in TRAIN? Thanks!

    Wednesday, October 21, 2015 6:51 PM

Answers

  • Our DBA is out for about 6 weeks. One of his regular jobs is to take a Database that is in our Prod environment and copy it to our Training environment.

    I have backed up and restored to different servers before, however the Database name in Prod is different than in the Train environment in this case.

    In other words I have a PROD database named DATABASE-XX that I want to copy to another SQL server and restore it to DATABASE-XX-TRAIN.

    Is there anything special I need to do, other than backup on PROD, copy to TRAIN and restore to the corresponding DB in TRAIN? Thanks!

    One of the important ones is the ensure the permissions for "TRAIN" are there after you restore a copy from PROD, since TRAIN permissions will be wiped out/over-written.

    You should backup your DATABASE-XX-TRAIN (just in case) also so you have a reference for 

    1. You need to extract the user logins and permissions from DATABASE-XX-TRAIN since you'll overwrite them
    (search revlogins from microsoft it's used to migrate users/logins  it will instruct you to create a stored procedure in the current server in master db.  The script will generate a script of all logins - training in our case - then save off the script for use to apply for the "TRAIN" database after it's overwritten.

    2.  I would also visually spot check and note the user logins in your training database and compare it with the revlogin script generated before doing so to be sure all the ones in the generated script are accounted for.   

    3. You may also need to check for orphaned logins to fix. 
      Conflicts between the instance security - logins  and the user database (new train database) - This probably won't happen but incase.



    • Edited by normchan Wednesday, October 21, 2015 7:37 PM changed some wording
    • Marked as answer by Jpeters2570 Wednesday, October 21, 2015 8:11 PM
    Wednesday, October 21, 2015 7:34 PM
  • Hi,

    You can do this, Also consider backing up all the security on the training database as this will be lost once the database is restored from prod. You will then have to re-apply the security after the restore.

    Backup in prod, copy to Train server, Right click on the training database >tasks> restore >database

    select device > Browse to the backup file

    Change the destination Database to Trainning DB

    Select overwrite as shown below

    Apply the security back on Train DB that was backed up.

    Fix Orphaned users if there are any.

    Hope that helps

    Cheers

    Bhanu



    • Edited by bhanu_nz Wednesday, October 21, 2015 7:51 PM
    • Marked as answer by Jpeters2570 Wednesday, October 21, 2015 8:11 PM
    Wednesday, October 21, 2015 7:49 PM
  • Hi,

    use the below script to script the permissions out on the training DB. This will produce scripted permissions. Save them to a secure location and apply them back on to the training DB once the restore is completed

    -- Capture permissions for all users in a DB
    
    
    SET NOCOUNT ON
    
     
    
    DECLARE @Database varchar(max)
    
    SET @Database = '<Db_Name>'-- set the database name here
    
     
    
    IF  EXISTS (SELECT * FROM SYS.SYSDATABASES WHERE name = @Database)
    
    EXEC('USE ' + @Database + ';
    
     
    
    DECLARE @temp table(Extract Varchar(MAX))
    
     
    
    INSERT INTO @temp (Extract)
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'''''''''' + name + '''''''''')IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + name + '''''''''') DROP USER [''  + name + '']''''); ''
    
    FROM sys.sysusers WHERE islogin = 1 AND hasdbaccess = 1 AND name NOT IN (''dbo'')
    
     
    
     
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS 
    
    (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + dp.name + '''''''''') 
    
    CREATE USER ['' + dp.name + ''] FOR LOGIN ['' + sp.name + '']''''); ''
    
    FROM sys.server_principals sp 
    
    JOIN sys.database_principals dp ON (sp.sid = dp.sid) 
    
    AND dp.name NOT IN (''dbo'')
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS
    
     (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + dp.name + '''''''''')
    
      CREATE USER ['' + dp.name + ''] WITHOUT LOGIN '''');  ''
    
    FROM sys.database_principals dp left 
    
    JOIN sys.server_principals sp ON (sp.sid = dp.sid) 
    
    where dp.principal_id between 5 and 16383
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; EXEC sp_addrolemember '''''''''' + User_Name([groupuid]) + '''''''''', '''''''''' + User_Name([memberuid]) + ''''''''''''''); ''
    
    FROM sys.sysmembers WHERE User_Name([memberuid]) NOT IN (''dbo'')
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + CASE [a].[state_desc] WHEN ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT '' ELSE [a].[state_desc]  END
    
     + '' '' 
    
     + [a].[permission_name] + CASE class WHEN 1 THEN '' ON ['' 
    
     + [c].[name] 
    
     + ''].[''
    
     + Object_Name([a].[major_id]) + ''] '' ELSE '''' END + '' TO ['' 
    
     + User_Name([a].[grantee_principal_id]) 
    
     + ''] '' + CASE [a].[state_desc] WHEN ''GRANT_WITH_GRANT_OPTION'' THEN '' WITH GRANT OPTION'' ELSE '''' END 
    
     + ''''''); '' COLLATE Latin1_General_CI_AS AS [SQL]
    
    FROM sys.database_permissions a 
    
     inner join [sys].[all_objects] b 
    
      ON [a].[major_id] = [b].[object_id]
    
     inner join [sys].[schemas] c
    
      ON [b].[schema_id] = [c].[schema_id]
    
     
    
     INSERT INTO @temp (Extract)  
    
     SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + [a].[state_desc] 
    
     + '' '' 
    
     + [a].[permission_name] 
    
     +  '' TO [''  
    
     + USER_NAME([a].[grantee_principal_id])
    
     + ''] '''');''
    
     from sys.database_permissions  a 
    
     where a.class=0
    
     
    
     INSERT INTO @temp (Extract)  
    
     SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + [a].[state_desc] 
    
     + '' '' 
    
     + [a].[permission_name] 
    
     + '' ON SCHEMA ::''
    
     + '' [''
    
     + SCHEMA_NAME([a].[major_id]) +
    
     + ''] TO [''  
    
     + USER_NAME([a].[grantee_principal_id])
    
     + ''] '''');''
    
     from sys.database_permissions  a 
    
     where a.class=3
    
     
    
    SELECT Extract AS [Extract] FROM @temp
    
    GO
    
    ')
    
    ELSE
    
    SELECT '--NO'
    
    GO
    
    

    You can't stop SQL Services while performing a restore as you can't perform a restore if the SQL Server is shut. Just take a one-off back of the training db just to make sure you are covered if something goes worng. This should be enogh.

    I also recommend you watch 'how to perform a DB Restore in sql server' on YouTube, to get a general idea.

    Hope this help

    Thanks

    Bhanu 

    • Marked as answer by Jpeters2570 Wednesday, October 21, 2015 8:50 PM
    Wednesday, October 21, 2015 8:46 PM

All replies

  • Our DBA is out for about 6 weeks. One of his regular jobs is to take a Database that is in our Prod environment and copy it to our Training environment.

    I have backed up and restored to different servers before, however the Database name in Prod is different than in the Train environment in this case.

    In other words I have a PROD database named DATABASE-XX that I want to copy to another SQL server and restore it to DATABASE-XX-TRAIN.

    Is there anything special I need to do, other than backup on PROD, copy to TRAIN and restore to the corresponding DB in TRAIN? Thanks!

    One of the important ones is the ensure the permissions for "TRAIN" are there after you restore a copy from PROD, since TRAIN permissions will be wiped out/over-written.

    You should backup your DATABASE-XX-TRAIN (just in case) also so you have a reference for 

    1. You need to extract the user logins and permissions from DATABASE-XX-TRAIN since you'll overwrite them
    (search revlogins from microsoft it's used to migrate users/logins  it will instruct you to create a stored procedure in the current server in master db.  The script will generate a script of all logins - training in our case - then save off the script for use to apply for the "TRAIN" database after it's overwritten.

    2.  I would also visually spot check and note the user logins in your training database and compare it with the revlogin script generated before doing so to be sure all the ones in the generated script are accounted for.   

    3. You may also need to check for orphaned logins to fix. 
      Conflicts between the instance security - logins  and the user database (new train database) - This probably won't happen but incase.



    • Edited by normchan Wednesday, October 21, 2015 7:37 PM changed some wording
    • Marked as answer by Jpeters2570 Wednesday, October 21, 2015 8:11 PM
    Wednesday, October 21, 2015 7:34 PM
  • Hi,

    You can do this, Also consider backing up all the security on the training database as this will be lost once the database is restored from prod. You will then have to re-apply the security after the restore.

    Backup in prod, copy to Train server, Right click on the training database >tasks> restore >database

    select device > Browse to the backup file

    Change the destination Database to Trainning DB

    Select overwrite as shown below

    Apply the security back on Train DB that was backed up.

    Fix Orphaned users if there are any.

    Hope that helps

    Cheers

    Bhanu



    • Edited by bhanu_nz Wednesday, October 21, 2015 7:51 PM
    • Marked as answer by Jpeters2570 Wednesday, October 21, 2015 8:11 PM
    Wednesday, October 21, 2015 7:49 PM
  • Thanks for the help. What is the process to backup and restore the security settings?

    I also plan to stop SQL services on TRAIN during the restore and snapshot the server. Worst case scenario, I can restore the snapshot.

    I was not sure what would happen if you restored from a different DB name so this makes me feel much better.

    Wednesday, October 21, 2015 8:14 PM
  • Hi,

    use the below script to script the permissions out on the training DB. This will produce scripted permissions. Save them to a secure location and apply them back on to the training DB once the restore is completed

    -- Capture permissions for all users in a DB
    
    
    SET NOCOUNT ON
    
     
    
    DECLARE @Database varchar(max)
    
    SET @Database = '<Db_Name>'-- set the database name here
    
     
    
    IF  EXISTS (SELECT * FROM SYS.SYSDATABASES WHERE name = @Database)
    
    EXEC('USE ' + @Database + ';
    
     
    
    DECLARE @temp table(Extract Varchar(MAX))
    
     
    
    INSERT INTO @temp (Extract)
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'''''''''' + name + '''''''''')IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + name + '''''''''') DROP USER [''  + name + '']''''); ''
    
    FROM sys.sysusers WHERE islogin = 1 AND hasdbaccess = 1 AND name NOT IN (''dbo'')
    
     
    
     
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS 
    
    (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + dp.name + '''''''''') 
    
    CREATE USER ['' + dp.name + ''] FOR LOGIN ['' + sp.name + '']''''); ''
    
    FROM sys.server_principals sp 
    
    JOIN sys.database_principals dp ON (sp.sid = dp.sid) 
    
    AND dp.name NOT IN (''dbo'')
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; IF NOT EXISTS
    
     (SELECT * FROM sys.database_principals WHERE name = N'''''''''' + dp.name + '''''''''')
    
      CREATE USER ['' + dp.name + ''] WITHOUT LOGIN '''');  ''
    
    FROM sys.database_principals dp left 
    
    JOIN sys.server_principals sp ON (sp.sid = dp.sid) 
    
    where dp.principal_id between 5 and 16383
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; EXEC sp_addrolemember '''''''''' + User_Name([groupuid]) + '''''''''', '''''''''' + User_Name([memberuid]) + ''''''''''''''); ''
    
    FROM sys.sysmembers WHERE User_Name([memberuid]) NOT IN (''dbo'')
    
     
    
    INSERT INTO @temp (Extract) 
    
    SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + CASE [a].[state_desc] WHEN ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT '' ELSE [a].[state_desc]  END
    
     + '' '' 
    
     + [a].[permission_name] + CASE class WHEN 1 THEN '' ON ['' 
    
     + [c].[name] 
    
     + ''].[''
    
     + Object_Name([a].[major_id]) + ''] '' ELSE '''' END + '' TO ['' 
    
     + User_Name([a].[grantee_principal_id]) 
    
     + ''] '' + CASE [a].[state_desc] WHEN ''GRANT_WITH_GRANT_OPTION'' THEN '' WITH GRANT OPTION'' ELSE '''' END 
    
     + ''''''); '' COLLATE Latin1_General_CI_AS AS [SQL]
    
    FROM sys.database_permissions a 
    
     inner join [sys].[all_objects] b 
    
      ON [a].[major_id] = [b].[object_id]
    
     inner join [sys].[schemas] c
    
      ON [b].[schema_id] = [c].[schema_id]
    
     
    
     INSERT INTO @temp (Extract)  
    
     SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + [a].[state_desc] 
    
     + '' '' 
    
     + [a].[permission_name] 
    
     +  '' TO [''  
    
     + USER_NAME([a].[grantee_principal_id])
    
     + ''] '''');''
    
     from sys.database_permissions  a 
    
     where a.class=0
    
     
    
     INSERT INTO @temp (Extract)  
    
     SELECT ''EXEC( ''''USE ' + @Database + '; ''
    
     + [a].[state_desc] 
    
     + '' '' 
    
     + [a].[permission_name] 
    
     + '' ON SCHEMA ::''
    
     + '' [''
    
     + SCHEMA_NAME([a].[major_id]) +
    
     + ''] TO [''  
    
     + USER_NAME([a].[grantee_principal_id])
    
     + ''] '''');''
    
     from sys.database_permissions  a 
    
     where a.class=3
    
     
    
    SELECT Extract AS [Extract] FROM @temp
    
    GO
    
    ')
    
    ELSE
    
    SELECT '--NO'
    
    GO
    
    

    You can't stop SQL Services while performing a restore as you can't perform a restore if the SQL Server is shut. Just take a one-off back of the training db just to make sure you are covered if something goes worng. This should be enogh.

    I also recommend you watch 'how to perform a DB Restore in sql server' on YouTube, to get a general idea.

    Hope this help

    Thanks

    Bhanu 

    • Marked as answer by Jpeters2570 Wednesday, October 21, 2015 8:50 PM
    Wednesday, October 21, 2015 8:46 PM
  • Looks like security is the same on both Databases to start with:

    PROD server

    TRAIN Server

    So my plan is:

    1. Stop SQL on TRAIN server
    2. Snapshot TRAIN server
    3. Backup security on TRAIN DB
    4. Backup PRODDB
    5. Copy PRODDB to TRAIN server
    6. Restore PRODDB to TRAINDB
    7. Restore security to TRAINDB
    8. Fix any orphaned users
    9. Start SQL service on TRAIN server

    Wednesday, October 21, 2015 8:48 PM
  • How is the backup currently being done? Do you need a fresh backup and restore? Why don't you use an existing backup so you are not touching production?

    I would suggest you take a copyonly backup so that it wont affect the backup chain and if you end up deleting the backup file you should not worry.

    https://msdn.microsoft.com/en-us/library/ms191495.aspx

    Below query will help you get the older backups performed for the database and where the backups are located.

    USE [YOUR DATABASENAME]   -- Replace this with your database name

    SELECT TOP 100
    s.database_name,
    m.physical_device_name,
    CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
    CAST(DATEDIFF(second, s.backup_start_date,
    s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
    s.backup_start_date,
    s.is_copy_only,
    CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
    CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
    CASE s.[type]
    WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
    WHEN 'L' THEN 'Transaction Log'
    END AS BackupType,
    s.server_name,
    s.recovery_model
    FROM msdb.dbo.backupset s
    INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
    WHERE s.database_name = DB_NAME() -- Remove this line for all the database
    ORDER BY backup_start_date DESC, backup_finish_date
    GO

    Thanks,

    -SreejitG

    Wednesday, October 21, 2015 8:58 PM
  • That's a good point. I will have a full nightly from the night before that I can use.
    Wednesday, October 21, 2015 9:00 PM
  • Hi,

    As mentioned in my earlier answer. You cannot perform a restore of a db in a server if is stopped. The SQL Server has to be up and running for you to perform a restore. This is very basic thing that is required.

    Hope this helps

    Thanks

    Bhanu

    Wednesday, October 21, 2015 9:05 PM
  • Hi,

    As mentioned in my earlier answer. You cannot perform a restore of a db in a server if is stopped. The SQL Server has to be up and running for you to perform a restore. This is very basic thing that is required.

    Hope this helps

    Thanks

    Bhanu


    Understood and thanks again!
    Wednesday, October 21, 2015 9:06 PM