How to copy production DB into New Developmant Database?

Question

How to copy production DB into New Developmant Database?

poratips

Hall of Fame

Points: 3994
More actions
November 1, 2012 at 6:25 pm

#96981

Hi,
We have Sql server 2008 R2 and i would like to set up my new test server and copy production DB to new test Server.
Could you please show me the steps, how can i set up test server?
Thanks,
Pora

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

poratips (11/1/2012)
Hi,
We have Sql server 2008 R2 and i would like to set up my new test server and copy production DB to new test Server.
Could you please show me the steps, how can i set up test server?
Thanks,
Pora

Simply restore a full backup from the production database. You are taking regular backups, right? 😉

The only problem with taking copies of a production database, no matter the method, is that you might not want Developers to see sensitive parts of the data and you certainly don't want to make a copy (no matter the method) if you have things like SSN stored in clear text (you really need to fix that in production, first).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

poratips Hall of Fame Points: 3994 More actions · Answer 2

Thanks so much for your prompt response.

We are taking back up - differential back up daily and during week end we take full back up.

We have 4-6 DB on one server and couple of them kind of Data Warehousing (Dimensional and Fact Table).

We don't have that much sensitive data and we have only couple developers.

Could you please let me know that i am taking correct steps?

1) Will install Sql server 2008 R2 on new server

2) Copy latest back up files for all the databases into new server

3) Restored one by one database backup into new server

4) Collect login script from source server and run into target server to handle orphan logins

5) Run DBCC check command to verify the restore

I am planning use SSMS, will be ok, right?

Regards,

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

poratips (11/3/2012)
Thanks so much for your prompt response.
We are taking back up - differential back up daily and during week end we take full back up.
We have 4-6 DB on one server and couple of them kind of Data Warehousing (Dimensional and Fact Table).
We don't have that much sensitive data and we have only couple developers.
Could you please let me know that i am taking correct steps?
1) Will install Sql server 2008 R2 on new server
2) Copy latest back up files for all the databases into new server
3) Restored one by one database backup into new server
4) Collect login script from source server and run into target server to handle orphan logins
5) Run DBCC check command to verify the restore
I am planning use SSMS, will be ok, right?
Regards,

That sounds about right. You might run into the occasional problem with a SID for a user or login being out of sync but that's the exception rather than the rule in light of your Step 4.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

poratips Hall of Fame Points: 3994 More actions · Answer 4

Thanks Jeff.

