Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
This is off-topic as it is a basic question about backup/restore. –  Mark Storey-Smith Sep 8 '13 at 23:11
    
A restore of the backup will do. You may have to recreate the logins, as those would have changed over time. –  StanleyJohns Sep 9 '13 at 3:19
add comment

3 Answers

Just restore them over top.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY;

You may have to use the MOVE option if the folder paths for data/log files don't match, e.g.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'D:\SQLData\file.mdf',
  MOVE 'log_file_name'  TO 'E:\SQLLogs\file.ldf';

You can get the file names (and anticipate their sizes, too) from:

RESTORE FILELISTONLY FROM DISK = '\\share\folder\file.bak';
share|improve this answer
add comment

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 ?

Note : Below solution can be automated using tsql or Powershell as described here

You can restore the databases with the MOVE and REPLACE option if the folder paths are different and to overwrite the existing ones:

USE master
GO
-- First determine the number and names of the files in the backup.
-- AdventureWorks2008R2_Backup is the name of the backup device.
RESTORE FILELISTONLY
   FROM disk = 'Path\AdventureWorks2008R2_Backup.bak'

--- KILL ALL Existing connections and wait for 5 seconds
alter database MyAdvWorks
set single_user with rollback immediate
waitfor delay '00:00:05'
alter database MyAdvWorks
set multi_user with rollback immediate

-- Restore the files for MyAdvWorks.
RESTORE DATABASE MyAdvWorks
   FROM disk = 'Path\AdventureWorks2008R2_Backup.bak'
   WITH RECOVERY, REPLACE
   MOVE 'AdventureWorks2008R2_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf', 
   MOVE 'AdventureWorks2008R2_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf'
GO

Then once the restore is done ... you have to sync the users as they will be orphaned in the database :

-- Exclude roles, and logins that are aliased to other users

declare @SQLText nvarchar(4000),@UserName nvarchar(128)

select  @UserName = min(name) from sysusers where issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null

while @UserName is not null
begin
    if exists (select name from master.dbo.syslogins where name = @UserName)
    begin
        select @UserName + 'is being syncronized'
        exec sp_change_users_login 'update_one', @UserName,@UserName
    end

    select  @UserName = min(name) from sysusers where name > @UserName and issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null

end

Another good script is from Ted Krueger Fixing orphaned database users in 2005 to 2012

share|improve this answer
    
sysusers? syslogins? tsk tsk. Should be using the principals catalog views, not the backward compatibility views. Anyway this shouldn't be necessary now if the logins and users were properly synced two years ago (I wasn't sure). –  Aaron Bertrand Sep 8 '13 at 20:14
    
@AaronBertrand Good point as I have been using this script for ages as we still have mix of environments from SQL 2000 to 2012 and we are still in the process of retiring the old ones .. Thanks for the insight. –  Kin Sep 8 '13 at 20:22
add comment

... would be data just appended to existing data and current logins stay unchanged ?

No, restoring a database backup over top of an existing database (WITH REPLACE) discards the existing database -- all of it -- and restores an exact copy of all the structures that were backed up. That's the whole point of the backup/restore process. There's no such thing as "restore with merge." While there are data merge facilities available (though probably not feasible in your environment), just... don't go there. Not for this.

What I would recommend is doing database restores and writing a script that gets run as part of the process which would drop the Production database users that will exist in the backup, and create the database users required for the QA environment.

It's highly desirable that this QA refresh process is as automated as possible, because eventually you want this to be completely self-serve, without needing to involve a DBA.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.