Detach and Attach database move filegroup

• arnold-491793

  Say Hey Kid

  Points: 709

  More actions

  March 20, 2010 at 10:38 pm

  #1137304

  Lynn Pettis (3/20/2010)

  ---

  There will be one small issue you may need to verify, any logins whose default database is MyDB (the one you are moving via detach/attach) will probably need fixing after you attech the database in its new location.

  Thanks.

  What I did is i stored all the users and current default database in security login like seen below.

  NameDefault Database

  myusersmydb-detach/attach

  Meaning this is the only one issue that i need to address using this command

  EXEC sp_defaultdb 'myuser','mydb detach/attach'

  Am i correct?

  Next time I will be aware of this. Thanks for your big help.

• Paul White

  SSC Guru

  Points: 150468

  More actions

  March 20, 2010 at 10:46 pm

  #1137307

  arnold-491793 (3/20/2010)

  ---

  Paul White NZ (3/20/2010)

  ---

  Small point: sp_attach_db is deprecated and will be removed in a future version. Use CREATE DATABASE...FOR ATTACH instead.

  There is a good SSC article on this topic by MVP Jonathan Kehayias: http://www.sqlservercentral.com/articles/Administration/65896/

  Is this applicable also in SQL Server 2000? I will do this activity in SQL server 2000. Will I face any problem later on if I use detach and attach in SQL 2K server?

  No, it does not apply to SQL Server 2000. I had assumed a later version, on the basis of this thread being in the SQL Server 2008 area of the site

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi