Rebuild Master Database in SQL 2000

To rebuild the master database
Shutdown Microsoft® SQL Server™ 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.

In the Rebuild Master dialog box, click Browse.

In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.

Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.

To start a named instance of SQL Server in single-user mode from a command prompt

From a command prompt, enter:
sqlservr.exe -c - m -s {instancename}

To start the default instance of SQL Server in single-user mode from a command prompt
From a command prompt, enter:
sqlservr.exe -c -m

Note  You must switch to the appropriate directory (for the instance of Microsoft® SQL Server™ you want to start) in the command window before starting sqlservr.exe.

To start the default instance of SQL Server from a command prompt

From a command prompt, enter:
sqlservr.exe -c

Note  You must switch to the appropriate directory (for the instance of Microsoft® SQL Server™ you want to start) in the command window before starting sqlservr.exe.

 start the default instance of SQL Server with minimal configuration

From a command prompt, enter the following command to start the default instance of Microsoft® SQL Server™ as a service:
sqlservr -c -f

Note  You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.

To start a named instance of SQL Server with minimal configuration

From a command prompt, enter the following command to start a named instance of Microsoft® SQL Server™ 2000 as a service:
sqlservr -c -f -s {instancename}

Note  You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.

 

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

Step 1 Generating Scripts for the Database Elements and Structures


1)Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).
2)At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.
3)Set the following Elements to the following Values
a.Script Collation , set to TRUE
b.Script Database Create, set to TRUE
c.Script of SQL Version, set to SQL SERVER 2000
d.Script foreign keys, set to FALSE
e.Script Triggers, set to FALSE
Then Hit the Next button
4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.
5)Click Finish

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.

Step2 Moving the data from 2005 to 2000

1)After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).
2)From the pop-up Dialog Box, select the Source Db and Click at the Next Button.
3)At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.
4) A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3 Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:

1)Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.
2)Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.
3)Set all the Elements on the List to a False Value except the ones that follow:
a.Include IF NOT EXISTS , set to TRUE
b.Script Owner, set to TRUE
c.Script of SQL Version, set to SQL SERVER 2000
d.Script foreign keys, set to TRUE
e.Script Triggers, set to TRUE

Then Hit the Next button
4)After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.
5)At the screen that follows hit the Select All button and the Next.
6)Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.
7)Click Finish Button.

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

Database Trace :- 3226

1. Successful backup entries are not added to the SQL Server error logs.
2. Entries are prohibited from being logged in the System event log.
3. Only failed backup event entries are logged.
4. No successful backup entries are logged.

A trace flag (3226) can be either specific to a session or a global trace flag.

A trace flag can be switched on or off by using the dbcc traceon and dbcc traceoff command

DBCC TRACEON(3226)
DBCC TRACEOFF(3226)

The .TUF & .WRK files in Log Shipping

.TUF File

.TUF file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.
 When the database is in Standby mode the database recovery is done when the log is restored; and this mode also creates a file on destination server with .TUF extension which is the transaction undo file.

This file contains information on all the modifications performed at the time backup is taken.

The file plays a important role in Standby mode… the reason being very obvious while restoring the log backup all uncommited transactions are recorded to the undo file with only commited transactions written to disk which enables the users to read the database. So when we restore next transaction log backup; SQL server will fetch all the uncommited transactions from undo file and check with the new transaction log backup whether commited or not.

If found to be commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.

.WRK File

The .wrk files are produced when the transaction log backups are copied from the backup location to the secondary server (by the agent job on the secondary).  The files are named .wrk during the copy operation; when they have been completely copied they are renamed to have the .trn extension.  The temporary naming using the .wrk extension ensures that the files are not picked up by the restore job until successfully copied.

So, if the .wrk file you are seeing is there all the time it is likely to be an old copy that failed for some reason.  So for normal operation, you'll see a .wrk file when each transaction log backup file is copied and then that will be renamed to a .trn file.
 

Physical Architecture of SQL Server

 

How To Remove Services Form Windows

Method 1 - manually delete registry entries

Windows services are registered under the following registry key.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
Search for the sub-key with the service name under referred key and delete it.

Method 2: Remove services through Command Line

C:\windows\system32> sc delete < SERVICE name>