数据库备份、还原、删除、收缩，创建登录用户，数据库用户等操作脚本

记录一下/

--备份数据库
use [master]
go
backup database [lnksys11] to 
disk = n'c:\backup\lnksys11.bak' with   --备份文件存放路径
noformat,
init,    --init:覆盖备份;noinit: 追加备份
name = n'lnksys11-full database backup', 
skip, 
norewind, 
nounload, 
stats = 10
go

--删除数据库
drop database [lnksys11]
go

--创建登陆帐户(create login)
if not exists (select 1 from master.dbo.syslogins where loginname='test')
  create login test with password='1234567890', check_policy = off, default_database=master
go

--还原数据库
use [master];
go
restore database [lnksys11] from
disk = n'c:\backup\lnksys11.bak'    --待还原文件位置
with file = 1,
--数据库文件,日志文件存放路径，
move n'wcs_data' to n'c:\program files\microsoft sql server\mssql10_50.mssqlserver\mssql\data\lnksys11.mdf',
move n'wcs_log' to n'c:\program files\microsoft sql server\mssql10_50.mssqlserver\mssql\data\lnksys11.ldf',
recovery,
nounload,
--replace,    --替换已有数据库
stats = 10
go

--收缩数据库，和日志文件
-----------begin-----------
use [lnksys11]
go
alter database [lnksys11] set recovery simple with no_wait
go
alter database [lnksys11] set recovery simple 
go
--收缩日志文件
declare @name varchar(50),
        @sql  varchar(100);
select @name = [name] from sys.database_files where [type]=1
set @sql = 'dbcc shrinkfile(n'''+@name+''', 10, truncateonly)';
exec (@sql)
go
dbcc shrinkdatabase([lnksys11])
go
--恢复数据库为完整模式
alter database [lnksys11] set recovery full with no_wait
go
alter database [lnksys11]  set recovery full
go
-----------end-----------

--创建数据库(lnksys11)用户
use [lnksys11]
go
if exists (select 1 from sys.sysusers where issqluser=1 and name='test')
  exec sp_dropuser 'test'
create user test for login test with default_schema=dbo
go
exec sp_addrolemember 'db_owner', 'test'
go

--判断是否存在用户自定义用户，如果存在则删除。
if exists(select * from sys.database_principals where name='test')
begin 
  declare @userrole varchar(20), 
          @sql varchar(300);
  --获取用户拥有的角色信息。
  declare cur_userrole cursor for select [name] from sys.schemas where principal_id=user_id('test')
  open cur_userrole 
  fetch next from cur_userrole into @userrole 
  while @@fetch_status=0
  begin
    --把架构所有者修改回来架构自身
    set @sql = 'alter authorization on schema::['+@userrole+'] to ['+@userrole+']; ';
    --删除角色拥有的成员 需要高版本(sql2017测试通过),2008 r2 无效
    set @sql = @sql+'alter role ['+@userrole+'] drop member [test]';
    exec(@sql);
    fetch next from cur_userrole into @userrole
  end
  close cur_userrole;
  deallocate cur_userrole;
  --删除用户
  drop user [test];
end;
go