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

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

2020-05-20 15:31MSSQL教程网 Sql Server

下面小编就为大家带来一篇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; --覆盖