SQL Server – How to Re-create SQL Log Using Detach

sql server

OK, before I start, I know you're not supposed to do this…

I have inherited a SQL Server instance (2008 R2 SP2 x64) which has a DB with a very large transaction log (full mode, with no tlog backups), and the log is in a compressed folder (don't ask). I need to sort this out and was thinking my best approach would be to detach the database (no reason why it shouldn't checkpoint) and re-attach the mdf ignoring the ldf so a new one is created in a proper folder structure. I can then implement a decent backup strategy.

I really don't want to backup this tlog as it's huge, and in a compressed folder so who knows how big it really is. Another option was to simply truncate the tlog but that's deprecated… Maybe backup the tlog to /dev/null (whatever the windows equiv is)?

Obviously I'd do a full backup before anything – these occur daily anyway.

I guess what I'm asking is, what is the safest approach here?

EDIT: actually, I've just thought – if I'm doing daily full backups, would the tlog not be useless immediately after the BU anyway?

Best Answer

No, please do not detach your database and move the log file. Follow the advice from Kin, spaghettidba, and others, but take a full backup first (you'll need to adjust db to your database name, and db_log and db.ldf to the log file logical and physical names, according to sp_helpfile):

BACKUP DATABASE db TO DISK = 'backup location' WITH INIT;

-- set it to single user
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- set the recovery mode to simple
ALTER DATABASE db SET RECOVERY SIMPLE;

-- run checkpoint twice
CHECKPOINT;
CHECKPOINT;

-- shrink the log file to something more reasonable (please not 1 MB):
DBCC SHRINKFILE(db_log, 1024); -- 1 GB

-- take the database offline
ALTER DATABASE db SET OFFLINE;

-- tell the system catalogs that you're moving the file
ALTER DATABASE db MODIFY FILE 
  (NAME = db_log, FILENAME = 'x:\other_drive\db.ldf');

Stop here. This should tell you:

The file "db_log" has been modified in the system catalog. The new path will be used the next time the database is started.

So, move the LDF file (which should now be about 1 GB) to x:\other_drive\ and then bring the database back online and multi-user:

ALTER DATABASE db SET ONLINE;
ALTER DATABASE db SET MULTI_USER;

(To be honest, I'd feel safer restoring the backup WITH MOVE; any time you start moving files around, you're opening yourself up to loss/corruption. But as long as you took your full backup right before you started this process, that should protect you.)

Now, leave the thing in simple recovery if you don't care about transaction logs and don't care to learn why they're important. But I really, really, really strongly recommend you read this post in full:

Try to understand your employer's tolerance for data loss, because I can assure you that most businesses cannot afford to be laissez-faire about this, and 24 hours of loss is absolutely not acceptable. If this is true in your workplace, then set the database back to full recovery, and immediately take a full+log backup to start the log backup chain:

ALTER DATABASE db SET RECOVERY FULL;

BACKUP DATABASE db TO DISK = N'backup_location\db_full.bak';
GO
BACKUP LOG db TO DISK = N'backup_location\db_init.trn';

Then create some kind of job that will take regular log backups throughout the day. Every hour might not be enough; again, make sure the owners of the data understand the trade-offs involved with not backing up the log often enough. I doubt that even an hour of data loss will be acceptable to them. And no, the .trn files will rarely be as large as the LDF itself; this will only happen in extreme scenarios where the log file blew up because of activity that happened since the last log backup.

Also, don't let SQL Server anywhere near compressed drives. Disk space is cheap; have them invest in enough space to adequately store your data and copies related to fault tolerance.

Related Question