What's a safe backup?
-
If you copy a file, and the copy fails, you still have the file. If you do a move in Windows, you potentially could lose the file or cause corruption.
This has happened before (reported by people) with large database files that are moved in Windows.
-
Brandie Tarvin (7/25/2011)
Ninja's_RGR'us (7/25/2011)
Please note he said COPY and not MOVE. If something bad happens during the move you lose the DB, copy is safer there.Ninja, I'm sorry, but this reply makes no sense to me. Could you rephrase?
What Steve said!
Edit : Note
-
GSquared (7/25/2011)
halifaxdal (7/25/2011)
I got this idea from Mark Armer, I agree that in some/or many cases SQL cannot be stopped however I still think his idea is surprised, unexpected and workable especially to some non-critical databases/servers.I agree it would work for those. And it's definitely out-of-the-box thinking and all that. I just can't see the merit to it. Yes, you can safely stop the server if it's not in use, but the question becomes why bother? It's more work, it doesn't scale well, and there's no benefit to it that I can see.
It probably isn't hurting anything, except taking more work to do, so go ahead with it if it solves some problem for you. But it does make me curious as to what benefit you get from it that you can't get by just issuing "backup database" commands.
This depends on how you defne "hurting" When you stop SQL, you lose the cache, there's then the cost of recaching and recompiling all the stored proces etc,..
As a rule I don't recommend such regular restarts of SQL. Also you now can't use the transaction logs to do point in time recovery. You lose a lot by doing a cold backup, it's deffinitly not a good idea as your default backup method.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated. -
Leo.Miller (7/25/2011)
GSquared (7/25/2011)
halifaxdal (7/25/2011)
I got this idea from Mark Armer, I agree that in some/or many cases SQL cannot be stopped however I still think his idea is surprised, unexpected and workable especially to some non-critical databases/servers.I agree it would work for those. And it's definitely out-of-the-box thinking and all that. I just can't see the merit to it. Yes, you can safely stop the server if it's not in use, but the question becomes why bother? It's more work, it doesn't scale well, and there's no benefit to it that I can see.
It probably isn't hurting anything, except taking more work to do, so go ahead with it if it solves some problem for you. But it does make me curious as to what benefit you get from it that you can't get by just issuing "backup database" commands.
This depends on how you defne "hurting" When you stop SQL, you lose the cache, there's then the cost of recaching and recompiling all the stored proces etc,..
As a rule I don't recommend such regular restarts of SQL. Also you now can't use the transaction logs to do point in time recovery. You lose a lot by doing a cold backup, it's deffinitly not a good idea as your default backup method.
Cheers
Leo
Exactly.
The reason I said "probably not hurting anything" is that the description given is, essentially, "small, unimportant databases that can be offline for arbitrarily long periods of time for their backups". Given those assumptions, query performance, et al, probably doesn't much matter, since, honestly, the databases probably don't matter much. Which also means the backups are probably deadweight anyway.
Even given all of that, it would be easier to use the built-in backup features than to do this stop-copy-start routine. Less work. And since these databases really don't matter to anyone, why spend more work than necessary on them? That's what I don't get.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
While wasting more time on this...
On another note, the only safe backup I know is the one I've successfully restored and then ran an error-free checkdb() on it. And then that backup file hasn't been touched by anything... of course if that drive fails you're in trouble bu that's another story.
-
Ninja's_RGR'us (7/26/2011)
While wasting more time on this...On another note, the only safe backup I know is the one I've successfully restored and then ran an error-free checkdb() on it. And then that backup file hasn't been touched by anything... of course if that drive fails you're in trouble bu that's another story.
Very true indeed !
Creating a backup is one thing, being able to restore it and perform the obligatory validity checks is another story, but needs to be performed to complete the DRP cycle !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Gianluca Sartori (7/25/2011)
Ninja's_RGR'us (7/25/2011)
There's just no reason to shutdown the server to take a backup.... unless you're working with Oracle. :Whistling:
It's called "cold backup" and it's advertised as "the only consistant backup". Strange, isn't it?
Gianluca Oracle is capable of doing hot backups. check this. I'm a big fan of Microsoft SQL Server and my knowledge of Oracle is very limited, but what you said about "cold backup" is just not accurate. You may want to check the link.
-
halifaxdal (7/25/2011)
Hi folks,I plan to do my backup this way:
1. stop server
2. copy all mdf/ldf to another place
3. restart server
Is it the best and safest way to run a reliable backup? will the maintenance jobs be backed up as well? where are those maintenance jobs stored in SQL 2005?
Thank you for you input.
Halifaxdal,
You already must have realized that your approach is not the best to backup SQL databases. I add you a few links that maybe interesting:
- http://www.sqlmag.com/article/backup-recovery/sql-server-backup-best-practices
- http://msdn.microsoft.com/en-us/library/ms187510.aspx
As Roy and Ninja already mentioned the maintenance jobs are stored on the MSDB database.
I hope this helps and please let us know if you need any extra information or help.
Good luck!
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply