SQL Server的一些常用語句

備份數據庫到磁盤
backup database testdb to disk='E:/mssql/backup/testdb.bak'

備份日誌到磁盤
backup log master to disk='E:/mssql/backup/testdb.trn'

查看備份中的文件信息
RESTORE FILELISTONLY   FROM DISK = 'E:/mssql/backup/testdb.bak'

從備份恢復到一個新的數據庫
RESTORE DATABASE  testdb_new
   FROM DISK = 'E:/mssql/backup/testdb.bak'
   WITH MOVE testdb_Data' TO 'E:/MSSQL/DATA/MSSQL/Data/testdb_new.mdf',
   MOVE 'testdb_Log' TO 'E:/MSSQL/DATA/MSSQL/Data/testdb_new.ldf'

查看系統存儲過程
select * from master..sysobjects where name like 'sp_%'

導出查詢結果到Excel
exec xp_cmdshell 'osql /S musqlserver /U sa /P mypwd /d testdb /Q "select top 10 * from testable" /o C:/my1.xls'

全备份pubs 数据库
/* 创建备份设备*/
use master
exec sp_addumpdevice 'disk' 'pubss' 'c:/mssql7/backup/pubss.dat'
/* 备份数据库*/
backup database pubs to pubss
/* 创建事务日志备份设备*/.
use master
exec sp_addumpdevice 'disk' 'pubsslog' 'c:/mssql7/backup/pubsslog.dat'
/* 备份事务日志*/
backup log pubs to pubsslog

恢复数据库pubs 至2000 年4 月1 日前的状态
restore database pubs
from pubss1 pubss2
with norecovery

restore log pubs
from pubsslog1
with norecovery

restore log pubs
from pubslog2
with recovery stopat = 'Apr 1 2000 15:00 am'