Differences between SQL2000,SQL2005 and SQL2008.

S.No	SQL 2000	SQL2005	SQL2008
1	Query Analyzer and Enterprise Manager are separate.	Both are combined as SQL SERVER MANAGEMEN STUDIO.	Both are combined as SQL SERVER MANAGEMEN STUDIO.
2	No XML datatype is used.	XML datatype is introduced.	XML datatype is used.
3	65,535 databases can be created.	2(pow(20))-1 databases can be created.	2(pow(20))-1 databases can be created.
4	Nill	Exception handling	Exception handling
5	Nill	Varchar(Max) datatype	Varchar(Max) datatype
6	Nill	DDL Triggers	DDL Triggers
7	Nill	Database mirroring	Database mirroring
8	Nill	Row number function for paging use.	Row number function for paging use.
9	Nill	Table fragmentation	Table fragmentation
10	Nill	Full text search	Full text search
11	Nill	Bulk copy update	Bulk copy update
12	Nill	Can’t encrypt	Can encrypt entire database
13	Compression not possible	Can compress tables and indexes(introduced in 2005 SP2)	Can compress tables and indexes
14	Datetime is used for both date and time	Datetime is used for both date and time	Date and time are separately used for date and time datatype
15	Nill	Narchar(max) and varbinary(max) is used.	Narchar(max) and varbinary(max) is used.
16	Nill	Nill	Table datatype is introduced.
17	Nill	SSIS is used.	SSIS is used.
18	Nill	Nill	Central Management Server(CMS) is introduced.
19	Nill	Nill	Policy based management is available.

SQL2008 SP3 Error

Errors in SQL2008 SP3 installation and the troubleshooting steps.

===============================================

I got below error when I trying to install SQL2008 SP3 patch.

To resolve above error code 0x84B20002, we need to do follow steps:

1. We need to search the missing MSI original file (‘sql_engine_core_inst.msi’) from the SQL2008 software folder or CD.
2. Then copy the missed MSI file to ‘C:\Windows\Installer\’ path.
3. Rename the original file ‘sql_engine_core_inst.msi’ to 12c57ede.msi(missing file)  in the above mentioned path(Find missing file name from the error).
4. Next Rerun the SQL2008 SP3 patch executable file.
5. Finally SP3 patch will apply successfully.

Have a Sucessful SP3 patch installation....

How to delete old backup files by script.

Extended procedure 'xp_delete_file ' is used for to delete the backup files in the server.

We need to specify the file type selected ((0 = FileBackup,1 = File Report),folder path(trailing slash), file extension which needs to be deleted (N'bak'),date(prior which to delete) and folder flag level((1 = include files in first subfolder level, 0 = not) as like below in the Sql Server Management Studio.

EXECUTE master.dbo.xp_delete_file 0, N’D:\SQLBackup',N'BAK',N'06/07/2011 10:18:49',1

By running above script, we can able to delete the old backup files prior to the above specified date.

Steps for sql server decommission activities.

How to decommission sql Instances from the server?

Before uninstall we need to follow some steps.

1. Make a note of service pack and hot fixes installed.

2. Backup the databases master, msdb, and model to be used for roll back purpose.

How to check which Service Pack installed?

Run below SQL Server command

SELECT SERVERPROPERTY('productversion')

, SERVERPROPERTY ('productlevel')

, SERVERPROPERTY ('edition')

GO

If you’re using SQL Server version 7, you need a different command:

SELECT @@VERSION

GO

How to find which Hotfix applied on SQL Server?

To determine the Hotfix number, since each one will be different, and many may be installed. To determine the Hotfixes on your server, download the program called HFNetChk (http://support.microsoft.com/kb/305385) from Microsoft. There’s a commercial version of this tool by the way that will keep your servers up to date, but this is the free one Microsoft provides.

There are now stored procedures you can use to show you the build number on your server, such as:

EXEC sp_server_info 

GO

and 

EXEC master..xp_msver

GO

Steps to be followed for uninstall sql server:

1. Uninstall SQL Server through Windows Control PanelàAdd/remove Programs.

    a. Uninstall SQL Server patches in descending order

    b. Uninstall SQL Server installation

2. Restart server

Steps to be followed for re-intstall the old sql server.i.e(roll back)

1) Reinstall SQL Server.

2) Apply any service packs and hotfixes that were installed earlier.

3) Restore the databases master, msdb, and model from the last backup that was taken before you installed.

When we go for decommission of Sql instances?

If current application has been migrated to sql farm servers or other environment, then existing systems will not being used. So we need to uninstall sql server instances from the server.

False server down alert from the monitoring server.

Server team changed dns name and ip address for server XXY.After that, we were receiving sql host down alert from the monitoring server YYX for the server XXY.What we do to stop that false alerts?

Resolution steps:

1. Go to the following path file://yyx/c$/WINDOWS/system32/drivers/etc
2. Find host file inside the etc folder.
3. Open the hosts file in the notepad and it shows like below comments#.
4. Add newserveripaddress   servername in the bottom content of the file.

# Copyright (c) 1993-1999 Microsoft Corp.

#

# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.

#

# This file contains the mappings of IP addresses to host names. Each

# entry should be kept on an individual line. The IP address should

# be placed in the first column followed by the corresponding host name.

# The IP address and the host name should be separated by at least one

# space.

#

# Additionally, comments (such as these) may be inserted on individual

# lines or following the machine name denoted by a '#' symbol.

#

# For example:

#

#      102.54.94.97     rhino.acme.com          # source server

#       38.25.63.10     x.acme.com              # x client host

127.0.0.1       localhost

10.176.12.245 XXY#we need to include new ip and hostname.

I’ve updated the host file in the monitoring server (YYX) to refer the new ip of XXY server.

That’s it.

Note: We need to change as per above notes after domain change and ip change.

Database Diagram generation issue

Description: For the SQL DB, I was trying to generate the Database Diagram in Microsoft SQL Server Management Studio Express

          But the application is throwing some error related to ownership of the database as given below...

"TITLE: Microsoft SQL Server Management Studio Express

------------------------------

Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, and then add the database diagram support objects.

Please resolve the error and install the necessary support objects to generate the diagrams.

Solution:

Issue due to recent restore of database from sql2000 to sql2005.We need to do run below steps to resolve the issue.

EXEC sp_dbcmptlevel 'yourdatabasename', '90';
go
ALTER AUTHORIZATION ON DATABASE::yourdatabasename TO "dbusername"
go

How to grant update permission to specific columns in a table in sql?

Do you know how to grant select permissions on TBL_XXXXXX table in XXX instance in XXXX server. And also grant update permissions on the same table at column level for the columns columnname1 and columnname2.?

By executing below query, select permission granted to the table ‘TBL_XXXXXX’ for the user ‘databaseusername’.

GRANT SELECT ON [TBL_XXXXXX] TO databaseusername

By executing below query, update permission granted to the specific columns in a table ‘TBL_XXXXXX’ for the user ‘databaseusername’

GRANT UPDATE(columnname1) ON [TBL_XXXXXX] TO databaseusername

GRANT UPDATE(columnname2) ON [TBL_XXXXXX] TO databaseusername

That’s it.