January 27, 2010 at 9:36 am
Thank You in advance for reading my post.
I have a DB server where the folder structure that the DB's are in doesn't match my other 6 DB servers. We have product that we offer as a hosted product so it's important to keep all our DB servers setup the same way, I.E. same paths, same files, same versions etc. All I want to do it using TSQL move 16 databases each residing in a different instance of SQL.
Here is the code I have below. My issue is that it doesn't move the cxtData file since it says "there has been a sharing violation" I'm not sure if I have the sequence correct here in my alter, and offline commands to the DB.
Basically I'm moving the DB's from "C:\CXT\Databases\CXTxxxx" to -> "C:\CXT\Database\CXTxxxx"
Could someone please take a peek at my code and let me know if there is anything out of order?
/* Move DB Files Script */
-- Enable CMDShell
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
-- Enable CMDShell
DECLARE @sql VARCHAR(1000)
DECLARE @DRIVELETTER VARCHAR(10)
IF (SELECT LEFT(@@SERVERNAME, 6)) = 'CXTDB1'
BEGIN
SET @DRIVELETTER= 'C' -- If the server is CXTDB1 it only has a C Drive.
END
ELSE BEGIN
SET @DRIVELETTER = 'D' -- If the server is NOT DB1 then it has a D drive.
END
DECLARE @INSTANCEID VARCHAR(30) = (RIGHT((SELECT CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)),4))
DECLARE @COPYCMD VARCHAR(1000)
DECLARE @RENCMD VARCHAR(1000)
DECLARE @PATH VARCHAR(1000) = @DRIVELETTER +':\CXT\Database\CXT' + @INSTANCEID + '\'
--#########################################################################################
SELECT @RENCMD = 'MKDIR '+@DRIVELETTER+':\CXT\Database\CXT' + @INSTANCEID
EXEC master..xp_cmdShell @RENCMD, NO_OUTPUT
-- Update DB Files path
SET @sql = 'ALTER DATABASE cxtData MODIFY FILE (Name=cxtData, FILENAME = ''' + @Path + 'cxtdata-' + @INSTANCEID + '.mdf'')'
PRINT @sql
EXEC (@SQL)
SET @sql = 'ALTER DATABASE cxtData MODIFY FILE (Name=cxtData_log, FILENAME = ''' + @Path + 'cxtdata-' + @INSTANCEID + '.ldf'')'
PRINT @sql
EXEC (@SQL)
SET @sql = 'ALTER DATABASE cxtArchiveData MODIFY FILE (Name=cxtArchiveData, FILENAME = ''' + @Path + 'cxtArchiveData-' + @INSTANCEID + '.mdf'')'
PRINT @sql
EXEC (@SQL)
SET @sql = 'ALTER DATABASE cxtArchiveData MODIFY FILE (Name=cxtArchiveData_log, FILENAME = ''' + @Path + 'cxtArchiveData-' + @INSTANCEID + '.ldf'')'
PRINT @sql
EXEC (@SQL)
-- Physically Move the DB Files
SELECT @COPYCMD = 'XCOPY ' + @DRIVELETTER + ':\CXT\Databases\CXT'+ @INSTANCEID +'\*.* '+ @PATH + ' /e /I'
PRINT @COPYCMD
EXEC master..xp_cmdShell @COPYCMD
-- Take DB's Offline
SET @sql = 'ALTER DATABASE cxtData SET OFFLINE WITH NO_WAIT'
PRINT @sql
EXEC (@SQL)
SET @sql = 'ALTER DATABASE cxtArchiveData SET OFFLINE WITH NO_WAIT'
PRINT @sql
EXEC (@SQL)
-- Bring DB Files Online
SET @sql = 'ALTER DATABASE cxtData SET ONLINE'
PRINT @sql
EXEC (@SQL)
SET @sql = 'ALTER DATABASE cxtArchiveData SET ONLINE'
PRINT @sql
EXEC (@SQL)
--#############################################################################################
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'cxtData');
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'cxtArchiveData');
January 27, 2010 at 10:22 am
Wow, you are really going the long way around to do change the path. No need for that all code, really. Especially not the xp_cmdshell methods.
A simple detach, move the file(s), and then attach. Since this is on the same box, there is no need to go thru the Alter database command.
I would use the sp_detach_db stored procedure, in case that any Full text indexes will not be dropped, and will be moved along with the database file(s). Then to attach the database in the new location
USE master;
GO
CREATE DATABASE <DatabaseName>
ON (FILENAME = '<Path to data file>_Data.mdf'),
(FILENAME = '<Path to Log File>_Log.ldf')
FOR ATTACH;
GO
Andrew SQLDBA
January 27, 2010 at 10:50 am
Andrew thank you for looking. I was under the impression that the sp_detach_db was being deprecated and should no longer be used.
http://weblogs.sqlteam.com/dang/archive/2009/01/18/Dont-Use-sp_attach_db.aspx
Essentially I need the DB files to be copied to their new paths. I would rather copy than move as it leaves a copy behind should I need to revert back. This is why I'm using xp_cmdshell.
So I just need to understand the order here in how this is supposed to work?
1. Create the folders in the new path which I'm doing with the xp_cmdshell
2. use alter the databases to take the databases offline?
3. use xp_cmdshell - XCOPY - To copy the DB files to their new home. C:\CXT\Database\CXTxxxx\ ?
4. Use the alter database command "ALTER DATABASE MyDatabase MODIFY FILE" to tell SQL that the files have been moved. ?
5. use alter database to bring back online. ?
I'm assuming that after these steps complete, that the files would be copied and running from their new location?
Can you confirm if this is the correct sequence?
Thank You in advance.
Maybe I don't fully understand the ALTER database command and why I might use it over the detach and attach? Could you explain as it seems they are similar ways to accomplish the same thing.
AndrewSQLDBA (1/27/2010)
Wow, you are really going the long way around to do change the path. No need for that all code, really. Especially not the xp_cmdshell methods.A simple detach, move the file(s), and then attach. Since this is on the same box, there is no need to go thru the Alter database command.
I would use the sp_detach_db stored procedure, in case that any Full text indexes will not be dropped, and will be moved along with the database file(s). Then to attach the database in the new location
USE master;
GO
CREATE DATABASE <DatabaseName>
ON (FILENAME = '<Path to data file>_Data.mdf'),
(FILENAME = '<Path to Log File>_Log.ldf')
FOR ATTACH;
GO
Andrew SQLDBA
January 27, 2010 at 11:10 am
One small question....
What user account are you using to run SQL Service under?
If it it not a domain account, then you cannot use xp_cmdshell to perform any command line operations. I would never use the command line within SQL Server. That is a huge security risk. Why not keep things extremely simple, and either use SSIS, or the steps that I stated in the first post.
You could have had the databases moved by now. No need to keep an old copy of the database around. You are only going to attach it. The reason for using Create Database statement, is this. There is no "Attach Database" command. You use Create Database with Attach. According to the SQL Books Online.
Read the SQL BOL, it will tell you the exact steps needed.
Andrew SQLDBA
January 27, 2010 at 12:48 pm
shaunspad (1/27/2010)
Andrew thank you for looking. I was under the impression that the sp_detach_db was being deprecated and should no longer be used.
Does Books Online have a "This feature is deprecated and will be removed in a future version of SQL Server" statement? If not, it's not deprecated.
Even if you don't use detach (and there are reasons not to), doing the file move in T-SQL is just complicating everything.
Run the alter database statements then take the DBs offline. Move the files (explorer's easiest) then bring the DBs back online.
That way you know if something goes wrong with the move and can take appropriate steps
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2010 at 12:57 pm
shaunspad (1/27/2010)
Could you explain as it seems they are similar ways to accomplish the same thing.
Because they are two ways to accomplish the same thing.
Pre-SQL 2005, the only database that ALTER DATABASE could move was TempDB. There are times when you don't want to detach to move files (replication as an eg)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2010 at 1:00 pm
Gail
I did not see anything in the SQL BOL, and I have also looked in the 2008 BOL. I see nothing about this going away. But I am blind in one eye and cannot see out of the other 😀
Andrew SQLDBA
January 27, 2010 at 1:11 pm
AndrewSQLDBA (1/27/2010)
I did not see anything in the SQL BOL, and I have also looked in the 2008 BOL. I see nothing about this going away.
Exactly. There's no deprecation message, hence sp_detach_db is not deprecated.
Now sp_attach_db is a different matter, that is deprecated and the replacement is CREATE DATABASE ... FOR ATTACH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2010 at 2:32 pm
Yeah looks like I read that page wrong. Sorry for the confusion.
However I'm not detaching the DB's when I move them, I'm simply taking them offline. I'm really trying to follow Microsoft's MSDN guide.
The only thing I'm really doing different here is I'm using the instance name as part of my paths, and I'm using xp_cmdshell to automate the moving of the files.
The account I'm using to do this is the administrator account which has the right permissions to each DB.
January 27, 2010 at 5:25 pm
shaunspad (1/27/2010)
Yeah looks like I read that page wrong. Sorry for the confusion.However I'm not detaching the DB's when I move them, I'm simply taking them offline. I'm really trying to follow Microsoft's MSDN guide.
The only thing I'm really doing different here is I'm using the instance name as part of my paths, and I'm using xp_cmdshell to automate the moving of the files.
The account I'm using to do this is the administrator account which has the right permissions to each DB.
I have to agree with Gail - you don't need (and shouldn't bother) with using xp_cmdshell to move your files. You are making the whole process much harder than it needs to be - just so you can automate the moving of the files.
Besides - it looks like all you really need to do is rename the folder from Databases to Database. Instead of moving the files, I would just take the database offline, alter the file location, rename the folder, bring database online.
I wouldn't worry about having a copy, because you are going to back up your databases prior to doing this anyways and worse case would be to perform a restore from those backups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply