Solved

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

Posted on 2009-07-02
6
1,279 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
[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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 500 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

734 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