Say a production DB is used in office hours only.
Scenario
A DB under Full recovery model. NO Tran logs backups have ever been taken. The Tran log has continued to grow and is now huge.
Probably a virtual machine with nightly image/disk snapshots
Solutions
1. Backup the Tran Log. Then arrange for reg. backups.
This Tran backup would be a huge and take a long time to backup/restore, making it unpractical?
Without backup history in msdb tables is there a way to estimate how long a Tran Log backup would take?
Or
2. Change Recovery mode
In a maintenance window (no one using system)
Make the DB read only
?Full backUp Database?
Switch to Simple Recovery
Shrink log file to a size large enough to minimise future growth
Switch to Full recovery mode
Make DB read write
Full Backup
Arrange Reg. Tran backups
Should I take a full backup before I start toggling Recovery mode I would hope it doesn't backup all of the Tran log? Time/Size
Is it safe to do Recovery mode change in this scenario, is it necessary to make it read only?
Thanks
(1) Yes, to me, highly impractical.
(2) No real need to make the db read only:
(A) take diff backup (if you feel safer that way);
(B) Switch to SIMPLE recovery;
(C) Issue a CHECKPOINT;
(D) WAITFOR DELAY '00:00:10' /* or however many secs you prefer*/;
(E) DBCC SHRINKFILE(2, <n_mb_final_log_size>);
(F) Verify shrinkfile worked (EXEC sys.sp_helpfile, for example);
(G) Switch to FULL recovery;
(H) Take a full backup;
(I) Implement tran log backups on a schedule.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 3, 2024 at 9:35 pm
Thanks Scott
Any experience of Disk/snapshots restores on Virtual machines with MSSQL.
Say the Data files on D: drive and Logs on E: restoring snapshots/backups of whole disks via VM, does this work or does MSSQL server find inconsistencies after restoring disks.
February 3, 2024 at 9:36 pm
Sorry, I couldn't tell you that, I'm not a SAN/hardware person, just a DBA.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 4, 2024 at 7:44 am
1) Attach a new hard disk or an empty LUN to the server.
2) Take the SQL Backup first (Full, differential and Trans), move the backup files it to a safer place or to the LUN in step 1.
3) To be on the safe side, take the VM Backup including C, D, E drives.... (do not alter anything on O/S, on drive space etc.), sometimes this may not work.
4) Now follow all the steps given by Scott.
You should be good.
We had a issue in the past where when we tried to store the VM backup, it was throwing an error saying the HD space is not similar to backup size. While coordinating with NS team, we found that some junior network admin(outsourced) made some changes to disk space and the backup was unable to restore. Always rely and give more preference to SQL backup method.
=======================================================================
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply