Home » SQL Server

Category Archives: SQL Server

SQL Server 2005 || Database backup failed because Full Text catalog files are OFFLINE


Error : Database backup is getting fail with below reason.

The backup of full-text catalog ‘FullTextCatalog’ is not permitted because it is not online. Check errorlog file for the reason that full-text catalog becomes offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Cause : This behavior occurs because a full-text catalog in the database is not online. To perform a full backup, SQL Server 2005 requires all the database files and full-text catalogs in the database to be online.

Possible Reason : The database is attached from somewhere. However, you specify the incorrect location for the full-text catalog folder during the attachment.

Solution 1 (If FTS files are available but SQL server showing offline):

1. Locate the folder that contains the files for the problematic full-text catalog.

2. Run the ALTER DATABASE statement. Specify in the statement the correct location for the full-text catalog.

3. Rebuild the problematic full-text catalog in the database.

4. Perform a full backup of the database in SQL Server 2005 again.

Solution 2 (If FTS files are not available):

1. Detach database & attach again with all MDF, NDF, LDF & FTS files.

Web Reference :

1) http://support.microsoft.com/kb/923355

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Renaming or Changing SQL Server Cluster Instance


Renaming or Changing SQL Server cluster instance process is bit different from renaming a stand-alone instance.

Refer : Rename or Change SQL Server Standalone Default Instance http://mssqlfun.com/2014/06/30/rename-or-change-sql-server-standalone-default-instance/)

Refer : Rename or Change SQL Server Standalone Named Instance (http://mssqlfun.com/2014/07/29/rename-or-change-sql-server-standalone-named-instance/)

The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). You can change the name of the virtual server, you cannot change the instance name like in the case of standalone named instance. You can change a virtual SQL server named SQLServerinstance1 to some other name, such as SQLServerNewinstance1, but the instance part of the name, instance1, will remain unchanged.

Rename a virtual server :-

1. Using Cluster Administrator, change the SQL Network Name or SQL Server DNS Name to the new name.

2. Take the network name resource offline. This takes the SQL Server resource and other dependent resources offline as well.

3. Bring the SQL Server resource back online.

Verify the Renaming Operation :-

1) Using @@ServerName

2) Using sys.servers

Minimize network propagation delay :-

ipconfig /flushdns

ipconfig /registerdns

nbtstat –RR

You are done ! You should ask your application teams to reconnect with new Name or if they are using IP address then no changes required.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Rename or Change SQL Server Standalone Named Instance


Today, We will go through with the steps of renaming or changing SQL Server Standalone Named Instance.

Renaming or Changing SQL Server Standalone Named Instance is almost same as SQL Server Standalone Default Instance. You can change a SQL server named SQLServerinstance1 to some other name, such as SQLServerNewinstance1, but the instance portion of the name, instance1, will remain unchanged.

Refer : Rename or Change SQL Server Standalone Default Instance ()

Step 1 : Check Current Instance & host name

Current Host Name : admin-0783e4076

Current SQL Instance Name : admin-0783e4076SQL2K5_2

Select @@ServerName ServerName, Host_name() HostName

Step 2 : Rename Host name & reboot the server

Step 3 : Try to connect with SQL server ADMIN-0783E4076SQL2K5_2 & you will face below error because no instance of server name [ADMIN-0783E4076SQL2K5_2] exists as server name got changed

Cannot connect to ADMIN-0783E4076SQL2K5_2.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

Step 4 : Try to connect with “.SQL2K5_2” And Re Check Instance & host name and Drop old server name from server list of SQL

Exec sp_dropserver [ADMIN-0783E4076SQL2K5_2]

Step 5 : Add new server name as default server in server list of SQL

Exec sp_addserver [Win2K3_1SQL2K5_2],local

You can check default & other server ddetails from sys.servers.

SELECT * FROM SYS.SERVERS

Step 6 : Restart SQL Services

Step 7 : Try to connect with [Win2K3_1SQL2K5_2] And Re Check Instance & host name

You are done !

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 1 for SQL Server 2012 Service Pack 2 Is Now Available !


The 1st cumulative update release for SQL Server 2012 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 1 contains all the hotfixes released since the initial release of SQL Server 2012 SP2.

Those who are facing severe issues with their environment, they can plan to test CU1 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP3 final release to deploy on your production environment, to have consolidate build.

KB Article For CU1 of SQL Server 2012 SP2

Download Link of SQL Server 2012 SP2 :

SQL Server 2012 SP2 : http://www.microsoft.com/en-us/download/details.aspx?id=43340

SQL Server 2012 SP2 EXPRESS : http://www.microsoft.com/en-us/download/details.aspx?id=43351

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

SQL Server 2012 with SP2 Slipstream ISO images fixed


Microsoft fixed SQL Server 2012 with SP2 Slipstream ISO images do not install SP2 issue. The new ISOs have been posted on their respective release channels (MSDN…) and are now available.

Refer : http://support.microsoft.com/kb/2984316

Source : http://blogs.msdn.com/b/sqlreleaseservices/archive/2014/07/18/sql-server-2012-with-sp2-slipstream-iso-images-fixed.aspx

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 11 for SQL Server 2012 Service Pack 1 Is Now Available !


