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

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
ljhodgettAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
ljhodgettAuthor Commented:
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
 
ljhodgettAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.