SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


After having moved a .mdf when SSMS would not start


After having moved a .mdf when SSMS would not start

Author
Message
dmodersk
dmodersk
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 46
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?
Andrew G
Andrew G
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2499 Visits: 2254
Is there any reason you can't just reattach the testdb from the location where you moved it?
liteswitch
liteswitch
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 598
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.
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4456 Visits: 3672
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’! **
dmodersk
dmodersk
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 46
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?
Andrew G
Andrew G
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2499 Visits: 2254
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search