Solved

MSSQL Log Backup error

Posted on 2011-09-21
5
881 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 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