What format does commvault restore to for SQL server backups?

Question

I have a pretty challenging task that I need to figure out.

I have 6TB of data in New Hampshire that I need to move to Texas. It is all SQL Server data. The data is backed up to tape via Commvault as well as a Netapp filer.

What I am trying to figure out is how I am going to move this amount of data. It has been stated that copying data across the WAN link is pretty much not an option.

That brings me to my question, when doing a Commvault restore from tape, will it just run through and dump a bunch of *.mdf and *.ldf's to a folder specified? Then I can just attach those in SQL?

The challenge here is what would take less amount of time:

A. Backup data to tape, ship tapes in mail to Austin, restore using Commvault, attach databases in SQL

B. Move physical database servers from Austin to New Hampshire, do SQL backups to filesystem on source SQL server, copy backups over to new SQL server, restore from filesystem

Guess it comes down to a tape vs. disk issue? 6TB of data, needs to be restored twice (onto two different SQL servers)

Thanks for your guidance!

Answer 1

I know this is an old question, but it is the first question that shows up when searching "commvault" on serverfault.

I have two years of experience with Commvault Simpana v9 and v10 as a support technician and a year as a systems engineer.

First Question

To answer your first question, yes, a typical database restore creates .mdf and .ldf files which are usually automatically mounted to the destination system.

Second question

The fastest way to restore databases, where restoring over a WAN is too slow, would be to restore the SQL database files using Performing Partial (Piecemeal) Restore of a Database (Out-Of-Place) to tape (.mdf,.ldf,.ndf files) and shipped it to the destination to be mounted to the SQL server.

If available, an alternative would be to stand up the database in a virtual recovery environment in New Hampshire where the backup data is being stored and route Austin traffic appropriately to the recovered SQL system. This would be much faster to achieve you Recovery Time Objective, however only feasible if you can route traffic appropriately.

---

There are a two ways that CommVault Simpana v9,v10, and v11 restore Microsoft SQL databases, Database Level Restore and Database File/FileGroup Level Restore. Each has variations depending on the desired outcome. See below for details:

v10 Microsoft SQL Database Restore Documentation

v11 Microsoft SQL Database Restore Documentation

Here are some directions that apply to v9, v10, and v11.

Default Database Restore (In-Place)

By default, a database is restored in the same location from where it was backed up using the CommVault Simpana SQL backup agent and the existing database files are overwritten. This restore leaves the database in an online state.

1. From the CommCell Browser, navigate to Client Computers | Client | SQL Server.
2. Right-click the Instance and then click All Tasks | Browse and Restore.
3. Click View Content.
4. In the right pane of the Browse window, select a non-system database you want to restore and click Recover All Selected.
5. Select Unconditionally overwrite existing database or files checkbox.
6. Click OK to start the restore.

This will start an immediate restore of the database back to the system from which it was backed up from and the data will be overwritten.

Database Restore (Out-Of-Place)

A database can be restored to another system that also has the SQL Database Backup Agent installed. This restore mounts the database in an online state on the destination system once the data has been restored.

1. From the CommCell Browser, navigate to Client Computers | Client | SQL Server.
2. Right-click the Instance and then click All Tasks | Browse and Restore.
3. Click View Content.
4. In the right pane of the Browse window, select a non-system database you want to restore and click Recover All Selected.
5. Select the Destination Server from the drop down.
6. Rename the database under the Database column and change the path of the database and log files under the Physical Path column.
7. Select Unconditionally overwrite existing database or files checkbox.
8. Click OK to start the restore.

This will start an immediate restore of the database(s) to the destination system selected and mount the database in an online state.

Restore Database using File or FileGroup Level (Out-Of-Place)

You can restore a database in its entirety by restoring all the files/filegroups that make up the database. This option does not allow for multiple database selection, however this is to your advantage as File Level restores of Databases are usually limited to a single stream per job.

Using multiple Database File Restore jobs you can increase your overall restore throughput and reduce the time needed to restore the entire dataset.

Note: If you're restoring a single large database to files it is preferable to break out the individual database files as separate jobs using Performing Partial (Piecemeal) Restore of a Database (Out-Of-Place)

1. From the CommCell Browser, navigate to Client Computers | Client | SQL Server.
2. Right-click the Instance and then click All Tasks | Browse and Restore.
3. In the Restore Options window, click the Advanced Options tab.
4. Select File/File Group and then click View Content.
5. In the right pane of the Browse window, select the database you want to restore.
6. Click Recover All Selected.
7. Select the Destination Server from the drop down.
8. Rename the database under the Database column and change the path of the database and log files under the Physical Path column.
9. Select Unconditionally overwrite existing database or files checkbox.
10. Click OK to start the restore

This will start an immediate restore of the database files to the destination system selected and mount the database in an online state.

Performing Partial (Piecemeal) Restore of a Database (Out-Of-Place)

If the size of a filegroup in a database is large, the restore operation may take considerable time. In such case, you can restore the database in stages.

Partial restores also known as Piecemeal Restore in SQL Server versions 2005 and later allows you to restore a database in stages.

Follow the steps given below to restore a database in stages at filegroup level:

1. From the CommCell Browser, navigate to Client Computers | Client | SQL Server.
2. Right-click the Instance and then click All Tasks | Browse and Restore.
3. In the Restore Options window, click the Advanced Options tab.
4. Select File/File Group and then click View Content.
5. In the left pane of the Browse window, navigate to the database that contains the filegroups you want to restore.
6. Select the filegroups you want to restore in the right pane and click Recover All Selected.
7. Select the Destination Server from the drop down.
8. Rename the database under the Database column and change the path of the database and log files under the Physical Path column.
9. Click Advanced.
10. In the Advanced Restore Options window, click the Options tab.
11. Select the Partial Restore check box.
12. Click OK to start the restore.

Perform the partial restore of all remaining filegroups one by one to restore the entire database. IMPORTANT NOTE: This cannot be done in parallel and must be done sequentially!