| Author |
Topic |
|
bobanjayan
Yak Posting Veteran
67 Posts |
 Posted - 2004-04-05 : 07:41:02
|
| Dear friends,Is full data recovery ( data upto database crash) possible in MSSQL 2000? Or it will give only upto the last backup?Regards.Boban OCP - DBA 8i & 9i.New in MSSQL. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-04-05 : 07:52:52
|
| Look at transaction log backups in bolSet the database to full recovery mode and add tr log backups.You backup the current active logYou restore the last full backup, las differential, all tr log backups since the backup restored then the active log that you backed up.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
bobanjayan
Yak Posting Veteran
67 Posts |
Posted - 2004-04-05 : 08:22:52
|
| THANKS.I hope it is in Database Properties => Options => Recovery => Model => Full.Right?But will it Archive the log files automatically?If it does then where is the default location?If my database is crashed. I took the back last week.My recovery Model is set to "FULL". But I didn't take the backup of current active log file. Can I make a full recovery of my database?Thanks in advance.Boban OCP - DBA 8i & 9i.New in MSSQL. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-04-05 : 08:27:58
|
| >> But will it Archive the log files automatically?No - you also have to implement tr log backups. If you don't the log files will just keep growing.It's one of my complaints about the defalt installation that it sets the recovery model to full which means that if you don't do anything the log file will eventually fill the disk and crash the system.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-05 : 08:31:01
|
| You can set the recovery mode on the model database to simple though. Going forward, all your new databases will be Simple. That's one of the first things I do now on an installation. I can then set them to full when I set up tlog backups.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-04-05 : 08:34:50
|
| Yep - it's what I always advise - doesn't help people who expect sql server to work out of the box though (which it does appear to do - just fails later when it's got lots of data added to it).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-05 : 08:57:50
|
| Such small details. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-05 : 11:15:55
|
| To be fair, though....anyone who expects to run SETUP.EXE and then right-click and hit CREATE DATABASE and never do anything again doesn't belong administrating a database. |
|
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2004-04-05 : 13:11:17
|
quote:
To be fair, though....anyone who expects to run SETUP.EXE and then right-click and hit CREATE DATABASE and never do anything again doesn't belong administrating a database.
Thats all I did. Lance Harra |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-04-05 : 18:10:28
|
quote:
Originally posted by bobanjayan
If my database is crashed. I took the back last week.My recovery Model is set to "FULL". But I didn't take the backup of current active log file. Can I make a full recovery of my database?
What state is it in now? You can only get as current as your most recent backup. If you have started the recovery process without backing up the active transaction log first, then you can only get as current as your most recent backup which will not be right up to the point of trouble.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-04-05 : 18:11:22
|
quote:
Originally posted by JohnDeere
quote:
To be fair, though....anyone who expects to run SETUP.EXE and then right-click and hit CREATE DATABASE and never do anything again doesn't belong administrating a database.
Thats all I did.Lance Harra
But now you're here...  --------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-05 : 18:12:33
|
| And what do you mean that your database is crashed? Have you tried attaching the MDF and LDF?Tara |
|
|
|
bobanjayan
Yak Posting Veteran
67 Posts |
Posted - 2004-04-06 : 01:13:55
|
| Who said my database is crashed!I added "IF" in front of the "CRASH" word.I just want to know,What will happen next!Boban OCP - DBA 8i & 9i.New in MSSQL. |
|
|
|
bobanjayan
Yak Posting Veteran
67 Posts |
Posted - 2004-04-06 : 06:37:14
|
| So,now what I understood is,After every small change we have to take the backup of transaction logs. Or schedule it for doing that.Thanks & RegardsBoban OCP - DBA 8i & 9i.New in MSSQL. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-06 : 08:11:15
|
| I highly doubt you are an OCP and DBA in Oracle 8i and 9i and you don't understand the concept of transaction log backups. |
|
|
|
bobanjayan
Yak Posting Veteran
67 Posts |
Posted - 2004-04-06 : 08:50:05
|
| Dear crazyjoe,What is your doubt? Whether I am a Oracle DBA ?F___ off.Boban OCP - DBA 8i & 9i.New in MSSQL. |
|
|
|
bobanjayan
Yak Posting Veteran
67 Posts |
Posted - 2004-04-06 : 09:39:41
|
| To be very frank, I didn’t understand the concept.First, after the database is crashed how can I take the backup of the transaction logs?Above all “Database – complete” and “Database Differential” are active when I right clicking on the database to take the backup. The transaction log is not active.I am little confused now. Oracle concept is,Every committed transaction is written to redo log files; if one file is full it will write to other. After the last file is full the process will overwrite the first one. Before overwriting it will create a backup copy of that. So that we are able to recover 99.9% of data from the archived log files after the database crash. Last 4 years I am working as an Oracle DBA. Now one of our client is in SQL2K, Only 3 days back I started SQL 2KBoban OCP - DBA 8i & 9i.New in MSSQL. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-06 : 12:31:46
|
| SQL Server does not use multiple transaction logs. But the one transaction log behaves similarly as the redo logs in Oracle. You need to backup your transaction logs occassionally in order to be able to restore to a point in time. We backup ours every 15 minutes. It just depends on your situation how often you need to back them up.I asked about what do you mean about the database crashing because there is no one answer for everything. So if you could give us some examples, then we can tell you how to restore.Tara |
|
|
|
bobanjayan
Yak Posting Veteran
67 Posts |
Posted - 2004-04-06 : 13:05:42
|
| Thank you Tara.I got how it functions.I asked you from my imagination."What will happen if it is crashed?"Let me read some Books and articles. Without that it is meaningless to debate.Thanks & Regards.Boban OCP - DBA 8i & 9i.New in MSSQL. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-06 : 13:27:43
|
quote:
Originally posted by bobanjayan
Dear crazyjoe,What is your doubt? Whether I am a Oracle DBA ?F___ off.
Let's put it this way: I am primarily a SQL Server DBA. It was once part of my job description to have limited administration duties in Oracle, so I took some Oracle training. I immediately realized the similarities between Oracle's REDO logs and SQL Server's TRANSACTION logs. I was surprised that someone who claimed to have the knowledge base you did wouldn't have made the same connection.It just surprised me, that's all. That and the language barrier combined to make you seem a little fishy to me. Sorry if I offended. |
|
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-04-06 : 14:39:14
|
quote:
Originally posted by bobanjayan
To be very frank, I didn’t understand the concept.First, after the database is crashed how can I take the backup of the transaction logs?
That is actually one of the first things you should try to do, providing the database is not in simple mode. This is how you can recover up to the minute/sec, whatever... |
|
|
|
Next Page
|
Full Data Recovery