SQL Server 2005 Database Log File

You must take in to account those key points before trying Database Log Backup.

# Set Database RECOVERY to FULL.
# Take full BACKUP of the Database.
# Now, you can proceed for Database Log BACKUP.


USE GuestAutomationSystemDB;

ALTER DATABASE GuestAutomationSystemDB SET RECOVERY FULL;

BACKUP DATABASE GuestAutomationSystemDB
TO DISK = 'C:\DBLOG\GuestAutomationSystemDB.bak';

BACKUP LOG GuestAutomationSystemDB
TO DISK = 'C:\DBLOG\GuestAutomationSystemDB_Log.bak';

To truncate an overgrown Database Log File please follow those steps carefully

# Take full BACKUP of the Database.
# Take Database Log BACKUP.
# Shrink Database Log File using DBCC SHRINKFILE Command.
# Take Database Log BACKUP WITH TRUNCATE_ONLY.
# Again, shrink Database Log File.


USE GuestAutomationSystemDB;

ALTER DATABASE GuestAutomationSystemDB SET RECOVERY SIMPLE;

DBCC SHRINKFILE (GuestAutomationSystemDB_log, 1);

BACKUP LOG GuestAutomationSystemDB WITH TRUNCATE_ONLY;

DBCC SHRINKFILE (GuestAutomationSystemDB_log, 1);

ALTER DATABASE GuestAutomationSystemDB SET RECOVERY FULL;

Thanks
A Rahim Khan

Advertisements
  1. Superb post however I was wondering if you could write a litte more on this subject?
    I’d be very grateful if you could elaborate a little bit further. Thank you!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: