Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2013-08-21 : 17:51:42
|
Hi gang ... long time no speak :)I have been asked to move a DB to new hosting, and from SSMS=10.50.2500 SSMS=10.50.4021, and from dedicated server in data centre to a virtual machine on the cloud.Went to copy the latest FULL Backup to the new Cloud VM and found it was a month old ... on checking it appears that the TLog backup has been failing for a month (which, due to the way the tasks are scheduled, has prevented the FULL backup running and, luckily!, old stale backups have not been cleared down).I ran a TLog backup and got:"BACKUP detected corruption in the database log. Check the errorlog for more information.BACKUP LOG is terminating abnormally."SQL Error log has:"Backup detected log corruption in database MyDatabase. Context is FirstSector. LogFile: 2 'E:\MSSQL\Log\MyDatabase.ldf' VLF SeqNo: xd5440 VLFBase: x177040000 LogBlockOffset: x19365fe00 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x1000004 LogBlock.StartLsn.Blk: x5b50000 Size: xd PrevSize: xeBackup Error: 3041, Severity: 16, State: 1.Backup BACKUP failed to complete the command BACKUP LOG MyDatabase. Check the backup application log for detailed messages."I ranUSE MyDatabaseGODBCC CHECKDB WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY, ALL_ERRORMSGS GO and got no output at all - no errors. (It took 12 minutes to run, so I assume it did do something!)DBCC was run on the original machine, I've copied and restored (without error) a FULL backup from earlier today onto the new Cloud server and then a "final" DIFF backup (after the DIFF backup got to 100% there was then a further 10 minute delay - I am assuming that was committing transactions in the DIFF - the DIFF file was 3GB, which is a huge amount for a single day's differences [compared to normal running])So ... hopeful that MDF is OK ... I was going to try a Single File Attach next. I took the restored DB (on new Cloud server) offline and am now copying the 50GB MDF file somewhere safe, plus the 52GB LDF file too (I guess its been stockpiling transactions for a month to have grown that big!!) in case I need to have a second go.Any advice or suggestions would be appreciated, thanks. |
|
|
Kristen
Test
22859 Posts |
Posted - 2013-08-21 : 18:17:54
|
Hmmm ... I stupidly took the DB offline (ready for copying the files to a safe location) rather than Detaching it ... I copied the files and then attempts a Single File Attach and it appeared to work:EXEC sp_attach_single_file_db 'MyDatabase', 'F:\MSSQL\DATA\MyDatabase.mdf' File activation failure. The physical file name "G:\MSSQL\LOG\MyDatabase.ldf" may be incorrect.New log file 'F:\MSSQL\DATA\MyDatabase.LDF' was created.I had already renamed MyDatabase.ldf so it would not be found by sp_attach_single_file_db, looks like this might have worked (other than that the LDF file is now on the wrong drive).I'm going to do a DBCC and then a Backup/Restore to get everything in the right place |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2013-08-22 : 04:25:12
|
This morning I found that the Log Backup task was complaining ... I hadn't realised that sp_attach_single_file_db created Log File as SIMPLE Recovery Model. Fixed that now (and all the palaver for pre-initiaising the Log re: VLFs) ... although that required yet-anther-full-backup to kickstart the TLogging.Blinking slow the virtual machine that the client has chosen ... no wonder its cheaper than what they had before; backup is 16.581 MB/s instead of the 62.592 MB/s they had before.How to make a drama into a 3-part-mini-series eh?  |
|
|
|
|
|
|
|
|
Corrupt LOG file - sp_attach_single_file_db