January 11, 2008 at 11:59 am
hi i am trying to move my datbases to different drive, some are on different server too. i know how to do it throught Mngmt Studio, since it is wuite often process i want to have this in script. can any one help me out here.
so here is what i am trying to do. some of the databases needs to be moved into different folder or drive of same server and some needs to be moved into different drive of different server.
ususally i do is copy all the data and log files of databased i am trying to move to different drive and backup and restore datbase using move option.
January 11, 2008 at 1:16 pm
The easiest way is to detach/reattach the database. You can do this via SSMS and then have SSMS generate the script. You will have to generate 2 scripts one for detach and the other for attach.
January 11, 2008 at 1:18 pm
FYI detach/attach is usually much faster than backup/restore.
January 12, 2008 at 9:53 pm
My vote is detach, attach.
January 14, 2008 at 1:17 pm
In SQL 2005 the best way is to ,take the database offline, then move the respective data/log files to desired location, then update the new location of the database files through alter database statement and then bring back online the database.
January 14, 2008 at 1:41 pm
How is that easier than detach/attach? I'd argue it's easier to make mistakes with the alter database command.
January 14, 2008 at 8:45 pm
In SQL 2005 the best way is to ,take the database offline, then move the respective data/log files to desired location, then update the new location of the database files through alter database statement and then bring back online the database.
I agree with Steve on this one. To take the database offline, copy the files, and supply new file locations is a lot more steps and has a higher margin of error. Attach/detach was created for situations such as this one.
January 15, 2008 at 2:53 am
detach db ---> copy db files to new location ---> attach.
one question: what do u do when the db files are large?
"Keep Trying"
January 15, 2008 at 7:20 am
If the database has more than 16 files then sp_attach_db will not work,
this will work with attach option by using create database command.
If database is having more than 16 files then we have to make use of create database gui tool or command and then mapping all the files of the database that we detached.
Draw back here is, once we detach, there will be absolutely no information about the database.All the database file information is lost from sysaltfiles. So when attaching a database if for somebody forgets the location, then its really a mess, as the database will not be brought back online.
Only option of getting information of files for the database is through backup.
If we do by taking database offline, then copying the file to the desired location, then alter database to the new location where the file is copied and then bringing database back online.
This way even if we forget to notedown any thing, but we have all the information about the database.
So for that reason I strongly prefer this method, also going forward in future versions attach and detach db will be deprecated.
January 15, 2008 at 7:29 am
Backup and restore may not be the fastest option (although if you have very large database files with very little data in then there won't be much in it) but it's the only one that doesn't involve manually moving any files. And for databases that can't be detached (for example those published for replication) I'd say it's certainly the easiest way.
John
January 15, 2008 at 7:35 am
If the files are large, you still have to copy them. There's no magic SQL Server way to get them to the new drive.
January 15, 2008 at 7:39 am
Steve
I back up my database to disk, then I restore to a different location using WITH MOVE. That would work, wouldn't it, and with no manual copying of files?
John
January 15, 2008 at 7:39 am
Moving of database files will work for database that are marked for replication will work with thsi method, that is take the database offline, move the database file, point the database file to the new location and then bring back databse online.
January 15, 2008 at 7:41 am
OK, I'll take your word for that, since I never tried it in SQL Server 2005, and the option isn't available in SQL Server 2000. You still have to move the files though, which is fiddly if there's a lot of them and they're all going to different places.
John
January 15, 2008 at 8:27 am
John,
It can be a pain, but I'd script it out so you can see it in front of you, maybe ask someone else to verify that you have things moving as planned. A few simple .CMD files will make a huge difference to easing the pain.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply