Setting up an SQL point in time backup
a. The database is in full recovery mode
b. SQL log backups are taken periodically using SQL's native scheduler (not available in Express editions of SQL), alternatively using 'sqlcmd' in .bat scripts triggered with Windows Task Scheduler
c. A backup client has been installed and connected with a Device Name and Password
1a. Full Recovery Mode
1b. Log Backups
i. Create an SQL Server Agent Task to backup the logs periodically
ii. The above example is scheduled to backup the logs multiple times a day and append the logs to the end of a .TRN file output the log backup as a .TRN file
2. Backup Client Selection Configuration
a. Stop the Redstor Online Backup Plus Service Controller through Windows Services
b. Navigate to the config.ini file and open it with a text editor (C:/Program Files/Redstor Online Backup Plus/config.ini)
c. Add the below line to the config.ini file and Save it:
d. Restart the Redstor Online Backup Plus Service Controller through Windows Services
e. Using the backup client's VSS MS SQL Plugin, select the database that you wish to perform a point in time recovery for
f. Using the backup client's Files and Folder plugin, configure a backup of the .TRN log file output folder
We now have a backup of the full database (using the SQL plugin) and a backup of the logs (using SQL's built in tasks and then the Files and Folder plugin), so we have everything selected for a point in time recovery. Next we will configure the schedules and scripts.
3. Backup Client Script Configuration
As part of this process, we need to clear out the .TRN log folder directory at the beginning of each day because the .TRN log file is being appended to as the day progresses. To do this, we will use the Script integration as part of the Schedule.
a. In the Backup Client, navigate to Preferences->Scripts
b. Press Add Script and create a script called "Delete transaction logs"
c. Set the Username to be a local administrator, specifying the password underneath
d. Insert the following for the script:
DEL /F C:\SQL_Logs\*.TRN
4. Backup Client Schedule Configuration
a. Navigate to Preferences->Schedule and schedule the MS SQL backup to run in the morning with the name "FULL"
b. Choose "Delete transaction logs" as the "Pre-backup script"
c. Allow this to run at a time in the morning (before we do the log backups) and choose everyday
d. In the "Backup:" section, ensure that only "MS SQL" is present. Remove all other plugins.
e. Press Save and then press Add Schedule to create a second schedule. Call this one "LOG1".
f. Leave the script sections blank
g. Allow this to run at a time after the full SQL backup should finish, everyday
h. In the "Backup:" section, ensure that only "Files and Folders" is present. Remove all other plugins.
i. Save this and then Add schedule again to create "LOGS2"
j. Repeat the process above (e to i) to create periodic schedules to run throughout the day. The more you add, the more granular points in time you will get.
The Point in Time recovery setup is now complete.