SQL Server''s Best Practice?????
-
hi just wnt 2 ask if i shrink my transaction logs to 0, is it a Good or Bad Practice?? Is there exist a risk of data corruption when i shrink my log files always to 0??
And also is it "OK" for me not to do any type of backups available in MS SQL server, instead i copy the whole "data folder" in a separate disk for my backups? I do this because it is much faster to attach a database rather than to restore a database from a backup device. So, in case a database crashed i just delete that database then re-attaches its backup MDF and LDF files. Its much faster process compared to restoring a db from a backup device...Right?!?
Then again, i might be wrong.......
-
Do u have replication set up?
You are wrong. You can not shrink transaction log to 0. What is your recovery model?
Backup Folder : It is not the way to take backup.
You may miss the advantages of SQL Server.....
How will you restore to a point in time?
Kindest Regards,R
-
You can only shrink logs to their original size, so no worries there. However you only want to shrink them to the size they need between backups. Anything else slows the server as they grow.
You can copy the folder, but to do that you need to detach the db and it is unavailable until the copy is finished. If you can stand the downtime, it works. If not, don't.
If you do this, you can only recover to the copy times. If you run fulls at night and logs during the day, you can recover to any log, or more importantly, inside the logs if need be.
Why do this? If someone deletes a table at 4:30pm that saw heavy insert or update activity during the day, do you want to explain to your boss that all that work is lost because your "fast" backup/restore method is from midnight the night before?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
STeve,
I posted a lengthy reply to this and it dissappeared. And I call myself a DBA?!
To me there are 2 basic recovery scenarios:
1) Corrupted database requiring point in time dump and logdump restore.
2) Hardware (usually RAID) failure.
The hardware failure (in my experience the more common failure) is much more quickly addressed if .mdf's, etc. have been copied and saved elsewhere. So I think all the backup methods need to be applied.
I have been looking for a good all inclusive article on this subject. Any suggestions.
Thanks, Chuck
-
One of the major issues with just copying the data folder is that you do not know what state the database is in when you copy the folder. When you perform a BACKUP DATABASE command, SQL Server ensures that the database state is correct, no matter how long it took you to complete the backup. This is especially important if you have tables or indexes for tables that are partitioned between different filegroups.
In this case, the ATTACH command can fail because of the differences (I have seen this happen). If this happens to you, what then?
If you are absolutely surethat there is no activity on the DB at the time you copy the files, then you are probably OK. However, to be absolutely certain, you would need to DETACH the database before copying, and re-ATTACH it after you are done.
As for shrinking logs to less than their original size - sure, you can do that. Just use Enterprise Manager, right-click on the database, select All Tasks / Shrink Database..., click the Files button, select the log file, and change the size to whatever you want it to be. I didn't realize that zero was an option, however.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply