This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is a BACKUP WITH FORMAT option?
BACKUP WITH FORMAT option tells SQL Server whether or not to overwrite the media header information. The FORMAT option will erase any information in a backup set that already exists when the backup is initialized.
What is a BACKUP WITH SKIP option?
Using the SKIP parameter will cause SQL Server to skip the expiration check that it normally does on the backup set. It doesn’t care if any backups existing in the backup set have been marked for availability to be overwritten.
What is BACKUP WITH CHECKSUM option?
When generating a backup checksum, BACKUP verifies that the data read from the database is consistent with any checksum or torn-page indication that is present in the database.
When creating a backup checksum, a backup operation does not add any checksums to pages. Pages are backed up as they exist in the database, and the pages are unmodified by backup.
How many copies are allowed when taking a backup using MIRROR Backup option?
Three copies are allowed in a Mirror backup apart from the original copy.
What is MAXTRANSFERSIZE option in Backup database command?
MAXTRANSFERSIZE : specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 64 KB ranging up to 4194304 bytes (4 MB). The default is 1 MB.
What is BUFFERCOUNT option in Backup database command?
BUFFERCOUNT specifies the total number of I/O buffers to be used for the backup operation. The total space that will be used by the buffers is determined by: buffercount * maxtransfersize.
What is Windows Azure Blob storage service Database backups?
SQL Server 2012 SP1 CU2, enables SQL Server backup and restore directly to the Windows Azure Blob service. Backup to cloud offers benefits such as availability, limitless geo-replicated off-site storage, and ease of migration of data to and from the cloud.
What are the Benefits with Windows Azure Blob storage service?
Flexible, reliable, and limitless off-site storage: Storing your backups on Windows Azure Blob service can be a convenient, flexible, and easy to access off-site option.
No overhead of hardware management
Cost Benefits: Pay only for the service that is used. Can be cost-effective as an off-site and backup archive option.
What is the Difference between WITH RECOVERY and WITH NORECOVERY parameter?
The RECOVERY command requests the current RESTORE to roll back all uncommitted transactions. When all uncommitted transactions are rolled back the database is placed into a ready state. The database is ready to use.
NORECOVERY allows the Database Administrator to restore additional backup files such as Differential or Transactional backups. While the database is in this state then users are not able to connect or access this database.
What is a Disaster Recovery?
Disaster recovery is a process that you can use to help recover information systems and data, if a disaster occurs.
Some examples of disasters include a natural or a man-made disaster such as a fire, or a technical disaster such as a two-disk failure in a Redundant Array of Independent Disks (RAID) 5 array.
What are your strategies to recover data when the disaster occur?
- Failover Clustering
- AlwaysOn Avaliability
- Database Mirroring
- Transaction Replication
- Log Shipping
- Strong backup strategies
- Virtual Machine Replication
Before designing a backup strategy, what should be your questions?
- How many hours a day do applications have to access the database?
- How frequently are changes and updates likely to occur?
- Are changes likely to occur in only a small part of the database or in a large part of the database?
- How much disk space will a full database backup require?
Can you encrypt data while creating a backup?
Starting in SQL Server 2014, SQL Server has the ability to encrypt the data while creating a backup. By specifying the encryption algorithm and the encryptor (a Certificate or Asymmetric Key) when creating a backup, you can create an encrypted backup file.
What is Partial Backups?
Partial backups are designed for use under the simple recovery model to improve flexibility for backing up very large databases that contain one or more read-only filegroups.
Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups.
Why can’t I just backup SQL Server’s data files with my Windows backup tool?
SQL Server is not like a word processing application. It manages its own files internally in order to guarantee the ACID (Atomic, Consistent, Isolated, Durable) properties of its databases.
If you simply copy the data file, ignoring the locks and ignoring the transactions that may be currently in progress, it means that when you attempt to attach that database later you will have a database file that is in an inconsistent state. It will generate errors.
It is only a small database. Why can’t I just write every table to disk to back it up?
Yes, you can use a tool like SQLCMD to write the tables to flat file but then, instead of a straightforward, single statement to restore the database, you will need a whole series of commands.
Can someone change the contents of a backup?
There is no direct way to modify the contents of a backup file. Since the backup is a page-by-page copy of the database.
When SQL Server reads each page, during the restore, it will calculate a checksum, based on its contents, and compare it to the value when it read the page during the backup. If someone did manage to change the data within the backup, these values won’t match and SQL Server will flag the page as corrupt.
What is Piecemeal Restores?
Piecemeal restore allows databases that contain multiple filegroups to be restored and recovered in stages. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one or more secondary filegroups. Piecemeal restore maintains checks to ensure that the database will be consistent in the end.
