|
数据库备份、还原、删除、收缩,创建登录用户,数据库用户等操作脚本
- --备份数据库
- 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
复制代码
|
|