Sql-server – SQL Server 2Kx: How to move a database to a different partition in the local file system

I've found a solution and I'm going to post it here in case anyone else is looking for this in the future. I can't say it's the best way to do this, but it worked for me and was relatively painless. I'll also disclose up front that I created the new schemas in the primary database when I first asked this question (a week or two ago) so I honestly don't know if this step is necessary.

I repeated this procedure for each database.

1. Created a unique schema name for the database.

2. Ran the following query for each table in the database:

   ALTER SCHEMA [new_schema_name] TRANSFER [table_name];

3. Used SSMS to generate CREATE scripts for all views, stored procedures, triggers and functions. (Fortunately I had few of these, so this went quickly.) I did have to append the schema name to all table refereces (e.g. [dbo].[table_name] changed to [new_schema_name].[table_name] but a search and replace made this fast and easy.

4. Used the SQL Server Import and Export Wizard to export tables from the database to the primary database.

5. Ran the CREATE scripts and deleted the old database.

While you could do this with a SQL Script, this is one of those cases where it's easier to use the management console.

Start the SQL Server 2008 Management Studio and connect to your server as a user with the sysadmin server role. Navigate to your database, right-click and select Tasks -> Detach..., click OK. Now you can move your database files to their new location. Finally, right-click the Databases folder and select Attach..., select the appropriate files and click OK. Your database will now be online using the files in their new location.