[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

BACKUP LOG cannot be performed because there is no current database backup.

Posted on 2009-07-02
6
Medium Priority
?
1,300 Views
Last Modified: 2012-05-07
Hi,

Im currently trying to create a job to backup our transaction logs on an hourly bases. When I use the following command: -

BACKUP LOG  [testdata] TO DISK = N'G:\testdata.trn';

It comes up with the following message: -

BACKUP LOG cannot be performed because there is no current database backup

The current backup strategy is a full backup everynight at midnight using the following commands in a job: -

-- database backup file manipulation
EXEC master..xp_cmdshell 'del Z:\MSSQL\BACKUP\ONE_DAY_OLD\testdata_old.bak /Q';      -- delete old copy of backup
EXEC master..xp_cmdshell 'copy Z:\MSSQL\BACKUP\DAILY\testdata.bak Z:\MSSQL\BACKUP\ONE_DAY_OLD\testdata_old.bak';  -- copy last night's backup to ONE_DAY_OLD
EXEC master..xp_cmdshell 'del Z:\MSSQL\BACKUP\DAILY\testdata.bak /Q';      -- delete current copy of backup

-- log file manipulation
EXEC master..xp_cmdshell 'del Z:\MSSQL\BACKUP\ONE_DAY_OLD\testdata_old.trn /Q';      -- delete old copy of backup
EXEC master..xp_cmdshell 'copy Z:\MSSQL\BACKUP\DAILY\testdata.trn Z:\MSSQL\BACKUP\ONE_DAY_OLD\testdata_old.trn';  -- copy last night's backup to ONE_DAY_OLD
EXEC master..xp_cmdshell 'del Z:\MSSQL\BACKUP\DAILY\testdata.trn /Q';      -- delete current copy of backup

BACKUP DATABASE [testdata] TO  DISK = N'Z:\MSSQL\BACKUP\DAILY\testdata.bak' WITH  NOINIT ,  NOUNLOAD ,  NAME = N'testdata_daily',  NOSKIP ,  STATS = 10,  NOZORMAT
BACKUP LOG  [testdata] TO DISK = N'Z:\MSSQL\BACKUP\DAILY\testdata.trn';

backup log [testdata] with truncate_only;

dbcc shrinkfile(testdata_log,0);

Am I right in saying that if you use backup log [testdata] with truncate_only; it breaks the backup chain and hence I am getting this message? Also that the shrinkfile command will commit the transactions of the log into the main database file?

Many Thanks
Lee
0
Comment
Question by:ljhodgett
  • 4
  • 2
6 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24761635
Kindly take a Full Backup and then try doing your Log Backup.

BACKUP LOG  [testdata] TO DISK = N'G:\testdata.trn';

And after that have regular Transactional Log Backups with reduced time intervals as of now.

http://msdn.microsoft.com/en-us/library/aa173551(SQL.80).aspx

Hope this helps
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24761645
>> Am I right in saying that if you use backup log [testdata] with truncate_only; it breaks the backup chain

Depends upon situation.
1. Using TRUNCATE_ONLY breaks the Backup chain if you do it before your Full Backup or Differential Backup.
2. Using TRUNCATE_ONLY breaks the Backup chain if you do it after your Full Backup or Differential Backup.

>> and hence I am getting this message?

No..

>> Also that the shrinkfile command will commit the transactions of the log into the main database file?

It will Discard the uncommitted transactions and it Wont commit.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24761652
The error you mentioned "BACKUP LOG cannot be performed because there is no current database backup" will happen:

When you change the database to simple to truncate the log, then back to full recovery. You have to do a manual full backup before you can do a log back up again.

Hence do a Full Backup manually now as mentioned earlier and this would solve this problem
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:ljhodgett
ID: 24761823
Hi,

Sorry the database is set as full recovery model.

I've run the following on the development server: -

BACKUP DATABASE [testdata] TO  DISK = N'C:\testdata.bak' WITH  NOINIT ,  NOUNLOAD ,  NAME = N'testdata_daily',  NOSKIP ,  STATS = 10,  NOFORMAT
BACKUP LOG  [testdata] TO DISK = N'C:\testdata.trn';

and it completes sucessfully. I can backup the transaction logs everytime afterwards using : -

BACKUP LOG  [testdata] TO DISK = N'C:\testdata.trn';

As soon as I do the following it comes up with the error messages when trying to backup the transaction logs: -

backup log [testdata] with truncate_only;

dbcc shrinkfile(testdata_Log,0);

Best Regards
Lee
0
 

Author Comment

by:ljhodgett
ID: 24761926
Hi,

I've found that the following command once executed give me the error message: -

backup log [testdata] with truncate_only;

Is this line strickly necessary?

Many Thanks
Lee
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24761968
>> backup log [testdata] with truncate_only;

Above statement is not required if you intend to run the below command after your Full Backup or Differential Backup.

dbcc shrinkfile(testdata_Log,0);
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

591 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