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.

Brian S
  • 21
  • 1

2 Answers2

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
mrdenny
  • 27,174
  • 4
  • 41
  • 69
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.

RateControl
  • 1,207
  • 9
  • 20