记录一下/ --备份数据库 use [master] Go BACKUP DATABASE [LnkSys11] TO DISK = N"C:BackUpLnkSys11.bak" WITH --备份文件存放路径 NOFORMAT,
记录一下/
--备份数据库
use [master]
Go
BACKUP DATABASE [LnkSys11] TO
DISK = N"C:BackUpLnkSys11.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:BackUpLnkSys11.bak" --待还原文件位置
WITH FILE = 1,
--数据库文件,日志文件存放路径,
MOVE N"WCS_Data" TO N"c:Program FilesMicrosoft SQL ServerMSsql10_50.MSSQLSERVERMSSQLdataLnkSys11.mdf",
MOVE N"WCS_Log" TO N"c:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLdataLnkSys11.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
--结束END--
本文标题: 数据库备份、还原、删除、收缩,创建登录用户,数据库用户等操作脚本
本文链接: https://www.lsjlt.com/news/2985.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0