RSS

Category Archives: backups

Troubleshooting “SQL Server Database Is In Recovery Pending State”

Overview

Users of SQL Server Database must be aware of Recovery states, that occurs when there is unintentional dropped SQL table or any other components that needs to be restored. The SQL Server Database is in recovery pending state at situations like restart of SQL Server, offline & online state of database or while restoring database from a backup. However, if there is any issue during this recovery process, error can be Read the rest of this entry »

 
Leave a comment

Posted by on August 30, 2016 in backups, problems

 

Tags:

SQL Server Transaction Log Backup Failing (Error 22029)

A Transaction Log is a file that contains all the records of transactions and the database modifications made by each transaction in SQL Server database. The log file plays a very important part of SQL Server database when it comes to disaster recovery and it should not be in corrupted state. There are several types of errors we came across while using SQL Server and one of the most common error is Failure in SQL Server Transaction Log or Error 22029. This page will be discussing about the same error in details.

SQL Server Transaction Log Backup Failing

Why It Occurs?

SQL Server Error 22029

This error in associated with the maintenance plan of the transaction log back up or maintenance plan options for the database in SQL Server.

There are many reasons why the above error in SQL Server occurs-

  1. The SQL Server transaction log backup error 22029 may be seen if your database maintenance plan is trying to make a transaction log backup of a database that is offline.
  2. It may occur when you try to create transaction log backup and when the database is using the simple recovery model.
  3. It may occur if you select the master database and you choose backup the transaction log as part of the maintenance plan option.
  4. This error may also be seen if the string for the target folder (where the database backup will be stored or the one used for transaction log backup or one used for reports) in the maintenance plan contains “-S”
  5. It may happen if you have less disk space for the transaction log backup.
  6. If the multiple databases are backed up in a single maintenance plan, this error might show up.
  7. In addition, it may occur if you have no right access permissions or authority. Example- the SQL agent does not have write access to the file storing the database backups.

How It Can Be Resolved?

One thing that should be kept in mind while creating maintenance tasks is to check the “write report to a text file in directory” so that all the details of whatever happening during the maintenance task is seen, especially when there is any error.

Database Maintenance Plan

  1. The reason why the first error occurs can be solved by connecting the database to the server. So that, the log of that database can be backed up successfully with the maintenance plan as the database is online.
  2. While creating the maintenance plan for transaction log backup under simple recovery model, we may find error. Why is it so?
    The simple recovery gives the simplest form of backup and restore. It supports both database and file backups but does not support log backups. The absence of log backups simplifies managing backup and restore.

    Tips: The simple recovery model is not appropriate for systems where loss of recent changes is unacceptable. In such cases, full recovery model is preferred.

    We should never run a transaction log backup on a database in the Simple Recovery model. Even though there is option to select all databases, we can select either database that are not in Simple Recovery model or change the database to the Full or Bulk-logged Recovery models to avoid this error. This problem has been fixed in SQL Server 2005 as the databases in simple recovery are ignored automatically.

  3. The third error says we cannot select master database and choose backup of transaction log because only full database backups of master database are allowed. Hence, we can create a separate maintenance plan for master database and not backup the transaction log or set master database to Full recovery and do transaction log backup.
  4. To solve the fourth error, we will not use strings that contain “-S” for a directory path. It only occurs on named instances of SQL Server 2000.
  5. The problem with less storage can be solved by allocating more disk space.
  6. If the error occurred due to sixth reason, then we can select a single or some databases while back up and keep in mind the type of recovery models with respect to transaction log backup.
  7. To perform transaction log backup of the database, we need to configure MS SQL Server to run under a user account that has administrator privileges to the machine.
 
Leave a comment

Posted by on December 21, 2015 in backups

 

Tags: ,

Transaction Log Corruption and Backup In SQL Server

Introduction

Corruption of SQL Server transaction log files is complicated because its consequences aren’t major except for the fact that they result in the failure of backups. However, that as well is a necessary task to be performed by every server admin for business continuity and thus, must not be ignored.

I/O subsystem is the culprit in most cases of Transaction Log file corruption just like a data file. Till the time this damage has been caused to the active portion of the file – required by SQL Server for some reason – it is possible that the cause remains unknown for a long duration. This may result in a major disturbance because corruption, which is undiscovered, will take equal amount of time in being rectified too, which may make the condition much worse too in the meantime. However, SQL Server has no way of detecting the corruption anyway before any kinds of consequences are surfaced. In order to detect the damage, the server will have to process the complete log file and that too particularly the active parts.

