locked
Creating copy of Database RRS feed

  • Question

  • Hi,

    I have a database created in SQL Server 2012. Currently this database is in production.

    I want to create a copy of the production database for testing purpose in the same server with different database name.

    Can you help me how to do it.

    It should not affect the production database. No try out's please

    Monday, June 10, 2013 10:12 AM

Answers

  • Hello,

    You can run a BACKUP command for the database with option "COPY_ONLY"; see Copy-Only Backups (SQL Server)

    Then you can restore this backup on your test server.


    Olaf Helper

    Blog Xing

    • Proposed as answer by Fanny Liu Tuesday, June 11, 2013 3:31 AM
    • Marked as answer by Fanny Liu Monday, June 17, 2013 1:28 AM
    Monday, June 10, 2013 10:18 AM
  • Hi,

    Right click the database that you want to copy, Select Tasks -> Copy Database

    Select the source and destination servers, then select the option Use the SQL Management Object Method. Make sure the database you want to copy is checked and click Next. Check the name and Filename for the Destination Database.

    http://msdn.microsoft.com/en-us/library/ms188664.aspx


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    • Proposed as answer by Fanny Liu Tuesday, June 11, 2013 3:31 AM
    • Marked as answer by Fanny Liu Monday, June 17, 2013 1:28 AM
    Monday, June 10, 2013 3:43 PM

All replies

  • Hello,

    You can run a BACKUP command for the database with option "COPY_ONLY"; see Copy-Only Backups (SQL Server)

    Then you can restore this backup on your test server.


    Olaf Helper

    Blog Xing

    • Proposed as answer by Fanny Liu Tuesday, June 11, 2013 3:31 AM
    • Marked as answer by Fanny Liu Monday, June 17, 2013 1:28 AM
    Monday, June 10, 2013 10:18 AM
  • Hi,

    Right click the database that you want to copy, Select Tasks -> Copy Database

    Select the source and destination servers, then select the option Use the SQL Management Object Method. Make sure the database you want to copy is checked and click Next. Check the name and Filename for the Destination Database.

    http://msdn.microsoft.com/en-us/library/ms188664.aspx


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    • Proposed as answer by Fanny Liu Tuesday, June 11, 2013 3:31 AM
    • Marked as answer by Fanny Liu Monday, June 17, 2013 1:28 AM
    Monday, June 10, 2013 3:43 PM
  • I would recommend Backup and restore if your db size is small, it will be faster than other methods..

    http://msdn.microsoft.com/en-us/library/ms186865.aspx


    Srinivasan

    Tuesday, June 11, 2013 5:03 AM