ÇÔ¸Õ¡ÒÃÂéÒ°ҹ¢éÍÁÙÅ SQL Server ä»ÂѧµÓá˹è§ãËÁèâ´Â¡ÒÃãªé¿Ñ§¡ìªÑ¹ á¡ÍÍ¡ áÅпѧ¡ìªÑ¹ Ṻ ã¹ SQL Server

• ÊÓËÃѺÃØ蹢ͧºÔµ SQL Server 2000 32 ä»·Õè MSDN àÇçºä«µìµèÍ仹Õé:
  .aspx http://msdn.microsoft.com/en-us/library/aa176560 (SQL.80)
  (http://msdn.microsoft.com/en-us/library/aa176560(SQL.80).aspx)
• ÊÓËÃѺÃØ蹢ͧºÔµ SQL Server 2000 64 ä»·Õè MSDN àÇçºä«µìµèÍ仹Õé:
  .aspx http://msdn.microsoft.com/en-us/library/aa274567 (SQL.80)
  (http://msdn.microsoft.com/en-us/library/aa274567(SQL.80).aspx)
• ÊÓËÃѺÃØ蹢ͧ SQL Server 2005 ä»·Õè MSDN àÇçºä«µìµèÍ仹Õé:
  .aspx http://msdn.microsoft.com/en-us/library/ms143547 (SQL.90)
  (http://msdn.microsoft.com/en-us/library/ms143547(SQL.90).aspx)

¢éÍ¡Ó˹´àº×éͧµé¹

• ·Ó¡ÒÃÊÓÃͧ¢éÍÁÙŢͧ°Ò¹¢éÍÁÙÅ·Ñé§ËÁ´ »Ñ¨¨ØºÑ¹à»ç¹â´Â੾ÒÐÇҧἹËÅÑ¡°Ò¹¢éÍÁÙÅ ¨Ò¡µÓá˹觻Ѩ¨ØºÑ¹
• ¤Ø³µéͧÁÕÊÔ·¸Ôì¢Í§¼Ùé´ÙáÅÃкº (sa) Ãкº
• ¤Ø³µéͧ·ÃÒºª×èÍáÅеÓá˹觷ÕèµÑ駻Ѩ¨ØºÑ¹¢Í§á¿éÁ¢éÍÁÙÅ·Ñé§ËÁ´ áÅÐá¿éÁÊÓËÃѺ°Ò¹¢éÍÁÙÅ¡ÒÃà¢éÒÊÙèÃкº
  
  ËÁÒÂà˵Ø:¤Ø³ÊÒÁÒö¡Ó˹´ª×èÍáÅеÓá˹觷ÕèµÑ駻Ѩ¨ØºÑ¹¢Í§á¿éÁ·Ñé§ËÁ´·Õèãªéã¹°Ò¹¢éÍÁÙÅ â´Âãªé¡ÒÃsp_helpfile¡Ãкǹ§Ò¹·Õèà¡çºäÇé:

  use <database_name>
  go
  sp_helpfile
  go

• ¤Ø³¤ÇÃÊÒÁÒöà¢éÒ¶Ö§°Ò¹¢éÍÁÙÅ·Õè¤Ø³¡ÓÅѧÂéÒÂẺàÍ¡ÊÔ·¸Ôì੾Òкؤ¤Å ¶éҤس»ÃÐʺ»Ñ­ËÒã¹ÃÐËÇèÒ§¡Ãкǹ¡Òà áÅÐ ¶éҤسäÁèÊÒÁÒöà¢éÒ¶Ö§°Ò¹¢éÍÁÙÅ·Õè¤Ø³ÂéÒ ËÃ×Í ¶éҤسäÁèÊÒÁÒöàÃÔèÁ SQL Server µÃǨÊͺ¡ÒÃÅçÍ¡¢éͼԴ¾ÅÒ´ SQL Server áÅÐ SQL Server ˹ѧÊ×ÍÍ͹äŹìÊÓËÃѺ¢éÍÁÙÅà¾ÔèÁàµÔÁà¡ÕèÂǡѺ¢éͼԴ¾ÅÒ´·Õè¤Ø³»ÃÐʺ

¡ÒÃÂéÒ°ҹ¢éÍÁÙżÙéãªé

1. á¡ÍÍ¡°Ò¹¢éÍÁÙŵèÍ仹Õé:

   use master
      go
      sp_detach_db 'mydb'
      go

2. ¢Ñ鹵͹¶Ñ´ä» ÊÓà¹Òá¿éÁ¢éÍÁÙÅáÅÐä¿ÅìºÑ¹·Ö¡¨Ò¡µÓá˹觷ÕèµÑé§ (D:\Mssql7\Data) »Ñ¨¨ØºÑ¹ä»µÓá˹觷ÕèµÑé§ (E:\Sqldata) ãËÁè
3. re-attach °Ò¹¢éÍÁÙÅ ªÕéä»·Õèá¿éÁã¹µÓá˹觷ÕèµÑé§ãËÁèà»ç¹´Ñ§¹Õé:

   use master
     go
     sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
     go

   ¡ÒõÃǨÊͺ¡ÒÃà»ÅÕè¹á»Å§ã¹µÓá˹觷ÕèµÑ駢ͧá¿éÁ â´Âãªé¡ÒÃsp_helpfile¡Ãкǹ§Ò¹·Õèà¡çºäÇé:

   use mydb
      go
      sp_helpfile
      go

   ¡Ãкǹ¡ÒÃFileName :¤èҢͧ¤ÍÅÑÁ¹ì¨ÐáÊ´§ãËéàË繶֧µÓá˹觷ÕèµÑé§ãËÁè

¡ÒÃÂéÒ°ҹ¢éÍÁÙŵÑÇÍÂèÒ§

¡ÒÃÂéÒ°ҹ¢éÍÁÙÅẺ¨ÓÅͧ

sql Server 7.0

1. µÃǨÊͺãËéá¹èã¨ÇèÒ ºÃÔÉÑ·µÑÇá·¹¡ÒÃà«ÔÃì¿àÇÍÃì SQL äÁè·Ó§Ò¹ã¹¢³Ð¹ÕéÍÂÙè
2. Follow the same procedure for moving user databases.

SQL Server 2005 and SQL Server 2000

1. In SQL Server Enterprise Manager, right-click the server name, and then click¤Ø³ÊÁºÑµÔ.
2. 㹡Ò÷ÑèÇä»á·çº ¤ÅÔ¡Startup Parameters.
3. Add the following new parameter:
   -c -m -T3608

1. Stop and then restart SQL Server.
2. Detach theÃٻẺ¨ÓÅͧdatabase by using the following commands:

   use master
      go
      sp_detach_db 'model'
      go

3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder to the E:\Sqldata folder.
4. Reattach theÃٻẺ¨ÓÅͧdatabase by using the following commands:

   use master
      go
      sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
      go

5. àÍÒÍÍ¡-c -m -T3608from the startup parameters in SQL Server Enterprise Manager or in SQL Server Configuration Manager.
6. Stop and then restart SQL Server. You can verify the change in file locations by using thesp_helpfile¡Ãкǹ§Ò¹·Õèà¡çºäÇé µÑÇÍÂèÒ§àªè¹ ãªé¤ÓÊÑ觵èÍ仹Õé:

   use model
      go
      sp_helpfile
      go

Moving the MSDB database

sql Server 7.0

1. Make sure that the SQL Server Agent is not currently running.
2. Follow the same procedure for moving user databases.

SQL Server 2005 and SQL Server 2000

1. ËÂØ´ áÅéÇ àÃÔèÁ SQL Server
2. µÃǨÊͺãËéá¹èã¨ÇèÒ ºÃÔ¡ÒâͧºÃÔÉÑ·µÑÇá·¹¢Í§à«ÔÃì¿àÇÍÃì SQL äÁè·Ó§Ò¹ã¹¢³Ð¹ÕéÍÂÙè
3. á¡Í͡Ẻmsdb°Ò¹¢éÍÁÙŵèÍ仹Õé:

   use master
   go
   sp_detach_db 'msdb'
   go

4. ÂéÒÂá¿éÁ Msdbdata.mdf áÅÐ Msdblog.ldf ¨Ò¡µÓá˹觻Ѩ¨ØºÑ¹ (D:\Mssql8\Data) ä»ÂѧµÓá˹觷ÕèµÑé§ãËÁè (E:\Mssql8\Data)
5. àÍÒÍÍ¡-c -m - T3608¨Ò¡àÃÔèÁµé¹ãªé§Ò¹¾ÒÃÒÁÔàµÍÃì¡Åèͧ㹵ÑǨѴ¡ÒÃͧ¤ì¡Ã
6. ËÂØ´áÅéÇ àÃÔèÁ SQL Server
   
   ËÁÒÂà˵Ø:¶éҤس¾ÂÒÂÒÁ reattachmsdb°Ò¹¢éÍÁÙÅ â´Â¡ÒÃàÃÔèÁ¡Ò÷ӧҹÃèÇÁ¡Ñº SQL Server-cµÑÇàÅ×Í¡ ¡ÒÃ-mµÑÇàÅ×Í¡ áÅÐʶҹТͧ¡ÒÃÊ׺¤é¹¡ÅѺ 3608 ¤Ø³ÍÒ¨ä´éÃѺ¢éͤÇÒÁáÊ´§¢éͤÇÒÁáÊ´§¢éͼԴ¾ÅÒ´µèÍ仹Õé:
   à«ÔÃì¿àÇÍÃì: ¢èÒÇÊÒÃà¡ÕèÂǡѺ 615 ÃдѺ 21 ʶҹР1 ºÃ÷Ѵ 1
   äÁ辺°Ò¹¢éÍÁÙŵÒÃÒ§ 3 ËÁÒÂàÅ¢ ª×èÍ 'ÃٻẺ'
7. reattach msdb °Ò¹¢éÍÁÙŵèÍ仹Õé:

   use master
   go 
   sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf' 
   go

USE MSDB 
Go 
EXEC sp_changedbowner 'sa' 
Go

¡ÒÃÂéÒ°ҹ¢éÍÁÙÅËÅÑ¡

sql Server 7.0 áÅÐ SQL Server 2000

1. à»ÅÕè¹àÊé¹·Ò§ÊÓËÃѺá¿éÁ¢éÍÁÙÅËÅÑ¡áÅÐá¿éÁÅçÍ¡·ÕèËÅÑ¡ã¹µÑǨѴ¡ÒÃͧ¤ì¡Ã¢Í§ SQL Server
   
   ËÁÒÂà˵Ø:¹Í¡¨Ò¡¹Õé¤Ø³ÍÒ¨à»ÅÕè¹µÓá˹觷ÕèµÑ駢ͧá¿éÁºÑ¹·Ö¡¢éͼԴ¾ÅÒ´µèÍ仹Õé
2. ¤ÅÔ¡¢ÇÒ·Õè SQL Server ã¹µÑǨѴ¡ÒÃͧ¤ì¡Ã áÅШҡ¹Ñé¹ ¤ÅÔ¡¤Ø³ÊÁºÑµÔ.
3. ¤ÅÔ¡¾ÒÃÒÁÔàµÍÃì¡ÒÃàÃÔèÁµé¹àÁ×è͵éͧ¡ÒôÙÃÒ¡ÒõèÍ仹Õé:

   -dD:\MSSQL7\data\master.mdf
      -eD:\MSSQL7\log\ErrorLog
      -lD:\MSSQL7\data\mastlog.ldf

   -ÇàÊé¹·Ò§¤Ãº¶éǹ¢Í§á¿éÁ¢éÍÁÙŢͧ°Ò¹¢éÍÁÙÅËÅÑ¡ä´é
   
   ÍÕ-àÊé¹·Ò§¤Ãº¶éǹ¢Í§á¿éÁºÑ¹·Ö¡¢éͼԴ¾ÅÒ´ä´é
   
   -làÊé¹·Ò§¤Ãº¶éǹ¢Í§á¿éÁºÑ¹·Ö¡¢Í§°Ò¹¢éÍÁÙÅËÅÑ¡ä´é
4. à»ÅÕè¹á»Å§¤èÒàËÅèÒ¹Õéà»ç¹´Ñ§¹Õé:
   1. àÍÒÃÒ¡ÒÃá¿éÁ Master.mdf áÅÐ Mastlog.ldf »Ñ¨¨ØºÑ¹
   2. à¾ÔèÁÃÒ¡ÒÃãËÁè·ÕèÃкصÓá˹觷ÕèµÑé§ãËÁè:

      -dE:\SQLDATA\master.mdf
            -lE:\SQLDATA\mastlog.ldf

5. ËÂØ´¡ÒÃ SQL Server
6. ¡ÒäѴÅÍ¡á¿éÁ Master.mdf áÅÐ Mastlog.ldf ä»ÂѧµÓá˹è§ãËÁè (E:\Sqldata)
7. àÃÔèÁµé¹à«ÔÃì¿àÇÍÃì SQL

sql Server 2005

¡ÒÃÂéÒ°ҹ¢éÍÁÙÅ tempdb

1. ¡Ó˹´ª×èÍä¿ÅìẺÅͨԤÑÅÊÓËÃѺ¡ÒÃtempdb°Ò¹¢éÍÁÙÅ â´Âãªésp_helpfileµèÍ仹Õé:

   use tempdb
   go
   sp_helpfile
   go

   ª×èÍ·Ò§µÃáÐÊÓËÃѺáµèÅÐá¿éÁ¨ÐÍÂÙèã¹¹Ñ鹪×èÍ:¤ÍÅÑÁ¹ì µÑÇÍÂèÒ§¹Õéãªéª×èÍá¿éÁàÃÔèÁµé¹¢Í§tempdeváÅÐtemplog.
2. ãªéãºá¨é§ÂÍ´à»ÅÕè¹°Ò¹¢éÍÁÙÅ ¡ÒÃÃкت×èÍá¿éÁẺÅͨԤÑÅà»ç¹´Ñ§¹Õé:

   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

   ¤Ø³¤ÇÃä´éÃѺ¢éͤÇÒÁáÊ´§¢éͤÇÒÁµèÍ仹Õé·ÕèÂ×¹Âѹ¡ÒÃà»ÅÕè¹á»Å§:
   ¢éͤÇÒÁ·Õè 1
   á¿éÁ 'tempdev' ã¹ sysaltfiles ¡ÒûÃѺà»ÅÕè¹ źá¿éÁà¡èÒËÅѧ¨Ò¡àÃÔèÁ¡Ò÷ӧҹ¢Í§ SQL Server ãËÁè
   ¢éͤÇÒÁ 2
   »ÃѺà»ÅÕè¹á¿éÁ 'templog' ã¹ sysaltfiles áÅéÇ Åºá¿éÁà¡èÒËÅѧ¨Ò¡àÃÔèÁ¡Ò÷ӧҹ¢Í§ SQL Server ãËÁè
3. ¡ÒÃãªésp_helpfileã¹tempdbwill not confirm these changes until you restart SQL Server.
4. Stop and then restart SQL Server.

¡ÅѺ仴éÒ¹º¹ | ãËé¢éÍàʹÍá¹Ð