Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jhnegrao
Yak Posting Veteran
81 Posts |
 Posted - 2007-01-26 : 12:45:15
|
| Hello, All!Good Morning!I have a doubts about SQL Server Backup and Restore. I'm rebuilding my actual recovery plan of my SQL SErver and I would like to make it better and reliable.My recovery plan are described as below:I have a "full backup" everyday at 5:00am, but I haven't used the SQL Server Full Backup procedure, I shut down my SQL Services and copy all Datafiles and LogFiles.I have another procedure developed by myself to backup the logs when the LogFiles become 70% full.I know that if I don't use Full Backup of the SQL Server, the log won't be truncated.I have a "Redo" Log every 2 days approximately. (Backup of the LogFiles when it become 70% full)My doubts is:Imagine that my servers has been broken and I lost almost all, just the tape backup is ok. This disaster happened on Wednesday at 10:00 o' clock andAs described before, I have a copy of the DataFiles from Monday, Tuesday and Wednesday and I have a "Redo" Log from Last Friday, Monday at 10:00am and Wednesday at 8:00am. I know that using conventional SQL Server Backup method, My Transaction Log is truncated, so I need to come back just the Full Backup from Wednesday and backup log from 8:00am. But, using copies of DataFiles and LogFiles with SQL Server Offline, I don’t know how the SQL Server will work, because using this procedure, I need to comes back The copy of the files from Wednesday and the backup of the Transaction Log from Monday at 10:00am and from Wednesday from 8:00am. How SQL Server works considering that all transactions between Monday 10:00 and Wednesday 5:00am (My last Full Backup) already has been committed to DataFile? The SQL Server identify which transaction already been committed and don’t execute it again?Anybody knows how it works? The SQL Server will execute everything already been committed again?ThanksJuliano Horta |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-26 : 13:15:09
|
| Don't try to make this up yourself. Your plan will probably not work - shutting down the server to back up the files is unreliable and difficult to restore from. Use the SQL Server backup as documented in Books Online.For some great articles that give more info about SQL Server backup, go here and scroll down to the links about Backuphttp://sqlteam.com/forums/topic.asp?TOPIC_ID=55210 |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-26 : 15:45:57
|
| "I haven't used the SQL Server Full Backup procedure, I shut down my SQL Services and copy all Datafiles and LogFiles."Why? and why not take a backup? Files will be smaller, and provide more flexibility when restored (e.g. physical / logical naming etc)"backup the logs when the LogFiles become 70% full."Why? That could be a considerable elapsed time. Why not just back them up every 10 minutes, reducing the interval of a disaster preventing recovery"I know that if I don't use Full Backup of the SQL Server, the log won't be truncated."Truncating the logs has nothing to do with Full Backup - the logs are truncated when you backup the logs themselves. However, if you never take a full backup there won't be any logs to back up either!I don't get it: Why don't you just make a Full Backup daily, and a Transaction Log backup every 10 minutes or so?Kristen |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-26 : 17:22:45
|
quote:
I don't get it: Why don't you just make a Full Backup daily, and a Transaction Log backup every 10 minutes or so?
Because he has "doubts about SQL Server Backup and Restore"  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-26 : 17:31:20
|
| I've never understood why they use doubt instead of the correct word, which is question. "Doubt" is perfectly legitimate, but it has a negative connotation. Each time I've seen it used, by someone from India typically, I've always thought that they should have used the word question instead.Tara Kizer |
|
|
|
|
|
|
|
|
Recovery Plan - Doubts