Solved

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

Posted on 2009-06-28
10
259 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?
0
Comment
Question by:adypips
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24730680
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.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24730707
>> 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.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24730719
backup/copy/restore with move remains the best.
0
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

 

Author Comment

by:adypips
ID: 24730729
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
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24730738
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.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24730743
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

0
 

Author Comment

by:adypips
ID: 24730767
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?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24730791
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

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 250 total points
ID: 24730800
Through SSMS, it would look like this

http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups

But unfortunately you dont have option to backup with COPY_ONLY for BACKUP through SSMS. That's the reason why I gave you the scripts to achieve it earlier.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24731580
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
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question