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?
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