SQL Server 2008 Moving Database Files To Other Location..Or..” Wh-Y-Y-Y-Y-Y-Y-Y-Y-Y-Y!!!?!?!”

After a particular non-knowledgeable expert was done,  we ended up with a database which had spread his files all over the server. Needless to say: besides having a crappy wannabe database, having the database and log files all over the server doesn’t make it any better.

So, let’s put at least some resemblance of order to this and move the database files to the correct locations where WE want them, and not so much where the installing software thinks it should be.

ALTER DATABASE crappyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE crappyDB  SET OFFLINE;

ALTER DATABASE crappyDB MODIFY FILE (
    Name = crappyDBlog,
    Filename = 'X:\Logs\crappyDB.ldf'
  );

The location above is the NEW location.

ALTER DATABASE crappyDB MODIFY FILE (
Name = crappyDBData,
Filename = 'Z:\Data\crappyDB.mdf'
);

The location above is the NEW location.

Now the database files  needs to manually move from their current location to the above location  (and if possible they  can also be renamed).

After this action,  bring the database back online:

ALTER DATABASE crappyDB SET ONLINE;
ALTER DATABASE crappyDB SET multi_user;
Advertisements

About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases, SQL Server 2008, Technical Stuff and tagged . Bookmark the permalink.

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