This is video blog guide about
How to take database Backup on SQL Server?
Video Description : How to take database Backup on SQL Server?
How to perform Backup and restore activity on SQL Server?
As a SQL DBA you should know how backup and restore process work
SQL Server support 3 type of backups
FULL Backup
Differential Backup
Transaction log Backup – This used to perform point in time restore
We can take backup using SSMS or T-SQL backup using SSMS.
If database recovery model is FULL then only we can take Transaction Log backup. Recovery Model of WideWorldImporters is Simple so T log backup is not allowed.
How to take database Backup on SQL Server using T -SQL:
Backup database [WideWorldImporters] to disk = ‘C:\Omkar\backups\backup\WideWorldImporters.bak’ with compression,stats =10
Will try same process with DBATools database which have FULL Recovery MODEL.After taking full and Differential backup it allowed to take transaction log backup because
Recovery model is FULL and extension for transaction log backup is .trn
We can schedule backup of databases using SQL Server agent job periodically. This will help to recover data in case of disaster. There are many options available to take backup
Create maintenance plan is one of the way which take backup base on schedule.
So we have create FULL backup plan which will run daily at 10 AM.
Same as above we can create backup job for differential backup and T log backup
If your database size is huge 500+ GB then best schedule will be
FULL Backup – Weekly
Differential -Daily except full backup schedule
T-Log backup every 15 min(Depend on how much data you want to recover)
This plan will help to recover as much as data in case of disaster.
Backup plan executed successfully and taken backup for each database present in plan.