Backup Differential não pode ser restaurado porque não há arquivos prontos para reversão – SQL Server

Esta mensagem ocorre quando você tenta restaurar um backup diferencial utilizando o SQL Server 2005 – 2012.

“System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)”.

Este erro está dizendo que não há nenhuma base de dados no modo no-operational (não-operacional), e, portanto, não foi feita de tal forma que as transações não comitadas (confirmadas) não fossem revertidas. Quando você restaura um backup no modo RESTORE WITH RECOVERY (padrão), indica que o roll forward deve ser realizado somente enquanto o backup FULL for restaurado, ou seja, quando o restore for finalizado nenhum arquivo de LOG ou Differential poderá ser restaurado, pois o arquivo de LOG estará truncado. Ao realizar o restore no modo RESTORE WITH NORECOVERY, após o final do restore do backup FULL o roll forward não será finalizado, assim deixando os arquivos de LOG “abertos” para que mais arquivos de LOG sejam restaurados.

A maneira mais fácil de reproduzir este erro é realizando um backup FULL da sua base de dados, e em seguida restaura-lá com a opção RESTORE WITH RECOVERY. Logo em seguida faça um backup Differential da mesma base de dados e tente restaurar o backup no modo RESTORE WITH RECOVERY. Ao realizar o backup ele irá retornar esta mensagem de erro:

msg_error

Para realizar o restore do Backup Differential da maneira correta, antes você precisa restaurar o backup FULL com a opção WITH NORECOVERY (este é o passo mais importante!):

step02

Após o restore verifique se o database está com o status “RESTORING”:

Use Master
GO
SELECT
	Name, State_Desc
FROM
	sys.databases
WHERE
	Name = N'DBTeste1'

step04

Se ele estiver com o status RESTORING, faça o restore do backup Differential com a opção WITH RECOVERY:

step01

E assim o Backup Differential será restaurado.

É importante e primordial lembrar-se que antes de restaurar um backup Differential ou de Log é preciso restaurar o backup Full da maneira correta para evitar esta mensagem de erro e refazer todo o processo do restore.

É isso aí!

Lendo Informações do Backup – SQL Server

Neste artigo irei mostrar alguns comandos T-SQL, que estão disponíveis do SQL Server 2005, que retornam informações úteis dos backups realizado, tais como: arquivos de data e log que estão no backup, horário de inicio e término do backup, integridade do backup realizado, etc.

Os comandos T-SQL são:

RESTORE FILELISTONLY
RESTORE LABELONLY
RESTORE HEADERONLY
RESTORE VERIFYONLY

Obs.: estes comandos apenas extraem as informações que estão no backup, isso sem a necessidade de restaura-lós.

Para saber mais detalhes de cada informação retornada nestes comandos, clique no título do comando.

RESTORE FILELISTONLY

Este comando retorna informações sobre os arquivos de dados (mdf e ndf) e log (ldf) armazenados em um dispositivo.

-- FROM DISK = Caminho\NomeDoArquivo.BAK
RESTORE FILELISTONLY FROM DISK = 'C:\Backups\ALAN_TESTE\ALAN_TESTE_FULL.bak'

Por exemplo, neste comando ele retorna algumas informações importantes como:
– LogicalName: nome lógico do arquivo no SQL Server;
– PhysicalName: caminho e nome do arquivo;
– Type: o tipo do arquivo, ex: se o Type for “D”, trata-se de um arquivo de dados.

bkp01

RESTORE LABELONLY

Este comando retorna um conjunto de resultados que contém informações sobre as mídias de backup identificadas pelo dispositivo de backup designado. É um modo mais rápido de descobrir que a mídia de backup contém. A instrução lê somente o cabeçalho da mídia, essa instrução terminará rapidamente mesmo quando estiverem sendo usados a mídia como a fita.

-- FROM DISK = Caminho\NomeDoArquivo.BAK
RESTORE LABELONLY FROM DISK = 'C:\Backups\ALAN_TESTE\ALAN_TESTE_FULL.bak'

RESTORE HEADERONLY

