[MSSQL] Help Declaring @Variable in Script

C

Carlosinfl

Loves the juice
Joined
Sep 25, 2002
Messages
6,633
I've been tasked to move two MDF files that are SECONDARY file group from my local C:\ to D:\ drive. I was also told I need to do this in a way where I declare variables so that this script CAN work on SQL Server (aka, make this move script portable). I'm new in my DBA role and learning SQL Server so I've never declared a variable for tasks in SQL code.
Below is what I'm doing which WILL work fine if manually executed however I couldn't take this SQL and run it on any other box as it's not portable. How would I make it portable declaring variables for the task at hand?

Code: 
USE [CacheDB]
GO

ALTER DATABASE [CacheDB] modify file
  (
    name = 'CacheDB_2',
    filename = 'd:\data\CacheDB_2.ndf'
  ) 
GO

ALTER DATABASE [CacheDB] modify file
  (
     name = 'CacheDB_3'
     filename = 'd:\data\CacheDB_3.ndf'
  )
GO

ALTER DATABASE [CacheDB] SET offline with ROLLBACK IMMEDIATE
GO

EXEC master.dbo.xp_cmdshell 'copy c:\sql\CacheDB_2.ndf d:\data\CacheDB_2.ndf'
EXEC master.dbo.xp_cmdshell 'copy c:\sql\CacheDB_3.ndf d:\data\CacheDB_3.ndf'

ALTER DATABASE [CacheDB] SET online 
GO

--DELETE THE OLD DATABASE FILES
EXEC master.dbo.xp_cmdshell ‘DEL /Q “c:\sql\CacheDB_2.mdf”‘
EXEC master.dbo.xp_cmdshell ‘DEL /Q “c:\sql\CacheDB_3.mdf”‘
GO
 
Rough Draft, but should work (Grouped it by datafile instead of filegroup, but meh, should still work):

Code: 
DECLARE 
	 @SourceNm VARCHAR(300)
	,@FileNm VARCHAR(300)
	,@SourcePath VARCHAR(3000)
	,@TargetPath VARCHAR(3000) = '<TargetPathHere>'
	,@AlterScr VARCHAR(500)
	,@CopyScr VARCHAR(500)
	,@DelScr VARCHAR(500)

SET @TargetPath = CASE WHEN RIGHT(@TargetPath,1) <> '\' THEN @TargetPath + '\' ELSE @TargetPath END

DECLARE 
	TblPath CURSOR FOR 
SELECT 
	a.name, a.physical_name FROM 
	CacheDB.sys.database_files a 
	JOIN 
	CacheDB.sys.filegroups b 
	ON 
		a.data_space_id = b.data_space_id
WHERE b.name in ('<Filegroup/s here')
OPEN TblPath 
  
FETCH NEXT FROM 
	TblPath INTO @SourceNm, @SourcePath 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 

	SELECT 
		@FileNm  = RIGHT(@SourcePath, LEN(@SourcePath) 
					- (LEN(@SourcePath)- PATINDEX('%\%',REVERSE(@SourcePath))) 
					- 1)

		,@AlterScr =    'ALTER DATABASE [CacheDB] modify file '+ 
					+ '(name = ''' + @SourceNm + ''' filename = ''' 
					+ @TargetPath + @FileNm + ''');'
		,@CopyScr = 'COPY "' + @SourcePath + '" "' + @TargetPath + @FileNm + '"'
		,@DelScr = 'DEL /Q "' + @SourcePath + '"'
	
	EXEC(@AlterScr)
	ALTER DATABASE [CacheDB] SET offline with ROLLBACK IMMEDIATE
	EXEC master..xp_cmdshell @CopyScr
	ALTER DATABASE [CacheDB] SET online 
	EXEC master..xp_cmdshell @DelScr
	FETCH NEXT FROM TblPath INTO @SourceNm, @SourcePath 
END 

CLOSE TblPath 
DEALLOCATE TblPath
 
You must log in or register to reply here.
Back
Top