Create new SQL 2008 database in external drive

Asked By kkorin 0 points N/A Posted on - 10/13/2011

Is it possible to create a new databases or move a database to an external drive? If so, how is it done, configuration wise?

 

• Status: Open
• Question Views: 1323
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MySQL Server

Best Answer by Johnny Smith

Go To Solution

Best Answer

Best Answer

Answered By Johnny Smith 0 points N/A #91935

Create new SQL 2008 database in external drive

First things first.

You need to back-up your data, just in case the process goes wrong. Then, use the sp_detach_db and sp_attach_db system procedures to detach and then attach your databases.

 

If users are trying to connect, it may help to set single user mode on the databases. After you detach the database with sp_detach_db you can move the files, then use sp_attach_db to attach them back to the SQL Server. If you want to move the system databases, it is a bit more tricky, involving setting a trace flag, moving the mdf and ldf files, stopping and starting the server, and finally changing the location of the master database. There are some gotchas possible in doing this.

About Johnny Smith

Questions
0

Answers
3

Best Answers
2

• Vote Up 0 Vote Down

• Posted on - 10/13/2011
• Question Category: MySQL Server

Answered By GordStive 5 points N/A #91936

Create new SQL 2008 database in external drive

Inside of Microsoft SQL Management Studio execute this script (replace "Name Of Database" with of the real database name):

 

  USE master

  GO

  sp_detach_db 'NameOfDatabase'

  GO

 

Copy the database at to the new location.

 

  You should note that paths are must be relative to the server SQL which is running on.

 

  USE master

  GO

  sp_attach_db 'NameOfDatabase', 'E:SQLServerDataFolderNameOfDatabase.mdf', 'E:SQLServerDataFolderNameOfDatabase_log.ldf'

  GO

 

For moving the TempDB system database to the new location site do like this,

 

USE master

  GO

  ALTER DATABASE tempdb modify file (name = tempdev, filename = 'E:SQLServerDataFoldertempdb.mdf')

  GO

  ALTER DATABASE tempdb modify file (name = templog, filename = 'E:SQLServerDataFoldertemplog.mdf')

 

Lastly restart the SQL Server for the changes to take effect. Once it's restarted, you can delete the tempdb.mdf and templog.mdf from the old location.

 

For more instructions visit the link. 

About GordStive

Questions
0

Answers
28

Best Answers
13

• Vote Up 0 Vote Down

• Posted on - 10/13/2011
• Question Category: MySQL Server