第六章 备份与恢复数据库

81SQLServerSQLServerMicrosoftSQLServerSQLServer2000SQLServerEnterprisemanager6.1SQLServerSQLServerSQLServer,:,,,82MCSE2000SQL2000,,,,SQLServer836.2SQLServer6.2.1Sysadmindb_ownerdb_backupoperatorNamepipeSQLServerSQLServer2000SQLServerSQLServerSQLServerNamedPipeSQLServerNamedPipesSQLServerEnterpriseManagerNamedPipes61684MCSE2000SQL2000DB_BACKUP_DISKD:BACKUPSDBBACKUP01DATTL_BACKUP_SQLBACKSQLBACKBACKUPSTLBACKUPDATTAPE_BACKUPTAPE0NamedPipesNP_BACKUPSQLBACKPIPESQLBACKUP6.2.2SQLServerASQLServermastermasterSQLServermastermastermasterrebuildmMssq1BinnRebuildmexeSQLServermastermaster1CREATEDATABASEALTERDATABASEDROPDATABASE2Sp_logdevice3Sp-addserver,sp_dropserverSp-addlinkedservermsdb85msdbmsdbSQLServermsdbmodelmodelmodelmastermsdbmodelmodelBSQLServerBACKUPLOGWITHTRUNCATE_ONLYBACKUPLOGWITHNO_LOGSQLServerWRITTEXTUPDATETEXTSQLServerWITHLOG86MCSE2000SQL2000SELECTINTOSQLServerSQLServerSQLServerEnterpriseManagersp_addumpedvicesp-addumpdevicesp_addumdeviceNamedPipeSQLServermastersysdevices32SQLServerEnterpriseManagerSQLServersp-addumpdevicesp_addumpdevice{device-type}[,logical_name][physical_name][,{{controller_type}}|device_status}}]device-typeDISK|TAPE|PIPE187USEmasterEXECsp_addumpdevicedisk,mybackupfile,C:Mssq17BackupMybackupfilebak2Mytape1tape0USEMasterEXECsp_addumpdevicetape,mytapel,tape0BACKUPDATABASEBACKUPDATABASEBACKUPDATABASESQLServerEnterpriseManagerSQLServerBACKUPDATABASE{database_name|@database_name_var}Tobackup_file[,n]backup_filebackup_file_name|@backup_file_name_evar|{DISK|TAPE|PIPE}={temp_backup_file|@temp_backup_file_var}masterUSEmasterBACKUPDATABASEnorthwindTODISK=c:TempMycustomersbak88MCSE2000SQL2000BACKUPSQLServerEnterpriseManagerBackupTransact-SQLSQLServerBACKUPDATABASEE{database_name|@database_name_var}TObackup_file}[,n][WITH[[,]FORMAT][[,]{INIT|NOINIT}][[,]RESTART]]INITNOINITSQLServerNOINITNOINITSQLServerINITSQLServerANSISQLServerNAMEbackup-set-namebackup-set-nameSQLServerFORMATFORMATSQLServerFORMAT89RESTARTSQLSererRESTARTRESTARTBACKUP6.2.3SQLServerSQLServerSQLServerSQLServerSQLServerSQLServerMicrosoftTapeFormatSQLServerSQLServerSQLServerSQLServerMicrosoftWindowsNTUNLOADSQLServerUNLOADSQLServerNOUNLOADUNLOADNOUNLOADSQLServerUNLOADBLOCKSIZEFORMATSKIPINITSQLServerBLOCKSIZE90MCSE2000SQL2000FORMATSQLServerMEDIANAMEMEDIADESCRIPTIONFORMATINITSKIPSKIPSQLServerANSIANSINOSKIPSQLServerANSISQLServer6.2.491BACKUPDATABASE{database_name|@database_name-var}TObackup_device[,n][WITH[BLOCKSIZE={blocksize|@blocksize_variable}][[,]DESCRIPTION={text|@text_variable}][[]DIFFERENTIAL][[]EXPIREDATE={date|@date_var}|RETAINDAYS={days|@days_var}][[,]PASSWORD={password|@password_variable}][[,]FORMAT|NOFORMAT][[,]{INIT|NOINIT}][[,]MEDIADESCRIPTION={text|@text-variable}][[,]MEDIANAME=[media_name|@media_name_variable][[,]MEDIAPASSWORD={mediapassword|@mediapassword_variable}][[,]NAME={backup_set_name|@backup_set_name-ar}][[,]{NOSKIP|SKIP}][[,]{NOUNLOAD|UNLOAD}][[,]RESTART][[,]STATS[=percentage]]]backup_device::={{logical_backup_device-name|@logical_backup_device_name_var}|{DISK|TAPE}={physical_backup_device_name@physical_backup_device_name_var}}backup_device92MCSE2000SQL2000WITH(1)BLOCKSIZESQLServer(2)DEXCRIPTIONtext|@text_variable255(3)DIFFERENTIAL(4)EXPIREDATE={date|@date_var}|REATINDAYS={days|@days_var}}SQLServer(5)FORMATNOFORMATFORMATFORMATINITSKIPNOFORMATINIT(6)INIT|NOINITINITSQLServerNOINITSQLServer(7)MEDIADESCRIPTION={text|@text_variable}255(8)MEDIANAME={media_name|@media_name_varible}128SKIP(9)PASSWORD={password|@password_variable}MEDIAPASSWORD={mediapassword|@mediapassword_variable}(10)NAME={backup_set_name|@backup_set_name_var}93128(11)NOSKIPSKIPNOSKIPSKIPANSIANSISKIPSQLServerANSI(12)NOUNLOADUNLOADNOUNLOADUNLOAD(13)RESTARTRESTART(14)STATS[=percentage]1094MCSE2000SQL2000BACKUPLOG{database_name|@database_name_var}{TObackup_device[,][WITH][BLOCKSIZE={blocksize|@blocksize_variable}][[,]DESCRIPTION={text|@text_variable}][[,]EXPIREDATE={date|@date_var}|RETAINDAYS={days|@days_var}][[,]PASSWORD={poassword|@password_variable}][[,]FORMAT|NOFORMAT][[,]{INIT|NOINIT}][[,]MEDITADESCRIPTIN={text|@text_variable}][[,]MEDIANAME={media_name|@media_name_variable}][[,]MEDIAPASSWORD={mediapassword|@mediapassword_variable}][[,]NAME={backup_set_name|@backup_set_name_var}][[,]NO_TRUNCATE][[,]{NORECOVERY|STANDBY+undo_file_name}][[,]{NOREWIND|REWIND}][[,]{NOSKIP|SKIP}][[,]{NOULOAD|UNLOAD}][[,]RESTART][[,]STATS[=percentage]]]}95backup_device::={{logical_backup_device_name|@logical_backup_device_name_var}|{DISK|TAPE}={physical_backup_device_name|@physical_backup_device_name_var}}BACKUPLOG{database_name|@database_name_var}TObackup_device[,n][WITH[[,]DIFFERENTIAL]]96MCSE2000SQL2000FORMATINITBACKUPDATABASE{database_name|@database_name_var}file_or_filegroup[,n]TObackup_device[,n][WITH][BLOCKSIZE={blocksize|@blocksize_variable}][[,]DESCRIPTION={text|@text_variable}][[,]DIFFERENTIAL][[,]EXPIREDATE={date|@date_var}|RETAINDAYS={days|@days_var}][[,]PASSWORD={poassword|@password_variable}][[,]FORMAT|NOFORMAT][[,]{INIT|NOINIT}][[,]MEDITADESCR