How to backup and restore MS SQL database

  • KM169526
  • 18-Oct-2001
  • 28-Apr-2014

Summary

This article provides information about what are the recommended procedures to backup SQL databases.

Question

How to backup and restore MS SQL database

Answer

Backing up and restoring MS SQL database

Backing Up Microsoft SQL Project Databases

Before you migrate a Microsoft SQL project database from the sourcedatabase server to the target database server, you must back up the projectdatabase.

To back up a Microsoft SQL project database:

1. To open SQL Server Enterprise Manager, choose Start -> Programs -> MicrosoftSQL Server.

2. Connect to the Microsoft SQL Server database, as follows:

  • Expand Microsoft SQL Servers.
  • Expand SQL Server Group.
  • Locate the server that contains the project database that you want toback up.

    3. Back up the project database to a specified location, as follows:

  • Expand the Databases list and select the project database.
  • Right-click the project database and choose All Tasks -> Backup Database.
  • Click Add. The Select Backup Destination dialog box opens.
  • Select File name and enter the full path of the target backup file. ClickOK.
  • In the SQL Server Backup window, click OK.

    Backing Up MSDE Project Databases:

    Before you migrate a MSDE project database from the source database server to the target database server, you must back up the projectdatabase.

    1. Bring up Command Prompt.
    2. Run the following command:
    OSQL -Usa -PmyPassword -n -Q "BACKUP DATABASE domain_project_db TO DISK = 'C:msdedomain_project_db.bak'"
    where:
    -U user
    -P password
    -n Removes numbering and the prompt symbol (>) from input lines.
    -Q Run query
    Notes:

  • MSDE is installed with Quality Center Starter Edition. Please contact HP Software Services to obtain the "sa" user password.
  • Query Analyzer is not included with the Microsoft Desktop Engine. When administrating MSDE, the OSQL utility is the only tool included.
  • Help documentation can be downloaded from the link below:
    http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/sqlbol.exe.

    Restoring Microsoft SQL Project Databases

    When migrating a Microsoft SQL project database, you restore the projectdatabase to the target database server.

    To restore a Microsoft SQL project database:

    1. To open the SQL Server Enterprise Manager, choose Start -> Programs ->Microsoft SQL Server.

    2. Connect to the Microsoft SQL Server project database, as follows:

  • Expand Microsoft SQL Servers.
  • Expand SQL Server Group.
  • Locate the server that contains the project database that you want torestore.

    3. Create a new project database for restoring your backed up project database:

  • Select the Databases folder.
  • From the right-click menu, select New Database.
  • In the General tab, type the name of the new project database in theName text box.
  • In the Data Files tab, set the size according to the size of the originalproject database.
  • Click OK.

    4. Restore the project database from the backup file, as follows:

  • Select the Databases folder.
  • From the right-click menu, choose All Tasks -> Restore Database.
  • From the Restore as database list, select the name of the project databasethat you created in the previous step.
  • Select From device and click the Select Devices button.
  • Click Add.
  • In the File Name box, specify the full path of the backup file (with the.BAK extension) and click OK.
  • Click OK.
  • In the Restore Database dialog box, click Options.
  • Select the Force restore over existing database check box.
  • Make sure that you have the appropriate path in the Move to physical filename column and click OK.

    Restoring MSDE Project Databases:
    1. Bring up Command Prompt.
    3. Login to OSQL:
    Example:
    OSQL -U sa -PmyPassword
    2. Run the following command:
    > USE master
    > GO
    > RESTORE DATABASE domain_project_db
       FROM DISK= 'C:msdedomain_project_db.bak'
    > GO

    Modifying "td" User Ownership in Microsoft SQL Server

    Modify the "td" user of the TestDirector database and make it identical tothe "td" user of the Microsoft SQL Server database.

    To modify "td" user ownership in Microsoft SQL Server:

    1. Choose Start -> Programs -> Microsoft SQL Server -> Query Analyzer.

    2. Login as sa and connect to your server.

    3. Select the TestDirector project and run the following commands:

  • EXEC sp_change_users_login 'Report'
    This unlinks the "td" user.
  • EXEC sp_change_users_login 'Update_One', 'td', 'td'
    This links the "td" user of the TestDirector database to the same ID as the"td" user of the Microsoft SQL Server database.

    Modifying "td" User Ownership in MSDE Server:
    Modify the "td" user of the Quality Center database and make it identical to the "td" user of the MSDE database.
    1. Bring up Command Prompt.
    2. Login to osql:
    OSQL -U sa -PmyPassword
    3. Run the following commands:
    Example:
    > USE domain_project_db
    > GO

    -- This select the database
    > EXEC sp_change_users_login 'Report'
    > GO
    -- This unlinks the "td" user.
    > EXEC sp_change_users_login 'Update_One', 'td', 'td'
    > GO
    -- This links the "td" user of the QC database to the same ID as the "td" user of the MSDE database.
    Note:
    For more information, please refer to
    Document ID 10388 - Error: "...General SQL error...Server user 'td' is not a valid user in database".