Create new SQL 2008 database in external drive

Asked By 0 points N/A Posted on -
qa-featured

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

 

SHARE
Best Answer by Johnny Smith
Go To Solution
Best Answer
Best Answer
Answered By 0 points N/A #91935

Create new SQL 2008 database in external drive

qa-featured

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
Answered By 5 points N/A #91936

Create new SQL 2008 database in external drive

qa-featured

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

Advantages and disadvantages of Bay pass Connection

Previous

I want to know the history of cloud computing

Next

Related Questions