Move User databases - Best approach & SQL Bounce needed?

Question

Post reply

Move User databases - Best approach & SQL Bounce needed?

balasach82

SSCrazy Eights

Points: 9653
More actions
January 5, 2013 at 10:42 am

#393874

I use SQL 2008. One of my database has its data file in drive D and log file in drive Z. Now, I need to move Data file from D to drive M.
I thought of using backup/restore with move and attach/detach. IMHO, backup/restore with MOVE is best option. Does someone confer with me on this or is there another best approach? After move, do i need to bounce sql service?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

offline the database

use alter database...move file to change the metadata for the file location

physically COPY the file to the new location specified in the alter database command

bring the database online.

No sql restart required.

delete the old file once confirmed everything ok

---------------------------------------------------------------------

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Contrary to popular belief you do not need to stop SQL Server, there, that's that over with 😉

To move the database files perform the following;

get the logical filename and current physical path\filenames using this query select name, physical_name from sys.master_files
where database_id = DB_ID('Corrupt2K8')
for each file you wish to move construct an ALTER DATABASE...MODIFY FILE statement like soalter database yourdb modify file(name = logicalname,
filename = 'drive: ewpath\filename.ext')
issue the move statements and ensure they complete successfully
take the database offline using alter database mydb set offline with rollback immediate
copy the database disk files to the new location(s)
bring the database back online
remove the files from then old location(s)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 3

Do you guys foresee any problem with

[Taking backup of db and restoring the same db and changing the file location while restoring]

Example:

Database: Inventory

Backup Inventory

Restore Inventory (replace existing Inventory DB), change data file location

Wont this method change the meta data?

george sibbald SSC Guru Points: 104210 More actions · Answer 4

it will work and the metadata will be updated. The owner of the database will change which might not be what you want.

alter database is the accepted best method for moving a database file from sql2005 onwards

---------------------------------------------------------------------

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

balasach82 (1/5/2013)
Do you guys foresee any problem with
[Taking backup of db and restoring the same db and changing the file location while restoring]
Example:
Database: Inventory
Backup Inventory
Restore Inventory (replace existing Inventory DB), change data file location
Wont this method change the meta data?

Did you not read my post??

Yawn, I feel an article coming on 🙂

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