We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Best method to copy a live Production SQL Server Database to a Development Server ?

Medium Priority
293 Views
Last Modified: 2012-05-07
Really I would like to know what is the best way of copy a MS SQL database. I have tried this but have run into a few problems - i.e. the Development server permissions wise doesn't match with the live SQL Server Database - so am getting lots of error messages when trying create a copy of the db (as the live SQL Server db it is using Integrated Security) is there are way around this?
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
If you have any downtime for your Production Database, then do a Backup of that database and restore that backup into your Development Server.

P.S: Kindly note that Backup and Restore resets the User access in Development Server and you have to take care of this.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> as the live SQL Server db it is using Integrated Security

This should not be an issue. If you grant appropriate rights to take a backup and restore then this wont be a problem at all.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Commented:
backup/copy/restore with move remains the best.

Author

Commented:
Thanks rrjegen17,

Though this SQL Server doesn't really have downtime very often  - is it possible to backup without downtime?

1) What do you mean by appropriate rights? How do I ensure these are set?

2) Do you recommend any tutorials or links that may assist me carry out this process?

3) Are there any alternatives to backup? so I can consider these as options?

Many thanks,

AdyPips
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Ok.. If you don't have downtime, then you can use WITH COPY_ONLY clause in this scenario

http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx

Hope this helps  you out.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Permissions required for BACKUP and RESTORE a database:

"BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles."

This is also mentioned in that link provided above.

More reference on RESTORE database below:

http://msdn.microsoft.com/en-us/library/ms186858(SQL.90).aspx

Author

Commented:
Thanks,

I see, will have a good read of the above, though this looks rather complex.

I am looking the easiest and simplest way of doing this, is there any shortcuts? Is SQL Server Management Studio (or any other software / component) able to automate this process?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Ok.. T-SQL Scripts to help you out in this regard:

Kindly replace ur_db_name and file_paths accordingly
Hope this helps
-- Backup Database In Production Server
 
BACKUP DATABASE ur_db_name
 TO DISK = 'file_path\file_name.bak'
   WITH COPY_ONLY;
 
-- Copy this bak file from Production to Development Server 
 
-- Restore Database In Development Server
 
RESTORE DATABASE ur_db_name
   FROM DISK = 'file_path\file_name.bak';

Open in new window

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012

Commented:
Using Backup/Restore is a fairly blunt tool.  You would be wise to use a third party tool to do this, such as:
SQL CompareĀ®
Compares and synchronizes SQL Server database schemas
http://www.red-gate.com/products/SQL_Compare/index.htm

SQL Data Compare"
Compares and synchronizes SQL database contents
http://www.red-gate.com/products/SQL_Data_Compare/index.htm
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.