How to Back Up a SQL Server Database
Your SQL Server database resides on a SQL Server that manages all interactions (reading, writing, etc) of data as well as all administrative tasks. Making a backup of the data is therefore not just a matter of copying some files that exist on the network. This article explains how to make a backup of your SQL Server database so the database (its structure and content) is encapsulated in a single backup file that can be archived, moved to another server, or shared with a services professional.
SQL Server Management Studio (SSMS)
This is a software application that is used to administer SQL Server and the databases being managed by that server. The steps that follow depend on this program. You will find a shortcut to this program on the Start menu under the Microsoft SQL Server 201X program group. If not present, the program may need to be downloaded and installed. For the full SQL Server product (not the Express edition), SQL Management Studio can be installed from your SQL Server installation media. For the Express version, you can use the following download links to obtain it:
- 2008 R2:http://www.microsoft.com/download/en/details.aspx?id=22985
Steps to Backup
In order to use the following steps to make a backup of your SQL Server database, you must have access to the server and have the necessary login credentials.
- Start SQL Server Management Studio. Upon starting the program, the Connect to Server window will appear. This is the same window that appears when you select the File menu and click Connect Object Explorer
- In the Server type box, select Database Engine
- In the Server name box, select (or type) the name of the SQL Server. Note that some servers may manage multiple SQL Server instances (denoted by in the form ServerNameInstanceName).
- In the Authentication box, select the appropriate option based on how security has been configured on your server-
- Windows Authentication - In this case, your access to the server and your permissions are based on your Windows login. For example, if your Windows login is a member of the Administrators group, your SQL Server may be configured to accept your Windows login as a SQL Server administrator.
- SQL Server Authentication - In this case, you will be prompted to enter a user name and password that is different than your Windows user name and password. The login credentials would be specific to what has been configured within SQL Server. The default SQL Server administrator account, for example, is
saaccount will provide full access to all server and database tasks and can be used to carry out the tasks in this article. If the login credentials for the
saaccount are not available to you, another account with sufficient permissions may be used.
- After successfully connecting, the Object Explorer pane will be shown on the left side of the window. This displays a tree-view with the name of the server as the top-most root node. Expand the server node and then expand the Databases node.
- Locate the database to be backed up and right-click on it.
- Click Tasks and then Backup.
- The Backup Database window will appear. Confirm that the Backup type drop-down is set to Full.
- Under Destination - Backup to, click Disk.
- Under Destination, click Add.
- The Select Backup Destination window will appear. Click the ellipses button to the right of the Filename box.
- Select the destination folder.
IMPORTANT: - This is not a network path. Therefore, if you are running SSMS on a client workstation and connecting to the server, you will either need to place the backup file in a folder that is shared on the network or you will need to login to the server's console in order to access it.
- In the Filename box, type a unique name for the backup file that is to be created.
- Click OK and then OK to dismiss the backup destination window.
- Click OK in the Back Up Database window to start the backup.
- As noted above, upon completion, you will be able to locate the backup file on the server's file system. If the backup must be sent, it should first be compressed (e.g., using WinZip) to reduce the transfer time.