Solved

MSSQL Log Backup error

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

737 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