Back To Blog

SQL Server interview questions: - Two Methods that explains to back-up SQL Server Database and Restore Database?

By Shiv Prasad Koirala in SQL
Aug 3rd, 2011
4713
0

There are basically two ways (methods) in order to back-up SQL Server Database and Restore database.

The following are the two methods to do the same.

1. By simple Detach and Attach Methods.
2. By creating a .bak file of the Database.

Let's see a simple example on both the above methods for creating back-up and restoring database.

Method1: -By simple Detach and Attach.

Let us assume that we have a Customer Database on which we have to perform the Back-up and Restore action.

In order to perform simple Detach and Attach method just right click on the database name > go to Tasks > Select Detach. A new window will pop-up from the check box of drop box just select the drop box and clicks ok.

As soon as you click on ok you find that now the Customer database is detach from the Databases.

Now, just browse to the location of "Customer.mdf" database and copy the both "Customer.mdf" and "Customer_log.ldf" and save both the file to your specified location.

Now, let's see how we can restore the database back to the Databases.

In order to restore "Customer" database right click on Databases > Select Attach a new will pop-up like below diagram from that click on Add then again a new window will open of locate database file from that select "Customer.mdf" and click ok again you will be revert back to the Attach database window from that click ok.

Now, you will see that "Customer" database is again attached to the Databases.

Method2: -By creating a .bak file of the Database.

In order to create a .bak file right click on "Customer" database > go to Tasks > Select back up.

As soon as you select back up a new window like below diagram.

Now click on ok, a message will be displayed like below diagram.

Now, just browse to the destination, you will see a backup file (.bakformat) file is created.

Now, let's see how to restore back the database.

As soon as you click on Restore Database a new window will appear like below diagram from that select from device and click [----] then again a new window will appear of specify back-up from that click on add.

As soon as you click on add then again a new window will appear like below diagram of Locate back-up file from that select the Database on which you have created the back-up and then click ok.

Now just click on ok.

Similarly, when you click on ok you will revert back to the restore database window from that select the database name and check the restore box as marked and later click on ok.