Importing data from SQL Server

When we're using SQL Server, we can use several different methods to transfer data into our SQL Server instance. Two very fast and straightforward options use backup, and the attach and detach option for SQL Server. If we're given a backup of a database, we can restore the backup to our SQL Server to get access to all tables and other objects that are stored in the database. Such a backup would usually be provided as one single file (the file extension really doesn't matter, but it's common to use .bak), which you can copy to your server and use either the SQL Server Management Studio or T-SQL code to restore the database in your environment:

USE [master]
GO

RESTORE DATABASE [DemoDB] FROM 
DISK = 'c:\SQLData\DemoDB.bak'

Such simple code will grab the file located on our disk drive C in the folder SQLData and restore the backup as a DemoDB database. A Data file and log file will be placed to the default locations configured on your SQL Server installation, so it does not matter where and how the database was stored previously. There are numerous options that you can provide to the restore command, but for restoring the database from a backup, we don't need to go any deeper.

If you would like to utilize Management Studio, you need to right-click Databases and select the restore database menu item. Navigate to the location on the disk where you have the backup file, select the backup file, and click OK. You'll then see the content of the backup file, and you can select the name for your new database where the data will be restored:

Depending on the size of the original database, the restore can take a few seconds, or up to a few hours, based on the size of the backup and the performance of your SQL Server environment.

Another option is to use Data Tier Application (DAC in SQL Server), which uses a special file type, .bacpac. This type of application can be created on SQL Server or deployed to SQL Server. It's a very interesting format for us, since this type of file is used frequently with Microsoft Azure SQL Database, which uses .bacpac files to import and export data. So if our primary source was a database running in the Azure public cloud, then the Data Tier application type of deployment would be used:

The Data Tier application is a logical unit that defines all of the SQL Server objects, such as tables and views, combined with instance level objects, such as logins, which are associated with the database. This offers great benefits for easy deployment, since the database users will be correctly mapped to SQL Server logins, which are part of the Data Tier application deployment. This approach allows developers and administrators to quickly exchange the .bacpac file for deployment, testing, and so on, without any need to exchange scripts or backup files that don't cover the entire configuration:

Once the import is finished, you can review all the steps that were part of the deployment plan:

If we don't have access to files such as a backup or a DAC file, and we have access to the original SQL Server, we can use the Data Migration Assistant. This tool would be primarily used as a migration tool between various versions of SQL Server, and to assess the current state of SQL Server for migration to a newer version or to Microsoft Azure SQL Database. One of the useful features of this tool is the ability to migrate data directly from older versions of SQL Server, such as the 2005 and 2008 versions, where backups from 2005 in particular may cause problems when restoring new versions of SQL Server 2016 and 2017.