Improve company productivity with a Business Account.Sign Up

x
?
Solved

MSSQL Log Backup error

Posted on 2011-09-21
5
Medium Priority
?
928 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 2000 total points
ID: 36942946
I am afraid I have no idea.
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

601 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