Menu
I want to add a file/file group to an existing database, but I need to get the path from a variable because it will be different when this script is finalized. When I check the script in SQL Management Studio 2008 R2, it returns an error at FILENAME = @Path.
How can I use that variable?
SCRIPT WILL NOT BE RUN FROM THE COMMAND LINE!
ALTER DATABASE [MyDB]
ADD FILEGROUP [MyDB_FileStream] CONTAINS FILESTREAM
GO
DECLARE @Path VARCHAR(MAX)
SET @Path = 'C:\whatEverIWantItToBe\ThisCouldChangeWithLogic\YouGetThePoint\'
ALTER DATABASE [MyDB]
ADD FILE
(NAME = 'MyDB_FileStream'
, FILENAME = @Path
)
TO FILEGROUP [MyDB_FileStream]
750
the name of the primary filegroup cannot be changed. You cannot move a database file from one filegroup to another filegroup.
In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases, right-click the database from which to add the files, and then click Properties. In the Database Properties dialog box, select the Files page. To add a data or transaction log file, click Add.
Using SQL Server Management StudioSelect the Files page. In the Database files grid, select the files to delete, click Remove, and then click OK. Select the Filegroups page. In the Rows grid, select the filegroup to delete, click Remove, and then click OK.
Using dynamic SQL:
Declare @Path nvarchar(max)
Declare @Sql nvarchar(max)
Set @Path = 'C:\whatEverIWantItToBe\ThisCouldChangeWithLogic\YouGetThePoint\'
Set @Sql = 'Alter Database [MyDb]
Add File( Name = ''MyDb_FileStream''
, FileName = ' + QuoteName( @Path, '''' )
+ ') To FileGroup [MyDbFileStream]'
Exec( @Sql )
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
