How to backup SQL Database

In day to day business, SQL DBA's have to take daily backup of their databases. Without taking backup their job can be hectic. So, in this tutorial we will find out different ways to take Sql Server Database Backup.

We can take Sql Server Database backup manually or we can schedule it so the databases gets backup automatically.  In this tutorial we will find out both the ways to take backup manually as well as schedule the same.

First let's try to take the backup manually as shown below:-

Before we starts, we need to be aware of below points:-

1.       Backups that are created by more recent version of SQL Server cannot be restored in earlier versions of SQL Server.

2.       To backup database you need to have administrative privileges.

3.       When you specify a back up task by using SQL Server Management Studio, you can generate the corresponding Transact-SQL BACKUP script by clicking the Script button and selecting a script destination.

The most important option that you need to know about SQLCMD are as follows:-

Note:- SQLCMD options are case-sensitive.

·       -S <sql_server_name>:-  the name of the SQL Server, including instance, if applicable. You may use "." (without quotes) if you are running SQLCMD on the same machine as SQL Server.

·       -d <database_name>:-  the name of the database on which the operation is to be performed. For example -d Test.

·       -U <user_name>:-  the SQL Server user account under which to run the specified command.

·       -P <password>:- the password associated with the specified user account.

·       -E :- use NT authentication to interact with SQL Server

·       -Q "<SQL-command>":- the command to issue to SQL Server.

·       -i <file_of_SQL_commands>:-  a file containing one or more commands to issue to SQL Server.

SQLCMD can also operate interactively. If you specify only the server, database, user name, and password, you will be presented with a prompt allowing you to enter and execute commands one at a time. The command "exit" exists interactive mode.

For more information about other options, you can type "SQLCMD -?" for help.

  

Let's start to back up a database:-

1.       Connect to the appropriate instance of the Microsoft SQL Server Database Engine, you would see the list of objects on the left hand side:-

2.       Expand Databases, you will find out many databases:-

 

3.       Right-click the database which you want to backup, go to Tasks, and then click Back Up. The Back Up Database dialog box appears. In my case, I am backing up "Test" database:-

 

Now we are in the General tab

4.       In the Database list box, verify the database name. You can optionally select a different database from the list.

 

5.       You can perform a database backup for any recovery model (FULL, BULK_LOGGED, or SIMPLE).

Here:-

Full:- 

A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored.

Bulk_logged:-

The bulk-logged recovery model is a special-purpose recovery model that should be used only intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data.

Simple:-

The simple recovery model provides the simplest form of backup and restore. This recovery model supports both database backups and file backups, but does not support log backups. Transaction log data is backed up only with the associated user data. The absence of log backups simplifies managing backup and restore. . However, a database can be restored only to the end of the most recent backup.

6.       In the Backup type list box, select Full.

Note:-  After creating a full database backup, you can create a differential database backup.

7.       For Backup component, click Database:-

8.       Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set:-

 9.       Optionally, in the Description text box, enter a description of the backup set:-

10.       Choose the type of backup destination by clicking Disk or Tape. Click on Disk and now hit on ADD button "Select Backup Destination" Dialog box will open:-

 

11.       Click on the Brows button and select the folder where you want to save the backup:-

In my case, I have saved the backup file on F drive in Chanchal\Sql_Database_Backup Folder with the name "Test_Full".  

12.       You have provided path and name of the backup file now click on OK.

Above dialog box will show you the path that you have selected.

13.       Now, again click on OK. You can see the path on Destination:-

 

14.       Now go to the "Option" tab. Select the "Overwrite Media" option. In my case I am Choosing "Overwrite All Existing Backup sets":-

15.       Now, Click on OK. Below message will appear:-

16.       Verify that the backup file has generated:-

You have successfully backed up the database. In case if you need to restore it you can perform restore database action.

Above we have performed manual way to backup the database. Now let's see how we can back up the database by using commands which then we can schedule to automate daily backup.