How to configure Incremental backups for MSSQL Database
Incremental Database Backup:
Create a folder for the backup in the Hardisk (Ex: C:\SQL_backup\Incrementail)
1. Open SQL Server Management Studio and connect using windows credentials.
2. Click Management and select Maintenance Plans.
3. Right-Click on Maintenance Plans and select New Maintenance Plans, Give naming Convention as Incremental_DB_Backup.
4. In Left side of Server Management Studio there will be a Tool Box within Maintenance Plan Tasks
Create a folder for the backup in the Hardisk (Ex: C:\SQL_backup\Incrementail)
1. Open SQL Server Management Studio and connect using windows credentials.
2. Click Management and select Maintenance Plans.
3. Right-Click on Maintenance Plans and select New Maintenance Plans, Give naming Convention as Incremental_DB_Backup.
4. In Left side of Server Management Studio there will be a Tool Box within Maintenance Plan Tasks
5. Drag and drop the Back Up Database Task and Maintenance Clean Task in the Incremental_DB_Backup Design area.
6. Right Click on the Back Up Database Task and select Edit.
7. Select the Type of Backup you want to do as Differential
8. Select the Database In Database(s) in these database list (Ex: TestDB.)
9. Select Create a Backup file for every database, select Create a sub-directory for each database
10. Give the path where we want to store the database backup as C:\SQL_backup\Inc and give the Backup file extension as bak and Click OK
11. Right Click on the Maintenance Cleanup Task and select Edit
12. In Search folders and delete files based on an extension
13. Give the Folder Path C:\SQL_Backup\Inc and give a file extension as bak in the file extension box and select Include first-level sub-folders.
14. File Age: Give the desired file retention period that how many days of backup you wanted to keep in the backup folder.
15. Click OK.
16. Right Click on the Back Up Database Task, select Add Precedence Constraint, Select Back Up Database Task in From and Maintenance Cleanup Task in To from the Control Flow and click OK
17. Now you will see a link between Back Up Database Task and Maintenance Cleanup Task. Press Ctrl+S to save the task
18. Go to the SQL Server Agent in the SQL Server Management Studio, Select the Incremental_DB_Backup and Right Click and select Properties in the properties sheet select Schedules and click New
19. Give some name in the name so that we can identify the Job Schedules
20. Select Daily in frequency, Recurs every in 1 day.
21. Select 12:00:00 AM in Daily frequency and click OK
22. Click Ok in Job properties.
Comments
Post a Comment