自动生成数据库还原

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)

as
Begin
 
 --创建数据库
 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)

End
GO