Solved

MSSQL Log Backup error

Posted on 2011-09-21
5
868 Views
Last Modified: 2012-05-12
Hi, i am trying to backup a transaction log of an MS SQL database.

However i keep getting the below error when running the backup.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Backup failed for Server 'servername'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


I am running the below script to see when the recent backups were run and the most recent database backup was done on the 16th. However there was no log or database backups done after that it just errors with the above error.

I am using the windows administrator user to run the backup.

The server is a Windows Server 2008 R2 server running MS SQl server 2008 R2.

If anyone has any ideas as to what is causing this that would be great.

PS it was working last week and nothing has been changed.

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY  
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date
0
Comment
Question by:brasso_42
  • 3
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36575238
The following error says it all:
BACKUP LOG cannot be performed because there is no current database backup

There has not been a successful FULL backup done of the database.  Until that happens, you cannot do any Transaction Log backups.

0
 
LVL 1

Author Comment

by:brasso_42
ID: 36578937
There was a successful full backup of the database done on the 16th.
no backups were done after that until the 19th which brings up the above error.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36580169
I guess SQL Server thinks otherwise.

Why don't you do another full backup and then you can do all the Transaction Log backups you want?
0
 
LVL 1

Author Comment

by:brasso_42
ID: 36942052
that fixes it for a day or two and then it goes back to saying no full backup found.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 36942946
I am afraid I have no idea.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question