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/)

How to change TempDB system Database files location?


Steps to change TempDB system Database files location :-

1) Check current file location of TempDB

Exec SP_helpDB TempDB

You can use below query as well for file location details.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(‘TempDB’);

2) Execute Alter Database command with Modify Filename option on Tempdb System Database

USE master

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = ‘E:SQL2K5_1TempDBtempdb.mdf’)

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = ‘E:SQL2K5_1TempDBtemplog.ldf’)

OUTPUT :-

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

3) Restart SQL Server (TempDB automatically recreate from scratch every time SQL Server restart. To change files location, Just need to alter the file location done in step 2 & after restart SQL server will create files at new mentioned location)

4) Check file location of TempDB – After restart

5) Delete Old TempDB files from previous location to clear disk space from unused & junk files

Note : TempDB file location cannot change with deattach attach method. TempDB availability is mandatory for SQL server to come online.

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

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

Cumulative Update – 2 for SQL Server 2014 RTM Is Now Available !


The 2nd cumulative update release for SQL Server 2014 RTM is now available for download at the Microsoft Support site. Cumulative Update 2 contains all the hotfixes released since the initial release of SQL Server 2014 RTM.

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

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

KB Article For CU2 of SQL Server 2014 RTM

Previous Cumulative Update KB Articles:

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

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

Cumulative Update – 13 for SQL Server 2008 R2 Service Pack 2 Is Now Available !


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

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

NOTE:
This is the final Cumulative Update for SQL Server 2008 R2 Service Pack 2. SQL Server 2008 R2 SP2 exits mainstream support on July 8, 2014.

I am Bit upset from Microsoft, they should release SQL Server 2008 R2 Service Pack 3 instead of CU13. Now, No enchantment or no future release of SPs for SQL Server 2008 R2 (http://mssqlfun.com/2014/06/12/mainstream-support-ending-soon-sql-server-2008-and-sql-server-2008-r2/).

KB Article For CU13 of SQL Server 2008 R2 SP2

Previous Cumulative Update KB Articles:

· CU#12 KB Article: http://support.microsoft.com/kb/2938478

· CU#11 KB Article: http://support.microsoft.com/kb/2926028

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

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

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

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

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

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

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

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

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

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

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

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

categories

Follow

Get every new post delivered to your Inbox.

Join 189 other followers