SQL Server - how to back up and restore sql server data

Asked By aman on 01-Aug-11 01:57 AM
hi all,

i have a database called as student and i want to create a backup of that student database and also want to restore it...how i can achieve this in sql server 2008.

thanks and regards
Aman Khan
Web Star replied to aman on 01-Aug-11 02:00 AM
there are a wizard for creating backup and restore database in sql server , also you can create T-Sql stored proc for that
see how can take backup of database and restore it when need
http://msdn.microsoft.com/en-us/library/ms177429.aspx
http://technet.microsoft.com/en-us/library/ms187048.aspx
Jitendra Faye replied to aman on 01-Aug-11 02:02 AM

You may backup and restore the SQL Server 2000 database with “SQL Distributed Management Objects (SQL-DMO)”.
SQL-DMO is a collection of COM objects that are designed for programming all aspects of managing Microsoft SQL Server.


Please refer to the following suggestions:


Since you are using the windows authentication, we need to add the login “YourMachineName\ASPNET” in the SQL Server.

In SQL Server Enterprise Manager,
expand Microsoft SQL Servers->SQL Server Group->YourSQLServerInstance->Security, right-click Logins and choose New Login…, add the login “YourMachineName\ASPNET” and assign the appropriate “Server Roles” and “Database Access” to it.

Right-click the References in the Visual Studio, and choose Add Reference…, find the “Microsoft SQLDMO Object Library” in the COM tab and select it, press OK.


private void btnBackup_Click(object sender, System.EventArgs e)
{
  if (saveFileDialog1.ShowDialog() == DialogResult.OK)  
  {
    SQLDMO.Backup backup = new SQLDMO.BackupClass();
    SQLDMO.SQLServer server = new SQLDMO.SQLServerClass(); 
    server.LoginSecure = true;   
   server.Connect(".","","");   
   backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
   backup.Database = "sql2000test"; 
   backup.Files = saveFileDialog1.FileName;  
   backup.Initialize = true; 
    backup.SQLBackup(server);
   server.DisConnect();  
   }  
   }

Try this code and let me know.

Riley K replied to aman on 01-Aug-11 02:02 AM
Here is a good description from MSDN

To back up a database


http://

To Restore

http://

Reena Jain replied to aman on 01-Aug-11 02:04 AM
Hi,

Restoring database backup through query:

----Make Database to single user Mode
ALTER DATABASE
SVCBK.bak
SET SINGLE_USER
WITH
ROLLBACK
IMMEDIATE

----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:\SVCBK.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\SVCBK.mdf',
MOVE 'YourLDFLogicalName' TO 'D:\SVCBK.LOG'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO

hope this will help you

Jitendra Faye replied to aman on 01-Aug-11 02:06 AM

Backing up or restoring your MS SQL Server database is a relatively simple process. However; in order to either back up or restore your database you will first need a few programs installed on your computer.

You will need to get the Ms SQL Server Utilities which include Enterprise Manager, Client Network Utilities, Query Analyzer and, of course, the Import/Export utility which you will use to transfer your data back and forth via DTS.
All of these are available when you install Microsoft MSDE.

+++++++++++++++++++++++++++++++++++++++++++++
Backing Up Your SQL Server Database
+++++++++++++++++++++++++++++++++++++++++++++

First we will explore the easiest way to do this which is by creating a brand new local database and then copying all of the objects from your existing remote database to the new local one.

1. Create a new blank database in Enterprise Manager.
2. Open your Import/Export Utility program which is the Data Transformation Services Import/Export Wizard.
3. Click Next and Select "Microsoft OLE DB Provider" for SQL Server in the Data Source drop down menu.
4. In the Server drop down menu select your remote server. If there are no servers listed - you will need to establish a connection to your databases.
=========
NOTE:: Please search the forum for How to Connect to an MS SQL Server Database using Enterprise Manager.
=========
5. Click the Radio Button that says "Use SQL Server Authentication" then enter your remote SQL Server Username and Password, select your SQL Server Database Name from the drop down menu and click next.
6. Now we enter our local database information just like we did the remote database information in steps 3-5 above. Click Next.
7. On this page we want to select the 3rd option Copy objects and data between SQL Server databases and click next.
8. On the Select Object to Copy page we will keep all of the defaults and simply click next to get to the next page.
9. Select the check box Run immediately and click next and then click finish.

If you followed the steps above correctly you will see the message "Successfully copied objects from Microsoft SQL Server to Microsoft SQL Server" and you will now have a copy of your remote SQL Server database on your local machine including all of your users and their permissions for each object.

