Post reply

Moving System Databases

  • June 27, 2013 at 3:02 am

    #395051

    Hi all,

    Today i moved the model,temp,msdb to new location and try to start the sql server. but sql server is not starting. The mistake that i have done is when using alter statement i specified extention .mdf instaed of .ldf for log files as well. when i look into error log it is stating operating system error 2.

    so i came to conclusion that sql is not able to fetch the ldf files of respective databases so its not starting.

    How can i revert the changes or how to bring this server online again. This is not production. I am trying in test servers. So any suggestions.

  • June 27, 2013 at 3:08 am

    #1627868

    Rename the files to match the names you gave SQL. No detach or offline or anything like that is necessary since SQL's not running, just change the file names. Hope you remembered what names you specified for the system DBs.

    With master you can just change the start up parameters to match the files.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 27, 2013 at 3:10 am

    #1627871

    Hi,

    I am not sure about revert back may be there is any way to regenerate datafile using log, but for log you can detach database then rename log file and again attach database then new log is created.

    I hope this may work.

  • June 27, 2013 at 3:16 am

    #1627874

    suneet.mlvy (6/27/2013)

    but for log you can detach database then rename log file and again attach database then new log is created.

    You can't detach system databases and rebuilding the log as you suggest is very dangerous as SQL cannot always recreate the log file and when it can't you're left with a recovery_pending database that will require a restore from backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 27, 2013 at 3:34 am

    #1627882

    sql server is not starting up.... so how can i make changes.

    i have to use alter statement for ldf files as well,that will solve the issue. but how can i do that. DAC is also not working.

  • June 27, 2013 at 3:43 am

    #1627886

    I didn't say change the file names in SQL. I said change the names of the files to match what you told SQL they would be.

    Open explorer, browse to where the file are, change the names of the files in the file system to match what you told SQL that they would be.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 27, 2013 at 4:25 am

    #1627898

    Thanks Gail .It worked like a charm...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply