2

Let me start off by saying that I am a SQL Server newbe so please be nice.

Background: I have inherited a project which requires a database export. I quickly found out that the log files have never been backed-up and therefore has grown to over 37 GB. This leaves only a few GB of storage left on the server which is not enough space to allow me to export.

Issue: I need to back-up the log files so I can truncate the file and get the space back. However, there is not enough room on the C: drive to perform this backup. We have installed a USB external drive but I can't get to external drive from the SQL Server backup wizard. The only option is the C: drive when I try to add a destination. My question is, how can I backup the log file to this external drive?

Database and Server Information:

SQL Server 2003 (Enterprise Manager Version 8) MS Server 2003 External USB Drive storage has over 1.8 TB of free space

Any help would be greatly appreciated.

1
  • 1
    SQL Server 2003? Must be a typo -- I know SQL Server 2000, 2005, 2008, 2008 R2.
    – LazyOne
    Jun 23, 2011 at 19:29

2 Answers 2

1

I'm assuming that you don't actually need point in time recovery of the data in the transaction log. Simply change the recovery mode of the database from full to simple. This will mark all the entries in the transaction log as overwritable. At that point you can shrink the transaction log to a more reasonable size.

ALTER DATABASE YourDatabase 
SET RECOVERY SIMPLE
0
USE DatabaseName
GO
BACKUP LOG DatabaseName
TO DISK = 'Location\DatabaseName_LOG.trn'
          WITH COMPRESSION,
               NAME = 'Log backup of DatabaseName’
GO

That should backup the transaction log to the external drive. Then you can proceed to trim the fat from the log. *Note "with compression" is only valid on SQL 2008 and above.

2
  • Note that WITH COMPRESSION was introduced in SQL 2008 and thus is not an option for earlier versions.
    – squillman
    Jun 23, 2011 at 19:47
  • Yes of course.. Jun 23, 2011 at 19:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .