Solved

MSSQL Log Backup error

Posted on 2011-09-21
5
874 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

756 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