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. |
To perform an SQL point-in-time restore, start BackupAssist and follow these steps:
- Select the File/Apps tab
- Select SQL Point-in-Time
- Select a server
- Server authentication
- Use integrated authentication will use the Backup user identity.
- Specify user identity will prompt you to enter the credentials for another user 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 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 Files\Apps tab has a Home page and a Tools page. The Home page is the default page and the starting point for performing a restore. The Tools page should only be used by experienced users or with the assistance of BackupAssist 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.
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
The Restore a local Server section lists the SQL servers on your local machine. If an SQL Server is present but not running, you can click Start. You can only restore to SQL servers that are running.
If you choose Restore a remote server, any remote SQL servers detected will appear in the drop-down list for selection. If a server is not shown, type its name or IP address into the drop-down field and click Refresh.
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 credentials.
Show more
There are two types of credentials:
The authentication account and the SQL Server’s service account will both need access to the backup used for the restore.
Note: Some SQL servers are configured to accept only SQL authentication.
This step determines the type of database that will be restored: a user database or a master database. Make your selection and click Next.
Show more
Master database recovery
Only select this if your SQL database is damaged, 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 add or confirm the location of your SQL database backups. If a backup is not listed, use the Browse field to type in the path of the backup or browse to it, then click Add. Confirm that the backups you need are listed and click Next.
Show more
You can locate and add multiple backups. All of these backups will be used to provide restore points.
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 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.