Month: January 2018

How to properly do an export and import of a database

Posted on Updated on

Last year, I came across a database that had 2 corrupted tables.  Fortunately, these 2 tables were actually not used so it can safely get ignored.  However with those 2 corrupted tables still in the database, I could not get a passing result when doing DBCC CheckDB against the database.  And I have tried exporting the database into an “empty” database created by the same application without success.

 

Here is how I fixed my problem:

Step 1 – Export Database

  • Launch SSMS as an admin and login as SA if possible or with a user with sysadmin rights.
  • Right-click on the database and select Tasks > Export Data.  Click Next.
  • Select SQL Server Native Client 11.x under Data Source, specify the Server Name, use SA (preferred).  And then select the source database.  Click Next.

Import

  • Under Destination, select SQL Server Native Client 11.x.  And specify the other values.
  • Click New under Database to create a new DB where the data will be exported to.  Click Next.
  • In the next screen, select Copy data from one or more tables or views.  Click Next.

Table Copy

  • In the next screen, select all tables you would like to export and select Next.  I omitted the Views at this stage as I do not need them.  And leave all the rest as default and click on Finish to start the export process.

Step 2 – Working on the Empty Database

  • In my case, the application my database was using has the ability to create an empty database with all views, schemas, keys, etc.  My only problem is that it then also populates some of the tables with default data.  I tried to truncate these tables got FK restrictions would not allow me.  So I had to run a delete from on all tables.

use <empty_db>

EXEC sp_msforeachtable @command1=”delete from ?”

GO

  • This command can also be used to make sure your database is really empty.  I have seen other posts where they suggest to delete all FKs and then delete the data and then reinstall FKs.  For me, that is too much work.

Step 3 – Importing Data into the Empty Database

  • Right-click on the Empty database, select Task > Import Data
  • Fill out the data source referencing to the exported database from the previous step.  Click Next.
  • In the Choose a Destination window, set it to the empty database.  Click Next.
  • Again select Copy data from one or more tables or views on the next screen.
  • In the next screen, select all tables.  When they are all selected, click Edit Mappings and select Enable Identity Insert.  Click Next.  And hit Finish to start the import.

 

You should not get any errors at this point.  But if you do, review the errors and make appropriate adjustments and try again.

 

——————————- Additional Info ———————————–

 

If you are having problems with constraints when deleting existing data on the table or when trying to import data.  Use the command below:

EXEC sp_msforeachtable @command1=”ALTER TABLE ? NOCHECK CONSTRAINT ALL”

 

However, you may need to know what constraints are currently set so you know what to enable later.  I use this command to show me what they are.

SELECT name, tbl=object_name(parent_obj)

FROM sysobjects

WHERE objectproperty(id, ‘CnstIsNotTrusted’) = 1

GO

 

SELECT *, name, is_not_trusted

FROM sys.foreign_keys

WHERE is_not_trusted = 1

GO

 

Disclaimer:  Scripts are provided AS IS, and author is not responsible with any issues resulting to usage of this script.

This entry was posted in SQL and tagged , , , , , , , .