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

转载

mb607022e25a607 2021-05-07 23:07:57

记录一下/

--备份数据库use [master]goBACKUP DATABASE [LnkSys11] TO DISK = N'C:\BackUp\LnkSys11.bak' WITH   --备份文件存放路径NOFORMAT,
INIT,    --INIT:覆盖备份;NOINIT: 追加备份NAME = N'LnkSys11-Full Database Backup', 
SKIP, 
NOREWIND, 
NOUNLOAD, 
STATS = 10GO--删除数据库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=mastergo--还原数据库USE [master];GORESTORE DATABASE [LnkSys11] FROMDISK = 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 = 10GO--收缩数据库，和日志文件-----------begin-----------use [LnkSys11]goalter database [LnkSys11] set recovery simple with NO_WAITgoalter database [LnkSys11] set recovery simple 
go--收缩日志文件declare @name varchar(50),        @sql  varchar(100);SELECT @name = [NAME] FROM sys.database_files where [type]=1set @sql = 'dbcc shrinkfile(N'''+@name+''', 10, truncateonly)';exec (@sql)godbcc shrinkdatabase([LnkSys11])go--恢复数据库为完整模式alter database [LnkSys11] set recovery full with NO_WAITgoalter database [LnkSys11]  set recovery fullgo-----------end-------------创建数据库(LnkSys11)用户use [LnkSys11]goif 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=dbogoexec sp_addrolemember 'db_owner', 'test'go--判断是否存在用户自定义用户，如果存在则删除。

use [LnkSys11]
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