資料庫備份、還原、刪除、收縮，創建登錄用戶，資料庫用戶等操作腳本

記錄一下/

--備份資料庫
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