Archive

Archive for the ‘T-SQL-Scripts’ Category

Copy backup file between different domains using mapped network drives

September 27, 2015 Leave a comment

How to copy backup files between to different domains having trust relationship?

 

use master
go
exec xp_cmdshell 'net use Z: \\sourceserver\H$\Backups password /user:domain\username'
go
exec usp_copybackupfiles 
@searchdirectory='Z:\',
@filefilter='.bak',
@database='AdventureWorks2014',
@Destination='H:\Backups\copyfiles\'
go
Exec master.dbo.xp_cmdshell 'net use Z: /delete'
go
Categories: T-SQL-Scripts

Copy backup files between locations –TSQL

September 27, 2015 Leave a comment

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)
	--DECLARE @searchdirectory				nvarchar(4000)	='H:\Backups'
	--DECLARE @filefilter					varchar(7)		='.bak'
	--DECLARE @database						varchar(128)	='AdventureWorks2014'
	DECLARE @useallmatchingfiles			int				=		0

	DECLARE @RestoreDataLocation					nvarchar(4000)
	DECLARE @RestoreLogLocation						nvarchar(4000)

	DECLARE @start int=1
	DECLARE @end int

	DECLARE @Source				nvarchar(4000)
	--DECLARE @Destination		nvarchar(4000)='H:\Backups\copyfiles\'
	DECLARE @cmd				nvarchar(4000)
	DECLARE @cmd2				nvarchar(4000)
	DECLARE @backupfile			nvarchar(4000)
	DECLARE @checkfile			nvarchar(4000)
	DECLARE	@combineBackups		varchar(max)

	IF NOT EXISTS (select * from master.sys.sysobjects where name='CopyBackupFileStatus')
		BEGIN
			CREATE TABLE master.dbo.CopyBackupFileStatus
			(
			id int identity,
			sourcelocation nvarchar(4000) not null,
			destinationlocation nvarchar(4000) not null,
			[filename]			nvarchar(4000) not null,
			[copydate]			datetime
			)
		END



	create table #Output (outputtext varchar(max))

	DECLARE @BackupFiles Table (id int identity,BackupFileName nvarchar(4000))
	DECLARE @fileexists TABLE (File_Exists BIT, File_Is_A_Directory BIT, Parent_Directory_Exists BIT)
	DECLARE @MovedBackupFiles Table (id int identity,BackupFileName nvarchar(4000))
	CREATE TABLE #vDBHList 
	(
	DBID INT NOT NULL       IDENTITY(1, 1),BackupName        nvarchar(128),BackupDescription  nvarchar(255),
	BackupType              smallint,ExpirationDate          datetime,Compressed              tinyint,
	Position                smallint,DeviceType              tinyint,UserName                 nvarchar(128),
	ServerName              nvarchar(128),DatabaseName       nvarchar(128),DatabaseVersion    int,
	DatabaseCreationDate    datetime,BackupSize              numeric(20,0),FirstLSN           numeric(25,0),
	LastLSN                 numeric(25,0),CheckpointLSN      numeric(25,0),DatabaseBackupLSN  numeric(25,0),
	BackupStartDate         datetime,BackupFinishDate        datetime,SortOrder               smallint,
	CodePage                smallint,UnicodeLocaleId         int,UnicodeComparisonStyle       int,
	CompatibilityLevel      tinyint,SoftwareVendorId         int,SoftwareVersionMajor         int,
	SoftwareVersionMinor    int,SoftwareVersionBuild         int,MachineName                  nvarchar(128),
	Flags                   int,BindingID                    uniqueidentifier,RecoveryForkID  uniqueidentifier,
	Collation               nvarchar(128),FamilyGUID         uniqueidentifier,HasBulkLoggedData       bit,
	IsSnapshot              bit,IsReadOnly                   bit,IsSingleUser            bit,
	HasBackupChecksums      bit,IsDamaged                    bit,BeginsLogChain          bit,
	HasIncompleteMetaData   bit,IsForceOffline               bit,IsCopyOnly              bit,
	FirstRecoveryForkID     uniqueidentifier,ForkPointLSN    numeric(25,0) NULL,RecoveryModel           nvarchar(60),
	DifferentialBaseLSN     numeric(25,0) NULL,DifferentialBaseGUID    uniqueidentifier,
	BackupTypeDescription   nvarchar(60),BackupSetGUID       uniqueidentifier NULL
	)
	Declare @version varchar(2)
	select @version = substring(convert(nvarchar(100),serverproperty('ProductVersion')),0, 
                                    charindex('.',convert(nvarchar(100),serverproperty('ProductVersion'))))
	--print @version
	if (convert (int,@version ) <12)
		begin
		 ALTER TABLE #vDBHList  ADD CompressedBackupSize int
		 ALTER TABLE  #vDBHList  ADD Containment int	 
		end

	else if (convert (int,@version ) >=12)
		begin
		 ALTER TABLE #vDBHList  ADD CompressedBackupSize int
		 ALTER TABLE  #vDBHList  ADD Containment int	 
		 ALTER TABLE  #vDBHList  ADD KeyAlgorithm nvarchar(32)	 
		 ALTER TABLE  #vDBHList  ADD EncryptorThumbprint varbinary(20)	 
		 ALTER TABLE  #vDBHList  ADD EncryptorType nvarchar(32)	 
		end

	--select @command = 'powershell "$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (1500, 25); $a = Get-Date; $a = $a.AddDays(-6); ' 
	select @command = 'powershell "' 
	select @command += '$path = '''+@searchdirectory+''';$files = @();$path.split('','') |% {$innerPath = $_; $files += get-childitem $innerpath}; '
	--select @command += '$files = $files  |? {$_.CreationTime -gt $a  -AND $_.Extension -eq '''+UPPER(@filefilter)+''' -AND $_.name.toupper().contains('''+UPPER(@database)+''')};  ; '
	select @command += '$files = $files  |? {$_.Extension -eq '''+UPPER(@filefilter)+''' -AND $_.name.toupper().contains('''+UPPER(@database)+''')};  ; '
	select @command += ' $files '

	if @useallmatchingfiles = 0 
	select @command += ' |? {$_.CreationTimeUTC -gt (($files | sort CreationTimeUTC -desc | select CreationTimeUTC -unique -first 1).CreationTimeUTC).AddSeconds(-1)} '

	select @command += ' | ft fullname;"'

	----print @command
	insert into #Output exec xp_cmdshell @command

	DELETE from #Output where outputtext IS NULL or outputtext ='FullName                                                                       ' 
	or  outputtext='--------                                                                       '
	insert into @BackupFiles
	SELECT * from #Output
	SET @end=@@ROWCOUNT

		WHILE @start <= @end

			BEGIN

				SELECT @Source=BackupFileName FROM @BackupFiles WHERE id=@start	
				SET @cmd='copy '+@Source+ ' ' +@Destination
				--print @Source

				SET @backupfile=REVERSE(SUBSTRING (REVERSE(@source),0,CHARINDEX('\',REVERSE(@source))))

					--//--
					--Check if file exists and do the verfication
					--//--
							SET @checkfile=@Destination+@backupfile
				
							insert into @fileexists
							exec master..xp_fileexist  @checkfile
				
							if (select File_Exists from  @fileexists)=1
								begin								
									set @cmd2='RESTORE HEADERONLY FROM DISK= '+''''+@checkfile+''''
									insert into #vDBHList
									exec(@cmd2)								
									insert into @MovedBackupFiles values (@checkfile)
								end						
							else
								begin
									EXECUTE xp_cmdshell @cmd,no_output
									insert into @MovedBackupFiles values (@checkfile)
									insert into master.dbo.CopyBackupFileStatus(sourcelocation,destinationlocation,[filename],copydate) values (@Source,@Destination,@backupfile,GETDATE())
								end
							DELETE FROM @fileexists
							--print @backupfile			
				SET @start=@start+1
			END
		
	drop table #vDBHList
	drop table #Output

	SET NOCOUNT OFF

END

 

How to use it?

 

usp_copybackupfiles 
@searchdirectory='H:\Backups',
@filefilter='.bak',
@database='AdventureWorks2014',
@Destination='H:\Backups\copyfiles\'

 

The script simple reads the file information from disk and copy the files to destination.

Categories: T-SQL-Scripts

Map/disconnect network drives – TSQL

September 27, 2015 Leave a comment

 

Configure Map Network Drive using T-SQL

 

exec xp_cmdshell 'net use Z: \\ServerName\H$\FolderName <password> /user:domain\username'

Disconnect Mapped Network drives

 

Exec master.dbo.xp_cmdshell 'net use Z: /delete'

Categories: T-SQL-Scripts

BULK Insert from text file

August 23, 2015 Leave a comment
CREATE TABLE #_txtrow  (_txtrow varchar(5000));


BULK INSERT #_txtrow From 'H:\ApacheCassandra\Monitoring\OutputLog\compactionstats\192_168_0_45\9.txt' 
WITH(DATAFILETYPE = 'char',ROWTERMINATOR = '\n');

SELECT * FROM #_txtrow

DROP TABLE #_txtrow
Categories: T-SQL-Scripts

SQL Server Server Property

June 10, 2015 Leave a comment

 

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY(‘productlevel’), SERVERPROPERTY(‘edition’)

 

image

Categories: T-SQL-Scripts

Robocopy : Copy files

June 10, 2015 Leave a comment

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 : \\ServerName\z$\Backup\FULL\
Dest : G:\BACKUPS\VOL01\Restore\
Files : *.*
Options : *.* /DCOPY:DA /COPY:DAT /R:1000000 /W:30

Copy all files from the Source Folder excluding files

PS C:\> Robocopy.exe \\ServerName\z$\Backup\DIFF G:\BACKUPS\VOL01\Restore /XF Filename_20150608_200017*.*

——————————————————————————-
   ROBOCOPY     ::     Robust File Copy for Windows
——————————————————————————-

Started : Wednesday, June 10, 2015 3:11:33 AM
Source : \\ServerName\z$\Backup\DIFF\
Dest : G:\BACKUPS\VOL01\Restore\
Files : *.*
Exc Files : Filename_20150608_200017*.*

Options : *.* /DCOPY:DA /COPY:DAT /R:1000000 /W:30

Categories: T-SQL-Scripts

List All Objects Created on All Filegroups in a Database

March 1, 2015 Leave a comment

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 f.name=’DataFileGroup’

 

image_thumb

Categories: T-SQL-Scripts