答案.sql

主要介绍了sql的基础教程

二、学生作业
1、在D盘建立test目录，所有操作都在此目录下。建表student，作完全备份；建表t1，做差异备份；建表t2，做日志备份；然后将表student、表t1，表t2全部删除，并恢复所有备份。再创建表t3，做t3的单独备份。
第一步:
exec sp_addumpdevice 'disk','myfback','d:\test\fb.bak'

exec sp_addumpdevice 'disk','mydback','d:\test\db.bak'

exec sp_addumpdevice 'disk','mylback','d:\test\lb.bak'

第二步:
create database aa
----------------------------------------------------------------------------------------------------------------------
建表student
use aa
go
create table student
(
 sno int primary key,
 sname varchar(10),
 ssex char(2),
 sage int,
 sdept varchar(10)
)
----------------------------------------------------------------------------------------------------------------------

完全备份
backup database aa to myfback
----------------------------------------------------------------------------------------------------------------------

建表t1
use aa 
go
create table t1
(
 sno int primary key,
 sname varchar(10)
)
----------------------------------------------------------------------------------------------------------------------

差异备份
backup database aa to mydback with differential
----------------------------------------------------------------------------------------------------------------------

建表t2
use aa
go
create table t2
(
 cno int primary key,
 cname varchar(10),
 grade int
)
----------------------------------------------------------------------------------------------------------------------

日志备份
backup log aa to mylback
----------------------------------------------------------------------------------------------------------------------

删除表
drop table t2
drop table t1
drop table student
----------------------------------------------------------------------------------------------------------------------

恢复完全备份(with norecovery)
use master
go
restore database aa from myfback with norecovery
----------------------------------------------------------------------------------------------------------------------

恢复差异备份(with recovery)
restore database aa from mydback with recovery
----------------------------------------------------------------------------------------------------------------------

恢复日志备份
use aa
go
drop table t1
drop table student
use master
go
restore database aa from myfback with norecovery
restore log aa from mylback with recovery
----------------------------------------------------------------------------------------------------------------------

创建表t3
use aa
go
create table t3
(
 sno int,
 cno int,
 grade int,
 primary key (sno,cno)
)
----------------------------------------------------------------------------------------------------------------------

单独备份t3
backup log aa to mylback

drop table t3
drop table t2
drop table t1
drop table student

use master
go
restore database aa from myfback with norecovery
restore log aa from mylback with file = 1, norecovery
restore log aa from mylback with file = 2, recovery