SQL DBA - Get Complete Information of a SQL Server

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

CONVERT(CHAR(100), SERVERPROPERTY('ProductVersion')) AS ProductVersion,

CONVERT(CHAR(100), SERVERPROPERTY('ProductLevel')) AS ProductLevel,

CONVERT(CHAR(100),SERVERPROPERTY('ResourceLastUpdateDateTime'))AS ResourceLastUpdateDateTime,

CONVERT(CHAR(100), SERVERPROPERTY('ResourceVersion')) AS ResourceVersion,

CASE

WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1

      THEN 'Integrated security'

WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0

      THEN 'Not Integrated security'

END AS IsIntegratedSecurityOnly,

CASE WHEN SERVERPROPERTY('EngineEdition') = 1

      THEN 'Personal Edition'

WHEN SERVERPROPERTY('EngineEdition') = 2

      THEN 'Standard Edition'

WHEN SERVERPROPERTY('EngineEdition') = 3

      THEN 'Enterprise Edition'

WHEN SERVERPROPERTY('EngineEdition') = 4

      THEN 'Express Edition'

END AS EngineEdition,

CONVERT(CHAR(100), SERVERPROPERTY('InstanceName'))AS InstanceName,

CONVERT(CHAR(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))AS ComputerNamePhysicalNetBIOS,

CONVERT(CHAR(100), SERVERPROPERTY('LicenseType'))AS LicenseType,

CONVERT(CHAR(100), SERVERPROPERTY('NumLicenses'))AS NumLicenses,

CONVERT(CHAR(100), SERVERPROPERTY('BuildClrVersion'))AS BuildClrVersion,

CONVERT(CHAR(100), SERVERPROPERTY('Collation'))AS Collation,

CONVERT(CHAR(100), SERVERPROPERTY('CollationID'))AS CollationID,

CONVERT(CHAR(100), SERVERPROPERTY('ComparisonStyle'))AS ComparisonStyle,

CASE WHEN CONVERT(CHAR(100),SERVERPROPERTY('EditionID')) = -1253826760

      THEN 'Desktop Edition'

WHEN SERVERPROPERTY('EditionID') = -1592396055

      THEN 'Express Edition'

WHEN SERVERPROPERTY('EditionID') = -1534726760

      THEN 'Standard Edition'

WHEN SERVERPROPERTY('EditionID') = 1333529388

      THEN 'Workgroup Edition'

WHEN SERVERPROPERTY('EditionID') = 1804890536

      THEN 'Enterprise Edition'

WHEN SERVERPROPERTY('EditionID') = -323382091

      THEN 'Personal Edition'

WHEN SERVERPROPERTY('EditionID') = -2117995310

      THEN 'Developer Edition'

WHEN SERVERPROPERTY('EditionID') = 610778273

      THEN 'Enterprise Evaluation Edition'

WHEN SERVERPROPERTY('EditionID') = 1044790755

      THEN 'Windows Embedded SQL'

WHEN SERVERPROPERTY('EditionID') = 4161255391

      THEN 'Express Edition with Advanced Services'

END AS ProductEdition,

CASE WHEN CONVERT(CHAR(100),SERVERPROPERTY('IsClustered')) = 1

      THEN 'Clustered'

WHEN SERVERPROPERTY('IsClustered') = 0

      THEN 'Not Clustered'

WHEN SERVERPROPERTY('IsClustered') = NULL

      THEN 'Error'

END AS IsClustered,

CASE WHEN CONVERT(CHAR(100),SERVERPROPERTY('IsFullTextInstalled')) = 1

      THEN 'Full-text is installed'

WHEN SERVERPROPERTY('IsFullTextInstalled') = 0

      THEN 'Full-text is not installed'

WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL

      THEN 'Error'

END AS IsFullTextInstalled,

CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSet'))AS SqlCharSet,

CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSetName'))AS SqlCharSetName,

CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrder'))AS SqlSortOrderID,

CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrderName'))AS SqlSortOrderName

ORDER BY CONVERT(CHAR(100), SERVERPROPERTY('Servername'))

SQL DBA - Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass'

TITLE: Microsoft Visual Studio

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

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Solution

To fix the problem do the following:

1.       Open the command prompt

2.       Navigate to C:\Program Files\Microsoft SQL Server\90\DTS\Binn

3.       Run C:\Program Files\Microsoft SQL Server\90\DTS\Binn>regsvr32 dts.dll

That should fix the problem.

SQL DBA - Delete OLD Backup files Using TSQL - SQL server 2005

Step 1 :

            USE MASTER

GO

Step 2 :

EXEC SP_CONFIGURE 'show advanced options',1

GO

Step 3 :

EXEC SP_CONFIGURE 'XP_CMDSHELL',1

GO

RECONFIGURE

GO

Step 4:

TSQL TO Delete All the Backup Files

DECLARE @DBbackuppath varchar(8000)

DECLARE @DBName varchar(255)

DECLARE @Command varchar(255)

DECLARE @Backup_finish_date datetime

DECLARE DATABASES_CURSOR CURSOR FOR

SELECT Physical_device_name,Backup_finish_date,database_name FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D' -- add a condition if u need a condition on Backup_finish_date

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBbackuppath,@Backup_finish_date,@DBName

WHILE @@FETCH_STATUS = 0

BEGIN

      FETCH NEXT FROM DATABASES_CURSOR INTO @DBbackuppath,@Backup_finish_date,@DBName

      set @DBbackuppath = 'del '+@DBbackuppath

      exec master..xp_cmdshell  @DBbackuppath

      END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

