How to export / import entire database using bulk copy (bcp) in SQL Server

How to export / import entire database using bulk copy (bcp) in SQL Server
How to export / import entire database using bulk copy (bcp) in SQL Server

Very often we will need to transfer records from tables from one system to another. Usually we do this work through a wizard, but there is also another tool in the SQL Server the bulk copy program or else bcp.

The tool bcp is divided into two, the bcp out which exports the data to txt files containing all records* and bcp in which imports txt files in database tables in selected instance.

*there is also the option of using the parameter queryout so that we can write a query to extract only selected records.

In the article we will see a query that I have made that transfers all tables contained in the database. He prepares the orders for us in two columns. At one to do bcp out from the instance and to the other for bcp in.

What should we watch out for?

Because the tables will be inserted in a random order to make the process possible we should pay attention to them foreign key constraint violations. The constraints these are logical rules that link two tables together, their job is to ensure that in a table that the primary key it's his foreign key another will still have that record.

As an example we can think that it is not possible to delete a record from a table with Countries as long as there are entries in the table addresses that have this country as their foreign key. We should first delete or change the country in these records and then be able to delete this country.

To avoid all these error messages we can either disable the constraint on the specific table:

ALTER TABLE db_test.dbo.customer NOCHECK CONSTRAINT MyConstraint

or to disable the constraints in the entire base without forgetting to enable them afterwards:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"

The code

All we need to do now is to run this script putting the parameters of username, password for the instance and the path where the files will be exported / imported:

declare 
@user varchar(50),
@pass varchar(50),
@pathout varchar(200),
@pathin varchar(200)

set @user = 'username'
set @pass = 'password'
set @pathout = 'c:\backups\'
set @pathin = 'c:\backups\'

SELECT 
'bcp '           --bcp
+  QUOTENAME(DB_NAME())+ '.'               
+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.'  -- 
+  QUOTENAME(name)                         
+ ' out ' +@pathout                        
+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
+  REPLACE(name,' ','')                    
+ '.txt -S'
+ REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','')  --server name
+' -U'+@user+' -P'+@pass+' '  --username  pass
+'-n -t[cdel] -r[rdel]'  ,  
AS  BCP_OUT,

'bcp '           --bcp
+  QUOTENAME(DB_NAME())+ '.'               
+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.' 
+  QUOTENAME(name)                         
+ ' in '+@pathin                          
+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
+  REPLACE(name,' ','')                   
+ '.txt -S'
+ REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','')  --server name
+' -U'+@user+' -P'+@pass+' '  --username  pass
+'-w -n'
+' -e'+@pathin +'logbcpin.txt'
+' -t[cdel] -r[rdel]'   ,  
AS  BCP_IN
from sys.tables

By running the script we will see two columns. One will have it bcp out script for each table containing the base to be exported and the other the corresponding procedure for bcp in to be introduced.

How to export / import entire database using bulk copy (bcp) in SQL Server

For example we will not run the whole column with all three tables but only the first one. All we have to do is do paste the command in one command prompt with elevated admin rights:

bcp [db_test].[dbo].[customer] out c:\backups\dbo_customer.txt -SSMATZOURANISLP\SQL19 -Usqladmin -Ppasssql -n -t[cdel] -r[rdel]
How to export / import entire database using bulk copy (bcp) in SQL Server

At this point the file has been created c:\backups\dbo_customer.txt with all table entries. If we were to import it to another computer, it would have to copy the file to the corresponding folder on the other machine.

In this example I'll just re-insert it into the same array after first emptying all its entries:

truncate table db_test.dbo.customer

Now with bcp in command in exactly the same way we will again import the records.

bcp [db_test].[dbo].[customer] in c:\backups\dbo_customer.txt -SSMATZOURANISLP\SQL19 -Usqladmin -Ppasssql -w -n -ec:\backups\logbcpin.txt -t[cdel] -r[rdel]
How to export / import entire database using bulk copy (bcp) in SQL Server

Ready!!!

How to export / import entire database using bulk copy (bcp) in SQL Server

After we finish the process we don't forget to again activate the constraints:

EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"

Sources:

Share it

Leave a reply