Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Default Database Drive Almost Full Expand / Collapse
Author
Message
Posted Thursday, August 5, 2010 4:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 5, 2010 5:11 PM
Points: 2, Visits: 2
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?
Post #964753
Posted Thursday, August 5, 2010 4:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:35 PM
Points: 975, Visits: 3,350
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
Post #964758
Posted Thursday, August 5, 2010 5:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
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).







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #964761
Posted Thursday, August 5, 2010 5:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 5, 2010 5:11 PM
Points: 2, Visits: 2
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.
Post #964766
Posted Thursday, August 5, 2010 9:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #964823
Posted Thursday, August 5, 2010 10:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 3, 2011 3:00 AM
Points: 164, Visits: 656
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
Post #964840
Posted Thursday, August 5, 2010 11:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #964841
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse