Tutorial: Import-Export-Backup Of MSSQL Database With Microsoft SQL Server Manager Express

Dr. Thomas E. Hicks
 Computer Science Department
Trinity University
 

Why Do

There are a number of reasons that you should back up your databases; three of the more important reasons would be:

  1. All hard drives will eventually go bad; a current backup will enable you to restore the databases.
     
  2. When developers are designing new procedures and queries, they will often test those functions on a backup copy of the database; once the testing proves to be correct, they can be used on the original database. If the procedures or queries are found to be faulty, the original database has not been destroyed.
     
  3. There will be times when you wish to transfer a copy of the database to a second computer system. Exporting from the original database system and Importing to the second computer system will enable you to establish the second database.

There are a number of reasons that you should back up your databases; three of the more important reasons would be:
The Database

1] We shall backup the Northwind database on Net1. Note that the Northwind database contains 8 tables.

2] The Customers database table contains 91 records. We shall open up our copy and make sure that the Customers table looks similar to that below.

 

A Zipped Copy Of Northwind.bak For You To Download

Northwind.zip
 
Export/Backup The Northwind Database

1] Using the mouse, right-mouse-click on the Northwind Database --> Select Tasks --> Select Back Up... (See Below!)

2] We are going to do a Full Backup. You can rename the backup, but we are going to leave ours as Northwind-Full Database Backup. This is going to create a file, called Northwind.bak in directory. Using the mouse, select/push the OK button.  (See Below!)
 

3] The BackUp takes only seconds to complete. Using the mouse, select/push the OK button.  (See Below!)


 

4] Take a look in directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup - you will see file Northwind.bak.  (See Below!)


 

Delete The Original Northwind Database

1] Using the mouse, right-mouse-click on the Northwind Database --> Select Delete (See Below!)

2] Using the mouse, select/push the OK button.
 

3] Note that the Northwind database is no longer there. (See Below!)


 

Import//Restore Northwind.bak To Create A New Database

1] Suppose we have a copy of our Northwind.bak in directory C:\  (See Below!)

 

IMPORTING PROBLEMS?

I have found that students are sometimes successful importing from drive C and sometimes not. It depends upon the permissions associated with the drive C of the computer. They will always have problems importing from the desktop. In order to consider the permissions we might need, it is best to examine the default folder that MSSQL uses to backup files. The required permission for the import is SQLServer2005MSSQLUser

Below, I have shown the default permissions on folder C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

What To Do If I Have Problems Importing:

Either Do
A] The easiest solution is to copy (right mouse click on file ->  Select Copy)  the file and paste (right mouse click on your backup directory -> Select Paste) it into the backup directory associated with your Microsoft SQL Server application. [Note: If you drag the file into that folder with the mouse, it will not have the right permissions. A drag file maintains the original file permissions, but a copy file acquires the permissions of the host folder.]

Or
 B] Provide full control of the backup file to the SQLServer2005MSSQLUser

2] Using the mouse, right-mouse-click on Databases --> Select Restore Database (See Below!)

3] Name the database Northwind. Select the From Device radio button. Select the to identify the backup file. (See Below!)

 

4] We need to Add the backup file. Using the mouse, select/push the Add button. (See Below!)
 

5] Locate the backup file in the top window. Using the mouse, select/push the OK button. I have selected Northwind.bak on C:\    (See Below!)
 

6] Using the mouse, select/push the OK button. (See Below!)
 

 

7] Check the box beside Northwind-Full Database Backup. Using the mouse, select/push the OK button. (See Below!)
 

 

8] Using the mouse, select/push the OK button. (See Below!)
 

9] As you can see below, once again we have a Northwind database whose Customer table has 91 records. This process can be used to make a copies of the Northwind database on other SQL/SQLEXPRESS Servers.  (See Below!)

 

Import//Restore Northwind.bak To Make A Duplicate Database Called Nortwind2.bak

1] Suppose we have a copy of our Northwind.bak in directory C:\  (See Below!)

2] Using the mouse, right-mouse-click on Databases --> Select Restore Database (See Below!)

3] Name the database Northwind2. Select the From Device radio button. Select the to identify the backup file. (See Below!)

 

4] We need to Add the backup file. Using the mouse, select/push the Add button. (See Below!)
 

5] Locate the backup file in the top window. Using the mouse, select/push the OK button. I have selected Northwind.bak on C:\    (See Below!)
 

6] Using the mouse, select/push the OK button. (See Below!)
 

 

7] Check the box beside Northwind-Full Database Backup. Using the mouse, select/push the OK button. (See Below!)

 

8] Using the mouse, select/push the OK button. (See Below!)


9] As you can see below, once again we have a Northwind2 database whose Customer table has 91 records. This process can be used to make a duplicate copy of the Northwind database on the same SQL/SQLEXPRESS Server. This copy might be used to test procedures and queries; it can also be used for development. (See Below!)
Alter The Original Northwind Database

1] Note that I have deleted the OrderDetail and the Orders tables from the Northwind database. I have also reduced the 91 Customer table records to only 12.  (See Below!)


 

Restore The Northwind Database From Northwind.bak

1] Suppose we have a copy of our Northwind.bak in directory C:\  (See Below!)

2] Using the mouse, right-mouse-click on Databases --> Select Restore Database (See Below!)

3] Name the database Northwind. Select the From Device radio button. Select the to identify the backup file. (See Below!)


4] We need to Add the backup file. Using the mouse, select/push the Add button. (See Below!)
 

5] Locate the backup file in the top window. Using the mouse, select/push the OK button. I have selected Northwind.bak on C:\    (See Below!)
 

6] Using the mouse, select/push the OK button. (See Below!)

 

7] Check the box beside Northwind-Full Database Backup. Using the mouse, select/push the OK button. (See Below!)

 

8] Using the mouse, select/push the OK button. (See Below!)

9] As you can see below, once again we have a Northwind database whose Customer table has 91 records. Tables Order Details and Orders have also been restored. This process can be used to databases on other SQL/SQLEXPRESS Servers.  (See Below!)