locked
Move SQLServer db location RRS feed

  • Question

  • I am learning VB.net 2005 and created a sample program to access a database created by the book's author.  Recently I installed SQLServerExpress2005 and reading another book.  I wanted to place that earlier .MDF in the same folder as the system databases.  I copied the database from my example folder to the \..\MSSQL\Data folder.  I then attached it through SSE2005 and it points to the orignal location.  How do I get it to point to the new location and forget the old location?  Thx Dave
    Tuesday, April 28, 2009 4:19 PM

Answers

  • hi Dave,

    >I didn't see the practice db listed in SQL Server Express.   Would it have been listed in the System Databases?

    nope, all registered user databases are listed in the "traditional" databases node of SQL Server Management Studio..

    >I did use the Attach function to bring the newly located (copy) in to the list.

    this is correct.. you have to browse the computer running SQL Server file system to select the primary data file of the database you want to attach.. obviously it have to be named (logically named) differently than the "original" database it it's still registered.. so, if your "original" database was named "myDb" pointing to d:\myFolder\myDb.Mdf and d:\myFolder\myDb.Ldf, if you copied the files to (say) C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\, you have to attach the C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\myDb.Mdf file, naming the new db "newMyDb" or the like if the old "myDb" is still registered.. or, if you detached/dropped the original "myDb" database, you can attach the new one with "myDb" name... but, at instance level, the logical database name must be unique.. the physical files do not (at least if they do not share the very same path)..

    when done attaching the database, please refresh the SSMS database node.. just in case :)
    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Tuesday, April 28, 2009 11:17 PM

All replies

  • hi Dave,

    you should not copy (move and/or backup as well) your database's phisical files while the SQL Server instance is up and running, as the database engine is in charge for handling and managing it.. cache operations could be pending, resulting in "bad and inconsistent physical files copies", that will/can "crash" when used.. consider that this edition of SQL Server usually sets a database specific property (autoclose) so that, when no active connections reference the database, the database engine provides a shutdown of it, in order to limit resources and prevent damages.. even in these cases you should not rely and should not trust physical file's copies..

    on the other side, loosely speaking, a SQL Server database consists of a set of physical files and a "registration" within the database instance.. this "registration" includes, as well as the logical name of the database, the physical files positions to the relevant database's files..
    to "move" a user database in other location, you have to unregister it, graphically via SSMS search for "detach" task or via SQL code using the system stored procedure "sp_detach db" ... when the database has been unregistered, you can move the files wherever you like [ok, this is not completely true as, in order to re-register the database, the database engine must be granted access and permissions on those files :) ]
    when ready (moving the files), you finally have to re-register the database.. this step can be graphically done (see SSMS, "attach" task) or via code.. you should prefer the new extended "CREATE DATABASE .... FOR ATTACH" syntax over the sp_attach_db system stored procedure, as that procedure has been deprecated by Microsoft..

    further info about the whole process can be found here..

    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Tuesday, April 28, 2009 5:09 PM
  • Hi Andrea
    Thnx for the information.  I didn't see the practice db listed in SQL Server Express.   Would it have been listed in the System Databases?  I did use the Attach function to bring the newly located (copy) in to the list.  That is when I noticed that it was pointing to the old location.  So I detached it and tried the Attach function again without using the lock file to see if that worked - which it didn't. SQLServerExpress was running when I did this.  It didn't seem to matter since it didn't look like the practice db was registered with SSE.  Just want to understand this process versus working with an Access file.  Appreciate your response.
    Tuesday, April 28, 2009 7:34 PM
  • hi Dave,

    >I didn't see the practice db listed in SQL Server Express.   Would it have been listed in the System Databases?

    nope, all registered user databases are listed in the "traditional" databases node of SQL Server Management Studio..

    >I did use the Attach function to bring the newly located (copy) in to the list.

    this is correct.. you have to browse the computer running SQL Server file system to select the primary data file of the database you want to attach.. obviously it have to be named (logically named) differently than the "original" database it it's still registered.. so, if your "original" database was named "myDb" pointing to d:\myFolder\myDb.Mdf and d:\myFolder\myDb.Ldf, if you copied the files to (say) C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\, you have to attach the C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\myDb.Mdf file, naming the new db "newMyDb" or the like if the old "myDb" is still registered.. or, if you detached/dropped the original "myDb" database, you can attach the new one with "myDb" name... but, at instance level, the logical database name must be unique.. the physical files do not (at least if they do not share the very same path)..

    when done attaching the database, please refresh the SSMS database node.. just in case :)
    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Tuesday, April 28, 2009 11:17 PM
  • Hi Andrea
    Having troubles with the computer running SSE.  May be the HD.
    Anyway I changed one of the records in the copy file.  Then closed out the SSE and opened Visual Studio and ran the program used to modify the orignal db file.  The change was not in the db, although the program timed-out while attempting to load the data.  Second run attempt was successful.  So perhaps I have to do that a little differently.  Wanted to organize all the SQL Server dbs in 1 logical folder structure; not scattered throughout the computer.  Thank you for taking the time to answer my questiuons.
    Dave
    Thursday, April 30, 2009 1:42 PM