SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 It's dead, Jim
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 17

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/07/2010 :  14:59:36  Show Profile  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/07/2010 :  15:51:23  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/07/2010 :  15:56:08  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/07/2010 :  15:57:11  Show Profile  Reply with Quote
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 ..."
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/07/2010 :  15:58:10  Show Profile  Reply with Quote
"Guess they want a second opinion"

So give it to them, over here?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/07/2010 :  15:59:54  Show Profile  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/07/2010 :  17:38:23  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/07/2010 :  17:40:26  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/07/2010 :  23:49:15  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/08/2010 :  03:46:04  Show Profile  Reply with Quote
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 ...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/15/2010 :  16:21:51  Show Profile  Reply with Quote
Did a bad update to all the rows in a table by forgetting a where clause, but have no backups because the company didn't want to pay for them, Jim.

http://www.sqlservercentral.com/Forums/Topic1035312-357-1.aspx





CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 12/15/2010 :  16:33:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
Listen to Gail and save yourself some money: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154229

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/06/2011 :  16:33:42  Show Profile  Reply with Quote
Hard disk failure, no backup for last 3 months, can't use database files from data recovery service, so what's next, Jim?

http://www.sqlservercentral.com/Forums/Topic1038086-266-1.aspx





CODO ERGO SUM

Edited by - Michael Valentine Jones on 01/06/2011 17:12:05
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/07/2011 :  11:02:17  Show Profile  Reply with Quote
Dropped a table and don't have a backup, Jim.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154928





CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/01/2011 :  19:56:02  Show Profile  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 02/02/2011 :  00:40:57  Show Profile  Visit jezemine's Homepage  Reply with Quote
hmm... I wonder what the POS in POSProd stands for. :)


elsasoft.org
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/02/2011 :  01:00:21  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/18/2011 :  17:08:29  Show Profile  Reply with Quote
No backups and disk with the data files is dead, Jim.
http://www.sqlservercentral.com/Forums/Topic1066584-391-1.aspx




CODO ERGO SUM
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 02/18/2011 :  17:11:59  Show Profile  Visit robvolk's Homepage  Reply with Quote
But...but...but...THIS GUY: http://www.sqlskills.com/BLOGS/PAUL/post/Unbelievable-tale-of-disaster-and-recovery.aspx
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/18/2011 :  17:45:55  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

But...but...but...THIS GUY: http://www.sqlskills.com/BLOGS/PAUL/post/Unbelievable-tale-of-disaster-and-recovery.aspx



This is more real world:
SQL Dumbass, Fighting dumbasses, one query at a time…
http://sqldumbass.com/






CODO ERGO SUM
Go to Top of Page
Page: of 17 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000