SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database refresh in isolated environment


Database refresh in isolated environment

Author
Message
Ayush Khan
Ayush Khan
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 111
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.
rollercoaster43
rollercoaster43
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 435
The fastest way would be to script your QA environment Logins and permissions and then restore the Prod backups in QA and add your QA login back.

or


Since there is no connection between the Prod and QA servers, and you need just the Data (Tables) from prod to QA, try this approach :

1.Use Generate scripts wizard on the prod server to generate the schema and data scripts of the 12 Databases and move the scripts to QA server(Genarating the schema and data option will differ depending upon the version of Sql Server).

2.On the QA server, drop all the tables in those 12 Databases using the below :

use [database1]
go
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
go
use [database2]
go
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
go
.......

3. Directly execute the schema and table data script which was taken from Prod in the respective Databases by pasting them in SSMS, or if they are too large to open in the editor, use sqlcmd to load them :


sqlcmd -S Servername -d Databasename -i Scriptpath
(sqlcmd can take time depending upon the data in the tables)


This will ensure that only the tables are recreated along with latest data in the QA environment.
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31107 Visits: 8986
of course if depends on the level of granularity of the grants for your current QA-accounts.

IMO scripting all grants may be the easiest approach.

Then, use restore db and run the scripts you generated in the previous step.


btw: When using SQLusers, we tend to use the same user names, but ensure sql passwords are different on every SQL instance.
After restore we can just use "ALTER USER" to resync. the accounts.
You will also need the generated scripts in this case, because of security being altered at QA side as part of the next release.
( but I hope that is included in your upgrade scripts )

Keep in mind when dropping objects, the grants for those objects that have been granted at object level, will also vanish.

Play it safe and always script it all before you start the next level and have your regular backups in plance.


You may even want to make some sqlagent jobs that will perform it all for you, or use Allens powershell variant to restore your production dbs to QA ( you'll need to modify the script a little bit if you want to fix it all )
Test Your SQL Server Backups with PowerShell

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101477 Visits: 33014
I'll go one step farther than the others. In addition to scripting everything out and then running a restore, I'd get the scripts into some sort of source control so that you have the ability to manage this stuff over time, automate it further, etc.

You also might want to look at third party tools such as Red Gate SQL Compare. You can then compare structures between two databases, identify the differences and generate scripts from there.

DISCLAIMER: I work for Red Gate.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search