Restore the SQL Database to an Alternate Instance on a Different Server

This section provides information about restoring a FileStream Enabled SQL Database to an alternate instance of a FileStream enabled Database in which both instances are on a different SQL server. For example, if the database is in the source server WIN-OP4O7723D3J and source instance INS1, it can be restored to the destination server WIN-GQPE5VH3FTH and destination instance INS3_FSE.

Follow these steps:

1. Log into a destination server Agent. For example: WIN-GQPE5VH3FTH.
2. 
2. On the Restore screen, to change the RPS location for restoring the servers and instances on an alternate server, click the Change button.
3. 
4. The Source screen appears.
3. To select the source RPS, click Select Recovery Point Server, select the source (WIN-OP4O7723D3J) server from the list, and then click OK.
4. 
5. The Restore page appears.
4. To restore the database to an alternate server, select the database (FSDB_1), and then click Next.
5. 
5. From the Restore Options screen, under Destination, select Restore to alternative location, and then click Next.
6. Note: If the data you attempt to restore is encrypted, provide the password as needed.
7. 
6. On the Restore - Job level Options page, do the following, and then click Next:
7. Recovery State
• RECOVERY Mode: By default, this option is enabled. It makes the SQL database online to allow data recovery and provides you permission to access the restored database.
• NORECOVERY Mode: The database transitions to a ‘RESTORING’ state to prevent users from accessing the database. To restore the last backup and bring the database online for usage, use the RECOVERY Mode option.
8. Database Consistency Check
9. To make sure that the database is consistent after a restore, select the Run database consistency check after the restore checkbox. This option checks the physical and logical integrity of objects in an SQL Server database. The Mark the restore as failed if the consistency check fails option indicates when the restore job fails if the database consistency check fails for the selected database.
10. Miscellaneous
• Force restore over existing files or database: This option overwrites the existing database files located at the restore destination. Not selecting this option for an existing database file can make the restore incomplete. You can skip this option when the database file is new.
• Restricted user access after restore: This option restricts access to the database file for a specific group of users such as sysadmin, dbcreator, and/or db_owner. These users have permissions to modify the database.
• Note: If the source database was already in the restricted mode during the backup, the restored database automatically stays in the same mode.
11. 
12. The Restore Options- Alternate Location page appears.
7. Click the Destination Instance Name drop-down list to view the size and the FileStream enabled status of the selected database (example: INS3_FSE).
8. Note: If a database is FileStream Enabled, the Destination Instance Name field lists only FileStream Enabled servers. However, if the FileStream is not enabled, the Destination Instance Name displays both FileStream enabled and disabled databases.
8. To rename the database, type the New Database Name as needed.
9. 
9. To configure or change the configuration at a database level, click the Configure button.
10. The Additional Database Options dialog appears.
10. In the Recovery Options tab, verify and make any changes to the database options, as needed.
11. In the Database Settings tab, do the following:
1. To select the Destination Location, click the Browse (…) button.
2. Note: When you change the destination location, it also updates the destination for all the database files.
2. Under File Name Settings (Optional), you can change the file names partially or fully. To replace the file name, provide the original file name in the Destination File Name Substring field and the new file name in the Replace With field.
3. Click Apply to make the changes.
4. Note: The new file names appear under the Destination File Name column.
12. 
12. Click OK to return to the Restore Options- Alternate Location page.
13. Click Next.
14. The Restore Summary page opens.
14. Review the information, and then click Finish to start the restore process.
15. 
16. After the restore finishes, view the restore status in the Activity Log. It also contains information about Database Consistency Check and Restrict user access.
17. 
18. The change in the database name reflects in the SQL Management Studio.
19.