February 16, 2009 at 8:41 am
What's the date of the most recent FULL ?? 5 months ago ?
To turn on transaction logging, so that you capture changes from now going forward, you need to set the database to FULL RECOVERY. This won't help your current problem, but will help in the future.
Then backup the transaction logs regularly .... say every hour. You also need to schedule regular FULL backups .... every night, or if the DB is huge and you don't have time, then every weekend, and run DIFFERENTIALS every night.
Go to Books Online (BOL) and read up on RECOVERY MODEL, and backup & recovery topics, disaster recovery etc...
February 16, 2009 at 9:07 am
I just saw Gail's post, I guess that's why I can't get this to work >>>
--this needs to run in 2 steps
--step1
GO
CREATE DATABASE TestRecov_SSC
GO
ALTER DATABASE TestRecov_SSC SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE TestRecov_SSC SET RECOVERY FULL
GO
USE TestRecov_SSC
GO
SELECT * INTO Data FROM master.sys.syscolumns
GO
SELECT COUNT(*) FROM Data
--11268
GO
BACKUP DATABASE [TestRecov_SSC] TO DISK = N'C:\TestRecov.bak' WITH NOFORMAT, INIT,
NAME = N'TestRecov_SSC-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
WAITFOR DELAY '00:00:05'
GO
DELETE FROM Data WHERE name like '%e%'
--8428 rows deleted
GO
SELECT GETDATE() AS PointInTimeRestore
GO
SELECT COUNT(*) FROM Data
--2840
GO
--step 2 change the stop at value to your current run time and execute (sorry no time to make a temp table and dynamic sql)
USE master
GO
RESTORE DATABASE [TestRecov_SSC] FROM DISK = N'C:\TestRecov.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
USE TestRecov_SSC
GO
SELECT COUNT(*) FROM Data
--11268 back in business 5 seconds ago
GO
USE master
GO
BACKUP LOG [TestRecov_SSC] TO DISK = N'C:\Test_Recov_Logs' WITH NOFORMAT, NORECOVERY, INIT,
NAME = N'TestRecov_SSC-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
USE master
GO
--restore at 2009-02-16 10:50:14.720
RESTORE DATABASE [TestRecov_SSC] FROM DISK = N'C:\TestRecov.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [TestRecov_SSC] FROM DISK = N'C:\Test_Recov_Logs' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'2009-02-16T10:58:56'
GO
USE TestRecov_SSC
GO
SELECT COUNT(*) FROM Data
--still 11268 .... expected 2840
--cleanup
USE master
GO
DROP DATABASE TestRecov_SSC
February 17, 2009 at 12:02 am
Ninja's_RGR'us (2/16/2009)
I just saw Gail's post, I guess that's why I can't get this to work >>>
You need to backup the log before the restore, not after. After a restore there's nothing much left in the log as it's replaced during the restore process.
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
February 17, 2009 at 5:34 am
Ya, I figured as much once I saw what happened with the log restore.
I know it didn't work, but I could really explain it to someone.
Thanks for filling the blanks Gail.
So it's back to you're screwed I guess.
I just hope it's not an unforgivable error.
February 17, 2009 at 7:55 am
Just a shot in the dark.... if you have any kind of system level backup you can get your .mdf and .ldf files from...... you could then just detach/attach.
Tim White
February 17, 2009 at 9:57 am
2 Tim 3:16 (2/17/2009)
Just a shot in the dark.... if you have any kind of system level backup you can get your .mdf and .ldf files from...... you could then just detach/attach.
Wouldn't the databases have to be detached prior to a filesystem backup being able to access the .mdf/.ldf in order to back them up?
ie, similarly to how you'd have to detach a database in order to "free" the mdf so you could take a copy of it
February 17, 2009 at 10:09 am
I'm trying not to get caught up in the shoulda, woulda, coulda's but only thinking about any and all possibilities to get the data back.
Maybe there is a copy of the .mfd and .ldf files on a tape he didn't know about. Stranger things have happened.
as stated - "shot in the dark".
Tim White
February 18, 2009 at 2:44 am
February 18, 2009 at 8:47 am
Hi,
if they were backing up the file system with any kind of an open file agent
AND
if there were no (or nearly none) transactions written during the backup
you might get back a more or less corrupt database.
After that there are several scenarios to recover, depending on the actual state of the db...
karl
Best regards
karl
February 18, 2009 at 9:55 am
Magy
sounds like the best advice is check what machine backup software you currently use. Check if there is an open file agent, if so trawl through your recent tapes looking for any traces of an MDF and LDF. You may get lucky
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 24, 2009 at 7:29 am
Hello,
I followed this with interest, what was the outcome, did you manage to find a backup somewhere?
Regards,
D.
February 24, 2009 at 8:10 am
No, I never did find a backup. It wasn't too terrible because the system was only a development system. But it definitely taught me a lesson!
February 24, 2009 at 8:19 am
Magy (2/24/2009)
No, I never did find a backup. It wasn't too terrible because the system was only a development system. But it definitely taught me a lesson!
Follow this mantra:
Backups, Early Backups, Frequent Backups, Verified Backups
😛
February 24, 2009 at 8:23 am
Also add one more thing...
Before any delete or Bulk Update, or DDL statements, Take a Back up...
-Roy
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply