{"id":2084,"date":"2021-05-07T08:00:00","date_gmt":"2021-05-07T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2084"},"modified":"2023-02-28T22:49:33","modified_gmt":"2023-02-28T19:49:33","slug":"pos-exagoyme-eisagoyme-olokliri-vasi","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-exagoyme-eisagoyme-olokliri-vasi\/","title":{"rendered":"How to export \/ import entire database using bulk copy (bcp) in SQL Server"},"content":{"rendered":"

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<\/strong> the bulk copy<\/strong> program <\/strong>or else bcp<\/strong>. <\/p>\n\n\n\n

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

*<\/strong>there is also the option of using the parameter queryout <\/strong>so that we can write a query to extract only selected records<\/em>.<\/p>\n\n\n\n

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

What should we watch out for?<\/h2>\n\n\n\n

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

As an example we can think that it is not possible to delete a record from a table with Countries <\/strong><\/em>as long as there are entries in the table addresses<\/strong><\/em> 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. <\/p>\n\n\n\n

To avoid all these error messages we can either disable the constraint on the specific table:<\/p>\n\n\n\n

ALTER TABLE db_test.dbo.customer NOCHECK CONSTRAINT MyConstraint<\/pre>\n\n\n\n
or to disable the constraints in the entire base without forgetting to enable them afterwards:<\/p>\n\n\n\n
EXEC sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"\n\nEXEC sp_msforeachtable \"ALTER TABLE ? CHECK CONSTRAINT all\"<\/pre>\n\n\n\n
The code<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
declare \n@user varchar(50),\n@pass varchar(50),\n@pathout varchar(200),\n@pathin varchar(200)\n\nset @user = 'username'\nset @pass = 'password'\nset @pathout = 'c:\\backups\\'\nset @pathin = 'c:\\backups\\'\n\nSELECT \n'bcp '           --bcp\n+  QUOTENAME(DB_NAME())+ '.'               \n+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.'  -- \n+  QUOTENAME(name)                         \n+ ' out ' +@pathout                        \n+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' \n+  REPLACE(name,' ','')                    \n+ '.txt -S'\n+ REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','')  --server name\n+' -U'+@user+' -P'+@pass+' '  --username  pass\n+'-n -t[cdel] -r[rdel]'  ,  \nAS  BCP_OUT,\n\n'bcp '           --bcp\n+  QUOTENAME(DB_NAME())+ '.'               \n+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.' \n+  QUOTENAME(name)                         \n+ ' in '+@pathin                          \n+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' \n+  REPLACE(name,' ','')                   \n+ '.txt -S'\n+ REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','')  --server name\n+' -U'+@user+' -P'+@pass+' '  --username  pass\n+'-w -n'\n+' -e'+@pathin +'logbcpin.txt'\n+' -t[cdel] -r[rdel]'   ,  \nAS  BCP_IN\nfrom sys.tables<\/pre>\n\n\n\n
By running the script we will see two columns. One will have it bcp out<\/strong> script for each table containing the base to be exported and the other the corresponding procedure for bcp in<\/strong> to be introduced.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
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 <\/strong>the command in one command prompt<\/strong> with elevated admin rights:<\/p>\n\n\n\n
bcp [db_test].[dbo].[customer] out c:\\backups\\dbo_customer.txt -SSMATZOURANISLP\\SQL19 -Usqladmin -Ppasssql -n -t[cdel] -r[rdel]<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
At this point the file has been created c:\\backups\\dbo_customer.txt<\/em> 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.<\/p>\n\n\n\n
In this example I'll just re-insert it into the same array after first emptying all its entries:<\/p>\n\n\n\n
truncate table db_test.dbo.customer<\/pre>\n\n\n\n
Now with bcp in command in exactly the same way we will again import the records.<\/p>\n\n\n\n
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]<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Ready!!!<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
After we finish the process we don't forget to again activate <\/strong>the constraints<\/strong>:<\/p>\n\n\n\n
EXEC sp_msforeachtable \"ALTER TABLE ? CHECK CONSTRAINT all\"<\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n