Create Backup, Restore, Attach database in SQL Server 2008


Most of the time we need to run our web application to different system. Someone keep the feature of creating database in there application. But if we need to backup or restore the database then what we will do?

In this post I am trying to show you everything about that.

First Create a backup of database.

Step1: Right Click on the Database of which you want to create backup. From there Click Task and then Backup.

Step 1

Step 2:Now Click OK to create backup. This create a backup file int the C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backpup\DatabaseName.bak by Default.

Step 2

You can also change this directory. To change this Directory First remove current location by Clicking on the Remove Button in the Right Side. Now Click on the add button. A new window will appear as following. From there first remove current location. Now Click on the Button as marked by red color.

Step

A new window will appear as follow. Now select the place where do you want to save the backup file. Provide the file name. Here I provide DatabaseTest. N0w OK.

Step

Now Click on the Options of the left menu. From right click on the Back up to a new media set….. as following image now provide a media set name. Here I give  Database Test. Now Click Ok to finish backup process.



Now Restore from that backup database:

First I am going to delete the database of which I created backup. To delete the database Right Click on the database and click Delete.

A window will appear as follow. Here I am deleting every information of this database. If this database is open by any connection then you will not be able to delete this database. To delete then check close existing connection. Now click OK to complete delete process.

Now from the object explorer right click on the Databases then click on the Restore Database.

A new window will appear as follow. From there first select From device , then click on the button of right side marked by red color.

A new window will appear as follow. Here it has already selected a backup file . select this and click remove. then click add again.

A new window will appear as from there select the file you on  which you made backup. Click ok.
Now following window will appear. From here first select the check box of database (marked). Then To database dropdown list select the database to which you want to restore. There will be a name of your database (Here DatabaseTest). Select it. Now press OK. If everything is ok then your database will be restored and show a successful message.

Now Enjoy.

Thanks.

About these ads

About Razib Chandra Deb

I completed my BSc in CSE from CSE Department of KUET. Currently I am working as a Software Engineer in Samsung R&D Institute Bangladesh(SRBD). I am interested in image processing.
This entry was posted in Uncategorized and tagged , , , , . Bookmark the permalink.

7 Responses to Create Backup, Restore, Attach database in SQL Server 2008

  1. baghlol says:

    Excellent info here… I am brand new at SQL Server so your instructions were invaluable. I would just like to add one point. When you are bringing the backup file to a different server, you need to have either the identicle DB name or you wont be able to restore it. I had to click on the Options Tab and check the option Overwrite the existing database (WITH REPLACE). Then it was able to restore.

  2. jose says:

    hi razibdeb
    i am new using visual basic , i have install sql server 2008 on a window 7, your explanation on backing up database is very very well explain, however i want to create a backup/ restore form with codes using VB language.
    please help..

    • razibdeb says:

      if you know how to execute SQL query in VB, then try the following query
      Backup Database DatabaseName To Disk = ‘C:\Temp\DatabaseName.bak’
      I executed this on Management studio and it work good. now u try it in your VB code. Hope this will work.
      Please tell me what happens. I do not know VB, I will try to make a post on this if i got some time.
      Thanks.

      • Gayathri says:

        How to restore the database on query in asp.net.Can u give that because i m having the error while trying that,
        The tail of the log for the database “test” has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
        After i m using the with replace it works correctly.But i want to know the reason why that error comes.

  3. jose says:

    my email martinez . josew@gmail.com

  4. rohith says:

    i am gettting an eroor-> The media set has 2 media families but only 1 are provided.All members must be provided.. plz reply… how to resolve this problem.. thank you!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s