August 5, 2010 at 4:52 pm
I have a client running a security badge app that originally installed SQL2005 on a 12GB C: Drive with Windows Server 2003. There is a 487GB D: partition. I am not a SQL Admin, but from what I read I thought it would be pretty easy, but I may be missing a simple step.
I created a D:\MSSQL\DATA folder and assigned the same User Permissions as the default C: data location. Using the Studio Management Console I changed the Default Data Location to the new location on D:. I then stopped the security application.
I thought at this point I would just have to Detach the databases, copy them to the new location and Attach them, but I can't Detach the main security program database.
Do I need to run any utilities to check DB integrity before I attempt to detach the DB's?
August 5, 2010 at 4:58 pm
That is because there is still a user connected to that database.
Change the default database that *You* are logging into SSMS with, and then log back into SQL. Verify that no users are connected by using sp_who2 command.
Once no users, or processes are connected to that database. You should be able to detach it. If that fails. Create a Full backup. Drop the database, re-create the same database in the other location, and then restore from the backup that you just took.
You do not have to have the same directory structure. You can store the databases in any directory that you like. I actually store mine in a directory of the same name, under a parent of "SQL Server 2008 Databases". I also store the log file(s) on a separate spindle. And then store the backups on a totally separate box.
Andrew SQLDBA
August 5, 2010 at 5:04 pm
I think Andrew is correct. If you have that database selected in EM, or a query window open, there's a shared lock on the database.
Select master, the right click your database and select detach (or run a script from master).
August 5, 2010 at 5:11 pm
In theory what I'm attempting should work aside from my non-familiarity handling the db's right?
Do I have to detach and move all the databases one-by-one including the SQL2005 default db's. I'm not clear whether the Database and Transaction locations are for user-created db's or all.
August 5, 2010 at 9:10 pm
You may move the user databases using Detach and Attach method. Detach the database, move the files to new the drive using explorer and attach database. You can do it through GUI or you may use the script as in this article.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 5, 2010 at 10:59 pm
Hi,
We cannot move a resource database straightway right. We can change the path of this database by using property --> advanced option. then only we can able to move this database..
Is it right?
Regards
Balaji.G
August 5, 2010 at 11:03 pm
Resource database cannot be moved. You cannot change the location of that database using SSMS since it is not listed there. Please refer this article on how to move System Databases.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply