This section explains how to perform an SQL point-in-time restore using an SQL Server Protection backup. This restore option allows you to restore databases to a local or remote SQL Server. If the backup job used a transactional schedule, the databases can be restored to a specific point in time.
SQL point-in-time restore requires the SQL Continuous add-on license.
Show : Database restore considerations
SQL Server protection backups can be used to restore selected user databases or the Master database.
User database restores
This involves restoring databases that are used to manage your information. These database restores can be for local or remote SQL servers. To perform a user database restore, you tick Selected databases in the Select Type step
Master database restores
This involves restoring the master database, which restores all the system databases used by SQL. The master database is composed of important system tables and catalogs that are required to rebuild your SQL Server.
If you are restoring after a disaster or performing a new installation or a migration, you should restore the master database before restoring any other databases. To perform a database restore, you tick Master database recovery in the Select type step.
Only a backup that includes all of the system databases can be used to perform a Master database restore. You must select these system databases from the database list when your backup is made.
A Master database restore can only be performed on the local SQL Server.
Remote database restores
If you need to restore the master database to a remote SQL Server, you should install BackupAssist on the remote server. The installation does not need to be licensed if you run SQLRestore.exe from the BackupAssist installation directory.
Database selections
The databases you wish to restore must already exist on the SQL Server. If they do not, you can choose to either restore the master database first, which will result in recreating all databases on the server, or to manually recreate each database you wish to restore. If you are restoring your SQL Server after a disaster or new installation, it is advised that you restore the master database before restoring any other databases.
Show : Database types
SQL Databases
When you create a backup job, you will be asked to select the SQL databases that you want to back up. This initial step is the same for both Basic and Transactional backup jobs. This section explains the types of databases in SQL, and any requirements or limitations that apply to the backup and restoration of each database type.
User databases
User databases contain the information that you have used SQL to store and manage. This is the business data that is backed up to protect your company’s information. This information should be backed up on a regular basis so that it can be recovered in the event of data loss or corruption.
System databases
SQL system databases are the databases used by the SQL Server itself. These need to be backed up in addition to the user databases, so that the entire SQL Server can be rebuilt in the event of system or database failure. The system databases vary depending on the version of SQL you are running, but generally include the following:
| Master | Records all the system-level information for an SQL Server, and is composed of system tables and catalogs, and is critical to your SQL Server. If you are restoring your SQL Server after a disaster or new installation, it is advised that you restore the master database before restoring any other database. The BackupAssist SQL Restore tool can be used to restore all other databases remotely but not the master database. If you need to restore the master database to a remote SQL Server, you must install BackupAssist on the server running SQL. You can only perform full backups of the master database. Transactional log, differential or file group backups of the master database are not allowed. |
| Msdb | Used by the SQL Server agent for scheduling alerts and jobs. Used to store data, including scheduled job information, backup and restore history information and DTS packages (data transmission services). Usually the msdb database is rather small, therefore full database backups provide a fast alternative to transaction log backups of the database. |
| Model | The model database is a template used by Microsoft SQL Server when creating other databases, such as tempdb or user databases. When a new database is created, the entire contents of the model database is copied to the new database. If the master or msdb databases are rebuilt, the model database is also rebuilt and any changes are lost. |
| Resource | A read-only database containing system objects included with SQL Server 2005. Makes upgrading to a new version of SQL Server an easier and faster. Because the resource database file contains all system objects, an upgrade can be accomplished by copying the single resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the resource database with the older version. |
| Tempdb | A workspace for holding temporary objects or intermediate results sets. It is important to have regular backups of these system databases, however, it is not necessary to back up the tempdb system database because it is rebuilt each time SQL Server is started. When SQL Server is shut down, any data in tempdb is deleted permanently. For this reason, do not store any application specific data in the tempdb database. Leave it exclusively for the SQL Server to use. |
Show : Data container considerations
System Protection backups can use Data containers as backup destinations. BackupAssist automatically mounts Data containers when restores are performed. However, there may be times when you want to do this manually. For example, if you want to check what is inside a Data container or have it available for another task.
To manually mount a Data container:
- Open Disk Management.
- Click the Start button.
- Type diskmgmt.msc into the Search Programs and Files text box.
- Click OK.
- Open Run from the Apps screen.
- Type diskmgmt.msc into the Search Programs and Files text box.
- Click OK.
- Select Action from the Disk Management menu.
- Select the menu option Attach VHD.
- Enter the path of the Data container that you want to mount and click OK.
- Right click BA_VHD_Container, select Change Drive Letter and Paths, and select Add.
- Select a drive letter for the Data container, using the drop down box, and select OK.
For Windows Server 2008 R2, SBS 2011 and Windows 7
For Windows Server 2012 and Windows 8 and later
The Disk Management console will open.
You will now have a drive named BA_VHD_Container in your list of available drives.
You can now browse the Data container using Windows Explorer.
To learn more see, Data Container guide
To perform an SQL point-in-time restore, start BackupAssist and follow these steps:
- Select the Restore tab
- Select SQL point-in-time
- Select Server
- Server authentication
- Use integrated authentication: Backup user identity.
- Specify user identity: Another user account (such as a Windows account) or an SQL Server user account.
- Select Type
- Select backup file
- Master restore
- Find backups
- If a location contains SQL databases, a confirmation tick and message will appear below the list.
- If backups were created in the default location, the confirmation tick will appear.
- If backups were created in another location, you can browse to that location or type in the path and press Add. This location will then appear in the main box as confirmed.
- Restore setup
- Select the time that you want to restore from.
- The Restore time field is used to provide a restore time for all the databases. When you click Search, the backups closest to the selected time will appear next to each database, and show the time that the backup was made.
- The Set restore time button is used to select a time that you want to restore from for each database separately.
- Select a date : dates that have backups will be indicated in bold.
- Select a time range: shows a list of time ranges available for that date. You can restore the database to any point in time between any of the time ranges listed. Select a time range from the list.
- Select a time from within that range: enter using the field provided.
- Select the databases that you want to restore using the tick-box next to each database, or the Select all option.
- If the restore time specified is not available, a warning icon will appear by the database, and the closest available time to that specified will be indicated.
- If the restore time selected is available, a success icon will appear to the right of the database.
- Click Next.
- Perform the restore
- Perform full backup
The Restore tab has a Home page and a Tools page. The Home page is the default page and the recommended starting point for performing a restore. The Tools page should only be used by experienced users or with the assistance of technical support.
This will open the SQL Restore tool and start the guided restore process. There is a Learn more about Restore link at the bottom of the screen.
Show more
This selection is one of multiple restore options available.
This step locates the SQL Server that you will restore to. You can select either a local or a remote SQL Server. Select the server to restore to and click Next.
Show more
You can restore to a local or remote SQL Server
Restore a local Server
The console will display a list of the SQL servers on your local machine. If an SQL Server is present but not running, you can click Start. Any servers that cannot be started may indicate a fault with the server itself. You can only restore to SQL servers that are running.
Restore a remote server
Any remote SQL servers detected will appear in the drop down list. If the server is not shown, select the Restore a remote server button, and type in the name of the SQL Server or its IP address into the drop down field.
When you select Next in the previous step, you will be prompted to provide credentials with access to the selected SQL Server. Enter the appropriate log on credentials.
Show more
There are two types of credentials:
The authentication account, the BA account (if different to the account used for this backup) and the SQL Server’s service account, all need access to the backup destination.
Note: Some SQL servers are configured to accept only SQL authentication.
This step is used to identify the type of restore you want to perform. The restore of a selected user database or a master database restore, to recover the SQL Server. Make your selection and click Next.
Show more
Master database recovery
Only select this if your SQL database is damage, missing or needs to be replaced / recreated. This option will restore the system databases that the SQL Server uses to operate. This is not for user or business databases; it is only for SQL recovery scenarios.
To restore the master database you need to have BackupAssist installed on the server itself.
Note: It is advised that you restore the master database before any other databases.
Selected databases
Select this option to restore your user / business databases to any point in time for which you have backups.
If you selected Master database recovery, you will be asked to select a master database to restore from the list of backups with master databases .
If you selected Master database recovery, you will be asked to start the master database restore and, when it has finished, continue with the other restore steps.
This step is used to find or confirm the location of your SQL database backup files. Add or confirm the location of your SQL Server Protection backups, and click Next.
Show more
You can locate multiple backups in multiple locations and add them to this list. All of these backups will be used to locate the database backup closest to the time you want to restore from.
You will be presented with a list of databases that can be restored using the backups identified in the previous step. Select what databases to restore and what time to restore them from.
Show more
Databases can be restored to a specific point in time if the Transactional scheme was selected (during the Schedule step) when the backup job was created.
To configure the restore setup:
Review the restore job settings and then select Start restore to the right of the window to restore all of the databases selected. Click Next once the restore process has completed.
Show more
The left pane will show the restore process steps and the right pane will show detailed information on the restore job's progress.
After restoring an SQL database, future transaction log backups cannot be used to restore a database to a specific point in time until a full backup runs. It is therefore recommended that you now run a full backup of the databases that were restored.
Show more
Click Start full backup.
Full backups of these databases will be stored in the same folder as the backup files you just restored from.
If your backup schedule already includes a full backup as the next SQL backup and you are certain you will not need to restore these databases to a specific point in time between the restoration and the next full backup, you can check Skip full backup and click Finish.
Your SQL Server Protection restore has now been completed.