Backup of transaction log fails

[magmo]

Hi


I try to do a backup of the transaction log on a sql server 2000. I run this script in the query analyzer...


BACKUP LOG MyTestDB
TO MyTestDB_Backup
WITH NO_TRUNCATE
GO


But then I get this error message...

Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'MyTestDB_Backup'. Update sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.



What does this mean, am I doing something the wrong way here. I'm pretty lost and would appreciate all the help I can get.



Regards


M

 

[gradley]

The system is looking for a backup device called MyTestDB_Backup.

You need to create the backup device first in the "backup" folder under "Management" in Enterprise Manager. The backup device you create can just point to a folder on your local or network drive.

 

[SQLBill]

Look up the command sp_addumpdevice in the BOL.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[magmo]

Hi



I got it to working by changing the script to..

BACKUP LOG MyTestDB
TO DISK = 'c:\test\MyTestDB_Backup'
WITH NO_TRUNCATE
GO


But the file that is created can't be the full log file, beacuse when I look at the online logfile it is 2.8 mb and the newly created logfile from the back up its only 60kb




Why is that, and how do I do a backup of the complete logfile with the database still online?


Regards


M

 

[SQLBill]

Take a look again at the size of the log file (Use Enterprise Manager, expand to the database. Single click on the database name, go to View and select TASKPAD. Look at the graph for the database files.

Each file (the data file and the log file) has two things that take up space. One is used space and the other is unused space. WIth the log, when it's backed up or meets checkpoints some of the data is removed. This leaves free space for the log to be filled again. You can get rid of the free space by shrinking the log, but it will just grow again when it needs space. When you back up the log (or data file) the free space doesn't get backed up.

-SQLBill

 

[magmo]

Hi


No I'm sorry by the newly created log file doesnt include all the information that I need. When I looked in this logfile I only saw actions that where made today. I need to see all the other actions that have been made in the past.

So I dont see how the logfile could bee the same.



Regards

M

 

[SQLBill]

How often do you make a full backup?
Do you do differential backups?
How often do you do transaction log backups?


The transaction log only keeps transactions that have occured since the last backup.

Is your database recovery mode set for SIMPLE or for FULL? If it's set for SIMPLE, the transaction log can't be backed up and it won't contain much information. SIMPLE causes checkpoints to be set and the log is 'truncated' based on those checkpoints.

-SQLBill

 

[magmo]

Aha, well I did run a backup of the database today. So that would explain the lack of later records.

Th database is set to recovery mode = "FULL".


I did not know that the logfile only contains records since the last backup. Thanks for telling me.


Regards


M