Microsoft SQL Server
Following sections will show how to configure your MSSQL Server system to use cxLink Backup functionalities.
It is possible to use CxLink Backup for a MSSQL Server standalone database.
Topics
Configure backint-related parameters
CxLink Backup for MSSQL Server needs one additional setting to be configured in order to have the solution working: Master database authentication. You can set this using username and password (password is encrypted in that case) or using Windows authentication with your administrator user. You can do this using interactive menu just by executing emory.exe.
Under Configuration for MSSQL:
_20? CxLink Backup: [Use arrows to move, enter to select, type to filter]_20 --------------------------------------------------------------------------------_20 Storage Providers profiles_20 Set storage providers for backup/restore_20> Configuration for MSSQL_20 Backups Lifecycle settings_20 Monitoring_20 --------------------------------------------------------------------------------_20 License_20 --------------------------------------------------------------------------------_20 Logging file settings_20 Debug traces_20 Show relevant information of this CxLink Backup_20 --------------------------------------------------------------------------------_20 Catalog & Backups management_20 --------------------------------------------------------------------------------_20 Generate troubleshooting package to be sent to Syntax Support_20 Migrate resources to last models_20 --------------------------------------------------------------------------------_20 Exit
Configuration submenu:
_10? CxLink Backup: Configuration for MSSQL_10? Select action to perfom: [Use arrows to move, enter to select, type to filter]_10> Master database username [sa]_10 Master database password [ ]_10 Windows Authentication [true]_10 Back
Take into account that Windows Authentication setting prevales over username and password; if Windows Authentication is true CxLink Backup will never use username and password.
You can also configure MSSQL Server settings using command line by executing emory.exe with parameters:
_10 datasource windows-authentication <windows-authentication>_10 Use Windows Authentication (MSSQL Server)_10_10 datasource master-database-user-name <master-database-user-name>_10 Master database username (MSSQL Server)_10_10 datasource master-database-password <master-database-password>_10 Master database password (MSSQL Server)
Execute a Backup Operation
You can perform a database backup using emory.exe mssql with different parameters:
_11PS C:\emory> .\emory.exe mssql backup --help_11Execute backup operation_11_11Usage:_11 emorymssql backup [flags]_11_11Flags:_11 --database string Database to execute backup for_11 -h, --help help for backup_11 --level string Backup level (FULL, DIFFERENTIAL) (default "FULL")_11 --threads float32 Number of parallel threads to start (default 1)
Command Line Full Offline Backup
To perform a full offline backup, database mut be offline.
Execute as Windows administrator specifying database name, backup level full and the desired number of threads to be started:
_10emory.exe mssql backup --database=emorydev --level=full --threads=4
Command Line Full Online Backup
To perform a full online backup is pretty much the same you executed for an offline one but with database in online state.
Execute as Windows administrator specifying database name, backup level full and the desired number of threads to be started:
_10emory.exe mssql backup --database=emorydev --level=full --threads=4
Command Line Differential Online Backup
Execute as Windows administrator specifying database name, backup level differential and the desired number of threads to be started:
_10emory.exe mssql backup --database=emorydev --level=differential --threads=4
Execute a Log Archive Operation
You can perform a database log archiving using emory.exe mssql log with different parameters:
_10PS C:\emory> .\emory.exe mssql log --help_10Execute log backup operation_10_10Usage:_10 emorymssql log [flags]_10_10Flags:_10 --database string Database to execute log backup for_10 -h, --help help for log_10 --taillog Execute tail-log backup
Command Line Log Archiving
Execute as Windows administrator specifying database name:
_10emory.exe mssql log --database=emorydev
Command Line Taillog Archiving
Execute as Windows administrator specifying database name and taillog flag:
_10emory.exe mssql log --database=emorydev --taillog
Execute a Restore Operation
You can execute a restore operation using emory.exe mssql restore from command line.
During all restore operations database can be either offline or online.
Take into account that restore operation options depend on the recovery model your database has configured.
_13PS C:\emory> .\emory.exe mssql restore --help_13Execute restore operation_13_13Usage:_13 emorymssql restore [flags]_13_13Flags:_13 --database string Database to execture restore for_13 --ebid string External backup id to restore_13 -h, --help help for restore_13 --norecovery Restore without recovery_13 --replace Restore with replace recovery_13 --stopat string Restore database to a certain Point in time (YYYY-MM-DD hh:mm:ss)
Command Line Full Database Backup Restore
- Recovery model : Simple
Just execute emory.exe mssql restore specifying database and corresponding full backup ebid to restore this:
_10emory.exe mssql restore --database=emorydev --ebid=20230313155004
Note: No threads have to be specified as CxLink Backup for MSSQL Server does the calculation for you taking into account backup files to be restored from provider.
- Recovery model : Full
In that case, if you want to resotre database as it was when backup was performed, restore has to be executed using replace option:
_10emory.exe mssql restore --database=emorydev --ebid=20230313155004 --replace
If you don't specify replace option, a taillog log backup has to be performed before the restore:
_10emory.exe mssql log --database=emorydev --taillog
Command Line Full Database Backup PIT Restore
You can execute a full database backup restore and specify a point-in-time using option stopat with format 'YYYY-MM-DD hh:mm:ss':
_10emory.exe mssql restore --database=emorydev --ebid=20230313155004 --stopat='2023-03-13 15:51:00'
Command Line Differential Backup Restore
To restore a differential backup you need to first restore its corresponding full backup using norecover option:
_10emory.exe mssql restore --database=emorydev --ebid=20230312090000 --norecover
And afterwards restore the differential backup:
_10emory.exe mssql restore --database=emorydev --ebid=20230313125004
Note: You could need to restore using replace flag or stopat depending on the recovery model configured
Command Line Differential Backup PIT Restore
You can execute a differential database backup restore and specify a point-in-time using option stopat with format 'YYYY-MM-DD hh:mm:ss':
_10emory.exe mssql restore --database=emorydev --ebid=20230313155004 --stopat='2023-03-13 15:51:00'
Note: You could need to restore using replace flag or stopat depending on the recovery model configured
Execute a Restore Log Operation
You can execute a restore log operation using emory.exe mssql restorelog from command line using log command:
_12PS C:\emory> .\emory.exe mssql restorelog --help_12Execute log restore operation_12_12Usage:_12 emorymssql restorelog [flags]_12_12Flags:_12 --database string Database to execture restore for_12 --ebid string External backup id to restore_12 -h, --help help for restorelog_12 --norecovery Restore without recovery_12 --stopat string Restore database to a certain Point in time (YYYY-MM-DD hh:mm:ss)
Command Line Restore Log
After a backup has been restored with the norecovery option you may need to apply archived logs by executing:
_10emory.exe mssql restorelog --database=emorydev --ebid=20230313155004
If you want to apply more archived logs you would need to specify norecovery option as follows:
_10emory.exe mssql restorelog --database=emorydev --ebid=20230313155004 --norecovery
You can use stopat option whenever you want to stop and recover your database in a certain point in time.
Execute a Recover Database Operation
You can execute a database recover using emory.exe mssql with recover command:
_10PS C:\emory> .\emory.exe mssql recover --help_10Execute recover operation only. To be executed after a database restore_10_10Usage:_10 emorymssql recover [flags]_10_10Flags:_10 --database string Database to execture recover for_10 -h, --help help for recover
Command Line Recover Database
After a successfull database restore or restore log operation with no recover, you may want to recover your database to a consistent state by execute:
_10emory.exe mssql recover --database=emorydev
Where are your backups stored?
They will be stored in the target bucket as:
Backups:
<HOSTNAME>/<DB>/<year>/<month>/<day>/<ebid>.<hostname>.<database>.<FULL|DIFFERENTIAL.<sequence_number>
Logs:
<HOSTNAME>/<DB>/<year>/<month>/<day>/<ebid>.<hostname>.<database>.<LOG|TAILLOG>
Example:
EC2AMAZ-LPNOIB7/StackOverflow2013/2023/03/13/20230313155004.EC2AMAZ-LPNOIB7.StackOverflow2013.FULL.000