DBCC CHECKDB is a built in utility provided for examining database integrity in SQL Server. Read the rest of this entry »

 
3 Comments

Posted by on September 22, 2015 in backups

 

Tags:

Get all Database Job History then Send Email

***************************************************Introduction********************************************************

Today i will Dessecus how to Get all Database job history then Send Email to The Technical Support “job History Report” then i will delete this log history

Get Daatabase job history

select * from msdb.dbo.sysjobs

Job history

Get database job Step history

select * from msdb.dbo.sysjobhistory

Hob Step History

Now i will make join between msdb.dbo.sysjobhistory and msdb.dbo.sysjobs by Job_ID  and i will  Convert the job tun Date time because if you select it without convert you will sedd the Data Formate “20121224” so i will convert it , also i have column name “Run_Status” this Column return 0 or 1 { 0 = the job is Failed , 1 = The job is Success } so i will make Case when on this Column this Script you can use it for to ways

1- job Failed history report

2- job Success history Report

but i will write now ho to get job all history Failed and Success

select J.job_id,J.nameas[Job Name],S.step_name,S.step_id,

CONVET (DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4 asRun_Date_Time,

RIGHT(‘000000’+CONVERT(varchar(6),S.run_duration), 6)as[Run Duration],

CASE S.run_status

WHEN 0 THEN‘JOB FAIL’

WHEN 1 THEN‘JOB Success’

END

,S.message,S.server,  CONVERT(DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4 asRun_Date_Time

from  msdb.dbo.sysjobhistory as S innerjoin msdb.dbo.sysjobs as J

on s.job_id=J.job_id

where Step_id<> 0

/*

************************************ Send Email With Database job History Report**************************************

If you need to know hot Configure your database mail See this post in the First http://wp.me/p1Oidq-9A

*/

DECLARE @xmlNVARCHAR(MAX)

DECLARE @bodyNVARCHAR(MAX)

SET @xml=CAST((SELECTJ.nameAS‘td’,,S.step_nameAS‘td’,,S.step_idAS‘td’,”,CASES.run_status

WHEN 0 THEN‘JOB FAIL’

WHEN 1 THEN‘JOB Success’

END AS‘td’,

,CONVERT(DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4  AS‘td’,”,S.messageAS‘td’

from msdb.dbo.sysjobhistory as S innerjoin msdb.dbo.sysjobs as J

on s.job_id=J.job_id

where Step_id<> 0

FOR XMLPATH(‘tr’),ELEMENTS)ASNVARCHAR(MAX))

SET @body=‘<html><body><H3>Database job history</H3>

<table border = 1>

<tr><font color=blue size=5 >

<td> Job Name </td> <td> Step Name </td> <td> Step ID </td> <td> Job Status </td> <td> Job_Run_Time </td> <td> message </td></font></tr>’

SET @body=@body+@xml+‘</table></body></html>’

EXEC msdb.dbo.sp_send_dbmail

@profile_name =‘DBA’,— replace with your SQL Database Mail Profile

@body =@body,

@body_format =‘HTML’,

@recipients =‘Mostafa@Safeerp.com’,— replace with your email address

@subject =‘Database job History’;

Report in Email

/*

************************************ Cleare job history**************************************************

If you need to know hot Configure your database mail See this post in the First

*/

EXEC MSDB.dbo.sp_purge_jobhistory

@job_name=N’testsenemail’; — JOB Nmar

GO

 
3 Comments

Posted by on December 24, 2012 in backups, backups

 

How to Check backup log /and How to Delete backup log

introduction

Today i will explain how to monitor your backup is Saved on or no also How to Delete the old backup history from the log

How to Check your Backup log or your backup history

we can Check the backup history by this way but before this step i need from you to Create Databae “backupTest” and take backup from it with 3 types Full,DIFF,LOG

select Database_name,backup_start_date,backup_finish_date,type from msdb.dbo.backupset

you can select all Column (*) to see all information

but i will customize it to help you how to check the FULL backup , DiFF backup , Log backup

********************************Check FULL abckup history************************************

SELECT database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected

FROM msdb.dbo.backupset where Type=‘D’ ORDER BY backup_set_id DESC

GO

********************************Check DIFF abckup history************************************

SELECT database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected

FROM msdb.dbo.backupset where Type=‘i’

ORDER BY backup_set_id DESC

********************************Check LOG abckup history************************************

SELECT

database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected

FROM msdb.dbo.backupset where Type=‘L’

ORDER BY backup_set_id DESC

GO

How to Delete your backup history:-

we can make delete for all backup history for any database by this Code

exec MSDB.DBO.sp_delete_database_backuphistory ‘Database name’

So this Code Delete history to the backup but for one database one by one so i will make Cursore to loop on all Server to get all database name then Delete the backup history for all Database

set ANSI_NULLSON

set QUOTED_IDENTIFIERON

go

Create PROC[dbo].DeletebackupHistory

AS

BEGIN

DECLARE @DB Nvarchar(50)

DECLARE GetDB CURSOR FOR

Select Name from Sys.Databases Where database_id> 4 and state_desc=‘ONLINE’

OPEN GetDB

FETCH NEXT FROM GetDB INTO @DB

WHILE @@FETCH_STATUS= 0

BEGIN

—-Delete backup history

exec MSDB.DBO.sp_delete_database_backuphistory @db

——————————-

FETCH NEXT FROM GetDB

INTO @DB

END— FETCH WHILE

CLOSE GetDB

DEALLOCATE GetDB

END

 

 
Leave a comment

Posted by on December 24, 2012 in backups, backups

 

SP Help you to take FULL,DIFF,LOG backup from all DB

Introducation

Database backup we have 3 typr from backup Full backup , Diffrential backup , log transaction backup now i write stored procedure make koop on all database on SQl Server instance then take backup from it this Stored procedure take 2 paramter

@BackupPath :- this the path of your hard disk

@backupType :- your database backup typy this parmater allow Number Value { 1 = Full backup , 2 = Diff backup , 3 = log Trans backup } any thing not like this 3 value Stored will give you Error.

Now you can Create this proc under your database master then you can call it

Note : if you want to put your database backup for starday in folder and Sunday in folder ……ETC please Check this Post http://wp.me/p1Oidq-1D

Create Stored Procedure TakeDBsBackupDaily

USE MASTER

GO

set ANSI_NULLSON

set QUOTED_IDENTIFIERON

GO

Create PROCEDURE[dbo].[TakeDBsBackupDaily]

@BackupPathnvarchar(500),@backupTypeint

AS

BEGIN

declare  @dnvarchar(2)

,@mnvarchar(2)

,@ynvarchar(4)

,@CurDatenvarchar(8)

,@hnvarchar(2)

,@minnvarchar(2)

,@secnvarchar(2)

,@CurTimenvarchar(8)

,@FNamenvarchar(500)

,@DbNamenvarchar(100)

,@BackupTypeNamenvarchar(200)

Set @d=DATEPART(“day”,(GETDATE()))

Set @m=DATEPART(“month”,(GETDATE()))

Set @y=DATEPART(“year”,(GETDATE()))

set @CurDate=replicate(‘0’,2len(@d))+@d+replicate(‘0’,2len(@m))+@m+replicate(‘0’,4len(@y))+@y

Set @h=DATEPART(“hour”,(GETDATE()))

Set @min=DATEPART(“minute”,(GETDATE()))

Set @sec=DATEPART(“second”,(GETDATE()))

set @CurTime=replicate(‘0’,2len(@h))+@h+replicate(‘0’,2len(@min))+@min+replicate(‘0’,2len(@sec))+@sec

DECLARE bK CURSORFOR

SELECT name FROM sys.databases WHERE Database_ID> 4 andstate_desc=‘Online’

OPENbk

FETCH NEXT FROM bk

INTO @DbName

WHILE @@FETCH_STATUS=0

BEGIN

—–FULL backup

IF @backupType= 1

begin

SET @FName= @BackupPath+‘\’+@DbName  +‘_D_’+@curdate+‘_FULL’+‘.bak’

print‘Backing up Database : ‘+@DbName+‘ …’

set @BackupTypeName=  @DbName+‘-FULL Database Backup’

PRINT @FName

BACKUP DATABASE @DbNameTO  DISK=@FName   WITH NOFORMAT,INIT, NAME = @BackupTypeName , SKIP, NOREWIND,NOUNLOAD,STATS= 10

End

—DIFF backup

Else IF @backupType = 2

begin

SET @FName = @BackupPath+‘\’+@DbName  +‘_D_’+@curdate+‘_DIFF’+‘.bak’

print‘Backing up Database : ‘+@DbName+‘ …’

set @BackupTypeName = @DbName+‘-DIFF Database Backup’

PRINT@FName

BACKUP DATABASE @DbNameTO  DISK = @FName WITH DIFFERENTIAL,NOFORMAT,NOINIT,  NAME = @BackupTypeName,SKIP,NOREWIND,NOUNLOAD,  STATS= 10

End

—Log backup

Else IF@backupType= 3

begin

SET @FName = @BackupPath+‘\’+@DbName  +‘_D_’+@curdate+‘Log’+‘.bak’

print‘Backing up Database : ‘+@DbName+‘ …’

set @BackupTypeName = @DbName+‘-Log_Trans Database Backup’

PRINT@FName

BACKUP LOG @DbNameTO  DISK = @FName WITH NOFORMAT,NOINIT,  NAME = @BackupTypeName,SKIP,NOREWIND,NOUNLOAD,  STATS = 10

End

Else

 

RAISERROR(‘Invalid Backup Type value.

Must be Write your Database backup type

1 = FULL backup

2 = Diffrential backup

3 = log Transaction backup

Or Contact the Adminstrator Mostafa Elmasry 0593205711′, 16, 1);

FETCH NEXT FROM bk

INTO @DbName

END

CLOSE bk

DEALLOCATE bk

END

####################################################################################

now try to Execute your Stored Procedure

Full backup

exec [TakeDBsBackupDaily] ‘E:\backups\Log’,1

DIFF backup

exec [TakeDBsBackupDaily] ‘E:\backups\Log’,2

Log backup

exec [TakeDBsBackupDaily] ‘E:\backups\Log’,3

 
Leave a comment

Posted by on December 23, 2012 in backups

 

list down all SQL Server backups

Dwenload Script : http://hotfile.com/dl/149375389/c747113/List_Dowen_SQL_Server_Backups.rar.html

/*——————————————————————————————————————————-

— Description    :     This SQL Script will list down all SQL Server backups which were not backup in last 2 days

— Copyright 2012 – DBATAG

— Author        :    DBATAG

— Created on    :    02/06/2012

— Version       :    1.1

— Dependencies  :

— Compatibility  : This will work on SQL Server 2000, 2005, 2008, 2008 R2 and even in SQL Server 2012

—————————————————————————————————————————-*/

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   bs.database_name,

   MAX(bs.backup_finish_date) AS last_db_backup_date,

   DATEDIFF(hh, MAX(bs.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]

FROM    msdb.dbo.backupset bs

WHERE     bs.type = ‘D’

GROUP BY bs.database_name

HAVING      (MAX(bs.backup_finish_date) < DATEADD(hh, – 48, GETDATE()))

UNION

–Databases without any backup history

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   master.dbo.sysdatabases.NAME AS database_name,

   NULL AS [Last Data Backup Date],

   9999 AS [Backup Age (Hours)]

FROM

   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset bs

       ON master.dbo.sysdatabases.name  = bs.database_name

WHERE bs.database_name IS NULL AND master.dbo.sysdatabases.name <> ‘tempdb’

ORDER By bs.database_name

————————————————————————————————————————————————

/*——————————————————————————————————————————-

Description    :     This SQL Script will list down all SQL Server backups which were taken in last 10 days with details

— Copyright 2012 – DBATAG

— Author        :    DBATAG

— Created on    :    02/06/2012

— Version       :    1.0

— Dependencies  :

— Compatibility  : This will work on SQL Server 2000, 2005, 2008, 2008 R2 and even in SQL Server 2012

—————————————————————————————————————————-*/

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   bs.database_name,

   bs.backup_start_date,

   bs.backup_finish_date,

   bs.expiration_date,

   CASE bs.type

       WHEN ‘D’ THEN ‘Full Database’

       WHEN ‘L’ THEN ‘Log’

       WHEN ‘I’ THEN ‘Differential’

       WHEN ‘F’ THEN ‘File Level’

       WHEN ‘G’ THEN ‘File Level Differential’

       WHEN ‘P’ THEN ‘Partial’

       WHEN ‘Q’ THEN ‘Differential partial’

   END AS backup_type,

   convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as ‘Backup Size in MB’,

   bmf.logical_device_name,

   bmf.physical_device_name,

   bs.name AS backupset_name,

   bs.description

FROM   msdb.dbo.backupmediafamily  bmf

   INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id

WHERE  (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() – 10)

ORDER BY

   bs.database_name,bs.backup_finish_date

 
Leave a comment

Posted by on March 10, 2012 in backups, backups

 
 
%d bloggers like this: