Msdb Corrupted/Suspected Solution SQL2000/2005

Msdb Corrupted/Suspected Solution SQL2000/2005

Msdb Database Suspected/Corrupted-Solution (SQL 2000&2005)

In Sql server 2000, you start the server with trace flag 3608.

In SQL Server 

Enterprise Manager, right-click the server name, and then click Properties.
On the General tab, click Startup Parameters.
Add the following new parameter:
-c -m -T3608
After this restart the sql server and detatch the msdb database and move the mdf and ldf files from the default folder C:\Program Files\Microsoft SQL Server\MSSQL\DAta
Then recreate msdb file by executing in new query window
instmsdb.sql (C:\Program Files\Microsoft SQL erver\MSSQL\Install)
Then remove-c -m -T3608 from the startup parameters in SQL Server Enterprise Manager
Then stop and restart the sql server 2000 and then recreate the maintenance plans

In SQL Server 2005
Detach the msdb database and install/create new msdb database. This will solve the problem. But the problem is that SQL Server 2005 does not allow to detach a system database. This will be solved by the following
another way is (you start the server with trace flag 3608. )
Start>Sql Server 2005>Configuration Tools>SQL Server Configuration Manager

Then click on SQL server 2005 services.
On the right side>Right click on SQL Server>Select Properties
Click on the Advance Tab and change the Parameters of Start up Parameters 
ie please add -m;-c;-T3608 in front of the existing parameter.
Then restart SQL server
detach the msdb database by query
use master
go
sp_detach_db ‘msdb’
go
and click Execute

Then move the existing mdf and ldf files of msdb fromC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data 

Then recreate msdb files open the nstmsdb.sql(C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install) in a new query window and execute, Then your msdb is restored. 

Before restarting the sql server remove the parameters (-m;-c;-T3608 )Then restart the sql server. Be and recreate the new maintenance plan.

Continue Reading

Are You Embarrassed By Your How To Recover Suspected Database Sql 2000/2005/2008 Skills? Here's What To Do

How to Recover Suspected Database SQL 2000/2005/2008

        “Database suspected” a very annoying issue in the field of Data management. Some time due to sudden power failure or some other reasons our database gets suspected and we can not use it anymore.

     To over come this issue, there are some tricks that may be helpful to recover your suspected database

1.       Restore latest backup

                 The easiest way to make suspected database is to restore it with latest available healthy backup. It’s always very easy and secure method.. but what if you don’t have backup ?

         Then you need to try out second option

2.       Using SQL Query

              You need to execute following query in query analyzer to overcome this issue

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

Note : Kindly take approval from competent authority before execution of above query as this query may result in some of data loss

3.       The another way to recover suspected database is

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

Hope any of  the above method will help you to recover your suspected database. Kindly comment if you have any another interesting method for this matter

Thanks

Continue Reading

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

Some of we are using SQL server 2000 in our offices for database creation. In SQL server 2005 when a database 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 the database 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

Continue Reading

Restore a SQL Server 2008 Database into SQL Server 2005

If you've tried to back up a database in SQL Server 2008 and then restore it into SQL Server 2005, you know that the database backups are not backward compatible. However, with SQL Server 2008 Management Studio, you can script data and schemas in SQL Server 2005 mode. This 10-Minute Solution takes you through the steps to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005.
  


Transferring databases from SQL Server 2008 to SQL Server 2005 is not possible with standard backup and restore facilities.

Leverage the scripting wizard in SQL Server 2008 to script data as well as schemas in SQL Server 2005 compatibility mode.

Using the "Generate SQL Server Scripts" Wizard

The Northwind database is no longer shipped as part of the SQL Server installation, but you can download it from go.microsoft.com. The data is scripted as INSERT statements.

To create the scripts, you have to run the "Generate SQL Server Scripts" wizard, which you can run within SQL Server Management Studio (once Object Explorer is connected to the appropriate instance) by right clicking on the database and selecting "Tasks –> Generate Scripts."

Figure 1 shows the initial dialog to the wizard. Click "Next" and complete the following steps in the wizard to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005:

Figure 1. Initial Dialog to Generate SQL Server Scripts Wizard: To create your scripts, you have to run the "Generate scripts" wizard.

1. Click "Script all objects in the selected database" (see Figure 2), and then click "Next."
   

   Figure 2. Select Database Dialog:Click the "Script all objects in the selected database" option.

2. Amend the script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True" (see Figure 3). (SQL Server 2000 is also supported.) If you are putting the database on a new instance for the first time, make sure the "Script Database Create" option is set to "True." Click "Next" when you are happy with the options.
   

   Figure 3. Choose Script Options Dialog:Set the "Script Data" option to "True."

3. Select "Script to file," select the file name, and choose "Single file" (see Figure 4). Click "Next" for a summary (see Figure 5).
   

   Figure 4. Output Option Dialog: Select "Script to file," select the file name, and choose "Single file." Figure 5. Script Wizard Summary: Review your selections for the wizard.



4. Now click on "Finish" to get progress messages while the script runs and completes (see Figure 6).
   

   Figure 6. Generate Script Progress Dialog: Click "Finish" and you will get progress messages.

   If the generation process fails, then you can use the "Report" option to see why.


5. When the scripting is completed, look for the following lines:

   
   CREATE DATABASE [Northwind] ON  PRIMARY 
   
   (NAME = N'Northwind', FILENAME =
    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.mdf' ,
    SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
   
    LOG ON 
   
   (NAME = N'Northwind_log', FILENAME =
    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.ldf' , 
    SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
   
   GO
   

   You will need to amend the paths to a valid path for the computer on which you are running. You also will need to comment out the following lines like this:
   

   
   --EXEC sys.sp_db_vardecimal_storage_format N'Northwind', N'ON'
   --GO
   

Once you have made these changes, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment. You can now test data against SQL Server 2008 and SQL Server 2005.

Continue Reading

SQL client installation 2000&2005

 SQL client installation 2000&2005

Step by step guide to installation SQL Client 2000&2005  POWER POINT format.

click download  SQL client installation

Continue Reading