Using SMO to Change SQL Server Database Database Locations

Question

I use the following code to change the default locations of the server database (where SQL Server stores the 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 is my problem: I can look in SQL Server Management Studio and see that the server property for the databases has been changed to newPath by newPath . However, when I use SMO to create a new database, the new database and the log file are created along the old path.

As soon as I restart the instance of SQL Server, the SMO code creates the database / log file in a new path.

Any ideas why I get this behavior?

Edit: One suggestion is that this change does not actually occur until the Sql server instance is restarted. However, if I open SSMS and create a new database in the instance, it will be created in a new path without restarting.

Answer 1

You must explicitly provide information about data and log files:

 // 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();