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

--备份数据库 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