• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4836
  • Last Modified:

move ms/sql database files to another disk

I have an MS/SQL server (7.0) installed on one machine, says the production machine.
All databases are on its D: drive.
For test purpose, I have installed a fresh MS/SQL server on another (test) machine. But on this machine, there is no space enough on the D: drive. I need to have everything says on the F: drive.
I have no problem to dump/restore databases but I want to know if there is another way. I mean. I have stopped the production machine and copied all the database physical files d:\mssql7\data to the other machine f:\mssql7\data through a network connection.
But when I start the test machine, it does not find other database than the master. I suppose that the physical location of the other databases is somewhere indicated in the master and this says D: not F:
So how to easily change that so that it finds all these databases on F: ?
0
LeTay
Asked:
LeTay
  • 3
  • 3
  • 2
  • +3
1 Solution
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
There are several ways to do what you are asking...and several impacts

If you want to do this OFFLINE then you can use the sp_attach sp_detach instruction.
If you want to do this ONLINE then you can use the restore with norecovery and move instruction.below an example

BACKUP DATABASE Northwind
   TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
   FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
   FROM DISK = 'c:\Northwind.bak'
   WITH NORECOVERY,
      MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.mdf',
      MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'
GO

Just take in consideration that using data through a network shared drive creates additional network contraints as the data and log files are and should be constantly locked.   Make sure you don't habe any bottleneck or you risk data corruption.  Hope this helped.

 
0
 
mcmonapCommented:
Hi LeTay,

To expand a little on Racimo, you can use sp_detach_db to detach the databases from your production server, this disconnects the database leaving the files intact.  you can then copy them to the relevant locations on your new server and sp_attach_db to attach them here.  With sp_attach_db you specify the new location of the files.  If required you can then re-attach the original files to your production server leaving you with two identical copies of the database, one on your new server the other on the original production box.

eg:
sp_detach_db yourDB

sp_attach_db yourDB, f:\mssql\data\youDB_data.mdf, f:\mssql\data\youDB_log.ldf

sp_detach_db full syntax:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_da-di_83fm.asp
sp_attach_db full syntax:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp
0
 
LeTayAuthor Commented:
Dear friends,
I know these different ways but the situation is as follows (and I need to start from this situation, as the test server is now outside the network and is not planned to go back on it) :
I have in fact all the \mssql7\data files from the production on the test machine (but on F: instead of D:) and I would like to be able to start from there, not from the production anymore...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Julian HansenCommented:
I would go with mcmonap's suggestion and simply do a db attach for the files.

You might find it easier from Enterprise Manager.

Open EM
Right Click the databases node under the server node for the DB server on the Test machine
Select All Tasks
Select Attach
Browse to mdf file of database on F: drive
Select file
Done

0
 
Thandava VallepalliCommented:
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
sp_attach and sp_detach should be sufficient if you only need to move away user's database.  However, keep in mind that attach/detach feature is risky when dealing with system db's.  (In case you want to move these as well).  A backup restore is less risky even though the specific procedure for system db's may look more complex.
Hope this helped...

Racimo
0
 
LeTayAuthor Commented:
Indeed it looks not so easy.
What I really did on the test machine is :
- installation of MS/SQL 7.0 (sp 1) (define data on f: ...)
- stop SQL server (and agent)
- copy the production data files (all, including master, model...) to f:, overriding existing
- restarted the SQL services
- started EM -> the databases are not visible there
- with ISQL, I can see the databases. I detached one of them successfully, but when attaching again, it says : Server: Msg 945, Level 14, State 2, Line 1 - Database 'MyDataBase' cannot be opened because some of the files could not be activated. (I am sure the data file name is correct)

Note when right clicking the databases node on the server node in the EM, the tasks list does not include detach or attach. Maybe because it is SQL 7.0 ?
0
 
mcmonapCommented:
Hi LeTay,

Why do you need the system databases - master, model, msdb?
I would not use the enterprise manager interface for attaching and detaching databases, (IMO) I do not think it is reliable.  You should use T-SQL as detailed on the links to the MS website above, you can execute T-SQL commands in query analyser or osql.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<I detached one of them successfully, but when attaching again, it says : Server: Msg 945, Level 14, State 2, Line 1 - Database 'MyDataBase' cannot be opened because some of the files could not be activated. (I am sure the data file name is correct)>>
make sure the logical name you are using is th right one

<<I would not use the enterprise manager interface for attaching and detaching databases, (IMO) I do not think it is reliable. >>
Exactly, Query Analyzer is a much more reliable way to do than EM.
0
 
LeTayAuthor Commented:
I use the query analyzer for issuing the commands...
But the logical name when attached is not the problem :
You can do the following without any problem (I tried) :
sp_detach_db 'testdatabase'
sp_attach_db 'anothername','filelocationoftestdatabase.mdf'
It works.
And anyway, I practically used the same logical name;
The problem is somewhere else...
0
 
Scott PletcherSenior DBACommented:
When attaching, you need to specify the location of the log file or use a proc that indicates that a log file does not exist.  Otherwise SQL will try to use the prior log file, which it won't be able to find since it's looking for D: not F:.  So:

EXEC sp_attach_db 'dbName', 'F:\full\path\to\data\file\database.mdf', 'F:\full\path\to\log\file\logfile.ldf'

Or:

EXEC sp_attach_single_file_db 'dbName', 'F:\full\path\to\data\file\database.mdf'


0
 
Scott PletcherSenior DBACommented:
>>  installation of MS/SQL 7.0 (sp 1) (define data on f: ...) <<

Btw, make sure you also apply a later sp at some point.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now