How to Restore a SQL Database From Backup

Owned by Knowledge Assistant
Last updated: Aug 23, 2024 by Moriah Yorkey
2 min read
Description of Issue

Restore a SQL Database from a backup

Context

  • SQL Server Management Studio

  • Restore Database

  • Database Backup

  • On-Prem

  • SaaS

  • Manual EERP Data Refresh

Cause

Database needs to be restored from database backup

Resolution

SaaS: Please contact TSM Support for assistance

On-Prem:

Back Up Database(s)

  1. Log into the source environment's database server with tylerservice or an administrative account

  2. Open Microsoft SQL Server Management Studio (SSMS) and connect to the server name for the instance where the source database resides

  3. Expand Databases

  4. Right click the database name and select Tasks > Back Up...

  5. Back up to Disk

    1. If there is a location already populated, remove it and click Add... to choose the desired file name and location

    2. Note: You need to add the .bak file extension to the end of the file name (Ex: D:\TylerTempBackups\munprod_01012023.bak)

  6. Click the Media Options tab

  7. Select Overwrite all existing backup sets

  8. Click OK when you are ready to start the backup and let the backup run until completion

  9. If the destination database is hosted on a different server, place a copy of the backup file on the destination database server

  10. Back up destination database (optional)

Restore Destination Database

  1. Connect to the SQL instance where the destination database resides

  2. Expand Databases

  3. Right click the destination database and select > Tasks > Restore > Database...

  4. Click the Options tab

    1. Under Restore Options, check off Overwrite the existing database (WITH REPLACE)

    2. Under Server connections, check off Close existing connections to destination database

      1. Note: If you select your source before going to the Options tab, Close existing connections to the destination database may not be available to select.

  5. Click the General tab

    1. Under Source, select Database or Device radio option

      1. Device (with the backup path populated) will be the typical selection

    2. Database - Select the database to restore from the drop-down list. The list contains only databases that have been backed up according to the MSDB backup history.

    3. Device - Select a specific backup file to backup from. This is the typical selection.

      1. Click the ellipses

      2. Click Add

      3. Locate backup file and click OK

      4. Click OK

    4. Under Destination, select the database to be restored

  6. Click the Files tab

    1. Expand the Restore As column and verify that this row matches the location of your target MDF and LDF

    2. Click OK when you are ready to start the restore. Let run until completed. 

  7. If the source environment is different than destination environment, 

  8. Once the restore completes, right click destination database name and select New Query

    1. Run the query below to change the database owner. Update database name and owner as needed.

      1. USE muntest
        EXEC sp_changedbowner 'muntest'

  9. Right click the newly restored database and click Properties

    1. Click the Files tab

    2. Verify Owner is set correctly (Ex: cashtest)

    3. Under Database files, update Logical Name if needed (Ex: muntest and muntest_log)

    4. Click OK

  10. Redeploy product environment from Tyler Deploy with Force Redeploy checked off

Additional Information

Â