--添加设备 declare @device_dat varchar(50),@device_log varchar(50) declare @device_dat_path varchar(2000), @device_log_path varchar(2000) set @device_dat='temp_dat' set @device_log='temp_log' set @device_dat_path='d:/accp5.0/database/backup'+'/'+@device_dat+'.dat' set @device_log_path='d:/accp5.0/database/backup'+'/'+@device_log+'.dat' exec sp_addumpdevice 'disk',@device_dat,@device_dat_path exec sp_addumpdevice 'disk',@device_log,@device_log_path --设备删除 exec sp_dropdevice @device_dat exec sp_dropdevice @device_log --数据库备份 declare @dbName varchar(50) set @dbName='MySchool_dboy' backup database @dbName to disk='d:/accp5.0/database/backup/temp_dat.dat' backup log @dbName to disk='d:/accp5.0/database/backup/temp_log.dat' --数据恢复,恢复之前 restore database newdb from disk='d:/accp5.0/database/backup/temp_dat.dat' with replace restore log newdb from disk='d:/accp5.0/database/backup/temp_log.dat' with replace --创建数据库副本 restore database newcopy from disk='d:/accp5.0/database/backup/temp_dat.dat' with move 'MySchool_dboy' to 'd:/accp5.0/database/backup/MySchool_dboy_copy.mdf', move 'MySchool_dboy_log' to 'd:/accp5.0/database/backup/MySchool_dboy_log_copy.ldf' --附加数据库sp_attach sp_attach_db stuinfo_case,'d:/sql server 2005/case/stuinfo_case.mdf','d:/sql server 2005/case/stuinfo_case.ldf' --注意:恢复数据库时一定要用绝对路径 --2、分离数据库stuinfo_case sp_detach_db 'stuinfo_case' ,'true' --sp_detach_db只能分离没人在用的数据库,如果有人用就要kill会话 --kill所有用户会话 use master go declare @tblConnectedUsers table(spid int) declare @sql varchar(200),@isPID int,@dbName varchar(20) set @dbName=MySchool_doy insert into @tblConnectedUsers select p.spid from sysprocesses as p (nolock) join sysdatabases as d (nolock) on p.dbid=d.dbid where d.[name]=@dbName go while 1=1 begin select top 1 @isPID =spid from @tblConnectedUsers where spid,0) order by spid asc if @@RowCount=0 break set @sql='kill '+Convert(varchar(10),@isPID) exec(@sql) End --删除事务日志 dump transaction MySchool_dboy with no_log --MySchool_dboy为数据库名 --截断事务日志: backup log MySchool_dboy with no_log --收缩数据库文件(如果不压缩,数据库的文件不会减小 dbcc shrinkdatabase(MySchool_dboy) --设置数据库自动收缩 sp_dboption MySchool_dboy ,autoshrink,true