When a database is configured to use a BULK-LOGGED Recovery Model then SQL Server will log minimal amount of information for operations such as SELECT INTO, BULK INSERT, BCP, CREATE INDEX, ALTER INDEX, and REBUILD INDEX etc within the transaction log file there by reducing the transactional log file size. The Point in Time recovery of the database is possible only if the last transaction log doesn’t have any BULK-LOGGED operations.
SQL Server supports different types of backups for databases. In this tutorial we will discuss in detail each of the available database backup options and how to perform backups using TSQL scripts and with SQL Server Management Studio (SSMS).
In order to take backups a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role else you will receive the below mentioned error while performing backups.
Full database backup represent the database at the time when the backup has finished. However, as the database size increases the full database backup takes more time to finish and it will also require more storage space. Hence for larger databases one must supplement a full database backup with a series of differential database backups and even transactional log backups if the database is in FULL or BULK-LOGGED recovery model. Transactional log backup is not allowed when the database is in SIMPLE recovery model this is by design.
DIFFERENTIAL database backup will only record the data which has changes since the last successful full database backup. This backed up data is known as differential base. When compared to full database backups the differential database backup are quick to complete and will also be smaller in size as this has only the changes thereby reducing the work loss exposure. Differential database backups are very useful especially when subset of database is modified most often than the rest of the database.
Transactional log backup is only possible when your database is in FULL or BULK-LOGGED recovery model. With the help of Transactional Log backup one can achieve Point in Time recovery for the database in case of any disaster It is highly recommended to perform transactional log backups at regular intervals to minimize the loss of work and to truncate the transactional log.
COPY_ONLY backup is a special type of SQL Server Backup which is independent of the sequence of conventional SQL Server backups. Normally whenever you take a backup it will affect how later backups are restored. However, when you use COPY_ONLY backup feature of SQL Server one can take a backup of the database without affecting the overall backup and restore procedures for the database.
Using MIRRORED Backup feature a DBA can create up to 3 identical copies of a database backup. This feature is available in SQL Server 2005 Enterprise Edition and later versions.
Using FILE backup one can backup SQL Server Data File individually. While backing up the database you can specify whole of FILEGROUP instead of specifying each database file individually within the FILEGROUP. In case if there is any file within the FILEGROUP OFFLINE may be because of file being restored then the whole FILEGROUP will be OFFLINE and cannot be backup up. A FILE backup can also serve as the differential base for differential file backups. For more information, see Differential Backups in SQL Server.
Using FILEGROUP backup one can backup all the data files within the SQL Server FILEGROUP individually. While backing up the database you can specify whole of FILEGROUP (i.e., PRIMARY or SECONDARY) instead of specifying each database file individually within the FILEGROUP.
PARTIAL Backups are designed for use under SIMPLE recovery model; thereby to improve flexibility to backup very large database which contain one or more read-only Filegroups. They 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. Instead, for a read-write database, a partial backup contains the data in the primary FileGroup, every read-write FileGroup, and, optionally, one or more read-only files.
A tail-log backup captures any log records which has not yet been backed up i.e., the tail of the transactional log to prevent any work loss and to keep the transactional log chain intact. Before you can recover a SQL Server database to its latest point in time, one must back up the tail of its transaction log without fail. Note that the tail log backup will be the last backup of which one can take to recovery the database.
This article explains the steps to be followed by a DBA or a Developer to Install SQL Server 2014.
More and more DBAs are now move towards using PowerShell to perform routine Database Maintenance Tasks on SQL Server. In this article we will go through the quick steps to determine which version of PowerShell is installed on the Server where SQL Server is hosted using TSQL Script.
SQL Server 2012 introduced a new feature namely Contained Databases. In this article we will learn the steps which a DBA needs to follow to Migrate an Existing Database to Partially Contained Database in SQL Server.
|Trending SQL Server Tips|