Backup Part-8

 

Q: How to Change the default SQL Server backup folder path?

A When you install SQL Server the path for the installation is generally something such as the following: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL.

open the registry using REGEDIT or some other tool
and if you navigate to this key: HKEYLOCALMACHINE\SOFTWARE\Microsoft\Microsoft
SQL Server\MSSQL.2 \MSSQLServer

Q How to change default SQL Server backup folder path?

A When you install SQL Server the path for the installation is generally something such as the following: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL.

open the registry using REGEDIT. Look for HKEYLOCALMACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer \BackupDirctory

Right Click BackupDirectory and Modify.

Q: While taking full backup can we take transaction log backup?

Yes 

Q: While taking full backup can we take differential backup?

It will allow differential backup operation. But it will success after

completion of Full Back up only. Yes

Q: How can you get last backup date of a database through t-sql query?
A In MSDB database, backupset table consists of all the details of backup. By querying this table, we can get these details.

1.select backup_start_date,backup_finish_date from msdb..backupset

Q: What is a SQL Server checkpoint?

A SQL Server checkpoint is the process of writing all dirty data file pages  out to disk. A dirty page is page that has changed in memory (buffer cache) since they were read from disk or since the last checkpoint. This is done regardless of the transaction that made the change.

SQL Server uses a protocol called Write Ahead Logging (WAL) and it is this process that writes all log records describing a change to the data page to disk before the actual page is written to disk.

Checkpoints can occur concurrently on any number of databases on an instance.

Q: How do database checkpoint occur?

Before a backup the database engine automatically performs a checkpoint, this ensures that all database changes are contain in the backup.

You issue a manual checkpoint command, a checkpoint is the run against the database in use

SQL Server is shutdown. If the checkpoint is skipped (SHUTDOWN WITH NOWAIT) the restart will take much longer

ALTER DATABASE is used to add or remove a database file.

If you change  the recovery model from bulk-logged to full or full to simple recovery model.

If your database is in full or bulk logged recovery mode checkpoints are run periodically as specified by the recovery interval server setting 

In simple recovery checkpoints are run when the log becomes 70% full or based on the recovery interval setting, which ever comes first.

Q: In which table all the Maintenance Plans meta data is stored?

·          sysdbmaintplan_databases: Contains one row for each database that has an associated upgraded database maintenance plan.

·         sysdbmaintplan_history: Contains one row for each upgraded database maintenance plan action performed.

·         sysdbmaintplan_jobs: Contains one row for each upgraded database maintenance plan job.

·         Sysdbmaintplans: Contains one row for each upgraded database maintenance plan.

Q: What is History CleanUp Task in Database Maintenance Plan?

The History Cleanup task deletes entries in the following history tables in the SQL Server msdb database.

·                   backupfile

·                   backupfilegroup

·                   backupmediafamily

·                   backupmediaset

·                   backupset

·                   restorefile

·                   restorefilegroup

·                   restorehistory

By using the History Cleanup task, a package can delete historical data related to backup and restore activities, SQL Server Agent jobs, and database maintenance plans.

Please visit other related articles...

https://sqldbaiq.blogspot.com/p/backup-iq.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-2.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-3.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-4.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-5.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-6.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-7.html

https://sqldbaiq.blogspot.com/p/backuprestore-part-8.html

References: Thanks to the all the SQL Server bloggers who wrote and shared the valuable information on their blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.