June 30, 2015 at 5:30 pm
Hi!
One of my servers had a disk crash. Unfortunately it was the disk storing the .mdf file.
I have the transaction log plus full backup and differential backups.
I'm trying to get back the information that is not in the backups, but (hopefully) are in the transaction log (a Tail log backup).
But I'm not having any progress. Any idea?
Thanks!
July 1, 2015 at 2:37 am
If the DB was in full recovery, and you still have the log file, and you can 'hack attach' the remaining files to a SQL instance of the same version, then you should be able to take a tail log backup (BACKUP LOG ... WITH NO_TRUNCATE)
What part are you struggling with?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2015 at 10:58 am
I would say, I'm struggling with the exact "how to...".
After some research, I've come up with this solution:
For this example, the database name is TEST and the backup file is test.bak, located in c:\test.bak
1.Stop SQL Server
2.Copy the .LDF file to a safe place.
3.Start SQL Server
4.Restore the DB with the full backup and the differentials. As show below:
RESTORE DATABASE [test] FROM DISK = N'C:\test.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE DATABASE [test] FROM DISK = N'C:\test.bak'
WITH FILE = 3, NOUNLOAD, REPLACE, STATS = 10
GO
Important: Replace the number in WITH FILE = # , it should be replaced with the position number of the backup in the backup file.
Where the backup numbers wanted are those from the FULL and the last Differential.
The command to get this info is:
RESTORE HEADERONLY FROM DISK = 'C:\test.bak'
5.Start SQL Server
6.Copy the .LDF file (don’t move, just copy, keep the LDF file in a safe place) to the local where the new LDF is. The system will ask if you want to replace. Replace the file.
7.Start SQL Server
8.Optional: If you have a sparing space in the server, make a copy of the backup file to a safe place. Since we are already dealing with a ‘disaster’, better safe than sorry.
9.Execute the following:
BACKUP LOG [test] TO DISK=N'C:\test.bak' WITH NO_TRUNCATE
RESTORE DATABASE [test] FROM DISK=N'C:\test.bak' WITH FILE = 1, NORECOVERY
RESTORE DATABASE [test] FROM DISK=N'C:\test.bak' WITH FILE = 3, NORECOVERY
RESTORE LOG [test] FROM DISK=N'C:\test.bak' WITH FILE = 4, RECOVERY
Important: As mentioned in step 4, replace accordingly WITH FILE = #
Voilá! The database should be ok now.
July 10, 2015 at 3:21 pm
5.Start SQL Server
Should step 5 is to Stop SQL Server or Start?
Regards,
SQLisAwe5oMe.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply