SQl Server : Backups

There are three types of backups:

  1. Full backup
  2. Differential backup
  3. Transaction log backup

Full backup

A full backup contains all the data in a specific database or set of file groups or files, and also enough log to allow for recovering that data. It is the base of both differential backup and transaction log backup.

Differential Backup

A “Differential” backup is a backup of any extent that has changed since the last “Full” backup was created.

Each time a new differential backup created it will contain every extent changed since the last full backup (NOT since the last differential backup).

When the database needs to be restored to the most recent time the user only need to restore the full backup and the most recent differential backup. All of the other differential backups can be ignored.

Transaction log backup

The transaction log is a serial record of all the transactions that have been performed against the database since the last transaction log back up.

With transaction log backups, you can recover the database to a specific point in time.
(see http://www.mssqltips.com/sqlservertutorial/119/sql-server-point-in-time-restore/)

Database should be set to “Full” or “Bulk-logged” recovery model for “Transaction Log” backups.

If the database is set to the “Bulk-logged” recovery model and a bulk operation was issued, then the entire transaction log needs to be restored.

A transaction log backup allows the user to back up the active part of the transaction log.  So after a “Full” or “Differential” backup the transaction log backup will have any transactions that were created after those other backups completed.  After the transaction log backup is issued, the space within the transaction log can be reused for other processes.  If a transaction log backup is not taken, the transaction log will continue to grow.

Reference:
http://www.mssqltips.com/sqlservertutorial/8/sql-server-transaction-log-backups/
http://www.todo-backup.com/backup-resource/sql-backup-software/sql-backup-types.htm
http://msdn.microsoft.com/en-us/library/ms187048.aspx

Advertisements

SQL Server : Bringing a database online from restoring or standby modes

If the database is in restoring state run the command below to bring the database online:

RESTORE DATABASE <Database Name> WITH RECOVERY


If the database is in read-only (standby) mode you can bring the database online in two steps

Step 01: Disconnect all the connections to the database
USE master
ALTER DATABASE <Database Name> SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE <Database Name> SET ONLINE

Step 02: Bring the database online
RESTORE DATABASE <Database Name> WITH RECOVERY

SQL Server : Logshipping Errors

Error 1:

Logshipping backup job fails at the primary server with the error message below:


Message
2014-10-31 14:36:18.44    *** Error: Backup failed for Server ‘<Server Name>’. (Microsoft.SqlServer.SmoExtended) ***
2014-10-31 14:36:18.45    *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2014-10-31 14:36:18.45    *** Error: BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2014-10-31 14:36:18.50    —– END OF TRANSACTION LOG BACKUP   —–

Solution

This message is thrown because the database is never backed up fully before. Do a full backup of the primary database to solve this issue.

Error 2:

Restore database job at the secondary server failed with the error message below:

Error: The restore operation cannot proceed because the secondary database ‘<Database Name>’ is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping)

Solution :

Restore the secondary database with the full backup of the primary database with NORECOVERY and REPLACE option. An example is given below:
USE [master]
RESTORE DATABASE [TestLST] FROM  DISK = N'G:\Temp\TestLS.bak' WITH  FILE = 1,
MOVE N'TestLS' TO N'E:\SQLData\TestLS.mdf',
MOVE N'TestLS_log' TO N'H:\SQLLog\TestLS_log.ldf',
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
GO

Error 3:

After some disk issues at the production server the logshipping continually failed. So I tried a full back up the primary database and restored the secondary database with this full  backup and started the log shipping again. And the logshipping backup of the primary database failed with the error message below:

Message
2014-10-31 13:34:00.33    *** Error: Backup failed for Server ‘<Server Name>’. (Microsoft.SqlServer.SmoExtended) ***
2014-10-31 13:34:00.33    *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2014-10-31 13:34:00.33    *** Error: BACKUP detected corruption in the database log. Check the errorlog for more information.
BACKUP LOG is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
Processed 8 pages for database ‘<Database Name>’, file ‘<Database Name>_Data’ on file 1.
Processed 2013584 pages for database ‘<Database Name>’, file ‘<Database Name>_1_Data’ on file 1.
Processed 1380536 pages for database ‘<Database Name>’, file ‘<Database Name>_2_Data’ on file 1.
Processed 1671736 pages for database ‘<Database Name>’, file ‘<Database Name>_3_Data’ on file 1.
90 percent processed.(.Net SqlClient Data Provider) ***
2014-10-31 13:34:00.37    —– END OF TRANSACTION LOG BACKUP   —–
Exit Status: 1 (Error)

Solution:

One of the solutions is to configure the log shipping all over again so the logshipping job will initialize the secondary database.

The other solution is to follow the steps below: (Ref: http://www.sqlskills.com/blogs/paul/how-can-a-log-backup-fail-but-a-full-backup-succeed/)

  1. Stop all user activity in the primary database
  2. Switch to the SIMPLE recovery model (breaking the log backup chain and removing the requirement that the damaged portion of log must be backed up)
  3. Switch to the FULL recovery model
  4. Take a full database backup (thus starting a new log backup chain)
  5. Start the logshipping job

Error 4

Logshipping primary database backup job failed with the following error:

Message
2014-11-03 14:53:02.80    *** Error: Backup failed for Server ‘<Server>’. (Microsoft.SqlServer.SmoExtended) ***
2014-11-03 14:53:02.85    *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2014-11-03 14:53:02.85    *** Error: Cannot open backup device ‘\\<Server>\LogShippingBackups\DatabaseName\DatabaseName_20141103145302.trn’. Operating system error 5(failed to retrieve text for this error. Reason: 15105).
BACKUP LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Solution

SQL Server agent does not have appropriate permission to access the shared folder. Granting access solves this problem.