How to Backup Database in SQL Server
SQL Server databases are some of the most common databases in use. The goal of this article is how to take backup database; create a Backup file (.bak) step by step for beginner using Microsoft SQL Server Management Studio.
Why do backup database
- A hard drive will eventually go bad; a current backup will enable you to restore the databases.
- 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.
- 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.
See below steps to take backup database using SQL Server:
step by step to take backup database using SQL Server
The steps mentioned in this article are applicable for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. If you are interested in knowing the steps you need to follow to restore a full database back up in SQL Server then read the following article…
- Start up SQL Server 2008 Management Studio and connect to server.
- Expand the Databases folder, If it is not expanded, click the “+” icon next to it.
- Right-click on the database which you want to take backup and select Tasks -> Back Up.
- The Back Up Database Dialog box will display. Under the “Source” section, ensure your database is selected for the “Database” and “Backup type” is “Full“.
- Under “Backup set” provide a Name, Description(optional) and Expiration date as needed.
- Select the Destination file that is already in the list by clicking on it, and Click “Remove“. (if you can see then)
- Now, click “Add…” under the “Destination” section to specify where to save the backup file.
- Select the File name and Path where the backup will be saved and click OK.
- Once you have completed the General settings. Select Options in the Select a page pane to view the advanced options.
- It may be necessary to select the “Overwrite all existing backup sets” option in the Options section if a backup already exists and is to be overwritten.
- Under Reliability section, you can select the checkbox Verify backup when finished option. By selecting this option you can make sure the database backup is good.
- Once this option is in place, all that is left to do is to run the backup! Click OK to begin the database backup.
- The Backup takes only seconds to complete. If the database backed up successfully, you should receive below a message.
- Take a look in directory “E:\Backup” you will see file SampleDB.bak.
If you are sound with the SQL Server then you know very well how to connect to server.