SQL 2005 - How to move System DB in SQL 2005

Moving System Database in SQL Server 2005

1. Ensure the Destination Drive is having enough space then the Current one.

2. For Example :-

       Current Sys DB path : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

       To Move                   : D:\System DB

3    Moving Model DB

           

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d.  Open a Command prompt ( Run -> cmd)

            e.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            f.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            g.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..

                 Dont give any details, Just open SSMS)

            h.  Select the New Query Option ( Now you give the Username and password)   .

            i.  Run this Query

                       

            SELECT name, physical_name AS CurrentLocation, state_desc

            FROM sys.master_files

            WHERE database_id = DB_ID(N'model')             

                        This will provide the current location of your Model DB.

            j. Use this Query to Detach the Model db.

                        use master

            go

            sp_detach_db 'model'

            go

      k. Move the Model DB files (.mdf & .ldf) file the Destination Folder

                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB

                       

            l. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            m.  Press Y . then the SQL Server will Shut down

            n.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

            l.  Run this Query

                        use master

            go

            sp_attach_db 'model','D:\System DB\model.mdf','D:\System DB\modellog.ldf'

            go

            p. Rerun this Query

                       

            SELECT name, physical_name AS CurrentLocation, state_desc

            FROM sys.master_files

            WHERE database_id = DB_ID(N'model')             

                        This will provide the current location of your Model DB.

                        ( I am Sure now the Model DB is moved to Destination Place)

4    Moving MSDB

           

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d.  Open a Command prompt ( Run -> cmd)

            e.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            f.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            g.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..

                 Dont give any details, Just open SSMS)

            h.  Select the New Query Option ( Now you give the Username and password)   .

            i.  Run this Query

                       

            SELECT name, physical_name AS CurrentLocation, state_desc

            FROM sys.master_files

            WHERE database_id = DB_ID(N'msdb')               

                        This will provide the current location of your Model DB.

            j. Use this Query to Detach the Model db.

                        use master

            go

            sp_detach_db 'msdb'

            go

      k. Move the MSDB files (.mdf & .ldf) file the Destination Folder

                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB

                       

            l. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            m.  Press Y . then the SQL Server will Shut down

            n.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

            o.  Run this Query

                        use master

            go

            sp_attach_db 'msdb','D:\System DB\msdbdata.mdf','D:\System DB\msdblog.ldf'

            go

     

            p. Rerun this Query

                        SELECT name, physical_name AS CurrentLocation, state_desc

            FROM sys.master_files

            WHERE database_id = DB_ID(N'msdb')               

                        This will provide the current location of your MSDB.

                        ( I am Sure now the MSDB is moved to Destination Place)

            q . Run this Query

                        USE MSDB

            Go

            EXEC sp_changedbowner 'sa'

            Go

4    Moving Master DB

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d. Move the Master DB files (.mdf & .ldf) file the Destination Folder

                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB

           

            e. Goto SQL Server Configuration Manager and open.

            f.  Select the SQL Sever 2005 Services.

            g.  Right click the SQL Server Service --> Properties --> Advanced (tab) --> Select the Startup Parameters.

            h.  Old Entry will be

                        -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf;

                        -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ERRORLOG;

                        -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf

                        Replace to

                        -dD:\System DB\master.mdf;

                        -eD:\System DB\ERRORLOG;

                        -lD:\System DB\mastlog.ldf

            i. goto cmd prompt and Type Sqlservr -f -T3608 and run          

            j.Run this Query

                        SELECT name, physical_name AS CurrentLocation, state_desc

            FROM sys.master_files

            WHERE database_id = DB_ID(N'master')                       

                        This will provide the current location of your master.

            k. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            l.  Press Y . then the SQL Server will Shut down

            m.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

5    Moving mssqlsystemresource DB

           

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

           

            d. Move the mssqlsystemresource DB files (.mdf & .ldf) file the Destination Folder

                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB

            e.  Open a Command prompt ( Run -> cmd)

            f.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            g.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            h.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..

                 Dont give any details, Just open SSMS)

            i.  Select the New Query Option  ( Now you give the Username and password)   .

            j.  Run this Query

                       

                        use master

            ALTER DATABASE mssqlsystemresource

            MODIFY FILE (NAME=data, FILENAME= 'D:\System DB\mssqlsystemresource.mdf');

            GO

            ALTER DATABASE mssqlsystemresource

            MODIFY FILE (NAME=log, FILENAME= 'D:\System DB\mssqlsystemresource.ldf');    

            GO

            k. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            l.  Press Y . then the SQL Server will Shut down

            m.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

6    Moving TEMPDB

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d.  Open a Command prompt ( Run -> cmd)

            e.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            f.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            g.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..

                 Dont give any details, Just open SSMS)

            h.  Select the New Query Option ( Now you give the Username and password)   .

            i.  Run this Query

                       

                        USE master;

            GO

            ALTER DATABASE tempdb

            MODIFY FILE (NAME = tempdev, FILENAME = 'D:\System DB\tempdb.mdf');

            GO

            ALTER DATABASE tempdb

            MODIFY FILE (NAME = templog, FILENAME = 'D:\System DB\templog.ldf');

            GO

            j. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            k.  Press Y . then the SQL Server will Shut down

            l.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

            m. Run this Query

                          SELECT name, physical_name AS CurrentLocation, state_desc

             FROM sys.master_files

             WHERE database_id = DB_ID(N'tempdb')

            ( I am Sure now the TEMPDB is moved to Destination Place)