CODEWITHSUNDEEP
c#sql-serversql-server-2008smo
C-Pound Guru
C-Pound Guru

Reputation: 16368

Use SMO to Change SQL Server Database Default Locations

I am using the following code to change the server database default locations (where the SQL Server keeps new database files and log files):

using Microsoft.SqlServer.Management.Smo;

Server smoServer = new Server(new ServerConnection(server, username, password));
server.DefaultFile = newPath;
server.Alter();
server.Refresh();

// Now create a database in the new location
Database smoDatabase = new Database(smoServer, database);
smoDatabase.Create();
smoServer.Refresh();

Here's my problem: I can look in SQL Server Management Studio and see the server property for Database Default Locations has been changed to the newPath. However, when I use SMO to create a new database, the new database and logfile are created in the old path.

Once I restart the SQL Server instance, the SMO code creates the database/logfile in the new path.

Any ideas why I'm getting this behavior?

Edit: One suggestion is that the change doesn't actually take place until the Sql Server Instance is restarted. However, if I open up SSMS and create a new database on the instance, it gets created in the new path without having to restart.

Upvotes: 4

Views: 4685

Answers (1)

TTRider
TTRider

Reputation: 658

You need to add information about Data and Log files explicitly:

// Now create a database in the new location
Database smoDatabase = new Database(smoServer, database);

FileGroup fg = new FileGroup(smoDatabase, "PRIMARY");
DataFile df = new DataFile(fg, "File1", @"c:\sql\file1.mdf");
LogFile lf = new LogFile(smoDatabase, "Log01", @"c:\sql\Log1.ldf");


smoDatabase.Create();
smoServer.Refresh();

Upvotes: 7

Related Questions