Okay I have a backup of an entire VM which runs SQL 2000.
For the particular database of interest:
I have an MDF modified 3/13
I have an LDF modified 4/1
Now I am not a SQL master by any means, unless you count being able to make sense of queries lol
For my objective I need to take these files and either:
Roll back the database to 3/13 essentially purging all these uncommitted transactions from the log bring the database back to where it was on 3/13 when that MDF was last modified.
Or second some how attach just the MDF alone so SQL doesn't even know the transactions in the LDF even exist because it doesn't have access to those logs.
I could be wrong but I'm thinking the first is easier except I don't know how to do it.
My endless searching gives me nothing but instructions on how to force SQL to commit all transactions currently sitting in the logs. That's one of the few things I already know how to do if I had to.
And in any case is certainly the reverse of what I'm trying to achieve with these logs.
If you have the MDF file you might be able to make use of this procedure call: sp_attach_single_file_db. The details are in Books Online, the SQL Help file.
Your VM backup includes MDF of 3/13 and LDF of 4/1 ? What date was the VM backup made?
I'm thinking that the discrepancy on dates is because SQL doesn't change the timestamp on MDF / LDF files each time there is an update, the date/time is set when SQL is closed (unlikely in your situation) or if something else significant happens to the file - e.g. the file is extended.
So assuming both files are on the same VM backup then I reckon both MDF and LDF are 4/1 (or maybe even later), but if they came from the same VM backup they will at least both be "in step".
You won't (AFAIK) be able to somehow rollback "stuff" that is in the LDF.
What would be better is a SQL Backup of the database taken at that time. If that were available, and also IF the database is in Full Recovery AND you have Log Backups from that time you would be able to restore-forwards the Log Backups using STOPAT to restore UP TO a moment-in-time. Absent that all your could restore to would be the date/time of a FULL backup, or a FULL backup and then a subsequent DIFFERENTIAL backup (which was based on the FULL backup - i.e. no subsequent FULL backup had been taken)
Do you per chance have any SQL Backups in addition to the VM one?
Possible point: If SQL backups WERE being taken regularly then MAYBE? they would have been on the local drive AND would also have been included in the VM backup.
In which case perhaps:
Restore the VM backup
Find the SQL Database Backup file (and appropriate subsequent LOG backups) of appropriate date/time, and Restore them. Job Done
Backup was done 4/1 and yes they are on the same backup.
I have weekly full / daily incremental's going 90 days back. but I don't have the storage beyond that.
Just a month end full.
I have a new server to migrate to on 2012 if I ever get the time to do it so I can dump this damn box.
Having to restore to 2005, backup again and restore to 2012 as part of the data migration process doesn't make my life any easier. However it did work in testing... anyway that's a whole other matter lol
The double upgrade involved in moving from SQL 2000 to SQL 2012 is inconvenient, I'll admit. The benefits, though, make it worth while and the upgrading is a one time effort. I'd encourage you to bite the bullet and do it.
You haven't got the incremental SQL backups, from that time, as part of the VM backup? i.e. in the 4/1 backup do you, also, have SQL backups (from the previous 90 days, [rolling])?
No. Normally I have SQL itself running it's own backups on the server on top of the VM backups (which are app aware) but I was fighting with mgmt. for a new storage array at the time so I had to kill those local backups for a few months....
I guess I can just try that sp_attach_single_file_db and see what happens.....
Worth a go ... but I'm very sceptical that that will help. The MDF and LDF are "as of that moment", so just restoring the MDF will only get you whatever was committed and, given that the VM backup was a moment-in-time, perhaps?? there is a chance that the MDF was inconsistent at that time. Perhaps when you restore the VM then SQL sorts out (rolls back / forwards) as necessary to get a consistent state, my worry would be that without the LDF it wouldn;t be able to do that.
I suppose DBCC CHECKDB would tell you whether the resulting MDF and empty, new, LDF had any errors - but I doubt, even then, that you would get an "earlier" state.
Good time to remind them of their short-sighted decision ...
they would have been on the local drive AND would also have been included in the VM backup.