Moving database to a different location.

  1. Detach the database as follows:
                            use master
                            go
                            sp_detach_db 'mydb'
                            go
                            
    
  2. Next, copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
  3. Re-attach the database. Point to the files in the new location as follows:
                            use master
                              go
                              sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
                              go
                              
    
    Verify the change in file locations by using the sp_helpfile stored procedure:
                            use mydb
                            go
                            sp_helpfile
                            go
                         
    
    The filename column values should reflect the new locations.

Moving a TempDb database is possible with just a alter database statement ( must restart SQL services after u executed this statement):

    USE master
    GO
    ALTER DATABASE tempdb
        MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb2005.mdf')
    ALTER DATABASE tempdb
        MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb2005.ldf')
    GO

{{opmerking.Naam}}:

{{opmerking.OpmerkingText}}

            

Saving your comment....

Naam is verplicht!
Email is verplicht!
Opmerking is verplicht!