Lm th? no ? l?ch tr?nh v t? ?ng sao lu c s? d? li?u SQL Server trong SQL Server Express

D?ch tiu ? D?ch tiu ?
ID c?a bi: 2019698
Bung t?t c? | Thu g?n t?t c?

TM T?T

Phin b?n SQL Server Express khng cung c?p m?t cch ? s?p x?p vi?c lm ho?c k? ho?ch b?o tr? b?i v? cc thnh ph?n ?i l? SQL my ch? khng ?c bao g?m trong cc phin b?n. V? v?y, b?n ph?i c m?t cch ti?p c?n khc nhau ? sao lu c s? d? li?u c?a b?n khi b?n s? d?ng cc phin b?n.   

Hi?n nay, SQL Server Express ng?i dng c th? sao lu c s? d? li?u c?a h? b?ng cch s? d?ng m?t trong nh?ng phng php sau y:

S? d?ng SQL Server Management Studio Express. i?u ny ?c ci ?t cng v?i SQL Server Express Advanced Service ho?c SQL Server Express Toolkit. ? bi?t thm chi ti?t, h?y vo trang web m?ng nh pht tri?n c?a Microsoft (MSDN) sau y:

T?o b?n sao lu c s? d? li?u ?y ? (SQL Server)

  • S? d?ng m?t k?ch b?n Transact-SQL s? d?ng d?ng sao lu c s? d? li?u c?a l?nh. ? bi?t thm chi ti?t, h?y vo trang web MSDN sau y:

    Sao lu (giao d?ch-SQL)

    Bi vi?t ny m t? cch s? d?ng m?t t?p l?nh Transact SQL cng v?i Windows Task Scheduler ? t? ?ng sao lu c s? d? li?u SQL Server Express trn c s? theo l?ch tr?nh.

  • THNG TIN THM

    B?n ph?i lm theo cc b?c sau ba ? sao lu c s? d? li?u SQL Server c?a b?n b?ng cch s? d?ng Windows Task Scheduler:

    B?c A: s? d?ng SQL Server Management Studio Express ho?c Sqlcmd ? t?o ra th? t?c sau y ?c lu tr? trong c s? d? li?u t?ng th?:

    / / B?n quy?n Microsoft Corporation.  T?t c? cc quy?n.

    / / M? ny pht hnh theo cc i?u kho?n c?a cc

    / / Gi?y php cng c?ng Microsoft (MS-PL, http://opensource.org/licenses/ms-pl.html.)

    S? d?ng [Th?c s?]
    GO 
    / ****** ?i t?ng: StoredProcedure [dbo].[sp_BackupDatabases] ****** /
    SET ANSI_NULLS ON
    i
    SET QUOTED_IDENTIFIER ON
    i
    --=============================================
    --tc gi?: Microsoft
    -t?o ngy: 2010-02-06
    -m t?: sao lu c s? d? li?u cho SQLExpress
    -Parameter1: databaseName
    --Parameter2: backupType F = ay, D = vi phn, L = ng nh?p
    --Parameter3: v? tr t?p sao lu
    --=============================================
    t?o th? t?c [dbo]. [sp_BackupDatabases]
    @ databaseName sysname = null,
    @ backupType CHAR(1),
    @ backupLocation nvarchar(200)
    AS
    ?t NOCOUNT ON; 
               
                DECLARE @DBs TABLE
                (
                      ID int IDENTITY PRIMARY KEY,
                      DBNAME nvarchar(500)
                )
               
                 -- Pick out only databases which are online in case ALL databases are chosen to be backed up
                 -- If specific database is chosen to be backed up only pick that out from @DBs
                INSERT INTO @DBs (DBNAME)
                SELECT Name FROM master.sys.databases
                where state=0
                AND name=@DatabaseName
                OR @DatabaseName IS NULL
                ORDER BY Name
               
                -- Filter out databases which do not need to backed up
                IF @backupType='F'
                      BEGIN
                      Xa @ DBs ni DBNAME IN ('tempdb', 'Northwind', 'qun r?u', 'AdventureWorks')
    k?t thc
    ELSE IF @ backupType = c '
    b?t ?u
    DELETE @ DBs ni DBNAME IN ('tempdb', 'Northwind', 'qun r?u', 'th?y', 'AdventureWorks')
    k?t thc
    ELSE IF @ backupType = 'L'
    b?t ?u
    DELETE @ DBs ni DBNAME IN ('tempdb', 'Northwind', 'qun r?u', 'th?y', 'AdventureWorks')
    k?t thc
    khc
    b?t ?u
    tr? v?
    k?t thc
    -khai bo bi?n
    DECLARE @ BackupName varchar(100)
    DECLARE @ BackupFile varchar(100)
    tuyn b? @ DBNAME varchar(300)
    DECLARE @ sqlCommand NVARCHAR(1000)
    DECLARE @ dateTime NVARCHAR(20)
    DECLARE @ Loop int
    -l?p qua cc c s? d? li?u m?t
    ch?n @ Loop = min(ID) t? @ DBs
    trong khi @ Loop l khng NULL
    b?t ?u
    - tn c s? d? li?u ph?i ? ?nh d?ng [dbname] k? t? khi c m?t s? - ho?c _ trong tn c?a h?
          SET @ DBNAME = '[' +(SELECT DBNAME FROM @DBs WHERE ID = @Loop) +']'
    - Set hi?n t?i ngy v gi? n yyyyhhmmss d?ng
    ?t @ dateTime = thay th? (chuy?n ?i (VARCHAR, GETDATE(),101),'/','') + '_' + thay th? (chuy?n ?i (VARCHAR, GETDATE(),108),':','')
    - t?o ra tn t?p sao lu trong ?nh d?ng path\filename.extension cho b?n sao lu ?y ?, diff v ng nh?p
    n?u @ backupType = 'F'
    ?t @ BackupFile = @ backupLocation + thay th? (thay th? (@ DBNAME, ' [', ''),'] ','') + '_FULL_' + @ dateTime + '.BAK'
    ELSE IF @ backupType = c '
    ?t @ BackupFile = @ backupLocation + thay th? (thay th? (@ DBNAME, ' [', ''),'] ','') + '_DIFF_' + @ dateTime + '.BAK'
    ELSE IF @ backupType = 'L'
    ?t @ BackupFile = @ backupLocation + thay th? (thay th? (@ DBNAME, ' [', ''),'] ','') + '_LOG_' + @ dateTime + '.TRN'
     
    -- Provide the backup a name for storing in the media
          IF @backupType = 'F'
                SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
          IF @backupType = 'D'
                SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
          IF @backupType = 'L'
                SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
     
    -- Generate the dynamic SQL command to be executed
     
           IF @backupType = 'F' 
                      BEGIN
                   SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
                      END
           IF @backupType = 'D'
                      BEGIN
                   SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                      END
           IF @backupType = 'L' 
                      BEGIN
                   SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                      END
     
    -- Execute the generated SQL command
           EXEC(@sqlCommand)
     
    -- Goto the next database
    SELECT @Loop = min(ID) FROM @DBs where ID > @ v?ng l?p
    k?t thc

    B?c B: trong m?t tr?nh so?n th?o vn b?n, t?o m?t t?p tin th?c thi ?c ?t theo tn Sqlbackup.bat, v sau sao chp vn b?n t? m?t trong nh?ng v d? sau y vo t?p tin , ty thu?c vo k?ch b?n c?a b?n:

    Example1:Sao lu ?y ? c?a t?t c? cc c s? d? li?u trong tr?ng h?p ?c ?t tn theo ?a phng c?a SQLEXPRESS b?ng cch s? d?ng xc th?c c?a Windows

    / / Sqlbackup.bat

    sqlcmd -S. \EXPRESS E -Q "EXEC sp_BackupDatabases @ backupLocation = c: \SQLBackups\', @ backupType = 'F'" 

    Example2: vi sai sao lu c s? d? li?u t?t c? trong tr?ng h?p ?c ?t tn theo ?a phng c?a SQLEXPRESS b?ng cch s? d?ng m?t SQLLogin v m?t kh?u c?a m?nh

    / / Sqlbackup.bat

    sqlcmd - U SQLLogin -P m?t kh?u -S. \SQLEXPRESS -Q "EXEC sp_BackupDatabases @ backupLocation = 'D:\SQLBackups', @ BackupType = c '"

    Lu ?: The SQLLogin shouldhave lc t nh?t l vai tr? nh i?u hnh d? ph?ng trong SQL Server.


    V d? 3:Nh?t k? sao lu t?t c? cc c s? d? li?u trong ?a phng tn l tr?ng h?p SQLEXPRESS b?ng cch s? d?ng xc th?c c?a Windows

    / / Sqlbackup.bat

    sqlcmd -S. \SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @ backupLocation='D:\SQLBackups\',@backupType='L'"

    V d? 4:Sao lu ?y ? c?a c s? d? li?u USERDB trong tr?ng h?p ?c ?t tn theo ?a phng c?a SQLEXPRESS b?ng cch s? d?ng xc th?c c?a Windows

    / / Sqlbackup.bat

    sqlcmd -S. \SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @ backupLocation = c: \SQLBackups\', @ databaseName = 'USERDB', @ backupType = 'F'"

    Tng t?, b?n c th? lm m?t vi sai sao lu c?a USERDB b?i dn trong s? ' cho tham s? @ backupType v b?n sao lu ng nh?p c?a USERDB b?i dn trong 'L' cho tham s? @ backupType .

    B?c C:L?ch tr?nh m?t cng vi?c b?ng cch s? d?ng Windows Task Scheduler ? th?c thi t?p tin th?c thi m b?n ? t?o ? b?c sinh ? lm i?u ny, h?y lm theo cc b?c sau:

    1. Trn my tnh ang ch?y SQL Server Express, b?m b?t ?u, tr? ?n T?t c? cc chng tr?nh, i?m ?n ph? ki?n, tr? ?n Cng c? h? th?ng, v sau nh?p vo tc v? theo l?ch tr?nh. 
    2. B?m p vo thm theo l?ch tr?nh cng vi?c. 
    3. Trong thu?t s? tc v? theo l?ch tr?nh nh?p ti?p theo. 
    4. nh?p vo tr?nh duy?t, b?m vo t?p tin th?c thi m b?n ? t?o ? b?c B, v sau nh?n Open. 
    5. Lo?i SQLBACKUP cho tn g?i c?a nhi?m v?, b?m vo hng ngy, v sau b?m ti?p theo. 
    6. Ch? ?nh thng tin cho m?t l?ch tr?nh ? ch?y nhi?m v?. (Chng ti ? ngh? b?n ch?y nhi?m v? ny t nh?t m?t th?i gian m?i ngy). Sau , nh?p ti?p theo.
    7. Trong l?nh v?c nh?p tn ng?i dng , g? tn ng?i dng, v sau nh?p m?t kh?u vo tr?ng nh?p m?t kh?u .

      Lu ? Ng?i dng ny nn t ?c giao vai tr? BackupOperator SQL Server c?p n?u b?n ang s? d?ng m?t trong cc t?p tin batch trong v d? 1, 3 ho?c 4.
    8. Nh?p vo ti?p theo, v sau b?m hon t?t. 
    9. Th?c hi?n tc v? theo l?ch t nh?t m?t th?i gian ? ?m b?o r?ng b?n sao lu ?c t?o thnh cng.

      Lu ?Th m?c cho t?p tin th?c thi SQLCMD th?ng l trong bi?n ?ng d?n cho cc my ch? SQL Server ?c ci ?t, nhng n?u bi?n ?ng d?n khng c danh sch c?p ny, b?n c th? t?m th?y n d?i < ci ?t v? tr > \90\Tools\Binn (v d?: c: Program Files Microsoft SQL Server\90\Tools\Binn).

      Lu ? nh?ng i?u sau y khi b?n s? d?ng cc th? t?c ?c di?n t? trong bi vi?t ny:

      • D?ch v? l?p l?ch tc v? Windows ph?i ch?y lc l cng vi?c ?c l?p bi?u ? ch?y. Chng ti ? ngh? r?ng b?n ?t lo?i kh?i ?ng cho d?ch v? ny l t? ?ng. i?u ny ?m b?o r?ng d?ch v? s? ch?y ngay c? trn kh?i ?ng l?i.
      • C nn c r?t nhi?u khng gian trn ? ?a m cc b?n sao lu ?c ang ?c vi?t. Chng ti ? ngh? b?n lm s?ch cc t?p tin c? trong th m?c sao lu th?ng xuyn ? ?m b?o r?ng b?n cha h?t dung l?ng ?a. Cc k?ch b?n khng ch?a logic ? lm s?ch t?p tin c?.

      Tham kh?o thm

    Thu?c tnh

    ID c?a bi: 2019698 - L?n xem xt sau cng: 29 Thang Sau 2012 - Xem xt l?i: 1.0
    T? kha:
    kbmt KB2019698 KbMtvi
    My d?ch
    QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
    Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny: 2019698

    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