March 23, 2010 at 6:13 am
hi
i have 400 database in one sever. all the data and log files are stored in one drive. now i want to change the file location of all the databases.i know using detach and attach method for a particular database.instead of manually changing one by one databases, is there any other best example for changing all databases file locations automatically.
Thanks
Rock..
March 23, 2010 at 8:24 am
rockingadmin (3/23/2010)
hii have 400 database in one sever. all the data and log files are stored in one drive. now i want to change the file location of all the databases.i know using detach and attach method for a particular database.instead of manually changing one by one databases, is there any other best example for changing all databases file locations automatically.
Thanks
Rock..
Use the alter database method instead of detach/attach.
Write a dynamic sql script to move the files.
First try to do in your Dev/test server.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 23, 2010 at 8:41 am
muthukkumaran (3/23/2010)
Use the alter database method instead of detach/attach.Write a dynamic sql script to move the files.
First try to do in your Dev/test server.
This is one way of doing it, but unfortunately it involves stopping SQL Server and moving every data and log file manually. Compare that with doing a backup and restore (assuming you have sufficient disk space). You can script the backups and the restores, and then go and have a cup of tea while the script is running. If your databases have a lot of free space in them then this method has even more advantages since backups will only back up data, whereas moving a file involves moving the all free space as well.
John
March 23, 2010 at 8:55 am
you will have to weigh up which will take longer, backup\restore or the alter database and copy file method (detach\attach is the worst way to go). Note COPY the files. not move.
remember if you use backup\restore the owner of the database could change which may be problematic to you.
Both require an outage for the database in one form or another. Personally I am a big fan of alter database for large no. of databases. It is wise to have backups to fall back on when doing this type of thing thought the risks are low if you are careful.
---------------------------------------------------------------------
March 23, 2010 at 8:57 am
John Mitchell-245523 (3/23/2010)
muthukkumaran (3/23/2010)
Use the alter database method instead of detach/attach.Write a dynamic sql script to move the files.
First try to do in your Dev/test server.
This is one way of doing it, but unfortunately it involves stopping SQL Server and moving every data and log file manually. Compare that with doing a backup and restore (assuming you have sufficient disk space). You can script the backups and the restores, and then go and have a cup of tea while the script is running. If your databases have a lot of free space in them then this method has even more advantages since backups will only back up data, whereas moving a file involves moving the all free space as well.
John
John,
I agree the safest way is backup/restore but the OP have 400
database so may be alter database is better option.Also he can script xp_cmdshell to copy the files.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 23, 2010 at 9:08 am
I don't agree backup\restore is safest way, a restore that fails leaves a database unusable, plus there is the database owner issue.
what could be safer than amending sys.master_files table via alter database command and then copying the files to that location?
---------------------------------------------------------------------
March 23, 2010 at 9:13 am
Good point from George about the failed restores. The change of ownership can be scripted, so I don't see that as an issue. You make your choice based on your own situation, as George said, but I don't see how the number of databases comes into it - a script will work as well for 400 databases as it will for one.
John
March 23, 2010 at 9:16 am
george sibbald (3/23/2010)
I don't agree backup\restore is safest way, a restore that fails leaves a database unusable, plus there is the database owner issue.what could be safer than amending sys.master_files table via alter database command and then copying the files to that location?
Safest side in the sense moving 800+ physical files 🙂
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 23, 2010 at 11:10 am
I would copy them rather than move them, and delete originals once databases online again with new location
---------------------------------------------------------------------
March 24, 2010 at 12:29 am
finally, which one is the best way to work around
1. script the file name,location and change the location with alter statement.
2.script to backup and restore in new location.
3.manually moving the files
i think first statement will be better to move. please suggest me if i am wrong..
Thanks,
Rock..:-)
March 24, 2010 at 12:56 am
rockingadmin (3/24/2010)
finally, which one is the best way to work around1. script the file name,location and change the location with alter statement.
2.script to backup and restore in new location.
3.manually moving the files
i think first statement will be better to move. please suggest me if i am wrong..
Thanks,
Rock..:-)
S,rock 1 one is best for u.As i already told write a dynamic sql and alter all physical files.
Read the following link and (Join the discussion) also.
Moving Database Files Detach/Attach or ALTER DATABASE?
By Jonathan Kehayias[/url]
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 24, 2010 at 12:58 am
Before moving the files
1.take all the current files location
2.take all the DB backups.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 24, 2010 at 1:42 am
Yeah definitely i will take backups of all databases. then i will work..
Thank you very much
Regards,
Rock..
March 24, 2010 at 2:59 am
rockingadmin (3/24/2010)
Yeah definitely i will take backups of all databases. then i will work..Thank you very much
Regards,
Rock..
Ur welcome.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 24, 2010 at 10:30 pm
There is a famous script by Vince Iacoboni that does the job. It may make your job easier.
Read carefully the first few pages. You will need to manually STOP all the SQL services when running it.
http://www.sqlservercentral.com/Forums/Topic311009-257-1.aspx
Discuss Content Posted by Vince Iacoboni » Moving the SQL 2005 System Databases
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply