[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

moving database

I want to move the database from one drive E to another drive F. Do I simply change the location from database property->file-> data.. E:\ changed to F:\...??

Will it cause any trouble?
0
wasabi3689
Asked:
wasabi3689
  • 10
  • 7
  • 2
  • +2
9 Solutions
 
Jesus RodriguezIT ManagerCommented:
First Detach the Database, Move the Database and the re-attach the Database
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no. you have 2 options:
* backup + restore WITH MOVE : during the restore you can specify new file locations.
* detach database, move the files on disk, attach database from new locations.

in both scenarios, you need to put in place some downtime.
0
 
Vijaya Reddy Pinnapa ReddyCommented:
You have two options

 (1) You can backup and restore your database; by using the WITH MOVE options you can relocate files.

 (2) You can detach your database, move the database files to the desired location, re-attach the database.
 
When you re-attach, you should pay attention to the database owner assignment. It may accidentially assign a different owner -be sure to use the correct owner.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
wasabi3689Author Commented:
When I use deattach or attach, the database will be downtime ( offline) or must be single user mode?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
single user mode is ok
0
 
wasabi3689Author Commented:
you mean no offline is necessary, correct? can you provide the script for moving? I can play in my machine first.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
moving the files on the disk is using either GUI + mouse on Windows File Explorer or command line to MOVE or COPY command ...
note sure what exactly you need as help there
0
 
wasabi3689Author Commented:
Here is the steps I plan to go for moving database. Please let me know if I miss something
I want to move the data file to K but not move the original log file in F


1. Stop the database service
2. copy the database file which is employee.mdf
E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
to
K: MSSQLData\

3. Keep the log file employee.idf location UNMOVE, still in F
F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data

4. Start the SQL service again

5. Set the employee database single user mode

ALTER DATABASE employee SET SINGLE_USER WITH ROLLBACK IMMEDIATE

6. de-attached the employee database.

7. Attach the employee database from K: MSSQLData\employee.mdf

This is done??

Do I miss something?

Question:

The employee.idf file will be automatically picked up even I don't move to K:\MSSQLData?????
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you don't need to stop the sql server service.

detach the database
copy move the files
attach the database

during the attach, you can specify exactly all the file locations.
sql server will ony pick up the log file if it is actually in the same folder as the data file
0
 
wasabi3689Author Commented:
Originally,  the data file and log file for employee database are in different drive, I just want to move the data file to K but keep the log in F, is it doable in this detach and attach?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, that is possible
0
 
wasabi3689Author Commented:
Setting single user mode is necessary step too if not stopping the service?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, that is eventually needed, at least to be sure.
0
 
wasabi3689Author Commented:
Finally question,

I am going to move not only employee database, but also all the system databases (master, model,...) from E to K, use detach ->move-> attach process.

I wonder if
moving the system database to K drive will cause database crashed? will this work. because I no longer need E drive and E drive will be removed completely, it's VM drive.

Thanks
0
 
Anthony PerkinsCommented:
Just a thought, but when moving files on the same server it is a lot simpler to take the database offline, change the path, move (or copy and later delete) the file(s) and finally set the database online.  This is by far the least disruptive way of doing this, there is no need to detach the database.  The problem with detaching the database is that you will lose settings such as TRUSTWORTHY, DB_CHAINING, etc.

Here is a good MSDN article on the subject:
Moving User Databases
http://msdn.microsoft.com/en-us/library/ms345483(v=sql.105).aspx
0
 
Anthony PerkinsCommented:
I am going to move not only employee database, but also all the system databases (master, model,...) from E to K, use detach ->move-> attach process.
System databases are more complicated, but can be moved as follows:
Moving System Databases
http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx
0
 
wasabi3689Author Commented:
Hi,

I try to copy database, all mdf file and log file to the new location? I have stop the SQL service. But it still give me the error

"This action cannot be completed beccause the file is open in SQL server"

Why?
0
 
wasabi3689Author Commented:
never mind, I fix this issue. It's copying.
0
 
wasabi3689Author Commented:
Hi,

I do a test before I run in production

I have this problem

 (2) You can detach your database, move the database files to the desired location, re-attach the database.

See my attach image

Any idea for this?
Capture-attach-db.JPG
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not sure how that can come, unless indeed the file is not a primary database file, or eventually if the versions of the 2 sql servers are not compatible
0
 
wasabi3689Author Commented:
Hi, I run into worse case.

I may need to re-install MS SQL. I want to know if I can still attach the database files I move to the new location after I re-install MS SQL 2008?? Backup is not option. I did not make a backup But I sucessfully move all user database into K drive. I want to re-attach the database there after I re-install MSSQL 2008?
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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