November 5, 2016 at 2:09 am
I have 40 databases on a single sql server instance
I wanna move them to 2 new data and log directories
Using backup and restore but would like to keep the old dbs until we are confident with the change
Q1 is it OK to have 80 dbs on one instance it's not too much
Q2 once I create the copys of the dbs can I turn the dbs off line
So once I restore from backup I will rename old dbs and rename new ones to the original name, is that practice OK? I find it to be safer than detach and attach
November 7, 2016 at 2:03 am
sqlserverDBA2016 (11/5/2016)
I have 40 databases on a single sql server instanceI wanna move them to 2 new data and log directories
Using backup and restore but would like to keep the old dbs until we are confident with the change
Q1 is it OK to have 80 dbs on one instance it's not too much
Q2 once I create the copys of the dbs can I turn the dbs off line
So once I restore from backup I will rename old dbs and rename new ones to the original name, is that practice OK? I find it to be safer than detach and attach
I think you are over complicating this task, creating a script that detaches-moves files-attaches the databases is relatively simple and straight forward. Users would loose the connection for a blip whilst the DBs are being moved.
😎
Questions:
1) Are you moving the databases to a different drives?
2) How large are the databases?
3) If the databases are being moved to a different location on the same file system, then what is the purpose of the exercise?
November 7, 2016 at 7:01 am
sqlserverDBA2016 (11/5/2016)
I have 40 databases on a single sql server instanceI wanna move them to 2 new data and log directories
Using backup and restore but would like to keep the old dbs until we are confident with the change
Q1 is it OK to have 80 dbs on one instance it's not too much
Q2 once I create the copys of the dbs can I turn the dbs off line
So once I restore from backup I will rename old dbs and rename new ones to the original name, is that practice OK? I find it to be safer than detach and attach
The best and supported way is to use the ALTER DATABASE ... MODIFY FILE command, check my article at this link for more detail
http://www.sqlservercentral.com/articles/Files+and+Filegroups/96031/[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply