cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Using SQL Server Studio to Back-up and Restore a SolarWinds Network Product Database

Level 13

Users of SolarWinds network products know that a Microsoft SQL Server database is a required component. Though all SolarWinds network products still include a utility called Database Manager its feature set is now limited to query operations.

So in this article, as a general courtesy to those who have been relying on Database Manager for backing-up and restoring SolarWinds product database, I'm going to provide steps for performing these operations using Microsoft SQL Server Management Studio and in the context of moving an existing database from one SQL Server database server to another. The procedures are relevant to database created in SQL Server 2005 and 2008.

Backup

  1. Using an administrator account, log on to the SQL Server database server where your SolarWinds product database currently resides.
  2. Click Start > All Programs > Microsoft SQL Server 200X > SQL Server Management Studio.
  3. Specify the server name of the current SolarWinds database server on the Connect to Server window.
  4. If you are using SQL Server Authentication, click SQL Server Authentication in the Authentication field, and then specify your credentials in the User name and Password fields.
  5. Click Connect.
  6. In the pane on the left, expand the name of the server hosting the SQL instance you are using for your SolarWinds product, and then expand Databases.
  7. Right-click the name of your SolarWinds database (for example, right-click "NCM_database), and then click Tasks > Back Up.
  8. In the Source area, select Full as the Backup type.
  9. In the Backup set area, provide an appropriate Name and Description for your database backup.
  10. If there is not already an appropriate backup location listed in the Destination area, click Add, and then specify and remember the destination path and file name you provide. This is the location where your backup is stored. Note: Remember, if your database is on a remote server, as recommended, this backup file is also created on the remote database server; it is not created locally.
  11. Click Options in Select a page pane on the left.
  12. In the Reliability area, check Verify backup when finished.
  13. Click OK.
  14. Copy the .bak file from your current SolarWinds database server to your new database server.

Restore

Restoring a database happens differently depending on the version (2005/2008) of SQL Server you are running.

SQL Server 2005

.

To restore your database backup file:

  1. Log on to the new database server using an administrator account.
  2. Click Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio.
  3. Click File > Connect Object Explorer.
  4. Specify the name of the new SolarWinds database server on the Connect to Server window.
  5. If you are using SQL Server Authentication, click SQL Server Authentication in the Authentication field, and then specify your credentials in the User name and Password fields.
  6. Click Connect.
  7. Click the name of your server to view an expanded list of objects associated with your server, and then right‑click Databases.
  8. Click Restore Database.
  9. Leave To database blank.
  10. Click From device, and then browse (…) to the location of your .bak file.
  11. Click Add, and then navigate to the .bak file and click OK.
  12. Click OK on the Specify Backup window.
  13. Check Restore.
  14. Select the name of your database from the To database field. It will now be populated with the correct name. For example, select "NCM_database".
  15. Click Options in the left Select a page pane.
  16. Check Overwrite the existing database.
  17. For each Original File Name listed, complete the following steps to ensure a successful restoration:
    1. Click Browse ().
    2. Select a directory that already exists.
    3. Provide a name for the Restore As file that matches the Original File Name, and then click OK.
  18. Select Leave the database ready to use by rolling uncommitted transactions…(RESTORE WITH RECOVERY).
  19. Click OK.
  20. Open and run the appropriate SolarWinds Configuration Wizard to update your SolarWinds installation.
  21. Select Database and follow the prompts. Note: Due to the nature of security identifiers (SIDs) assigned to SQL Server 2005 database accounts, SolarWinds recommends that you create and use a new account for accessing your restored Orion database on the Database Account window of the Orion Configuration Wizard.

SQL Server 2008


To restore your database backup file on a server running SQL Server 2008:

  1. Log on to the new database server using an administrator account.
  2. Click Start > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio.
  3. Click File > Connect Object Explorer.
  4. Specify the name of the new SolarWinds database server on the Connect to Server window.
  5. If you are using SQL Server Authentication, click SQL Server Authentication in the Authentication field, and then specify your credentials in the User name and Password fields.
  6. Click Connect.
  7. Click the name of your server to view an expanded list of objects associated with your server, and then right‑click Databases.
  8. Click Restore Database.
  9. Leave To database blank.
  10. Select From device, and then click Browse ().
  11. Confirm that File is selected as the Backup media.
  12. Click Add.
  13. Navigate to the .bak file, select it, and then click OK.
  14. Click OK on the Specify Backup window.
  15. In the Destination for restore area, select the name of your database from the To database field. Note: The To database is now populated with the correct name. For example, select "NCM_database".
  16. Check Restore next to the database backup you are restoring.
  17. Click Options in the left Select a page pane.
  18. Check Overwrite the existing database (WITH REPLACE).
  19. For each Original File Name listed, complete the following steps to ensure a successful restoration:
    1. Click Browse ().
    2. Select a directory that already exists.
    3. Provide a name for the Restore As file that matches the Original File Name, and then click OK.
  20. Select Leave the database ready to use by rolling uncommitted transactions…(RESTORE WITH RECOVERY), and then click OK.
  21. Open and run the appropriate SolarWinds Configuration Wizard to update your SolarWinds installation.
  22. Select Database and follow the prompts.  Note: Due to the nature of security identifiers (SIDs) assigned to SQL Server 2008 database accounts, SolarWinds recommends that you create and use a new account for accessing your restored Orion database on the Database Account window of the Orion Configuration Wizard.
5 Comments
Level 12

Doc! Doc!

It was all incredibly detailed at 8:30pm after a long day of migrating like a lost Canada goose ....

Until step 19 b. "Select a directory that already exists."

Wha?

So, should I have created a new db directory under the previous root, D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ or what?

I know this was all in the Migration Guide, but that guide seems to assume that you have a single server, with SQL on it, and are migrating to a single server with SQL on it.

So I got tricked by not seeing any devices being monitored on the Admin page, because the original server was still linked in the database, and the Config Wizard, well, let's just say, don't leave the Shire ...

Restoring the NPM DB to the existing dir produces (note that I edited out the links to Microsoft's helpful web sites because they never are helpful):

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'xxxxxx01'.  (Microsoft.SqlServer.SmoExtended)

&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: RESTORE cannot process database 'NetPerfMon' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Smo)

So I asked the GoogleBird about the error. It said:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/37ee8e24-7aaa-472b-861a-fc0cc513338a/unable-...

The answer from JRSEAP there looked inviting. In a bird sized nutshell:

Go to Security > Logins > your login user. Right click to get properties and where it says Default, put the database back to "Master."

But I found I had to also close SQL Studio, and reopen.

Now to see if the brain transplant to the goose took ... it didn't ... now running the Config Wizard again ...

=Foon=

Level 13

Hey Foonly,

Thanks for the comments. You're right, to avoid the dark influence of Mount Doom, and preclude the kind of resource contentions that you ran into SQL Server instance hopping on the same host, I assume a less complicated case in which migration goes from one SQL server host to another.

The instruction regarding a directory that already exists is a clumsily worded pointer to the directory to which you copied the backup file(s). In short, did you get successfully migrated? Was there more to do after running Config Wizard?

DPW

Level 7

Using Microsoft SQL server management studio u can easily take backup and restore the backup database by following simple manual steps. Above explained steps are the same but u can check this blog http://www.sqlrecoverysoftware.net/blog/backup-and-restore-in-sql-server.html to get more understand the procedure through diagrams.

Level 8

Using Microsoft SQL server management studio we can do easily....

Level 15

Informative post.  And great discussion.  Thanks!

About the Author
If I were a HAL 9000 series computing machine I might be in an operational state on a space vessel somewhere in our little solar system, closer to Jupiter than Earth, with some probability of lethal malfunction; and to understate the obvious, I would not be helping anyone here on thwack.com. But I do or try to help people on thwack.com watch their bits better. Therefore, I am probably not a HAL 9000 series computing machine. I alternate between feeling ambiguously clear (state='0' if you like) and clearly ambiguous (state='1' as it were). I enjoy verbing nouns.