Este comando retorna informações sobre os backups (Backup Set) armazenados em um dispositivo. É um dos comandos mais utilizados, pois retorna todos os backups armazenados no dispositivo, seus tipos e quais databases eles pertecem, o usuário que realizou o backup, a data de inicío e fim dos backups, collation, modo de recovery, etc.

-- FROM DISK = Caminho\NomeDoArquivo.BAK
RESTORE HEADERONLY FROM DISK = 'C:\Backups\ALAN_TESTE\ALAN_TESTE_FULL.bak'

bkp02

RESTORE VERIFYONLY

Este comando verifica se o conjunto de backup está completo e se todo o backup pode ser lido. Porém, RESTORE VERIFYONLY não tenta verificar a estrutura dos dados contida nos volumes de backup. Se o backup for válido, o retorno será de sucesso.

-- FROM DISK = Caminho\NomeDoArquivo.BAK
RESTORE VERIFYONLY FROM DISK = 'C:\Backups\ALAN_TESTE\ALAN_TESTE_FULL.bak'

Caso o backup seja válido, o retorno deverá ser:

The backup set on file 1 is valid.

Essas informações são importantes para verificar o status do backup para evitar erros ao restaurar o backup, entre outras informações.
É isso aí!

Query últimos Backups – SQL Server Pt. 2

Como dito no último post, irei mostrar uma simples query que retornará onde os Backups foram salvos e o tamanho dos mesmos.

A query irá retornar os seguintes campos:

Database_Name: o nome do database.
Physical_Device_Name: o local e nome do arquivo do backup realizado. Caso o Backup tenha sido salvo em uma fita, o resultado será parecido como esse: {7878439E-5007-4EB3-96AC-447AEE19EBE6}1
Size: o tamanho do backup em MB.
Backup_Start_Date: horário de início do backup.
Backup_Finish_Date: horário de término do backup.
Seconds_Duration: o tempo, em segundos, da realização do backup.
Backup_Type: tipo do Backup.
Server_Name: nome do servidor onde foi realizado o backup.

Nessa query estou utilizando um parâmetro de dias x backup_start_date para listar os backups.

Links úteis:
BackupSet
BackupMediaFamily

Use msdb 
GO
SET NOCOUNT ON
GO

DECLARE @Dias Int

Set @Dias = 0

SELECT 
	 S.Database_Name
	,M.Physical_Device_Name
	,Convert(Decimal(12,2), S.Backup_Size / 1024 / 1024) As Size
	,S.Backup_Start_Date
	,S.Backup_Finish_Date
	,Cast(DateDiff(Second, S.Backup_Start_Date , S.Backup_Finish_Date) As Varchar(4)) As Seconds_Duration
	,Case S.Type
		When 'D' Then 'Full'
		When 'I' Then 'Differential'
		When 'L' Then 'Transaction Log'
	End As BackupType
	,S.Server_Name
FROM 
	msdb.dbo.BackupSet S
JOIN 
	msdb.dbo.BackupMediaFamily M
ON 
	S.Media_Set_ID = M.Media_Set_ID
WHERE 
	S.Database_Name In (SELECT Name FROM Sys.Databases)
AND	S.Backup_Start_Date > Convert(Char(10), (DateAdd(Day, - @Dias, GetDate())), 121)
-- Para listar todos os databases sem o parametro de dias,
-- comente a linha as duas linhas acima (S.Database... e S.Back...) e troque pelas linhas abaixo.
/*
	S.Database_Name = 'MyDataBase'
*/
ORDER BY 
	S.Backup_Start_Date DESC, S.Backup_Finish_Date

Query últimos Backups – SQL Server Pt. 1

Neste post irei mostrar uma query que mostra o último Backup realizado em todos os databases, exceto o tempdb, pubs, northwind e o model.

* Em um próximo post irei mostrar o tamanho dos backups realizados e onde os backups foram salvos.

A query retorna os seguintes campos:

Servidor – Instancia: retorna o servidor (hostname) e a instância dos databases.
Database_Name: nome do database
Full_Dias: diferença de dias do último backup FULL realizado.
Full_Termino: data e hora do término do backup FULL.
Full_Tamanho_MB: tamanho do backup FULL em MB.
Diff_Dias: diferença de dias do último backup DIFFERENTIAL realizado.
Diff_Termino: data e hora do término do backup DIFFERENTIAL.
Diff_Full_Diff: diferença dos dias entre o backup FULL e o DIFFERENTIAL.
Diff_Tamanho_MB: tamanho do backup DIFFERENTIAL em MB.
Tran_Minutos: diferença em minutos do último backup LOG realizado.
Tran_Termino: data e hora do término do backup LOG.
Tran_Tamanho_MB: tamanho do backup LOG em MB.

