Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

Home > Success Center > Network Performance Monitor (NPM) > Backup and restore SQL database instance using a .BAK file

Backup and restore SQL database instance using a .BAK file

Created by Milton Harris, last modified by Magdalena.Markova on Nov 02, 2017

Views: 9,701 Votes: 8 Revisions: 22

Overview

How to backup and restore SQL database instance using a .BAK file.

Environment

All NPM versions

Detail

Here is an 8 minute, 21-second video (taken from the Solarwinds Inc Youtube channel) that shows you how to backup and create a *.BAK file for backup.

When you have the BAK file moved over to your new SQL box, you restore the database from the BAK file and name it with the same database instance name you had on the old SQL server.

Before you begin

Review and consider the following requirements before you begin backing up and restoring your database: 

  • You must have DBOwner rights to be able to restore the BAK file, otherwise, if you try to import the BAK file back in, you will get an error.
  • Use a 'sa' or sa-equivalent account when attempting to back up and restore to achieve the best results.
  • Be particular about the account you use for creating the backup, even if you decide to use another file type for the backup, such as MDF or LDF files. Administrator-like accounts with all rights give you the highest chance of creating the backup file successfully.

SolarWinds Support is not responsible for supporting the BAK file itself. If Microsoft tools are used to create the BAK file, Microsoft Support is responsible for support, should the BAK file itself become corrupted.

Back up the database into a BAK file

  1. Open the SQL Studio Manager.
  2. Select the database instance to back up in the left navigation pane.
  3. Right click > Tasks > Backup.
  4. For Destination, select Disk.
    The destination file should have the extension *.BAK.

 

Restore the database from a BAK file

You can refer to the Restore a Database Backup Using SSMS Microsoft article for reference  (© 2016 Microsoft, available at https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms, obtained on November 2, 2017).

  1. Open the SQL Server Management Studio in Object Explorer, right-click on Databases Node and select Restore Database.
  2. The Restore Database dialog box will be displayed on the General page.
    1. The name of the restoring database appears in the To database list box. To create a new database, enter its name in the list box.
    2. Select ‘From device’.
    3. Click the button to display ‘Specify Backup’ Dialog.
    4. Click ‘Add’  to browse the .bak file from the directory and click OK.

     
  3. Click on "Files" tab, and ensure that the "Restore As" column do not already have the files with the same file name. Otherwise, the restore will fail. Ensure that all the files are unique.

    In the above example, the Data file is stored in D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SolarWindsOrion.mdf.

    If the file exists on the SQL server in the exact same path, rename the file or change its path.

 

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.

 

Last modified

Tags

Classifications

Public