自动生成数据库还原

    技术2022-05-11  14

    CREATE procedure proc_Create_DB@createDBName varchar(200),@sqlUid varchar(100),@sqlPwd varchar(100),@c_companyCode char(12),@dbAdr varchar(50),@dbSize char(10),@D_syscode varchar(50)

    asBegin  --创建数据库 execute  ('IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'''+@createDBName+''')'+  'DROP DATABASE ['+@createDBName+']'+ '; '+ 'CREATE DATABASE ['+@createDBName+']'+  '; '+ 'exec sp_addlogin '''+@sqlUid+''','''+@sqlPwd+''','''+@createDBName+'''') ;

     --还原数据库并移动到数据库指定的盘符 execute('RESTORE DATABASE '+@createDBName+' FROM DISK=''C:/oneTone_db_2008.db'''+ ' with '+ ' move ''oneTone_db_2008'' to ''C:/Program Files/Microsoft SQL Server/MSSQL/Data/'+@createDBName+'.mdf'','+ ' move ''oneTone_db_2008_log'' to ''C:/Program Files/Microsoft SQL Server/MSSQL/Data/'+@createDBName+'_log.ldf'','+ ' replace') ;

     --修改数据库逻辑文件名与新建的数据名同名: execute('Alter DataBase '+@createDBName+ ' MODIFY FILE(NAME=''oneTone_db_2008'',NEWNAME='''+@createDBName+''')'+ '  Alter DataBASE '+@createDBName+ '  MODIFY FILE(NAME=''oneTone_db_2008_log'',NEWNAME='''+@createDBName+'_log'')') ;

     --添加用户 --给用户添加数据库中 --把用户添加在角色中

     execute ('use '+@createDBName+';  exec sp_grantdbaccess '''+@sqlUid+''''+ ';  '+ 'EXEC sp_addrolemember ''db_owner'', '''+@sqlUid+''''+ ';  ') ;

     insert into [com-edudb2009]..k_dbInfo(c_companyCode,D_syscode,D_dbName,D_dbUserName,D_dbpwd,D_dbAdr,D_dbSize) values (@c_companyCode,@D_syscode,@createDBName,@sqlUid,@sqlPwd,@dbAdr,@dbSize)

    EndGO


    最新回复(0)