The 11th cumulative update release for SQL Server 2012 Service Pack 1 is now available for download at the Microsoft Support site. Cumulative Update 11 contains all the hotfixes released since the initial release of SQL Server 2012 SP1.

Those who are facing severe issues with their environment, they can plan to test CU11 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP2 final release to deploy on your production environment, to have consolidate build.

KB Article For CU11 of SQL Server 2012 SP1

Previous Cumulative Update KB Articles:

· CU#10 KB Article: http://support.microsoft.com/kb/2954099

· CU#9 KB Article: http://support.microsoft.com/kb/2931078

· CU#8 KB Article: http://support.microsoft.com/kb/2917531

· CU#7 KB Article: http://support.microsoft.com/kb/2894115

· CU#6 KB Article: http://support.microsoft.com/kb/2874879

· CU#5 KB Article: http://support.microsoft.com/kb/2861107

· CU#4 KB Article: http://support.microsoft.com/kb/2833645

· CU#3 KB Article: http://support.microsoft.com/kb/2812412

· CU#2 KB Article: http://support.microsoft.com/kb/2790947

· CU#1 KB Article: http://support.microsoft.com/kb/2765331

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Steps to install Service Pack for SQL Server 2005 – Multiple Instances


Steps to install Service Pack for SQL Server 2005 on multiple instances in one go :-

Step 1 : First page of Service pack setup. Informatory details only.

Step 2 : Confirm EULA (End User License Agreement) to move ahead.

Step 3 : Select instance & features you want to patch or upgrade.

Note : You can upgrade or patch all available SQL Server instances in one go. You can select the check box of each component or available instance to upgrade that in same installation.

Step 4 : Provide authentication details to setup to connect to Database Engine to execute upgrade scripts etc.

Step 5 : Select checkbox, if you want to report error & usage details to Microsoft.

Step 6 : Currently, Below files related to SQL Server are in use. Instillation need lock on these files to upgrade them during installation. Microsoft is quite smart here, You can continue with your installation when these files is in use. Only impact if that, You need to reboot your Windows box aafter installation so that after reboot SQL can replace the below files with upgraded one to continue with upgraded version.

Step 7 : You have provided all details, Now click on install & relax.

Step 8 : Installation completed.

Step 9 : You can view installation summary for more details. More useful in case of any issue or errors.

Step 10 : Additional step, you can click finish & you are done.

Normally, We do not perform Provisioning step on servers.

Step 11 : Reboot Windows Box.

Step 12 : Check SQL server version, SQL Jobs, Backup status, Windows error log, SQL error log as sanity check to check, if we face any issue after patching.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

How to Check SQL Server Installation Date Time ?


You need to run the following query and it will give you the date of your SQL Server Installation.

SELECT @@SERVERNAME SERVERNAME, CREATE_DATE ‘INSTALALTIONDATE’

FROM SYS.SERVER_PRINCIPALS

WHERE SID = 0X010100000000000512000000

SID 0X010100000000000512000000 is belongs to user "NT AUTHORITYSYSTEM". This user create at the time of installation only.

If you liked this post, do like on Facebook at

Reference : Rohit Garg ()

Mainstream Support End || SQL Server 2008 and SQL Server 2008 R2


Mainstream Support for SQL Server 2008 and SQL Server 2008 R2 was ended on July 8th 2014. This will result no enchantment or no future release of SPs. Somehow, Critical bug fixes may be release as QFE etc.

This sequence stop with SQL Server 2008 Service Pack 3 CU16 and SQL Server 2008 R2 Service Pack 2 CU13.

Refer Links :-

http://mssqlfun.com/2014/07/02/cumulative-update-13-for-sql-server-2008-r2-service-pack-2-is-now-available/

http://blogs.msdn.com/b/sqlreleaseservices/archive/2014/03/17/cumulative-update-16-for-sql-server-2008-sp3.aspx

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

How to restore MSDB & MODEL database – SQL Server System Databases?


The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. Issues in MSDB restore that may result loss of all scheduling information, as well as the backup and restore history. You cannot restore a database that is being accessed by users. If SQL Server Agent is running, it can access msdb. Therefore, before restoring msdb, stop SQL Server Agent.

The model database is used as the template for all databases created on an instance of SQL Server. SQL Server created tempdb every time SQL Server is started that required existence of model database. Whenever new database created on SQL Server, DB Engine took entire contents of the model database & its database options to copy & create the new database.

System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server 2008 SP2, you must use a database backup that was created after the server instance was upgraded to SQL Server 2008 SP2.

MSDB & Model database restore is almost same as other user database restore.

Steps to restore SQL Server MSDB & MODEL Database :-

1) For MSDB Database Stop SQL Server Agent Services to disconnect SQL Server Agent session from MSDB

2) Close all connection from MODEL & MSDB database

3) Restore MSDB from desired backup

RESTORE DATABASE MSDB

FROM DISK = ‘<Backup File Location>’

WITH REPLACE

RESTORE DATABASE MODEL

FROM DISK = ‘<Backup File Location>’

WITH REPLACE

4) You are done.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Follow

Get every new post delivered to your Inbox.

Join 202 other followers