A query é essa:

USE master 
GO
SET NOCOUNT ON
GO

DECLARE @Server varchar(40)

Set @Server = Convert(varchar(35), ServerProperty('machinename')) + '\' + @@ServiceName

BEGIN TRY
	SELECT 
		 @Server As 'Servidor - Instancia'
		,FR.Database_Name
		,DateDiff(Day, FR.Backup_Finish_Date, GetDate()) As 'Full_Dias'
		,FR.Backup_Finish_Date  As 'Full_Termino'
		,Convert(Char,Convert(Numeric(12,2),(FR.Backup_Size / 1024 / 1024))) As Full_Tamanho_MB
		,DateDiff(Day, DR.Backup_Finish_Date, GetDate()) As 'Diff_Dias'
		,DR.Backup_Finish_Date  As 'Diff_Termino'
		,Case 
			When DR.Backup_Finish_Date Is Null Then Null
			Else DateDiff(Day, FR.Backup_Finish_Date, DR.Backup_Finish_Date)
		End As 'Dias_Full_Diff'
		,Convert(Char,Convert(Numeric(12,2),(DR.Backup_Size / 1024 / 1024))) As Diff_Tamanho_MB
		,DateDiff(Minute, TR.Backup_Finish_Date, GetDate()) As 'Tran_Minutos'
		,TR.Backup_Finish_Date As 'Tran_Termino'
		,Convert(Char,Convert(Numeric(12,2),(TR.Backup_Size / 1024 / 1024))) As Tran_Tamanho_MB
	FROM 
		msdb.dbo.backupset As FR
	LEFT OUTER JOIN
		msdb.dbo.backupset As TR
	ON
		TR.Database_Name = FR.Database_Name
	AND TR.Type = 'L'
	AND TR.Backup_Finish_Date =
		(
			(SELECT	Max(Backup_Finish_Date) 
			FROM	msdb.dbo.backupset B2 
			WHERE	B2.Database_Name = FR.Database_Name 
			And B2.Type = 'L')
		)
	LEFT OUTER JOIN
		msdb.dbo.backupset As DR
	ON
		DR.Database_Name = FR.Database_Name
	AND DR.Type = 'I'
	AND DR.Backup_Finish_Date =
		(
			(SELECT Max(Backup_Finish_Date) 
			FROM	msdb.dbo.backupset B2 
			WHERE B2.Database_Name = FR.Database_Name 
			  And B2.Type = 'I')
		)
	WHERE
		FR.Type = 'D' -- full backups only
	AND FR.Backup_Finish_Date = 
		(
			SELECT Max(Backup_Finish_Date) 
			FROM msdb.dbo.backupset B2 
			WHERE B2.Database_Name = FR.Database_Name 
			And   B2.Type = 'D'
		)
	And	FR.Database_Name In (SELECT name FROM master.dbo.sysdatabases) 
	And	FR.Database_Name Not In ('tempdb','pubs','northwind','model')

UNION ALL

	SELECT
		 @Server
		,Name
		,NULL
		,NULL
		,NULL 
		,NULL
		,NULL 
		,NULL
		,NULL
		,NULL
		,NULL
		,NULL
	FROM 
		master.dbo.sysdatabases As Record
	WHERE
		Name Not In(SELECT DISTINCT Database_Name FROM msdb.dbo.backupset)
	And Name Not In('tempdb','pubs','northwind','model')
	ORDER BY
		1, 2
END TRY
BEGIN CATCH
	SELECT message_id, text FROM sys.messages WHERE message_id = @@ERROR AND language_id = 1033
END CATCH

Criar rotinas de backup através do Transact-SQL

Hoje em dia é de suma importância ter rotinas para realizar os chamados backup’s diários.

A maneira mais prática de se fazer um backup diário é através de uma rotina (script) Transact-SQL.

