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
Configuration
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:
? CxLink Backup: [Use arrows to move, enter to select, type to filter]
--------------------------------------------------------------------------------
Storage Providers profiles
Set storage providers for backup/restore
> Configuration for MSSQL
Backups Lifecycle settings
Monitoring
--------------------------------------------------------------------------------
License
--------------------------------------------------------------------------------
Logging file settings
Debug traces
Show relevant information of this CxLink Backup
--------------------------------------------------------------------------------
Catalog & Backups management
--------------------------------------------------------------------------------
Generate troubleshooting package to be sent to Syntax Support
Migrate resources to last models
--------------------------------------------------------------------------------
Exit
Configuration submenu:
? CxLink Backup: Configuration for MSSQL
? Select action to perfom: [Use arrows to move, enter to select, type to filter]
> Master database username [sa]
Master database password [ ]
Windows Authentication [true]
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:
datasource windows-authentication <windows-authentication>
Use Windows Authentication (MSSQL Server)
datasource master-database-user-name <master-database-user-name>
Master database username (MSSQL Server)
datasource master-database-password <master-database-password>
Master database password (MSSQL Server)
Execute a Backup Operation
You can perform a database backup using emory.exe mssql with different parameters:
PS C:\emory> .\emory.exe mssql backup --help
Execute backup operation
Usage:
emorymssql backup [flags]
Flags:
--database string Database to execute backup for
-h, --help help for backup
--level string Backup level (FULL, DIFFERENTIAL) (default "FULL")
--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:
emory.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:
emory.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:
emory.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:
PS C:\emory> .\emory.exe mssql log --help
Execute log backup operation
Usage:
emorymssql log [flags]
Flags:
--database string Database to execute log backup for
-h, --help help for log
--taillog Execute tail-log backup
Command Line Log Archiving
Execute as Windows administrator specifying database name:
emory.exe mssql log --database=emorydev
Command Line Taillog Archiving
Execute as Windows administrator specifying database name and taillog flag:
emory.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.
PS C:\emory> .\emory.exe mssql restore --help
Execute restore operation
Usage:
emorymssql restore [flags]
Flags:
--database string Database to execture restore for
--ebid string External backup id to restore
-h, --help help for restore
--norecovery Restore without recovery
--replace Restore with replace recovery
--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:
emory.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:
emory.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:
emory.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':
emory.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:
emory.exe mssql restore --database=emorydev --ebid=20230312090000 --norecover
And afterwards restore the differential backup:
emory.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':
emory.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:
PS C:\emory> .\emory.exe mssql restorelog --help
Execute log restore operation
Usage:
emorymssql restorelog [flags]
Flags:
--database string Database to execture restore for
--ebid string External backup id to restore
-h, --help help for restorelog
--norecovery Restore without recovery
--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:
emory.exe mssql restorelog --database=emorydev --ebid=20230313155004
If you want to apply more archived logs you would need to specify norecovery option as follows:
emory.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:
PS C:\emory> .\emory.exe mssql recover --help
Execute recover operation only. To be executed after a database restore
Usage:
emorymssql recover [flags]
Flags:
--database string Database to execture recover for
-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:
emory.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