InformationNOW – SQL 2005
Database Backup and Restoration
Backing up a SQL 2005 Database
Users are advised to create an hourly or nightly database backup maintenance plan. Occasionally, it may be necessary to manually initiate a database backup. Follow the steps below to do this on a SQL 2005 server.
• Click the Windows Start button and select All Programs. Navigate to the Microsoft SQL 2005 folder and select SQL Server Management Studio.
• Choose the type of Authentication to use. Windows Authentication is the default type and will log in using the current server login information.
• Click Connect.
• A database backup will be created with the file extension of .bak. Right-click on the InformationNOW Database and select Backup Database. The screen shown below will appear:
• Most fields will be pre-populated. The default destination for the data backup file is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\. To change this location, click the Remove button to the right.
• The destination box will now be blank. Click the Add button to enter the new destination for the backup file. Users should select a location that may be readily accessed if necessary.
• In the Select Backup Destination screen, add the new location of the database backup.
• Click the ellipsis button to select the directory into which the backup file is to be saved. • The screen show below will appear.
• Select OK to saved the information and return to the previous screen.
• Click OK again to return to the Back Up Database - InformationNOW screen.
• Next, click on the Options page on the left side of the screen.
• To back up this single database, select the radio button next to Overwrite all existing backup sets.
• Other options are available and may be selected at the user’s discretion. The screen shots shown above provide an example for sending data for STI support purposes.
• Close the Management Studio. If this backup file is being sent to STI for support issues or for data conversion, please compress the file into a .zip format.
• To compress the file, locate the folder into which the data was backed up. Right-click the .bak file and select Send To. Then select Compressed (zipped) Folder.
• The compressed file (with an extension of .zip) may now be sent to STI via the Support Utility.
Restoring a SQL 2005 Database
This section provides instructions on restoring SQL Server 2005 data from a backup. Users are advised to test data backups periodically to ensure that they are carried out properly.
• After installation, click the Windows Start button and select All Programs. Navigate to the Microsoft SQL 2005 folder and right-click on SQL Server Management Studio.
• Select Send To Desktop (create shortcut). This will place a shortcut icon to SQL Server Management Studio on the desktop.
• Go to the desktop and double-click the SQL Server Management Studio shortcut. The Management Studio will open, with the connection screen displayed.
• Click Connect.
• The next step is to restore a backup of the data as a database. Right-click on Databases and select Restore Database.
Note: If you are overwriting an existing InformationNOW database, you must expand Databases, right-click the database and then select Restore.
• The Restore Database – InformationNOW screen will appear.
• In the To database field, enter the name of the new database (for example, InformationNOW, as shown above). • Select the radio button next to From device. In this example, the data will be restored from a drive on the computer.
To select the location on the computer, click the ellipsis button to the right. • The Specify Backup screen will appear. Add the location of the database backup.
• Select OK to return to the previous screen.
• Select OK again to return to the Restore Database – InformationNOW screen.
• Check the box to indicate the backup to restore.
• The Original File Name(s) and backup location(s) will be listed in the box in the middle of the screen. These may or may not match the location in which the data is to be placed at the current time.
• If necessary, select each field one at a time and restore the data to the proper directory.
• For optimal performance, restore the data to the data drive and restore the transaction logs to the drive designated for the logs.
• Users are advised to name any log file by adding _log at the end of the file name, using the file extension .ldf. Name any data file with the extension of .mdf.
• In most cases, it will not be necessary to select the additional options on this screen. However, if the database is being returned from STI Support or if the database already exists, check the Overwrite existing database option. It is recommended that users back up the existing database prior to overwriting it, in case of future need.
• Select OK to start the restoration procedure.
• Once the restoration is complete, select OK again and exit the Management Studio.