Neste exemplo eu vou montei uma Procedure responsável por efetuar o Backup das bases!
Ele possui um parâmetro. Este parâmetro é o caminho aonde vai ser salvo os arquivos.BKP.
Não precisa se preocupar com um arquivo existente. Esta procedure verifica e deleta arquivo existente.

Dica: Faça um Job para executar esta procedure diariamente.

Stored Procedure

USE [Master]

If Exists(SELECT * FROM Sys.Procedures WHERE Name = ‘PROC_BACKUP’)
DROP PROCEDURE PROC_BACKUP
GO

CREATE PROCEDURE PROC_BACKUP
(

— Folder para os arquivos de backup. Caso nenhum parâmetro seja passado, ele assume o parâmetro default
@Path varchar(256) = ‘C:\BackUp\’

)
AS

EXEC SP_CONFIGURE ‘Show Advanced Options’,1
RECONFIGURE
EXEC SP_CONFIGURE ‘XP_CMDSHELL’, 1
RECONFIGURE

— Declara variáveis
DECLARE @Name                varchar(50)       — Nome da base
DECLARE @FileName       varchar(256)      — Nome do arquivo
DECLARE @Cmd                 varchar(120)      — Comando para apagar arquivo de backup existente

— Declara cursor
DECLARE db_Cursor CURSOR FOR

SELECT
Name
FROM
Master.dbo.SysDataBases
WHERE
Name Not In(‘master’, ‘model’, ‘msdb’, ‘tempdb’)

OPEN db_Cursor
FETCH NEXT FROM db_Cursor INTO @Name

While @@FETCH_STATUS = 0
Begin
Set @FileName = @Path + @Name + ‘.BAK’
Set @Cmd = ‘DEL ‘
Set @Cmd = @Cmd + @FileName

EXEC XP_CMDSHELL @Cmd

— Realiza Backup
BACKUP DATABASE @Name TO DISK = @FileName

FETCH NEXT FROM db_Cursor INTO @Name

End

CLOSE       db_Cursor
DEALLOCATE  db_Cursor

EXEC SP_CONFIGURE ‘XP_CMDSHELL’,0

Continuar a ler

SQL Server – Backup Restore em disco remoto

Uma das maiores dificuldades que percebo em alguns fóruns relacionados a SQL Server é o Backup/Restore em disco remoto, bom, antes de tudo, é preciso dizer que até a versão disponível completa atual (SQL Server 2008), o mesmo não reconhece discos remotos sozinho!, e caso você já tenha tentado realizar alguma ação e se deparou com este erro, provavelmente você foi no meu computador e mapeou a pasta correto? Se sim, ERROU! Você mapeou o disco remoto para o SO e não para a instância SQL Server.

Para solucionar este problema, é preciso “enganar” o SQL Server com um simples comando DOS. O código abaixo realiza isso, lembrando que ao final do código , o parâmetro para o xp_cmdshell estará ativo, o que é um risco imenso a seu servidor, após utiliza-lo, lembre-se de desativa-lo.

GO
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
EXEC xp_cmdshell 'net use p: \\IpDoServidorRemoto\DiscoRemoto$ /user:DominioDeRede\UsuarioDeRede Senha'

By: Fabrizzio Caputo

SQL Server – Verificar o ultimo Backup Full

Olá,

Uma das principais atividades de um DBA é cuidar do backup de uma base e garantir que esses dados estejam seguros, segue abaixo uma query bem util, onde é possivel verificar a ultima data em que o mesmo foi realizado.

SELECT 
	 T1.Name As DatabaseName
	,Coalesce(Convert(varchar(12), MAX(T2.Backup_Finish_Date), 101),'Not Yet Taken') As LastBackUpTaken
	,Coalesce(Convert(varchar(12), MAX(T2.User_Name), 101), 'NA') As UserName
FROM 
	sys.sysdatabases T1 
LEFT OUTER JOIN 
	msdb.dbo.backupset T2 
ON 
	T2.Database_Name = T1.Name 
GROUP BY 
	T1.Name
ORDER BY 
	T1.Name

A query é bem util pois pode ser utilizada para relatorios online por exemplo.

By: Fabrizzio Caputo