September 8, 2013 at 2:30 am
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.
September 9, 2013 at 2:34 am
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.
September 9, 2013 at 3:36 am
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[/url]
Johan
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 9, 2013 at 4:11 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply