How do you restore a database on MSSQL Server?

Version 2
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    APPLIES TO:

    BMC Remedy AR System Server



    QUESTION:

    I am using MS SQL Server as a database for AR Server and I have a backup of the ARSystem database. I need instructions on how to restore it.


    ANSWER:

     

    Legacy ID:KA344322

      

    This KB is intended to be used as a guideline for one possible way to restore a database for MS SQL Server. Recognize that there are different ways of performing a restore of a database and this is normally performed by the Database Administrator. Furthermore, this is a Microsoft product and all functionality of the product is supported by Microsoft. Remedy does not support the backup or restore of the ARSystem database. The reason for this KB is that we are sometimes asked to share our expertise in this area. We have found the following to be successful. Please proceed cautiously as it is possible that different environments, backup methods, and/or goals of restoring data may be required that are not mentioned in this document.


    Scenario #1.
    The database is MS SQL Server 7.0 and you are restoring the backup of the ARSystem database on a machine where the MS SQL Server has no existing ARSystem database.

    The entire operation will can be done using MS SQL Enterprise Manager utility. Make sure that the backup file (i.e. ARSystem.bak) is accessible from this server. If possible, try to have the backup file on the machine where it is going to be restored. It is important to have a good idea of the size of the database that was backup up. The size of the backup file will give you a general idea.

    Part One - Creating a Database to restore the backup into.
    1. Launch Enterprise Manager and drill down to the Database folder.
    2. Right-click on the Database folder and choose New Database. On the General Tab, specify a Name of ARSystem. Doing so should populate the values for the File Name, Location, Initial Size (MB) and File Group as seen in the middle of the dialog window. The default location should be the MSSQL7 install directory in a sub-folder called Data. The actual default file name in this path should be ARSystem_Data.mdf. For purposes of this KB, I am assuming that the default location and file name will be adequate, as will the file properties at the bottom of the dialog window (i.e. Automatically grow file, by 10%, unrestricted, etc.).
    3. Click the number just below the Initial size column and observe that you can now type in the size that you want. The size is measured in Megabytes (MB). I recommend making the size at least the same size as the database that was backed up. If you do not know the exact size, side on making it larger to ensure there is plenty of space.
    4. Click the Transaction Log tab at the top of the New Database dialog window. You will see the same defaults for File Name, Location, Initial Size (MB) and File Group as existing for the database on the General tab. One difference is the the file name for the transaction log will be ARSystem_Log.ldf. Again, for purposes of this KB, I am assuming that the default location and file name will be adequate, as will the file properties at the bottom of the dialog window (i.e. Automatically grow file, by 10%, unrestricted, etc.).
    5. Click the number just below the Initial size column and note that you can now type in the size that you want. Just as for the database, it is measured in Megabytes. Microsoft generally recommends the transaction log be roughly half the size of the database.
    5. Choose OK at the bottom of the dialog window. It may take a few minutes depending on the size you specified (the larger the database, the longer it takes depending on hardware).

    Part Two - Restoring the database as an Overwrite of the newly created empty ARSystem database.
    1. Select the newly created ARSystem database in the Database folder.
    2. Right-click and select All Tasks. From the submenu, choose Restore Database.
    3. On the General tab of the Restore Database dialog window, the "restore database as:" value at the top of the window should default to ARSystem since you had that database selected when you right-clicked to select Restore Database.
    4. Below this, select the Restore method. We have had success using the From Device method.
    5. In the Parameters section, Note the small window for Devices. Select the Select Devices button to the right of this space.
    6. Another dialog window will appear. In the Restore From: section, click the Add button. Another dialog window will appear. Select the File Name option on the left if it is not selected by default. Click the button to the right of the space for the file name and browse to where the backup file is located. Note this could be on the same server or a shared network drive. It is recommended to have the backup file locally on the machine so that the restore function does not have to read the backup file over the network. When selected, choose OK.
    7. You will be returned to the Choose Restore Devices dialog window and will see that the path and file name of the backup file will be in the Device name space. Choose OK at the bottom of this window.
    8. You will be returned to the original Restore Database dialog window. Select the Options tab at the top of the window. Near the top, check the box next to "Force Restore over existing database" selection.
    9. Below this in the Restore Database file as: section, note the "Logical File Name" and "Move to physical file name" values. The "Move to physical file name" should be the values for the database you created, namely the path of MSSQL7/Data/ARSystem_Data.mdf (assuming you took the defaults from the steps in Part 1) and MSSQL7/Data/ARSystem_Log.ldf. Do not change the "Logical File Name". This value is read from the backup and is probably ARSystem_dat and ARSystem_Log assuming the database that was backed up was called ARSystem.
    10. You ready ready to select OK at the bottom of the window to perform the restore.

    Part 3 - Assigning the correct database ownership to the newly restored ARSystem database.
    1. Go into the Security folder and select Logins.
    2. Right-click and choose New Login.
    3. In the New Login dialog window, type in a name of ARAdmin. Under Authentication, select SQL Server Authentication (assuming you are not planning on using Windows NT Authentication with AR Server). Type in a password of AR#Admin#. Note you will be asked to confirm the password.
    4. Choose OK at the bottom of the window.
    5. Under Tools near the toolbar for Enterprise Manager, select SQL Server Query Analyzer. This will launch the Query Analyzer utility. We are going to use a stored procedure command to make the newly created ARAdmin user the DBO of the newly created and restored ARSystem database.
    6. Click in the white space and you should get a cursor in the upper left hand corner. Type in the following:
    sp_changedbowner ARAdmin, true
    7. Select the green arrow on the toolbar. You should get a message saying that the dependent aliases have been mapped to the new database owner.

    At this point, the operation should be complete. The database should be restored and the ARAdmin user on MS SQL Server should be the DBO (database owner) of the ARSystem database.

    Scenario  #2
    The database is MS SQL Server 7.0 and you are restoring the backup of the ARSystem database on a machine where the MS SQL Server has an existing ARSystem database with a ARAdmin user who owns the database.

    In this scenario, the assumption may be that you are restoring a database from backup because of a recent problem with the ARSystem database and you want to simply overwrite your existing database with that of the backup.

    To perform this operation, you should make sure the Remedy services are not running first. Essentially you will proceed from Part 2 above as if you were the one who created the Arsystem database. The fact that it is not empty will not matter assuming you are going to overwrite it with the backup.

    An important aspect of this operation is Part 3 from above. We you perform the restore, MS SQL Server will automatically make the user who performs the restore, most likely the SA user, the database owner (or DBO). The ARAdmin user will still exist as a user account on the MS SQL Server, but you will need to run the stored procedure command from MS Query Analyzer to make ARAdmin DBO of the newly restored ARSystem database again.

    Scenario  #3
    The database is MS SQL Server 7.0 and you are restoring the backup of the ARSystem database BUT there is an existing ARSystem database and you do NOT want to restore the database as an overwrite of the already present ARSystem database. Therefore you want to restore the database from backup as a different name other than ARSystem so that the MS SQL Server will essentially have two ARSystem databases, one called ARSystem and one with the name of your choice (example: ARSystem_temp) .

    One reason for this scenario could be so that the ARS server could temporarily point to the other database that is not named ARSystem. This is done by adding the Db-name: parameter to the ar.cfg file in Remedy/Conf.
    In this manner, you could have ARS pointing to a single instance of MS SQL and have two separate databases you could connect to with the only disadvantage being that you could not connect to them simultaneously. This could be considered a low budget development/test environment.

    To accomplish this task, again you would perform the same steps mentioned above starting at Part 1. The only difference would be that instead of creating a New (Empty) Database named ARSystem, you would name it something else, like ARSystem_temp. As you follow the steps in Part 2, you would just select this database instead.

    Scenario  #4
    How would the instructions for restoring a database different with MS SQL Server 2000?

    There is only one minor difference with the procedures listed above for MS SQL 7.0 and it is only in Part 1 where you are creating a New Database. The difference is that SQL 2000 has three tab on the Create New Database dialog window. The first Tab is where you specify the Name. The second tab is where you specify the values for the File Name, Location, Initial Size (MB) and File Group for the Database. The third tab is where you specify the values for the File Name, Location, Initial Size (MB) and File Group for the Transaction log.

    All other steps and procedures are the same for MS SQL Server 2000.




      
    Related Products:  
       
    1. SQL-BackTrack for Microsoft SQL Server

     


    Article Number:

    000031011


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles