| Author |
 Topic  |
|
Kristen
Test
United Kingdom
22191 Posts |
 Posted - 12/07/2010 : 14:59:36
|
I'm curious on that one:
I do a RESTORE to overwrite TEST database from FullBackup_01.BAK at 01-Jan-2010 09:00:00
I work on TEST database ...
I do a RESTORE to overwrite TEST database from FullBackup_02.BAK at 07-Jan-2010 09:00:00
Can I then do a TLOG "tail" backup, and restore FullBackup_01.BAK and the TLog(s) up to 07-Jan-2010 08:59:59 ??
Or was the Tlog killed with the Restore of FullBackup_02.BAK? |
Edited by - Kristen on 12/07/2010 15:01:04 |
 |
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/07/2010 : 15:51:23
|
If you restore over an existing database it is equivalent to dropping the existing database and restoring the new one. Nothing survives
--
Gail Shaw
SQL Server MVP |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/07/2010 : 15:56:08
|
quote:
Originally posted by Michael Valentine Jones
They restore over the test DB ever week, and the changes made to the test DB were wiped out by the latest restore.
I think that got asked over at SSC a week of so back and I told them not recoverable. Guess they want a second opinion.
--
Gail Shaw
SQL Server MVP |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/07/2010 : 15:57:11
|
Thanks Gail. Whilst this seems entirely reasonable, I wonder if MS shouldn't provide a "save you from yourself" bail out to this scenario - not truncating the TLog, or somesuch ...
Even intentional RESTOREs like this could subsequently go "I should not have done that ..." |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/07/2010 : 15:58:10
|
"Guess they want a second opinion"
So give it to them, over here?  |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 12/07/2010 : 15:59:54
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by Michael Valentine Jones
They restore over the test DB ever week, and the changes made to the test DB were wiped out by the latest restore.
I think that got asked over at SSC a week of so back and I told them not recoverable. Guess they want a second opinion.
--
Gail Shaw
SQL Server MVP
The thread I referenced was from SSC two days ago, and you posted that answer there, so maybe that is the one you are thinking of.
CODO ERGO SUM |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/07/2010 : 17:38:23
|
quote:
Originally posted by Kristen
Thanks Gail. Whilst this seems entirely reasonable, I wonder if MS shouldn't provide a "save you from yourself" bail out to this scenario - not truncating the TLog, or somesuch ...
Why? Let's stop people dropping databases, deleting data or saving over existing files in apps like Word while we're at it.
There's safety checks already. You have to use WITH replace to overwrite a database where the DB is in full recovery (or bulk logged) and the log has not been backed up WITH NORECOVERY. If you try without that option (which is not the default), there's a very obvious error message.
We can't stop people been stupid and I am not in favour of several more 'are you absolutely, totally, completely sure you really want to do this' type checks.
If the overwritten database had anything of value in it, it should have been backed up. That's the failure, the lack of backups not MS having not provided 20 more safety nets to prevent people from themselves.
--
Gail Shaw
SQL Server MVP |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/07/2010 : 17:40:26
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by GilaMonster
quote:
Originally posted by Michael Valentine Jones
They restore over the test DB ever week, and the changes made to the test DB were wiped out by the latest restore.
I think that got asked over at SSC a week of so back and I told them not recoverable. Guess they want a second opinion.
The thread I referenced was from SSC two days ago, and you posted that answer there, so maybe that is the one you are thinking of.
I fail at reading obviously. I thought I'd seen it here too....
--
Gail Shaw
SQL Server MVP |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 12/07/2010 : 23:49:15
|
We restore test databases on request by developers from production database backups and have jobs setup for the most common requests.
We have always warned developers that these test databases are subject to being overwritten at any time, there are no backups, and that is is up to them to save the code for any stored procedures, tables, functions, etc. that they develop in those databases.
The head of development is 100% behind this and if anyone complained about something being lost, the first question he would ask is why the code wasn't in source control.
CODO ERGO SUM |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/08/2010 : 03:46:04
|
Yes, I do agree "Are you sure you are sure?" (recurse) messages are a waste of time.
But I can imagine well intentioned backup systems failing too.
FWIW our homegrown backup system automatically starts backing up a database from the moment it is created - unless the database name starts with the prefix "RESTORE_" - so I would like to think that it would not happen here ... but we do bodges and work-arounds at times, nonetheless ... though they almost always end with blood on the carpet ... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 02/01/2011 : 19:56:02
|
Sorry, s**t happens and we happen to not have a log file or backup, Jim
Enjoyed the reply from dekj:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75072
"I found myself in the position of having an MDF file (and an NDF file, a expansion file that placed several tables on another drive), with no LDF log file and no backup. Things happen in life, and this happened to me.
...
I also found the “SQL Server experts” that answered the calls of help for this situation in internet forums were of no help. They mainly seemed to respond by describing the hapless users in this situation as idiots residing in a hopeless situation."
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 02/01/2011 20:25:23 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 02/02/2011 : 00:40:57
|
hmm... I wonder what the POS in POSProd stands for. :)
elsasoft.org |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 02/02/2011 : 01:00:21
|
Grrr, I find that insulting.
p.s. "I found that there were ways of recreating the log file in SQL Server 2003 and 2005, but those mechanism were taken out of SQL Server 2008"
--
Gail Shaw
SQL Server MVP |
Edited by - GilaMonster on 02/02/2011 01:04:49 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
|
Topic |
|
It's dead, Jim
