Link to home
Start Free TrialLog in
Avatar of Paul MacDonald
Paul MacDonaldFlag for United States of America

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:

Msg 102, Level 15, State 1, Line 20
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;

Open in new window


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
Avatar of Peter Hutchison
Peter Hutchison
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Paul MacDonald

ASKER

Thanks Peter, that was my mistake - the result of trying different solutions.  The code currently reads...
ALTER DATABASE [@DBName] MODIFY FILE ( NAME = [@OldMDFName], FILENAME = 'F:\' + @NewMDFName );
ALTER DATABASE [@DBName] MODIFY FILE ( NAME = [@OldLDFName], FILENAME = 'F:\' + @NewLDFName );

Open in new window

...but...
ALTER DATABASE [@DBName] MODIFY FILE ( NAME = [@OldMDFName], FILENAME = 'F:\' + [@NewMDFName] );
ALTER DATABASE [@DBName] MODIFY FILE ( NAME = [@OldLDFName], FILENAME = 'F:\' + [@NewLDFName] );

Open in new window

...errors out the same way.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ended up solving this one ourselves.