Paul MacDonald
asked on
SQL script help: "Moving a database"
I'm trying to develop a script to change the drive letter where my SQL server looks for its databases. I've cobbled this together, but I can't figure out my way around this error:
The script is supposed to unmount the database, update SQL Server on its "new" location, then mount the database again. Any help would be appreciated.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '+'.
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '+'.
DECLARE @DBName varchar(50) = '';
DECLARE @NewMDFName varchar(50) = '';
DECLARE @NewLDFName varchar(50) = '';
DECLARE @OldMDFName varchar(50) = '';
DECLARE @OldLDFName varchar(50) = '';
DECLARE @LogicalName varchar(50) = '';
SET @DBName = 'TestLun1a';
SET @NewMDFName = @DBName + '.mdf';
SET @NewLDFName = @DBName + '.ldf';
USE Master;
SET @OldMDFName = (SELECT name FROM sys.master_files WHERE database_id = DB_ID(@DBName) AND type_desc = 'ROWS');
SET @OldLDFName = (SELECT name FROM sys.master_files WHERE database_id = DB_ID(@DBName) AND type_desc = 'LOG');
ALTER DATABASE [@DBName] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [@DBName] MODIFY FILE ( NAME = [@OldMDFName], FILENAME = 'F:\' + @NewMDFName] );
ALTER DATABASE [@DBName] MODIFY FILE ( NAME = [@OldLDFName], FILENAME = 'F:\' + @NewLDFName] );
ALTER DATABASE [@DBName] SET ONLINE;
The script is supposed to unmount the database, update SQL Server on its "new" location, then mount the database again. Any help would be appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ended up solving this one ourselves.
ASKER
Open in new window
...but...Open in new window
...errors out the same way.