deleting *.cache files from SQL Server

Cache files are generated when Data collection is enabled and Collection set is running. You can delete the files if not required after disabling the Data Collector. USE msdb; GO EXEC dbo.sp_syscollector_disable_collector; To enabled Data Collector USE msdb; GO EXEC dbo.sp_syscollector_enable_collector ;    

Script LOGIN HASHED PASSWORD

LOGINPROPERTY function gives you the stored has password. SELECT LOGINPROPERTY(‘LoginName’,’PASSWORDHASH’); Output 0x020056986E23A5C403130F4B2DAD7ADBC835B9A662E70AE1937A7B7097DE2B36C58C3FDCB8912CF28DERP0A348918F559D53BF8868BA42D7FE1E400AE636862F939BC1DACFAEE3DB To use this hashed password , you have to use HASHED keyword. CREATE LOGIN LoginName WITH PASSWORD=0x020056986E23A5C403130F4B2DAD7ADBC835B9A662E70AE1937A7B7097DE2B36C58C3FDCB8912CF28DERP0A348918F559D53BF8868BA42D7FE1E400AE636862F939BC1DACFAEE3DB HASHED;  

Get List of ASSEMBLY and PATH

Get List of assembly and there paths SELECT assembly = saa.name, path = af.name FROM sys.assemblies AS sa INNER JOIN sys.assembly_files AS af ON sa.assembly_id = af.assembly_id WHERE sa.is_user_defined = 1;

Script – Server Level – Database Level Permissions

The Script scripts all the permission granted to each login on Server and Database Level. DECLARE @Start int=1 DECLARE @End int DECLARE @DatabaseName varchar(100) DECLARE @cmd nvarchar(4000) DECLARE @Permission Table (ID int Identity,Script varchar(max)) DECLARE @DbList Table (id int identity,DBName varchar(100)) INSERT INTO @DbList(DBName) SELECT name FROM sys.databases where name not in (‘master’,’model’,’msdb’,’tempdb’) and state=0 […]

Copy backup file between different domains using mapped network drives

How to copy backup files between to different domains having trust relationship? use master go exec xp_cmdshell ‘net use Z: \sourceserverH$Backups password /user:domainusername’ go exec usp_copybackupfiles @searchdirectory=’Z:’, @filefilter=’.bak’, @database=’AdventureWorks2014′, @Destination=’H:Backupscopyfiles’ go Exec master.dbo.xp_cmdshell ‘net use Z: /delete’ go  

Copy backup files between locations –TSQL

Handy Script to Copy the latest backup file between locations /* Written by : Mohammad Sufian Location : Singapore Copywrite : None. Suggestion/Feedback : gosgenq@gmail.com Supported versions: SQL Server 2008 R2 to SQL Server 2014 */ CREATE PROCEDURE dbo.usp_copybackupfiles @searchdirectory nvarchar(4000), @filefilter varchar(7), @database varchar(128), @Destination nvarchar(4000) AS BEGIN SET NOCOUNT ON DECLARE @command varchar(8000) […]

Map – Disconnect network drives – TSQL

Configure Map Network Drive using T-SQL GO xp_cmdshell “net use Z: \\ServerName\drive$\FolderName <password> /user:domainusername GO Disconnect Mapped Network drives GO Exec master.dbo.xp_cmdshell ‘net use Z: /delete’ GO

BULK Insert from text file

CREATE TABLE #_txtrow (_txtrow varchar(5000)); BULK INSERT #_txtrow From ‘H:\<folder name>\filename.extension’ WITH(DATAFILETYPE = ‘char’,ROWTERMINATOR = ‘n’); SELECT * FROM #_txtrow DROP TABLE #_txtrow

Robocopy : Copy files

Copy all files from the Source Folder PS C:> robocopy Source Destination ——————————————————————————- ROBOCOPY     ::     Robust File Copy for Windows ——————————————————————————- Started : Tuesday, June 9, 2015 9:08:16 AM Source : \ServerNamez$BackupFULL Dest : G:BACKUPSVOL01Restore Files : *.* Options : *.* /DCOPY:DA /COPY:DAT /R:1000000 /W:30 Copy all files from the Source Folder excluding files PS […]

List All Objects Created on All Filegroups in a Database

How can I find which object belongs to which filegroup. Is there any way to know this?   SELECT quotename(schema_name(o.schema_id)) +’.’+ quotename(o.[name]) ObjectName, o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id AND o.type = ‘U’ –and […]

List All Objects Created on All Filegroups in a Database

Table on Filegroup (s) SELECT quotename(schema_name(o.schema_id)) +’.’+ quotename(o.[name]) ObjectName, o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id AND o.type = ‘U’ –and f.name=’DataFileGroup’