This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is the difference between Hot and Cold Backup?
Performing backup while the database is online is called Hot backup. Stopping SQL server service and copying MDF and LDF files is called cold backup which is not really happening in production.
Can I backup databases from multiple instances of MS SQL Server?
Yes. You can backup databases from multiple instances of MS SQL Server running on your local computer. However, you may not be able to backup the databases from multiple instances of the MS SQL Server hosted at multiple locations in your network.
Can you perform Log shipping and Mirroring using Simple Recovery Model?
No, You cannot perform Log shipping and Mirroring with Simple Recovery Model.
Can we perform a SQL Server compressed backup?
Starting SQL Server 2008, we can perform a compressed backup. All types of backups can be performed with compressed option.
When a checkpoint file is updated?
- Database file creation and zeroing is completed.
- After each backup set is processed.
- The redo part of a recovery is finished.
If your database size is bigger, why database backup performance is important?
It reduces the time that the extra I/O workload (and potentially CPU workload for compressed backups) is present on the system.
It reduces the potential for the transaction log file to have to grow to accommodate the transaction log generated while a backup operation is running, especially for long-running full database backups.
How you are improving your backup performance?
The easiest way to improve backup performance is to allow the backup operation to parallelize, which is known as backup striping.
By default, there’s a single data reader thread for each drive letter or mount point being read from and a single data writer thread for each backup device being written to.
In general, having more reader and writer threads should improve performance up to the point where the I/O subsystem becomes a bottleneck either for reads or writes.
A more advanced way to improve backup performance is to manually specify the number of backup I/O buffers to use (with the BUFFERCOUNT option) and the size of each buffer (with the MAXTRANSFERSIZE option).
What do you suggest to improve backup performance?
- Backup stripping / perform parallel with multiple backup device.
- Setting of maxtransfersize parameter.
- Setting of buffercount parameter.
- Setting of compression parameter.
What is Native Backup Compression (SQL Server 2008)?
Database backup compression helps in creating a database backup in a compressed format.
T-SQL Script to take compressed backup.
|
1 2 3 |
BACKUP DATABASE AdventureWorks2012 TO DISK = D:\Backup\AW2012_compress.bak' WITH COMPRESSION |
T-SQL Script to calculate compression ratio.
|
1 2 |
SELECT backup_size/compressed_backup_size FROM msdb..backupset |
How compressed backup impact to the performance of the database?
By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations.
What are the restrictions of compressed backup?
- Compressed and uncompressed backups cannot co-exist in a media set.
- Previous versions of SQL Server cannot read compressed backups.
- NTbackups cannot share a tape with compressed SQL Server backups.
My backup was failed what may be the possible scenarios?
- Disk was Full or Server was busy.
- May be problem with the network.
- If the domain is not running and SQL Server service is running with domain account.
- Problem with MSDB and SQL Agent.
- Error while reading the transaction log file.
- CHECKSUM errors
- Database has entered into Suspect and restoring mode.
- Transaction log file is full.
What is RESTORE WITH RESTART option?
RESTORE WITH RESTART option, which lets you restart an interrupted restore operation. Periodically, a restore operation writes a checkpoint file that describes the point to which the restore has progressed.
This option is really meant for large backups that span several tapes; it lets you restart a restore operation without having to go back to the first tape. However, it can also be useful for restoring disk-based backups.
For differential backups, how is the data determined for those backups?
DCM page contains information about the extent which are changed after the Full backup. Diff. backup process reads information about the changed extents and those extents are added in the differential backup.
What is the meaning of the values in the Type column in backupset table?
- D – Database
- I – Differential database
- L – Log
- F – File or filegroup
- G – Differential file
- P – Partial
- Q – Differential partial
What is Recovery Time Objective (RTO)?
Recovery Time Objective (RTO) is the amount of time which data or hardware is desired to be restored after a data corruption or hardware failure.
What is Recovery Point Objective (RPO)?
Recovery Point Objective (RPO) describes a point in time that data can be restored from. For instance, if there is data corruption, Data loss or unavailability, at what point in time can a valid copy of the data be restored from? RPO is usually measured as minutes, hours, days, weeks etc.
