asked
backing up sql server logs to external drive
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.
There's a couple of options here and it very much depends upon the backup situation with your database. 1. If you are performing regular backups and don't care about the log you could switch the database to simple recovery, perform a checkpoint and shrink the log 2. If you aren't performing regular backups and don't care about the log you could switch the database to simple recovery, perform a checkpoint and shrink the log (I'm really hoping that this database is backed up though otherwise you could be in a world of hurt 3. Use a TSQL command to perform the backup: BACKUP LOG TO DISK = '\Backup\.trn'. IF this doesn't work because SQL can't see the drive then try using the computer name and a UNC path: BACKUP LOG TO DISK = '\\\$\Backup\.trn'. Once backed up you'll be able to shrink the log and perform regular log backups.
Since the logs have never been backed up, write them off. Change the recovery model on the database to simple, and then, as you can, shrink the log back down to a usable size. Then reenable full recovery and set up log backups. That's the best way to go.
