CODEWITHSUNDEEP
sql-server
Nelda.techspiress
Nelda.techspiress

Reputation: 643

Script to change sql server database file location

I need to change all the file locations for the databases in my instance. Basically I need to move the instance to a new location on the same server due tospace limitations of the current directory. The files will all be moved to the same new location and the names will remain the same. So it is only the path that needs to change. (This sounds so simple, it's frustrating that I can't get it to work. :-) )

I'm trying to use sp_MSForEachDB but can't seem to get the quotes correct to pass in the new file location in the FILENAME = parameter of the ALTER DATABASE command.

Here's what I have so far. 

DECLARE @command NVARCHAR(1000)--, @filename NVARCHAR(100)

SELECT @command = '
BEGIN
DECLARE @filename nvarchar(100)
USE ?
SELECT @filename = 
    "F:\DevworxExtStorage\Databases\MSSQL12.DEVSQL2014\MSSQL\DATA\" + ? + ".mdf"

ALTER DATABASE ? MODIFY FILE ( NAME = ?, FILENAME = 
"F:\DevworxExtStorage\Databases\MSSQL12.DEVSQL2014\MSSQL\DATA\") 

SELECT @filename = 
"F:\DevworxExtStorage\Databases\MSSQL12.DEVSQL2014\MSSQL\DATA\" + ? + "_log.ldf"
ALTER DATABASE ?_log MODIFY FILE ( NAME = ?, FILENAME = @filename) 


END'

exec sp_MSforeachdb @command

SELECT @command = 'ALTER DATABASE ? SET offline'
exec sp_MSforeachdb @command

I'm getting

Msg 102, Level 15, State 1, Line 10 Incorrect syntax near '@filename'.

This is a developmental database instance so taking it up and down is not an issue. I'd prefer this method over the backup/restore as it will be done in seconds rather than minutes. We will also be using this multiple times in the future so making it a script is important. I know the steps in the script work when issued manually.

I just need help in debugging the script which I think is a problem with getting the quotes correct. Thanks in advance for any help!

Upvotes: 3

Views: 2041

Answers (1)

gotqn
gotqn

Reputation: 43636

You have to change a little bit the quotes:

DECLARE @command NVARCHAR(1000)--, @filename NVARCHAR(100)

SELECT @command = '
BEGIN
DECLARE @filename nvarchar(256)
USE ?
SELECT @filename = 
    ''F:\DevworxExtStorage\Databases\MSSQL12.DEVSQL2014\MSSQL\DATA\'' + DB_NAME() + ''.md''
END 

SELECT @filename
    '
exec sp_MSforeachdb @command

Use single quotes for strings, and double single quotes to escape a single quote.

Upvotes: 1

Related Questions