T-SQL命令
1、备份设备:备份或还原操作中使用的磁盘或磁带文件称为备份设备.
创建备份设备:
SP_ADDUMPDEVICE
…disk|tape‟,‟logical_name‟,‟physical_name‟
删除备份设备:
SP_DROPDEVICE ‟logical_name‟|‟physical_name‟
SP_ADDUMPDEVICE'DISK','backup1','E:\BACKUP1.DAT'
2、备份数据库：
BACKUP DATABASE “数据库名” TO “备份设备名”[……n] [WITH [DIFFERENTIAL]
[,NAME=‟备份名‟]]
BACKUP DATABASE ST TO BACKUP1
use st
CREATE TABLE clients
(
cid int,
cname char(8),
address char(50)
)
BACKUP DATABASE ST TO BACKUP1 WITH DIFFERENTIAL
3、备份事务日志文件:
BACKUP LOG “数据库名” TO “备份设备名”[……n] [WITH NAME=‟备份名‟]
BACKUP LOG ST TO BACKUP1
4、查看备份设备中原数据库和事务日志的文件信息：
RESTORE FILELISTONLY FROM …备份设备名‟
RESTORE FILELISTONLY FROM BACKUP1
5、查看备份设备中备份文件信息：
RESTORE HEADERONLY FROM …备份设备名‟
RESTORE HEADERONLY FROM BACKUP1
6、还原数据库
RESTORE DATABASE ‘数据库名‟FROM …备份设备名‟
[WITH [FILE=file_number]
[, NORECOVERY]]
DROP DATABASE ST
RESTORE DATABASE ST FROM BACKUP1
7、还原事务日志
RESTORE LOG ‘数据库名‟FROM …备份设备名‟[WITH [FILE=file_number]
[, NORECOVERY]]
BACKUP LOG ST TO BACKUP1 WITH NORECOVERY
RESTORE DATABASE ST FROM BACKUP1 WITH NORECOVERY
RESTORE DATABASE ST FROM BACKUP1 WITH FILE=2。