Cach s? d?ng chc nng Tach va inh kem ? di chuyn c s d liu SQL Server

D?ch tiu ? D?ch tiu ?
ID c?a bi: 224071 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

Tm t?t

Bi vi?t ny m t? cch thay ?i v? tr cc t?p d? li?u v t?p nh?t k? cho b?t k? c s? d? li?u Microsoft SQL Server 2005, SQL Server 2000 ho?c SQL Server 7.0 no.

? bi?t thm thng tin v? cch di chuy?n c s? d? li?u h? th?ng trong SQL Server 2008, h?y xem ch? ? "Di chuy?n C s? D? li?u H? th?ng" trong SQL Server Books Online. ? xem ch? ? ny, h?y truy c?p trang web Mang li Nha phat trin Microsoft (MSDN) Di chuy?n C s? d? li?u H? th?ng.

Thng tin thm

Cc b?c m b?n ph?i th?c hi?n ? thay ?i v? tr cho m?t s? c s? d? li?u h? th?ng SQL Server khc v?i cc b?c m b?n ph?i th?c hi?n ? thay ?i v? tr cho c s? d? li?u ng?i dng. Nh?ng tr?ng h?p ?c bi?t ny ?c m t? ring.

Lu ? C s? d? li?u h? th?ng SQL Server 7.0 tng thch v?i SQL Server 2000. Khng nh km c s? d? li?u ch?, m?u, msdb SQL Server 7.0 ho?c cc c s? d? li?u phn ph?i v?i SQL Server 2000. N?u b?n ang s? d?ng SQL Server 2005, b?n ch? c th? nh km c s? d? li?u c?a SQL Server 2005 v?i m?t b?n sao c?a SQL Server 2005. T?t c? cc v d? trong bi vi?t ny gi? ?nh r?ng SQL Server ?c ci ?t trong th m?c D:\Mssql7. Ngoi ra, cc v d? c?ng gi? ?nh r?ng t?t c? cc t?p c s? d? li?u v t?p nh?t k? ?u n?m ? th m?c D:\Mssql7\Data m?c ?nh. Cc v d? di chuy?n t?p d? li?u v t?p nh?t k? cho t?t c? cc c s? d? li?u t?i th m?c E:\Sqldata.

V? tr d? li?u m?c ?nh cho SQL Server 2005 v SQL Server 2000 nh sau:

iu kin tin quyt

  • Sao lu t?t c? c s? d? li?u, ?c bi?t l c s? d? li?u ch? t? v? tr hi?n t?i c?a chng.
  • ?m b?o r?ng b?n c cc quy?n c?a qu?n tr? vin h? th?ng (sa).
  • ?m b?o r?ng b?n bi?t tn v v? tr hi?n t?i c?a t?t c? cc t?p d? li?u c?ng nh t?p nh?t k? c?a c s? d? li?u.

    Lu ? B?n c th? xc ?nh tn v v? tr hi?n t?i c?a t?t c? cc t?p m c s? d? li?u s? d?ng b?ng cch dng quy tr?nh lu s?n sp_helpfile:
    use <database_name>
    go
    sp_helpfile
    go
  • B?n ph?i c ?c quy?n truy c?p vo c s? d? li?u m b?n ang di chuy?n. N?u b?n g?p s? c? trong qu tr?nh ny ?ng th?i khng th? truy c?p c s? d? li?u m b?n ? di chuy?n ho?c n?u b?n khng th? kh?i ?ng SQL Server, h?y ki?m tra nh?t k? l?i SQL Server v SQL Server Book Online ? bi?t thm thng tin v? cc l?i m b?n ang g?p ph?i.

Di chuy?n c s? d? li?u ng?i dng

V d? sau y di chuy?n c s? d? li?u c tn mydb. C s? d? li?u ny ch?a m?t t?p d? li?u, Mydb.mdf v m?t t?p nh?t k?, Mydblog.ldf. N?u c s? d? li?u m b?n di chuy?n c nhi?u t?p d? li?u ho?c t?p nh?t k?, h?y ch? ?nh cc t?p d?i d?ng m?t danh sch ?c phn cch b?ng d?u ph?y trong quy tr?nh lu s?n sp_attach_db. Quy tr?nh sp_detach_db khng thay ?i b?t k? s? t?p c trong c s? d? li?u b?i v? quy tr?nh sp_detach_db khng li?t k cc t?p .
  1. Kh?i ?ng SQL Server 2005 Management Studio. ? th?c hi?n vi?c ny, b?m Kh?i ?ng, b?m T?t c? chng tr?nh, b?m Microsoft SQL Server 2005, sau b?m SQL Server Management Studio.
  2. B?m Truy v?n M?i, sau tch c s? d? li?u nh sau:
    use master
       go
       sp_detach_db 'mydb'
       go
  3. Sao chp t?p d? li?u v t?p nh?t k? t? v? tr hi?n t?i (D:\Mssql7\Data) sang v? tr m?i (E:\Sqldata).
  4. nh km l?i c s? d? li?u. Tr? t?i cc t?p ? v? tr m?i nh sau:
    use master
      go
      sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
      go
    Xc minh thay ?i trong v? tr t?p b?ng cch s? d?ng quy tr?nh lu s?n sp_helpfile:
    use mydb
       go
       sp_helpfile
       go
    Cc gi tr? trong c?t tn t?p ph?i ph?n nh v? tr m?i.
