01 - Data Migration Techniques

Hi,

 

Here we'll cover– Restore Another SQL Server's Database from Backup to SQL Server 2000

 

There are different methods to achieve the same, viz.

 

1.      Backup & Restore

2.      Detaching and Reattaching

3.      DTS Wizard

4.      Enterprise Manager SQL Scripting

5.      Copy Database Wizard

6.      BCP

7.      BULK INSERT

 

 

 

Backup & Restore

 

-          This is one of the method of migrating databases from server to server - Back up the source database and restore it on the destination server.

-          This method works for 7.0 to 2000 and 2000 to 2000 transfers.

-          You cannot restore SQL Server 6.5 databases into SQL Server 2000.

-          We can either use Enterprise Manager or Query Anlayzer to do it.

 

-          Right Click any DB || Tasks || Backup

-          Right Click any DB || Tasks || Restore

 

 

Detach & Attach

 

-          This presumes that you wish to bring the database offline in the source server and copy it to the destination server.

-          There are two methods for doing this, through Enterprise Manager and through Transact-SQL in Query Analyzer.

 

DTS Wizard

 

-          DTS Wizard allows you to import and export schemas and data from selected source and destination databases.

-          To load DTS Wizard, go to Start | Programs | Microsoft SQL Server | Import and Export Data.

-          Select Next at the DTS Import/Export Wizard dialog box.

-          Select the data source for your source data. You can reference another SQL Server data source, such as ODBC data source, Sybase, or Teradata.

-          Next we select the destination settings, choosing the server, the connection method, and the destination database.

-          Proceed further will take you to compelte the wizard.

 

 

Enterprise Manager SQL Scripting

 

 

-          If you just want to transfer the schema from an existing SQL Server 7.0 or 2000 database, you can use the SQL scripting functionality.

-          Right-click the database you wish to script in Transact-SQL and select All Tasks | Generate SQL Scripts.

-          Proceed further will take you to complete the wizard

 

 

Copy Database Wizard

 

 

-          You can use the Copy Database Wizard to copy or move databases between SQL Server 7.0 and SQL Server 2000, and between SQL Server 2000 instances.

-          Keep in mind that you cannot move or copy system databases or databases involved with replication.

-          If any of the databases are in single-user mode, Copy Database Wizard will fail.

 

-          Copy Database Wizard does not work if identical database names exist on the source and destination.

-          You must be logged in with sysadmin privileges on both servers.

-          Databases are moved sequentially and not simultaneously, even if you select multiple databases in one operation

 

-          Right Click any DB || Tasks || Copy Database

-          Proceed further will take you to compelte the wizard.

 

Use BCP

 

-          Once you have scripted out your schema and migrated it to the destination database, BCP is a great choice for extracting and loading data.

-          i.e It does not migrate DB, instead used for extracting & laoding data, one migration has already been taken place.

-          You can run BCP in a command prompt or using xp_cmdshell in Query Analyzer.

 

e.g

 

-          How to export the data from the pubs.dbo.jobs table and import into the SofaOrders.dbo.jobs table.

-          This presumes you have extracted the required table schema to the SofaOrders database already, using SQL scripting

 

 

First  BCP OUT Pubs.Jobs table	BCP "pubs.dbo.jobs" OUT C:\jobs.txt –T –SDOPEY\DOPEY –c   Press Enter to run. You should see feedback regarding the number of rows copied.
Now we have the jobs.txt data file, we can import this data into the SofaOrders.dbo.jobs table	BCP "SofaOrders.dbo.jobs" IN C:\jobs.txt –T –SDOPEY\DOPEY –c     Press Enter to run. You should see feedback regarding the number of rows copied.

 

 

-          The previous example shows a very simple BCP OUT and IN procedure; usually a production BCP OUT or IN is more complicated.

-          To handle those, required is to work with BCP format file.(Not covered here).

-          The format file gives default information used in eithera BCP IN or OUT operation, designating how data is stored and what columns each data column belongs to.

-          SQL Server includes a method for generating a format file automatically, saving you time compared with having to generate this file column by column.

 

 

BULK INSERT

 

-          BULK INSERT is like BCP, but in just one way. i.e

-          BCP does not allow to  export data from SQL Server to a text file, however it allows to copy a data file into a database table.

-          BULK INSERT performs very well as it executes within the SQL Server process, whereas BCP and DTS execute within their own processes.

 

-          If you are aiming to import data using the fastest method, you should test trade-off between the three - BULK INSERT, DTS, and BCP to decide which works best.

 

 

Import this data into the SofaOrders.dbo.jobs table, using the jobs.txt data file created ib BCP example

USE SofaOrders BULK INSERT SofaOrders.dbo.jobs FROM 'c:\jobs.txt' WITH ( FORMATFILE = 'c:\jobs_fmt.txt' )     Press F5 to execute. You should get a prompt showing the number of rows inserted.

 

 

 

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Tech Lead