=========
Here is the problem with the above routine.
First we don't want to keep creating new databases each time we back up our remote database. What we would rather do is create one similar local database and then keep updating that database when new information needs to be backed up from our remote or visa versa. This is where DTS, or the Data Transformation Services comes into play.
=========

Now let's assume we want to back up only one table from the database we just created above. This is slightly different from what we did above.

1. Start out by following the procedures 1-6 above until we get to the "Select Table Copy or Query" page.
2. Now on this page instead of selecting the 3rd radio button we are going to select the 1st one: "Copy Table(s) view(s) from the source database and click next.
3. This bring up a grid with three columns being Source, Destination and Transform. We will select only on of the table for this example to demonstrate but when you make a backup of your database you will want to back up all the tables and follow the same steps outlined here.
4. Select a table from the destination column, preferably one that has a unique ID field. You will see information appear in the destination column when you do.
5. Use the drop down menu from the destination column and select the table you wish to copy this information to. Usually this is the same table. Watch out for owner names because sometimes DTS wants to give the destination table a new owner ame rather than the one that it there such as DBO.
6. Once you have your destination table selected properly, go to the transform column and click on the grey box with the 3 dots...
7. On the new window that pops up there are 2 columns of radio buttons. In the left column we want to select either "Delete Rows in destination table" or "Append rows in destination table". Select Delete Rows so our tables will match. If you select Append by accident you will get an error when you try to run the project.
8. On the right column of radio buttons, and if we selected a table with a unique id field, we want to select "Enable identity insert". This will allow DTS to insert identity columns into our existing table without causing an error.
9. Click OK to close the popup window and then click next.
10. Select Run immediately, click next and then Finish.

If you followed the steps above you should see your DTS Package run successfully and get the message "Successfully copied X Table(s) from Microsoft SQL Server to Microsoft SQL Server".

There you have it.
Restore Your SQL Server Database

If you want to restore your remote database simply reverse your source and destination SQL Servers in the steps above.

Ravi S replied to aman on 01-Aug-11 02:10 AM
HI

Backup a database.

Now I will backup AdventureWorks database on BKKSQL2005 which runs Microsoft SQL Server 2005 to a file.

  1. Connect to source server. Open Microsoft SQL Server Management Studio and connect to BKKSQL2005.
  2. Right-click on the AdventureWorks database. Select Tasks -> Backup…
    Backup a SQL Server database
  3. On Back Up Database window, you can configure about backup information. If you’re not familiar these configurations, you can leave default values. Here are some short descriptions.
    1. Database – a database that you want to backup.
    2. Backup type – you can select 2 options: Full and Differential. If this is the first time you backup the database, you must select Full.
    3. Name – Name of this backup, you can name anything as you want.
    4. Destination – the file that will be backup to. You can leave as default. Default will backup to “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
    5. Click OK to proceed backup.

    Select source and destination to backup

  4. Wait for a while and you’ll see a pop-up message when backup is finished.
    Backup success pop-up message
  5. Browse to the destination, you’ll see a backup file (.bak format) which you can copy to other server for restore in the next step. Default backup directory is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
    The backup file from SQL Server 2005 Server

Restore the database.

Next, I will restore the AdventureWorks database from a file that I’ve created above to BK01BIZ001 which runs Microsoft SQL Server Express Edition.

  1. Copy the backup file from source server to destination server. I’ve copied into the same directory as source server.
    The backup file
  2. Connect to destination server. Open Microsoft SQL Server Management Studio Express and connect to BK01BIZ001.
  3. Right-click on Databases. Select Restore Database…
    Restore the database
  4. Restore Database window appears. On Source for restore, select From device and click [...] buttton to browse file.Select source device
  5. On Specify Backup, ensure that Backup media is “File” and click Add.
    Select the backup media to restore
  6. On Locate Backup File, select the backup file. This is the backup file that was created in Backup a database section and was copied to this server. Click OK. OK.
    Select the backup file to restore
  7. Back to Restore Database window.
    1. On Destination for restore, select “AdventureWorks”.
      Note: If you haven’t added the backup file on Source before (step 4-6), you won’t see the database name on Destination.
    2. On Source for restore, check the box in front of the backup name (in Restore column).
    3. Click OK.

    Select a destination database to restore

  8. Wait until restore finish and there’ll be a pop-up message notify.
    Restore success pop-up message
  9. Now you’ll see the restored database on the destination SQL Server.
    The restored database on SQL Server 2005
Mark Willium replied to aman on 11-Aug-11 07:20 AM
Hello Aman,

Follow this link: http://www.eukhost.com/web-hosting/kb/backup-and-restore-ms-sql-server-2008-database/. It has full details about sql server 2008 backup and restore with descriptive images.