Moving database files to a different location


Step 1

ALTER DATABASE <databaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE <databaseName> SET OFFLINE;

Step 2

ALTER DATABASE <databaseName> MODIFY FILE
(
Name = <logicalfilename>,
Filename = ‘<fullpathofthefile including.mdf>’
);

ALTER DATABASE WSS_Content_CCS MODIFY FILE
(
Name = <logicallogfilename>,
Filename = ‘<fullpathofthefile including.ldf>’
);

Step 3

Check permissions on the files in their new location,

i.e. SQL Server should have full control.

Step 4

ALTER DATABASE <databaseName>  SET ONLINE;

ALTER DATABASE <databaseName> SET MULTI_USER;

eg.

ALTER DATABASE StateServiceDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE StateServiceDatabase SET OFFLINE;

ALTER DATABASE StateServiceDatabase MODIFY FILE
(
Name = StateServiceDatabase,
Filename = ‘N:\SPTEST_StateServiceDB\StateServiceDatabase.mdf’
);

ALTER DATABASE StateServiceDatabase MODIFY FILE
(
Name = StateServiceDatabase_log,
Filename = ‘O:\SPTEST_StateServiceDB\StateServiceDatabase_log.LDF’
);

ALTER DATABASE StateServiceDatabase SET ONLINE;

ALTER DATABASE StateServiceDatabase SET MULTI_USER;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: