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

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

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.

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]

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]

Ready!!!

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

EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"

Sources:

• Bulk Copy Utility (bcp)