July 22, 2010 at 3:39 pm
I had a corrupt database issue to day and I wished to recreate the database from the backup (start from scratch). So, I dropped the original using 'drop database' and then deleted it from the DATA folder. Next I ran a 'create database' command to create a container for the restore. Then I attempted to restore but got an error.
At this point I won't include the error as I am simply wondering if the approach itself is inherently flawed.
July 22, 2010 at 4:04 pm
Its not necessary to drop the old database or create an empty database to restore into. the restore process itself creates the database.
'with replace' will overwrite the existing database of the same name IF the logical files in the backup and the database match, if not you need the with move clause.
---------------------------------------------------------------------
July 22, 2010 at 7:38 pm
George Sibbald, thanks once again. That worked!
I forgot to restore the log and the transactions were rolled back edit* dev environment. I assume this means lost, but do you mind telling me what exactly rolledback and rolled forward means, in db terms?
thanks.
July 23, 2010 at 3:35 am
if you still have all the backups you could do the full restore again and use 'norecovery' option, then you could restore the tran logs, only specifying recovery on the last tran log to restore.
roll back and roll forward is the 'recovery' part of a restore (or bringing database online after a server restart), it brings the database to a consistent state. It guarantees any transactions commited at the point the full backup COMPLETED are written to disk, any transactions started but not committed are rolled back. A full backup contains just the info from the transaction log to be able to complete this process (it does not contain the complete transaction log)
BOL has good info on this, better than my potted version above.
---------------------------------------------------------------------
July 23, 2010 at 11:46 am
roll back and roll forward is the 'recovery' part of a restore (or bringing database online after a server restart), it brings the database to a consistent state. It guarantees any transactions commited at the point the full backup COMPLETED are written to disk, any transactions started but not committed are rolled back. A full backup contains just the info from the transaction log to be able to complete this process (it does not contain the complete transaction log)
BOL has good info on this, better than my potted version above.
I have been searching for answers there too but still can't find the answer to this: When rollback occurs, are any transactions that haven't been 'checkpointed' still in the transaction log in tempdb? Because sometimes my understanding is that rolled back is synonymous with 'deleted'.
July 23, 2010 at 12:40 pm
I like the rule "never do anything you can't undo"
if, after dropping the database your restore had failed ...
... a safer approach would be to: detach db, rename mdf/ndf/ldf, restore
July 23, 2010 at 1:01 pm
doobya (7/23/2010)
I like the rule "never do anything you can't undo"if, after dropping the database your restore had failed ...
... a safer approach would be to: detach db, rename mdf/ndf/ldf, restore
Gave that a whirl and I see the difference. thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply