How to Copy Schema and Some Data from SQL Server to Another Instance

How to copy schema and some data from SQL Server to another instance?

I solved it as follows. I created a custom winform app to export their data:

1. use SMO (Transfer class) to script the entire schema to a .sql file
2. loop through all specified tables (actually I just listed the large-growth ones to ignore, and process everything else) using: select * from
3. capture the records into DataTable objects and dump them to .xml files
4. Zip the whole folder up to be sent back to me

And I have a custom winform app I use locally on my dev system to import the data:

1. Create an empty database
2. Run the .sql file to build the schema
3. Disable all constraints on all tables (using sp_foreachtable)
4. loop through all .xml file and bulk import them using SqlBulkCopy object
5. Re-enable all constraints (using sp_foreachtable)

This approach works well for my database, but I would not necessarily advise this for some database designs. In my case, there are many "small" configuration tables, and only a few "large" tables with minimal relations between them so I can easily ignore the large ones. If there were more complex relations it would probably not be a good solution...

Seems to be working great so far. I was hoping to find a good way to do this without writing custom code, but I got it working with only a few hours of dev time.

FYI, I almost got it working with sqlpubwiz, but the option to script data is all-or-nothing... you can't ignore specific tables... that was a deal-breaker for my app.
The Transfer object in SMO is a very useful class - it only requires a few lines of code to script an entire database schema including all dependencies in the correct creation order to recreate the database from scratch!

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.aspx

Copy table(s) with data from one schema to another?

In SQL Management studio right click the database that has the source table, select Tasks -> Export data.

You will be able to set source and destination server and schema, select the tables you wish to copy and you can have the destination schema create the tables that will be exported.

Also, if copying to the same server and database but different schema be sure to:

1. Use the Sql Server Native Client (see
   
   https://i.stack.imgur.com/Qqhbd.png) for Source and Destination
   
   parameters
2. Select the same database name for the Source and
   Destination parameters
3. Choose copy data from one or more tables or
   views (optional) In the Select Source Tables and Views GUI
4. Change the destination table's schema to something different than the
   source schema (i.e. type something like "newschema.tablename")

How to copy complete schema to a new schema

Use SQLCMD variables in create scripts.

You will write schema independent create object scripts, something like:

CREATE TABLE [$(schema)].Table1 ...
CREATE PROCEDURE [$(schema)].Proc1...

Then you will execute it as:

sqlcmd -v schema ="Customer1" -i c:\script.sql

Export table data from one SQL Server to another

Try this:

1. create your table on the target server using your scripts from the Script Table As / Create Script step

2. on the target server, you can then issue a T-SQL statement:

   INSERT INTO dbo.YourTableNameHere
      SELECT *
      FROM [SourceServer].[SourceDatabase].dbo.YourTableNameHere
   

This should work just fine.

copy database structure from sql server to other

Follow Below steps for generate script :

• Right Click on Database
• Select task
• Select Generate Script from Task
• Follow the steps

• Finally finish for complete this process

  You can either use the SQL Server Management Object API (see task "creating, altering and removing databases"):

C# Code for generate sql script :

public string GenerateScript()
{
      var sb = new StringBuilder();

      var srv= new Server(@"Your Database Server Name");
      var db= server.Databases["Your Database name"];

      var scrpt = new Scripter(srv);
      scrpt.Options.ScriptDrops = false;

      var obj= new Urn[1];
      foreach (Table tbl in db.Tables)
      {
          obj[0] = tbl.Urn;
          if (tbl.IsSystemObject == false)
          {
              StringCollection sc = scripter.Script(obj);

              foreach (var st in sc)
              {
                  sb.Append(st);
              }
           }
       }
            return sb.ToString();
 }

Copy entire database contents (schema and data)

You can script the database schema and data using SSMS

Right click on the database. Choose tasks....then choose generate sql scripts.

Specify all objects. Then in the options menu choose at the bottom to generate scripts for data.

Below are some links that will be helpful

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

Transfer data from one database to another database

There are several ways to do this, below are two options:

Option 1
- Right click on the database you want to copy

• Choose 'Tasks' > 'Generate scripts'

• 'Select specific database objects'

• Check 'Tables'

• Mark 'Save to new query window'

• Click 'Advanced'

• Set 'Types of data to script' to 'Schema and data'

• Next, Next

You can now run the generated query on the new database.

Option 2

• Right click on the database you want to copy

• 'Tasks' > 'Export Data'

• Next, Next

• Choose the database to copy the tables to

• Mark 'Copy data from one or more tables or views'

• Choose the tables you want to copy

• Finish

Copy tables from one database to another in SQL Server

On SQL Server? and on the same database server? Use three part naming.

INSERT INTO bar..tblFoobar( *fieldlist* )
SELECT *fieldlist* FROM foo..tblFoobar

This just moves the data. If you want to move the table definition (and other attributes such as permissions and indexes), you'll have to do something else.

---

Related Topics