I recently found an excellent automated backup procedure at http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm (may wrap). I put it in place and my MSSQL backups have been solid ever since. I was previously using Veritas 8.6 and found all sorts of problems when I finally got round to disaster-recovery testing. This method runs as a simple scheduled task, generates simple files on my NAS and includes the system state. I can post my backup .cmd and .sql files if they'd be useful to anyone. -----Original Message----- From: Jim Kenzig http://thin.net [mailto:jimkenz@xxxxxxxxxxxxxx] Sent: 01 September 2004 13:53 To: thin@xxxxxxxxxxxxx; windows2000@xxxxxxxxxxxxx Subject: [windows2000] IT Could happen to you...SQL Database Corruption 101 Hi All, As some of you may or may not of noticed my web site the Thin.net had some "issues" yesterday. Issues which led me down an all night path of why you need to regularly backup the transaction logs of your SQL 2000 database. First off all disclaimers on the Thin.net apply. I am a NOVICE SQL admin, I have had to learn SQL because it is showing up everyplace. (Now even in Citrix) You need to know some basic backup procedures to save your soul. Perform these functions at your own risk. I take no liability about what you do to your system by using this information. OK Here goes: Apparently there are two files critical for you database. One has a .mdf extension and the other has a .ldf extension. The transaction logs as I understand it reside in the .ldf portion of your Database My web site is completely backended with SQL via the use of Cold Fusion. There are about 4 different databases that control different things there. I regularly back up the databases to a file on my drive and then back up the back up file onto my DLT Tape drive. I have always been able to restore my databases from backup by performing the following procedure: Expand the server that contains the "Database" directory that has your databases that need restored. 1. Right click on "Databases" < Select "New" < Restore Database 2. Under the "General" Tab < Select the radio button for "From Device" a) Add device b) browse to directory - only one database should be added since the application will create a .mdf and .ldf file (logs) for each database. Adding in the names of ALL databases will result in ONE .mdf and .ldf file shared for all of them. c) after database is added in, click OK 3. Back on "General" tab, make sure "Database Complete" is selected. 4. Also check that the database name you are restoring is accurate in text field (located at top of menu). 5. Go to "Option" menu. At botton of menu, select "Leave Database Operational. Do not restore Transition log." if you aren't restoring logs and only database. 6. Click OK A progress bar should appear on the screen indicating the restore is in progress. Repeat these steps for each database that needs to be restored. Now if you had the smarts to take your database offline and backup its ldf and .mdf files you could of simply taken the database offline and swapped out these files into your database directory with the corrupted ones and put the database back online. Or you could right click on the server and attach the database by selecting these files. But what happens when your backup .bak file is too old or is corrupted and either the .mdf file or the .ldf file is corrupted. You pretty much end up SOL if you haven't been backing up something Anyways here is what happened to me and it could very easily happen to you. The .ldf file ran out of space on my Ads database for some reason. This database also controls some other stuff on my site and it virtually broke my site by being down. I knew it was out of space because that is what the error message said. It even told me how I could try and fix it. The error read: Error: 9002, Severity: 17, State: 6 The log file for database 'aspads' is full. Back up the transaction log for the database to free up some log space. Ok so I learned that there is a tick box when you back up the database to clear out free space in the transaction log. So I tried to just back up the transaction log to clear it and that did not work. We'll I thought I had a good .bak file yesterday but it turns out that my backup job wasn't set up exactly right. My fault. After a bunch of different restore attempts, I ended up going back a year where I did have a good backup to restore what I could and then enter the rest (til 3 am this morning) to get the database up to date. Lesson learned: Make sure that: BACKUP FREQUENTLY! 1. Your database .mdf and .ldf files are set up with enough space to grow on your drive. You can take your database offline, right click on properties for it and make changes to these in the appropriate fields and put the database back online. 2. Backup the .bak files from your backup often. 3. Take the database offline and make a backup copy of the .ldf and .mdf files so that you can return to a point in time if all else fails. 4. Look carefully at your maintenance plan. SET ONE UP IF YOU DON'T HAVE ONE! SQL Server has a great wizard that sets it up for you. Be certain that the Transaction log is getting backed up and cleared of extra space by right clicking on your maintenance plan in the MMC and choosing properties and then clicking on the Transaction Log Backup tab and checking the box to BACK UP THE TRANSACTION LOG AS PART OF THE MAINTENCE PLAN. I don't think this is chosen by default so you need to do this after you set up your maintence plan. There is a similar choice if you do a manual backup. By backing up the transaction log it will free up some space in it so that it doesn't run out of space and crash your database. IT CAN and DID happen to me. Do this and you will save yourself a lot of headaches in the future. Trust me. My only savior was that I was able to restore my database from an older bak file using the above procedure. Hope this helps someone out there. If you have anything to add let me know. I'll post this as an article on Ondemandaccess.com once I have all ideas and feedback. Regards, Jim Kenzig http://thin.net ******************************************************** This Weeks Sponsor StressedPuppy.com Games Feeling stressed out? Check out our games to relieve your stress. http://www.StressedPuppy.com ******************************************************** To Unsubscribe, set digest or vacation mode or view archives use the below link. http://thethin.net/win2000list.cfm Gallai'r e-bost yma gynnwys gwybodaeth gyfrinachol a/neu ddeunydd hawlfraint. Os ydych chi'n meddwl eich bod wedi derbyn yr e-bost yma drwy gamgymeriad rydym yn ymddiheuro am hyn; peidiwch os gwelwch yn dda â datgelu, anfon ymlaen, printio, copïo na dosbarthu gwybodaeth yn yr e-bost yma na gweithredu mewn unrhyw fodd drwy ddibynnu ar ei gynnwys: gwaherddir gwneud hynny'n gyfan gwbl a gallai fod yn anghyfreithlon. Rhowch wybod i'r anfonwr fod y neges yma wedi mynd ar goll cyn ei dileu. Mae unrhyw safbwynt neu farn a gyflwynir yn eiddo i'r awdur ac nid ydynt o anghenraid yn cynrychioli safbwynt neu farn Ymddiriedolaeth GIG Gogledd Orllewin Cymru. Gallai cynnwys yr e-bost yma gael ei ddatgelu i'r cyhoedd o dan Gôd Bod yn Agored y GIG neu Ddeddf Rhyddid Gwybodaeth 2000. Nid oes modd gwarantu cyfrinachedd y neges ac unrhyw ateb. Bydd y neges yma ac unrhyw ffeiliau cysylltiedig wedi cael eu gwirio gan feddalwedd canfod firws cyn eu trosglwyddo. Ond rhaid i'r sawl sy'n derbyn wirio rhag firws ei hun cyn agor unrhyw ymgysylltiad. Nid yw'r Ymddiriedolaeth yn derbyn unrhyw gyfrifoldeb am unrhyw golled neu niwed a allai gael ei achosi gan firws meddalwedd. This e-mail may contain confidential information and/or copyright material. If you believe that you have received this e-mail in error please accept our apologies; please do not disclose, forward, print, copy or distribute information in this e-mail or take any action in reliance on its contents: to do so is strictly prohibited and may be unlawful. Please inform the sender that this message has gone astray before deleting it. Any views or opinions presented are to be understood as those of the author and do not necessarily represent those of the North West Wales NHS Trust. The contents of this e-mail may be subject to public disclosure under the NHS Code of Openness or the Freedom of Information Act 2000. The confidentiality of the message and any reply cannot be guaranteed. This message and any attached files will have been checked with virus detection software before transmission. However, recipients must carry out their own virus checks before opening any attachment. The Trust accepts no liability for any loss or damage, which may be caused by software viruses. ******************************************************** This Weeks Sponsor StressedPuppy.com Games Feeling stressed out? Check out our games to relieve your stress. http://www.StressedPuppy.com ******************************************************** To Unsubscribe, set digest or vacation mode or view archives use the below link. http://thethin.net/win2000list.cfm