Recover/Repair a suspected database in SQL Server 2000 using Query

Sunday, 10 March 20130 comments


Some of we are using SQL server2000 in our offices for databasecreation. In SQL server 2005 when adatabase is suspected we can easily recover or restore it with a query
 "EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(‘yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER"

But while using SQL server 2000 this query was not properly working. Most of thedatabase in SQL server 2000 is due to the corruption of log file. The following is an easy way for recovering/repairing  a sql server  2000 suspected database.This is 5 Step process

Step 1 : This is for replacing dbname/ldf path name

Run the Query using Query Analyser (QA)

Use master
Go
sp_configure 'allow updates', 1
Reconfigure with override
Go

Step 2: execute the following script
Update sysdatabases set status= 32768 where name = 'your db name'


Step 3: 
Restart MSSQLSERVER service, the database will be in Emergency mode

Step 4: Rebuild Log. From QA execute script
DBCC REBUILD_LOG ('your db name', 'db path log name{eg: E:\postman_Log.LDF}')
after running this -You got a 
--Message - Warning: The log for database 'postman' has been rebuilt.

Step 5 From QA execute following script

Use master
Go
sp_configure 'allow updates', 0

AFter this your database is recovered but the same is in single user mode. Please go to properties and set it to multi user mode , or use the query 
ALTER DATABASE yourDBname SET MULTI_USER
courtsey.saparavur
Share this article :

Labels

/Accountant (4) /DA (3) /Exam (3) /I.tax (1) /IPo (2) /Interest Calculator (4) /Leave (1) /ORDERS (1) /PM Gr-1 (8) /PM Gr.-1 (1) /Rail Reservation (1) /Railway (1) /Update-2 (1) /Update-3 (1) /tv (1) ACCOUNT PBS/PACS (1) ADHAR(UID) (3) ALLOWANCES (2) ANSWER KEY (1) Accountant/question Paper (1) Admit card/PM Grade1 (2) Allowances.Empoly News (1) BANKING (1) BOOK FOR DEPARTMENTAL EXA (3) BROADBAND (1) BSNL (1) CCBridge Vrs.6.0 (2) CGHS Health Tips Staffs and Welfare (3) COMPUTER AND HARDWARE (10) Clear Log Files........ (1) Computer Guidlines (5) Cricket (1) Current Afairs (1) Customer care news (8) DB - Analyzer (19) DEPARTMENTAL ORDER (4) Departmental New (1) Departmental News (71) Do software (1) Dop News /RMS (1) Dop News pension (1) Dte Order/Tfr.and Posting (1) ECS SOFTWARE (1) EVENT (1) Employees News (53) Employees News/Education (2) Empolyment News (8) Exam/Result (21) F (1) GDS (1) GPF (3) General Issue (2) General Knowledge (12) General Knowledge/cheque (1) Gold coins (3) HCL compaint (1) Hardware/security Tips (1) Health Tips (2) IMO (1) INTERNATIONAL ARTICLS WEB (1) INTRES (2) IPO (2) IPO SOLVED PAPER (1) IPO/ASPOs (15) IPS Web (1) Income tax (2) Interest Calculator (2) Internet tips (2) LGO PAPER (2) Leave Rule (2) MODEL PAPER (37) MODEL PAPER/IPO (19) MODEL PAPER/PM GR-1 (21) MOF_Rat (1) Mail (1) Meghdoot 7.0 (4) Mobile Tip (4) Money Gram IMTS (1) NEW (1) NEWS (41) NPS (5) Negative list (1) New Pension Scheme (2) News Railway (6) News Tax (1) News Tchnology (1) News:-Increament (1) OTHERS (35) Order (3) PA/SA (6) PLI and RPLI (7) PM Gr.-1 (1) POST BANK OF INDIA (3) POSTAL ABBREVIATIONS (1) POSTAL HISTORY (1) PS Group/question Paper (1) Pass Book Printer (1) Pension staffs and welfare (2) Point of Sale (6) Postal Information (4) Postal Insurance (1) Postal News (1) Postman (1) Postman Module (1) Postmaster (2) Postmaster Cadre (7) QUESTION PAPER2011 (1) QUESTION PAPER2012 (1) Qestion Paper/2012 (1) R LIST (1) R-Net (2) Recovery tips SQL (5) Recruitment PA/SA Form (3) Result (5) Result/IPO (2) Rulling and low (2) SAVING BANK (27) SB ORDER (8) SBCO (5) SERVICE TAX (1) SPEED NET (14) SPEED POST/ORDER (4) SQL (7) SUB ACCOUNT (3) SYLLABUS FOR PA/SA (1) Server/Backup (2) Solve Paper PM-1 (3) Solve Paper PS GR B (1) Staffs and Welfare (7) Study Materials (8) TDS (2) TOOL (6) Tech News (6) Tech tips and Tricks (13) Treasury (1) Trouble shooting (1) Union News (6) Update/Sanchay Post (15) WUMT POs (1) Window Server (1) Windows (4) Windows Tips (4) Windows xp (1) Workfow............ (1) budget (2) e Services (3) e services Meghdoot7 (3) eMO (3) eMO ResetRegistration (1) ePayment (1) latest software (6) oder/DA (5) operating (1) sanchay post (22) security Tips (1) syllabus /pmgr1 (1) upsc (1)
 
Support : Creating Website | Johny Template | Mas Template
Copyright © 2011. PURNEAHPO - All Rights Reserved
Template Created by Creating Website Published by Mas Template
Proudly powered by Blogger