We need to backup the following type of data in MS SQL:
-
Backup database
-
Backup transaction log
MS SQL can backup by the following type:
-
Full backup the entire contents of the database and changes made to the database during the backup operation.
-
Differential backup the differences between the last full database backup and the differential backup operation was executed.
-
Log backup contains all of the log records since the last log backup.
-
File backup contains either a file or filegroup.
-
Partial backup similar to a full backup but excludes all read-only file groups. Read-only database contains the only the primary filegroup.
-
Tail-Log backup contain all log since the last log backup. It is used in a disaster recovery process.
MS SQL’s log has 3 models:
-
Full model record every operation for recoverability purposes. It is used in production environment.
-
Bulk-Logged model only record what were modified. It is used in non-production environment.
-
Simple model is unable to process log backup. It is used in non-production environment.
How to change log models in GUI:
-
Open “SMSS” > expand “Databases” > right-click database > select “Properties”
-
Select “Options” > select the value of “Recovery model:”
How to change log models in T-SQL:
How to backup network shared folder:
-
It must be using UNC path.
-
We can add MS SQL Server account to shared folder with full control permission.
Why need to backup to multi-file:
-
It can make the backup process more faster.
-
Since the each backup file is smaller, it is easier for movement.
How to backup to multi-file in GUI:
-
Open “SMSS” > expand “Databases” > right-click database > select “Tasks” > select “Back Up…”
-
Select “General” > At “Destination” click “Add…” to add multi backup file.
How to backup to multi-file in T-SQL:
