locked
Moving Sql Server 2005 DataBases from C: to D: RRS feed

  • Question

  •  

    How do you move databases from C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

    to d:\MSSQL.1\MSSQL?

     

    I want to copy them, then detach/retach at the new location

     

    Thanks

    Friday, December 7, 2007 1:46 PM

Answers

All replies

  • You need to Detach the database and copy the files to new location and then reattach it....pls refer this links, http://support.microsoft.com/kb/224071 . You can make use of the  procedures sp_detach_db and sp_attach_db  to perform the  operation or  you can make  use of GUI refer, http://msdn2.microsoft.com/en-us/library/ms191491.aspx.For  moving system databases refer, http://sql-articles.com/articles/msysdb.htm

    Pls note that before detaching the dbs make a note of all the default databases for all your logins as once you detach the db and reattach it the default databases might be missing or misplaced !

    - Deepak
    Friday, December 7, 2007 5:32 PM
  • thanks.  i found this link that details the steps we need to follow.
    http://weblogs.asp.net/dmckinstry/archive/2006/12/04/moving-tfs-databases.aspx

    i'll have our DBAs look into the the default databases for logins that you've mentioned.   
    Monday, December 10, 2007 4:19 PM
  • ok, i can get to the Reports, however, when I click on them I get this:

    • An error has occurred during report processing. (rsProcessingAborted)
      • Cannot create a connection to data source 'TfsOlapReportDS'. (rsErrorOpeningConnection)
        • For more information about this error navigate to the report server on the local server machine, or enable remote errors

    any ideas?
    Monday, December 10, 2007 6:27 PM
  • nevermind....rebooted the database server and everythings up and running
    Monday, December 10, 2007 7:26 PM
  • Hi,

    Follow below steps:

    http://support.microsoft.com/kb/224071

    Moving the tempdb database

    You can move tempdb files by using the ALTER DATABASE statement.
    1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:
      use tempdb
      go
      sp_helpfile
      go
      The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
    2. Use the ALTER DATABASE statement, specifying the logical file name as follows:
      use master
      go
      Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
      go
      Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
      go
      You should receive the following messages that confirm the change:
      Message 1
      File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.
      Message 2
      File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.
    3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
    4. Stop and then restart SQL Server.

    http://sql-bi-dev.blogspot.com.au/2010/06/moving-database-files-from-one-drive-to.html

    http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/



    Thanks Shiven:) If Answer is Helpful, Please Vote

    Monday, August 13, 2012 4:54 AM