RAVI VERPULA BLOG SPOT

  Missing MSI’s/MSP’s error during SQL Server Patching Missing MSI’s/MSP’s errors will be coming    1. while Applying Service packs   2. While Installing SQL Server instance (another instance in same box)  Reason: As the error states a possible Windows Installer database file (.msi) or the Windows Installer patch file (.msp) is missing from the Windows Installer cache. This normally occurs when the setup is stopped mid-way or due to file corruptions.  Also As part of C drive space management windows team used to delete files from folder ‘ C:\windows\Installer\’   where our SQL Server instance dependent  MSI/MSP files are located. so if these files are deleted we will get MSI/MSP files missing errors while applying SQL Service packs (or) doing another Instance of SQL Server in the same server  Fix: Below is an amazing support link by Microsoft which explains the cause and resolution for the above error. It also provides a vb script  FindSQLInstalls.vbs  that helps find the missing .msi

  CREATE TABLE #CPUValues ( [ index ] SMALLINT , [ description ] VARCHAR ( 128 ), [ server_cores ] SMALLINT , [ value ] VARCHAR ( 5 ) ) CREATE TABLE #MemoryValues ( [ index ] SMALLINT , [ description ] VARCHAR ( 128 ), [ server_memory ] DECIMAL ( 10 , 2 ), [ value ] VARCHAR ( 64 ) ) INSERT INTO #CPUValues EXEC xp_msver 'ProcessorCount' INSERT INTO #MemoryValues EXEC xp_msver 'PhysicalMemory' SELECT SERVERPROPERTY ( 'SERVERNAME' ) AS 'instance' , v . sql_version , ( SELECT SUBSTRING ( CONVERT ( VARCHAR ( 255 ), SERVERPROPERTY ( 'EDITION' )), 0 , CHARINDEX ( 'Edition' , CONVERT ( VARCHAR ( 255 ), SERVERPROPERTY ( 'EDITION' )))) + 'Edition' ) AS sql_edition , SERVERPROPERTY ( 'ProductLevel' ) AS 'service_pack_level' , SERVERPROPERTY ( 'ProductVersion' ) AS 'build_number' , ( SELECT DI

  Please do a Refresh from Production Database(PROD) to QA/DEV Database (DEV) on a Server ... --- Steps in Line 1. Take a Full Backup(.bak) of Source Database. --Completed 2. Validate the taken Backup. -- Completed 3. Take the Full Backup(.bak) of Destination Database. --Completed 4. Validate the taken Backup. -- Completed 5. Restore the Source Database (.bak) file to the destination Database. -- Completed 6. Valid the Restoration. -- Completed 7. Fix Orphan User. --Completed --- Step wise ---1. Take a Backup of a PROD Database --(Source) BACKUP DATABASE [PROD] TO DISK = N'C:\Tech DBA\Refresh\PROD_Backup\PROD_Full_Backup_10_10_2020.bak' WITH NOFORMAT, NOINIT, NAME = N'PROD-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO ---2. Validate Backup declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'PROD' and backup_set_id=(select max(backu

  Problem For compliance auditing, a customer asked for a list of users who have read or write access in any database on the SQL Server instance. Although there are several tables that can provide us the information, the permissions can be at the instance level or at the individual database level.  Further, the permissions can be granted through a role, a role that is member of another role or directly to a login or database user, so it requires some time to prepare the report. Solution The script below gives you the requested information in a small amount of time. For example, in one server with 25 databases it completed in 14 seconds. When it is run in the whole registered servers through a multi-server query (85 instances and 335 databases), it took 5 minutes and 38 seconds to return 10,372 rows with all the information. SQL Server Permissions Script Below is the script. ; WITH [ explicit ] AS ( SELECT [ p ].[ principal_id ], [ p ].[ name ], [ p ].[ type_desc ], [ p ].[