Sqlserver事务备份和还原的实例代码(必看)

create database mydb
use mydb
go
create table account(
id varchar(16),
name varchar(16),
balance float
)
go
select * from account

insert into account(id, name, balance) values(‘620101’, ‘liyong’, 300)
insert into account(id, name, balance) values(‘620106’, ‘mali’, 400)
–insert into account(id, name, balance) values(‘620009’, ‘chenying’, 800)
insert into account(id, name, balance) values(‘646009’, ‘chenying’, 800)
–delete from account where id = ‘620009’
go
update account set balance = balance – 1000 where id = ‘620101’
update account set balance = balance + 1000 where id = ‘620106’
–消息 547，级别 16，状态 0，第 1 行
–UPDATE 语句与 CHECK 约束”CK_Blance”冲突。该冲突发生于数据库”mydb”，表”dbo.account”, column ‘balance’。
–语句已终止。

go
–alter table account
–alter COlumn balance int
go
alter table account
add constraint CK_Blance check(balance >= 0)
go
alter table account
drop constraint CK_Blance
–定一个事务
–从liyong扣钱往mali加钱
begin transaction
update account set balance = balance – 1000 where id = ‘620101’
if((select balance output from account where id = ‘620101’) < 0)
begin
PRINT(‘余额不足！’);
ROLLBACK;
end
else
begin
update account set balance = balance + 1000 where id = ‘620106’
commit;
PRINT(‘转账成功！’);
end
go
sp_help
–备份设备
sp_addumpdevice ‘disk’, ‘xk_bak’ ,’d:\xk_bak’
–备份数据库
backup database mydb
to xk_bak
–还原数据库
restore database mydb from disk = ‘d:\xk_bak’
with replace; –覆盖