SQL Server: Copying a Database, including Constraints, Keys, Indexes...

In SQL Server, if you wish to copy the structure of an entire database, including the Constraints, Keys, Indexes, etc., don't use the Import option, as it does not include the constraints, keys, indexes, etc. Instead, within SQL Server Management Studio, right click a database, select Tasks, then "Generate Scripts."

Now you may go through the wizard to create a script to copy your entire database. On the "Select Database" page, check the box at the bottom to "Script all objects in the selected database." 

On the "Choose Script Options" page, review the True/False settings. Scroll down to the "Table/View Options." If you wish to have a blank set of tables, leave the "Script Data" option False, otherwise, change it to True.  

If you wish to have the script create your database, set "Script Database Create" to True, then when the script is created, change the name of the database to the desired new database. Otherwise, create your new database manually, then change the first line of the created script to USE [databasename].

On the Output Option page, I select to "Script to New Query Window," but you could easily save it to a file in order to use the file on another server.

Make sure to change the database name you are creating to your new database, whether you are creating a new database or not. Right click the script window, and select to Execute the script.

Although the server roles will be in the new database, the db_datareader and db_datawriter permissions will not be there, so be sure to add these to the anonymous IISUser role used by your websites.

For further scripting information, please see my previous post on How to Deploy a local MDF Database to a Remote SQL Server Database.

May your dreams be in ASP.NET!

Nannette

 

4 Comments

  • This doesn't apply to using backup/restore option in SQL Server, or does it? At least that is what I am using to "copy" a database from one machine to another.

  • No, this is not a backup/restore.

    You can use a backup/restore.

    Backup your database, even from a different server. Then on your new server, I delete the database I want to restore. Then I create a new one with that name using the GUI interface. It is totally empty.

    then in the sql query window I run this command:

    RESTORE DATABASE databasename from Disk = 'C:\backups\databasename.bak' WITH REPLACE;

    This will allow you to restore everything. Indexes, keys, identity fields, permissions, stored procedures, data.

    I used the above when I converted my SQL Server 2000 databases to my SQL Server 2008 box.

    Do the BACKUP from your 2000 box. Then on the 2008 box, use the file created for the RESTORE.

  • http://blogs.msdn.com/b/sreekarm/archive/2009/09/11/move-a-database-from-one-server-to-another-server-in-sql-server-2008.aspx

  • Here's a link to more ways to copy a database:

    http://blogs.msdn.com/b/sreekarm/archive/2009/09/11/move-a-database-from-one-server-to-another-server-in-sql-server-2008.aspx

Comments have been disabled for this content.