Lu ? Bi vi?t 922804 trong C s? Ki?n th?c Microsoft m t? s? c? x?y ra v?i c s? d? li?u SQL Server 2005 trn b? nh? k?t n?i tr?c ti?p v?i m?ng. ? bi?t thm thng tin, h?y b?m vo s? bi vi?t sau ? xem bi vi?t trong C s? Ki?n th?c Microsoft:
922804 S?A L?I: Sau khi b?n tch c s? d? li?u Microsoft SQL Server 2005 ?c lu tr? trn b? nh? k?t n?i tr?c ti?p v?i m?ng, b?n khng th? nh km l?i c s? d? li?u SQL Server
Xem xt s? c? ny. Ngoi ra, h?y xem xt cc quy?n ?c p d?ng cho c s? d? li?u khi c s? d? li?u ?c tch trong SQL Server 2005. ? bi?t thm thng tin, h?y xem ph?n "Tch v nh km C s? d? li?u" c?a ch? ? "B?o m?t D? li?u v T?p Nh?t k?" trong SQL Server Books Online. ? xem ch? ? ny, h?y truy c?p trang web MSDN B?o m?t D? li?u v T?p Nh?t k?.

Di chuy?n c s? d? li?u m?u

? di chuy?n c s? d? li?u m?u pubs v c s? d? li?u m?u Northwind trong SQL Server 2000 ho?c SQL Server 7.0 ho?c ? di chuy?n c s? d? li?u m?u AdventureWorks v c s? d? li?u m?u AdventureWorksDW trong SQL Server 2005, h?y th?c hi?n theo quy tr?nh tng t? dnh cho di chuy?n c s? d? li?u ng?i dng.

Di chuy?n c s? d? li?u m?u

SQL Server 2005 v SQL Server 2000

SQL Server 7.0

Di chuy?n c s? d? li?u MSDB

SQL Server 2005 v SQL Server 2000

SQL Server 7.0

Di chuy?n c s? d? li?u ch?

SQL Server 2005

SQL Server 2000 v SQL Server 7.0

Di chuy?n c s? d? li?u tempdb

B?n c?ng c th? di chuy?n cc t?p tempdb b?ng cch s? d?ng l?nh ALTER DATABASE.
  1. Xc ?nh cc tn t?p l-gic cho c s? d? li?u tempdb b?ng cch s? d?ng sp_helpfile nh sau:
    use tempdb
    go
    sp_helpfile
    go
    Tn l-gic c?a t?ng t?p c trong c?t tn. V d? ny s? d?ng tn t?p m?c ?nh l tempdev v templog.
  2. S? d?ng cu l?nh THAY ?I C S? D? LI?U, ch? ?nh tn t?p l-gic nh sau:
    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
    B?n s? nh?n ?c thng bo sau xc nh?n thay ?i:
    Thng bo 1
    T?p 'tempdev' ?c s?a ?i trong sysaltfiles. H?y xa t?p c? sau khi kh?i ?ng l?i SQL Server.
    Thng bo 2
    T?p 'templog' ?c s?a ?i trong sysaltfiles. H?y xa t?p c? sau khi kh?i ?ng l?i SQL Server.
  3. Vi?c s? d?ng sp_helpfile trong tempdb s? khng xc nh?n nh?ng thay ?i ny cho ?n khi b?n kh?i ?ng l?i SQL Server.
  4. D?ng, sau kh?i ?ng l?i SQL Server.

Tham kh?o

? bi?t thm thng tin, h?y b?m vo s? bi vi?t sau ? xem bi vi?t trong C s? Ki?n th?c Microsoft:
912397 Khng th? kh?i ?ng d?ch v? SQL Server khi b?n thay ?i tham s? kh?i ?ng cho b?n sao ?c ghp c?m c?a SQL Server 2000 ho?c c?a SQL Server 2005 thnh gi tr? khng h?p l?
274188 Ch? ? "Kh?c ph?c ng?i dng m? ci" trong Books Online b? thi?u
246133 Cch chuy?n thng tin ng nh?p v m?t kh?u gi?a cc b?n sao c?a SQL Server
168001 Quy?n v thng tin ng nh?p c?a ng?i dng trn c s? d? li?u c th? khng chnh xc sau khi c s? d? li?u ?c khi ph?c

? bi?t thm thng tin, h?y xem cc sch sau:
Microsoft Corporation
Microsoft SQL Server 7.0 System Administration Training Kit (B? o t?o Qu?n tr? H? th?ng Microsoft SQL Server 7.0)
Microsoft Press, 2001
Microsoft Corporation
MCSE Training Kit: Microsoft SQL Server 2000 System Administration (B? o t?o MCSE: Qu?n tr? H? th?ng Microsoft SQL Server 2000)
Microsoft Press, 2001
Microsoft Corporation
Microsoft SQL Server 2000 Resource Kit (B? Ti nguyn Microsoft SQL Server 2000)
Microsoft Press, 2001

Thu?c tnh

ID c?a bi: 224071 - L?n xem xt sau cng: 08 Thang Ging 2014 - Xem xt l?i: 5.0
p d?ng
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
T? kha:
kbsqlsetup kbinfo KB224071

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com