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

After having moved a .mdf when SSMS would not start Expand / Collapse
Author
Message
Posted Thursday, June 27, 2013 5:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:24 AM
Points: 4, Visits: 18
We had run out of space on a drive that was storing several .mdf files and I could not start SSMS so I stopped all MSSQL services and moved a test db's .mdf file. Things are moving again but after having read this article about the proper way to move them [url=http://www.sqlservercentral.com/articles/Administration/65896/][/url] I am wondering how I can get the test db back working again.

I was thinking using the article's method to move another file and then manually move the test db back to its original location. Does that sound like a good plan?
Post #1468398
Posted Thursday, June 27, 2013 9:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:54 PM
Points: 1,193, Visits: 1,666
Is there any reason you can't just reattach the testdb from the location where you moved it?
Post #1468413
Posted Friday, June 28, 2013 5:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:35 AM
Points: 69, Visits: 565
I'd recommend moving the .mdf file back, taking the database offline, move the files, run an alter database statement to move the pointers to the new file location then bring the database online again.
Post #1468518
Posted Friday, June 28, 2013 6:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 2,262, Visits: 2,725
The database will not come ONLINE because there is a mismatch between the location of the mdf-file stored in the system database and the physical location of the file on disk. Alter the location in the system database with the statement below (change to your needs) and bring the database online.
ALTER DATABASE [{dbname}] MODIFY FILE (NAME = {logical_name}, FILENAME = '{full_path_and_filename}')
ALTER DATABASE [{dbname}] SET ONLINE



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1468523
Posted Wednesday, July 10, 2013 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:24 AM
Points: 4, Visits: 18
I had a chance to try to let the master files know that the database is moved, but things are complicated by the fact that the drives where the files are located are clustered.

I had tried moving the .mdf file to a cluster disk that should be associated with another instance of sql server on the same box and tried moving it to the local disk but both gave me the same message:

Cannot use file 'C:\datatel\data\ActiveCampusTest.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

I was thinking I could add the cluster disk as a dependency of this server instance, but I'm not sure if that's possible since the cluster disk is already part of another instance. The other option I was thinking was to move the entire database to another instance. Any ideas where I should go next?

Post #1472218
Posted Wednesday, July 10, 2013 6:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:54 PM
Points: 1,193, Visits: 1,666
You can't add a dependency unless the disk is part of the same resource group, which it's not if it's for another instance.

You need to provision more space or add a new disk to that resource group. If you move to another instance, you'll need to reconfigure the app, alias, clients, transfer jobs, logins etc.

Post #1472403
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse