Post reply

Attach Database files on remote Computer to SQL2K Engine

  • January 11, 2006 at 11:03 pm

    #92554

    G'day all,

    I've Googled around but not found a solution, so I'll shortcut straight to the experts here.

    The situation is that we have an SBS2K3 server Premium with SQL2000 (must be installed on the SBS2K3 server.  We also have a rather large SQL database that we wish to store on a separate NAS server running Storage Server 2003.

    Is it possible to Attach or Restore a database so the database files reside on a different server from the one actually running the SQL 2000 Engine.

    (P.S. This is not my Idea, I'm just the por sod who needs to get it working or prove it impossible.)

    Thanks in advance,

  • January 12, 2006 at 1:43 am

    #614258

    Charles,

    if you have a share on your NAS server which you map on your SQL server to a drive letter it is possible to attach the datafiles.

    Even though I strongly recommend not doing this. First of all it's definitely not supported by Microsoft and second you risk data inconsistency through network errors.

    Markus

     

    [font="Verdana"]Markus Bohse[/font]

  • January 12, 2006 at 5:53 pm

    #614517

    MarkusB,

    I'm well aware of the stupidity in what they want me to do, and despite my screams for sanity I've been over ruled by powers that be. So here I sit.

    I've already attempted to simply map one of the NAS shares to a drive letter, but I found that this failed completely.

    Enterprise Manager won't list mapped drives for browsing after a database to either restore or attach and manually entering the paths didn't work either.  I also tried writing the restore query but it gave the same result.

    As you stated, Microsoft don't support it, and I suspect they have emphasized this by restricting databases to local drives.

    Anyone know of a way around this?

    Regards,

  • January 13, 2006 at 12:54 am

    #614538

    Charles,

    I'm not sure why it's not working for you, but I once succesfully placed database files on a mapped drive. Unfortunately it's quite some time ago so I don't remember the exact details. Did you try attching the database from QA ? 

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • January 13, 2006 at 8:07 am

    #614651

    You need to set the trace flag 1807 to configure SQL server to with network-based database files.  You can find a list of some of the documented and undocumented trace flags right here on SQLServerCentral.

     

    Lynn

  • January 13, 2006 at 11:58 am

    #614719

    Before you do this, scare the decison makers. Type up a formal letter stating you are willing to do this and will accomplish it to the best of your ability. However, since the database could become corrupt if there is the network 'drops' while transactions are occuring, they have to be willing to accept the possible loss of all the data.

    -SQLBill

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

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