When i do again Database Refresh on a By Weekly or Monthly, so i need to follow the same steps or is it any other approach or method? I need to refreh regualarly all the Databases (We have more than one server and each server contains different Database including Datawarehousing Database which contains Fact and Dim tables.

Thanks,

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

poratips (11/4/2012)
Thanks Jeff.
When i do again Database Refresh on a By Weekly or Monthly, so i need to follow the same steps or is it any other approach or method?

Atleast you need to fix orphan logins plus data masking (so that developers cant see the sensitive data like SSN no or email addresses)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

poratips Hall of Fame Points: 3994 More actions · Answer 6

Thanks So much.

I have another situation that my developer wants some tables from Prodction DB server to their desktop m/c.

We have already sql express installed and Database and tables ae already there but no data.

In this Database, we need some of the tables with for specific date and dept.

What will be the best way to handle this?

My problem is that i can't use Task - Copy wizard from prod server as i need some tables I need to copy data full and some of the tables i need to restrict by Date but this Date will last thre months and

I have Date column in all those tables which i need jsut last three months data.

Thanks,

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

poratips (11/6/2012)
Thanks So much.
I have another situation that my developer wants some tables from Prodction DB server to their desktop m/c.
We have already sql express installed and Database and tables ae already there but no data.
In this Database, we need some of the tables with for specific date and dept.
What will be the best way to handle this?
My problem is that i can't use Task - Copy wizard from prod server as i need some tables I need to copy data full and some of the tables i need to restrict by Date but this Date will last thre months and
I have Date column in all those tables which i need jsut last three months data.
Thanks,

use different and new thread for this

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Eric M Russell SSC Guru Points: 125519 More actions · Answer 8

poratips (11/6/2012)
Thanks So much.
I have another situation that my developer wants some tables from Prodction DB server to their desktop m/c.
We have already sql express installed and Database and tables ae already there but no data.
In this Database, we need some of the tables with for specific date and dept.
What will be the best way to handle this?
My problem is that i can't use Task - Copy wizard from prod server as i need some tables I need to copy data full and some of the tables i need to restrict by Date but this Date will last thre months and
I have Date column in all those tables which i need jsut last three months data.
Thanks,

If the developers need subsets of records from specific tables, then BCP out to files in native format, and the developers can then BCP the files into tables on their local instance. Insure that the column sequence and datatypes in development are exactly the same as what's in production, and you won't have to screw with format files, making this a breeze.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

poratips Hall of Fame Points: 3994 More actions · Answer 9

I will be building brand new test enviornments ans need to restrict user for security purpose so need to create Group for each Role

anad assign user to each Role Group, i am planning to group in Active directory, what will be the best way to do?

Once i install sql server, resotored DBs then howcan i collect the login script and run into new Db to avoid Orphan user issue?

If you give some feedback, appreciated!

Thanks,

Eric M Russell SSC Guru Points: 125519 More actions · Answer 10

poratips (11/13/2012)
I will be building brand new test enviornments ans need to restrict user for security purpose so need to create Group for each Role
anad assign user to each Role Group, i am planning to group in Active directory, what will be the best way to do?
Once i install sql server, resotored DBs then howcan i collect the login script and run into new Db to avoid Orphan user issue?
If you give some feedback, appreciated!
Thanks,

You can assign AD domain groups to SQL Server roles. That's the best way to do it.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

poratips Hall of Fame Points: 3994 More actions · Answer 11

Hi,

I have few more questions too.

I have also few Databases coming from one DB server and other one DB coming from other DB server so I will be copying total 6 databases (5 + 1) and total size of all those databases will be around 250 GB.

Could you please guide me to set up Database File size, log files, Temp DB and other parameters set up?

Do i need to do with any Master, MSDS Db too?

We will be setting up Active Diectory Group so is it any best method to set up user for reporting purpose and other DB level access?

We are planning to make this as much as much production kind of so we can do all kind of testing.

Thanks so much for your help!

Regards,

pora

Eric M Russell SSC Guru Points: 125519 More actions · Answer 12

poratips (11/13/2012)
Hi,
I have few more questions too.
I have also few Databases coming from one DB server and other one DB coming from other DB server so I will be copying total 6 databases (5 + 1) and total size of all those databases will be around 250 GB.
Could you please guide me to set up Database File size, log files, Temp DB and other parameters set up?
Do i need to do with any Master, MSDS Db too?
We will be setting up Active Diectory Group so is it any best method to set up user for reporting purpose and other DB level access?
We are planning to make this as much as much production kind of so we can do all kind of testing.
Thanks so much for your help!
Regards,
pora

By default, user accounts are members of only the public role, which means they can access no tables or stored procedures. Grant select and execute permission to roles, and then add each user accounts to the appropriate role.

The operating system, data files, tempdb, and log files all need to be accessed in parallel. Therefore, split the location of these files on separate physical drives, so the i/o of one won't become a bottleneck for the other. Read up on MSDN or sqlskills.com about optimizing tempdb and placement of data and log files.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 13

poratips (11/13/2012)
We are planning to make this as much as much production kind of so we can do all kind of testing.

try to clone as much as of prod enironment in terms of

data files(data drives their size too) same for tempdb and log files too.

master db is not required but yes msdb for same maintenance plan and jobs (specially app related jobs)

login you need to transfer but only those which will help in application and testing people and also logins for sql stuff support.

data refresh from prod user db.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 14

poratips (11/1/2012)
Hi,
We have Sql server 2008 R2 and i would like to set up my new test server and copy production DB to new test Server.
Could you please show me the steps, how can i set up test server?
Thanks,
Pora

If you already have a test instance set up and you just want to copy the objects (with or without data) from the production server, you can use the Generate Scripts task.

In SSMS, right-click the database name, select Tasks --> Generate Scripts

You will see a dialog box Choose Objects.

Select the object you want to create, then on the following steps you can designate where to copy the script, and--by using the Advanced Options--fine tune what gets scripted.

This method can also script all the data so it can generate HUGE text files. But once the script is generated you can then run it against the new instance to create a working copy for your testing environment. The nice thing about this method is that it will work in situations where trying to restore a backup is problematic (such as shared hosted servers for example).