Moving Databases into different drive

Question

Moving Databases into different drive

Viewing 6 posts - 16 through 20 (of 20 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

If you are worried about moving all the files correctly, write the file moves as a .BAT file before-hand, then run that once you have detached. That becomes your documentation of the file move.

It's pretty easy to put together a .BAT file for that kind of thing, if you have a copy of Notepad, or any other text editor (including Query Analyzer or SSMS).

In SSMS, you can even run the .BAT commands using the SQLCMD Mode, without having to open a separate command prompt window.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

And just thought of this: add a separate BAT/CMD file for each new drive letter. It will help you keep organized, you can count the number of files to be sure you've got them all, run the different drives in parallel (save a little time) and check that all the files expected on G: are in that file.

rajankjohn SSCertifiable Points: 5061 More actions · Answer 3

I would say backup/restore if there is a huge amount of free space in the database. If the datafiles dont have much free space, go for detach/attach.You need to ensure that you take down the accurate file details before you detach 🙂 I have once detached a database from a dev server wherre there were a large number of databases and all the datafiles were having the same name. Had a hard time to find out the correct set of files!

GSquared SSC Guru Points: 260824 More actions · Answer 4

Rajan (1/16/2008)
I would say backup/restore if there is a huge amount of free space in the database. If the datafiles dont have much free space, go for detach/attach.You need to ensure that you take down the accurate file details before you detach 🙂 I have once detached a database from a dev server wherre there were a large number of databases and all the datafiles were having the same name. Had a hard time to find out the correct set of files!

If there's a huge amount of free space in the database, you're going to have an easier time moving it if you shrink it before-hand, regardless of your method of moving it.

On the second thing, document before, document during, document after!

Make darn sure you know exactly what files you are moving, and to what servers. Make sure before you start that the servers have enough disk space for the files! Make sure your method of moving them won't be so long (because of file size) that the database will be down for too long, and that the method of moving them will even work. (You can't copy a 1 terabyte database onto a DVD, for example; and you also probably don't want to copy it through a 10-base T network card at either end of the connection, or through a busy router either.)

Document all the things you check. Record them (and not in the database you are moving).

Think of more things to check, and document those too.

And then assume that you forgot something and try to figure out what it was.

On a complex database move, the documentation is worth it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Rajan (1/16/2008)
I would say backup/restore if there is a huge amount of free space in the database. If the datafiles dont have much free space, go for detach/attach.You need to ensure that you take down the accurate file details before you detach 🙂 I have once detached a database from a dev server wherre there were a large number of databases and all the datafiles were having the same name. Had a hard time to find out the correct set of files!

It is fairly easy to recover the database file name and locations from a backup file using the RESTORE FILELISTONLY command. You will, of course, have made and tested a backup file before detaching a database.

restore FilelistOnly from disk = 'D:\MyBackupDir\MyBackupFile.bak'