26 October 2018

19 October 2018

Data and Log File Move to New Location

Below is the script we can use to move the data and log files for the databases except master.


DECLARE
    @counter    INT = 1,
    @max        INT = 0,
    @NEW_DATA_PATH VARCHAR(500)='E:\XXXX\Data',
    @NEW_LOG_PATH VARCHAR(500)='F:\XXXX\Log'
  
DECLARE @FILE_LIST TABLE (
    [ID]    int IDENTITY,
    [DBNAME] VARCHAR(100),
    [LNAME]   VARCHAR(100),
    [PNAME]   VARCHAR(500),
    [TYPE_DESC]  VARCHAR(50)
    )  
INSERT INTO @FILE_LIST
SELECT n.NAME,f.NAME,f.PHYSICAL_NAME,f.TYPE_DESC
FROM MASTER.SYS.MASTER_FILES f,MASTER.SYS.DATABASES n WHERE n.NAME not in('MASTER') 
and f.DATABASE_ID=n.DATABASE_ID

SELECT @max = COUNT([ID]) FROM @FILE_LIST

WHILE @counter <= @max
BEGIN

 DECLARE @FILE_TYPE VARCHAR(100),
         @DBNAME VARCHAR(100),
         @LOGICAL_NAME VARCHAR(100),
         @PHYSICAL_NAME VARCHAR(500) ;
          
 SELECT @FILE_TYPE=TYPE_DESC,@DBNAME=[DBNAME],@LOGICAL_NAME=[LNAME],
 @PHYSICAL_NAME=SUBSTRING([PNAME],LEN([PNAME])-CHARINDEX('\',REVERSE([PNAME]))+2,LEN([PNAME])-1) 
 FROM @FILE_LIST WHERE [ID] = @counter
    
    IF(@FILE_TYPE='ROWS')
    BEGIN
    --ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
        --PRINT @FILE_TYPE+','+@DBNAME+','+@LOGICAL_NAME+','+@PHYSICAL_NAME
        PRINT 'USE MASTER 
               GO 
               ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_DATA_PATH+'\'+@PHYSICAL_NAME+''')
               GO'
  END
  ELSE
   PRINT 'USE MASTER 
          GO 
          ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_LOG_PATH+'\'+@PHYSICAL_NAME+''')
          GO'
     SET @counter = @counter + 1
      
 END


09 June 2018

Delete Maintenance Plans

We need to perform the below steps to delete the jobs or maintenance plans in such cases from sysmaintplan_log table or fromsysmaintplan_subplans table .
Step 1: 
Execute the below query to obtain the Maintenance plan name and Id 
SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS 
Step 2: 
Replace the Id obtained from Step 1 into the below query and delete the entry from log table
DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' ' 
Step 3: 
Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below,
DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '
Step 4: 
Finally delete the maintenance plan using the below query where ID is obtained from Step1
DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '
Step 5: 
Check and delete the jobs from SSMS if it exists.

Many thanks to kapil

10 May 2018

Cannot detach a suspect or recovery pending database. It must be repaired or dropped.

Note: Below method of removing database is only when database shows RECOVERY_PENDING because of  MDF/LDF files missing.

Solution:

ALTER DATABASE DBNAME SET OFFLINE
GO
sp_detach_db 'DBNAME'
GO


In Other Cases Please Refer to (Please Note: Check with customer for dataloss)

https://dbamohsin.wordpress.com/2012/01 /23/cannot-detach-a-suspect-or-recovery-pending-database/

Prefer: Use latest backups available to restore the database.