locked
What is the best way to create a development database? RRS feed

  • Question

  • What is the best way to create a development database or a mirror of the live database?    I scripted the database and files, but now that I'm trying to load the data from one DB to another(on the same server with same name but _dev suffix) using SSIS, but it fails while writing to the destination file, because the first column is an identity field.   I can get it to work if I don't map that field, but I want it to be the same in both databases since it's the key that other related files are depending on - created with related transactions.  I would like this to automatically refresh when needed and be a mirror of my live database.  

    Thanks in advance!!

    Wednesday, March 27, 2013 10:31 PM

Answers

  • One note on the backup restore method - in general you do not want to be making your own prod backups without involving the DBA's.  In a perfect world you would use a copy of the backups that they are already doing.  Under no circumstance should you create a backup in a non standard location without talking to the backup operators, since you could run the risk of them not knowing where the source for an emergency restore is.

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Marked as answer by Mike Yin Thursday, April 4, 2013 5:22 AM
    Thursday, March 28, 2013 12:29 PM
  • Hi,

    Hi, you shouldn't have any issues with "orphaned logins" if both the origin and destination server\instance are the same.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    • Proposed as answer by Mike Yin Friday, March 29, 2013 9:08 AM
    • Marked as answer by Mike Yin Thursday, April 4, 2013 5:22 AM
    Thursday, March 28, 2013 5:23 PM

All replies

  • I see you are trying to ask 2 Question rather you are facing 2 issues

    a) Enable MIrroing

    b) Achieve Mirroring via SSIS.

    Though i would assume you are best served by taking a backup of database and restore as and when required.

    For a) you should read this http://msdn.microsoft.com/en-us/library/ms190941.aspx, and this is more of DBA ask to set it up. 

    For b) Using SSIS i think your approach is create SSIS package to read data and then then tranfer it as required. for SSIS approach you would need to always overwrite the destination data like the image below. I am showing you the data transfer for a single table



    Abhinav
    http://bishtabhinav.wordpress.com/

    Wednesday, March 27, 2013 10:57 PM
  • Easiest way - backup and restore.

    Chuck Pedretti | Magenic – North Region | magenic.com

    Wednesday, March 27, 2013 11:38 PM
  • Thursday, March 28, 2013 4:26 AM
  • Use Copy database wizard:

    http://msdn.microsoft.com/en-us/library/ms188664%28v=sql.110%29.aspx

    Not a good choice for a prod database

    The detach/attach method is relatively fast but not appropriate for a production environment

    The SMO method is extremely slow, puts a large load on the DB and rarely works for large databases.


    Chuck Pedretti | Magenic – North Region | magenic.com

    Thursday, March 28, 2013 11:14 AM
  • Hi,

    In my opinion, the easiest and safest way is a SQL Backup/Restore

    You're taking a consistent photo of your database and restoring it into another box.

    You risk losing database integrity by copying on a per-table basis : what happens if some user is writing to table #23 (which you copied 5' ago) while

    you're in the middle of the table #38 copy?

    SQL Backup and restore can be performed by SSIS.

    1. Launch your BACKUP DATABASE xyz TO DISK on the origin server (where DISK is a Windows share or a shared folder on the destination server)

    2. On your destination server, kill any connections to the database that will be replaced and put it on offline mode

    3. Restore from BACKUP

    4. If you have orphaned logins (which is the case when relying on SQL authentication), fix them


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thursday, March 28, 2013 12:17 PM
  • One note on the backup restore method - in general you do not want to be making your own prod backups without involving the DBA's.  In a perfect world you would use a copy of the backups that they are already doing.  Under no circumstance should you create a backup in a non standard location without talking to the backup operators, since you could run the risk of them not knowing where the source for an emergency restore is.

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Marked as answer by Mike Yin Thursday, April 4, 2013 5:22 AM
    Thursday, March 28, 2013 12:29 PM
  • Hi,

    I concur with Chuck's recommendations.

    By the way, for your particular need I recommend to use BACKUP WITH COPY_ONLY (so you don't break "live" backup log chains)


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thursday, March 28, 2013 12:37 PM
  • Thanks,   it sounds like the back up and restore is the best option, since the key to the files is guid and will not allow me transfer data.    I think I tried both the copy db wizard and the back up and restore options but they were problematic.   Don't remember off hand.   I'll try them again.   The duplicate database will be on the same server with the same name but a suffix of _dev added.  

    Thursday, March 28, 2013 5:20 PM
  • Hi,

    Hi, you shouldn't have any issues with "orphaned logins" if both the origin and destination server\instance are the same.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    • Proposed as answer by Mike Yin Friday, March 29, 2013 9:08 AM
    • Marked as answer by Mike Yin Thursday, April 4, 2013 5:22 AM
    Thursday, March 28, 2013 5:23 PM