SQL Server: Clone a database with all its objects

Posted on by

A previous post covered the steps to copy table structure and data to a new or existing database.

To copy all the objects (tables, users, constraints, etc.) from one SQL Server database to another database, use the Generate Scripts function in SQL Server Management Studio.

  1. In the Object Explorer, right click the name of the database. Select Tasks > Generate Scripts…

  2. The Script Wizard will open. Click Next.

  3. Select the source database. If you want to copy ALL objects, make sure Script all objects in the selected database is checked. If the box is not checked, you will get a chance to choose the objects you want to include later.

    Click Next.

  4. Make changes to the default options if needed. Click Next.>

  5. Select Script to New Query Window. (If you want to distribute the structure or copy the database to a different server, you may want to choose the option to script to a file instead.) Click Next.

  6. The progress of the scripting process is shown.

  7. A query window will open. The first line is USE [yourdatabasename]. Change the name to the destination database by clicking on the name. A popup menu will open, displaying all the available databases on the server.

  8. Click Execute on the Query Window toolbar to run the script and start the copy.

For further details on all the options available see This article

Resources