douguachan2879 2011-08-26 15:18
浏览 89
已采纳

将SQL Server 2008数据库架构克隆到新数据库 - 编程(PHP?)

I've done some Googling and searching on SO, but I have not been able to find much help on the matter. I am designing a web service which utilizes an Microsoft SQL Server 2008 Server. The relevant structure is something like this... There is a main database which houses all Primary Account/Company information (Company name, address, etc..). In addition, there are databases for each Account/Company which houses all of the relevant (meta?)data for that account (users, settings, etc...).

SQL2008 Server
|---MainDatabase
|-------Accounts Table
|-----------Account Record where ID = 1
|-----------Account Record where ID = 2
|-----------Account Record where ID = 3
|---AccountDatabase00001
|-------Users Table for account where ID = 1
|---AccountDatabase00001
|-------Users Table for account where ID = 2

When a new account is created (let's say, ID=3), I am trying to figure out a way to clone the table schema and views (NOT the data) of AccountDatabase0001 into a new database called AccountDatabase00003. I could use virtually any language to perform the duplication as long as it can be called from a PHP page somehow.

Has anyone come across such a PHP script, or a script in any other such language for that matter? Is there a command I can send the the SQL server to do this for me? I'm sure I could find my way through manually traversing the structure and writing SQL statements to create each object, but I'm hoping for something more simple.

  • 写回答

2条回答

  • doutan3463 2011-08-26 20:54
    关注

    I found a remotely simply way to accomplish this in PHP with a little help from a custom-written stored procedure which need only exist in the database you wish to clone (for me it's always AccountDatabase_1). You pass the Table name to the Stored Procedure, and it returns the script you need to run in order to create it (which we will do, on the second database). For views, the creation script is actually stored in the Information_Schema.Views table, so you can just pull the view names and creation code from that to create clones very easily.

    STORED PROC GenerateScript()

    USE [SOURCE_DATABASE_NAME]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER Procedure [dbo].[GenerateScript] 
    (            
        @tableName varchar(100)
    )            
    as            
    If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)            
    Begin            
        declare @sql varchar(8000)            
        declare @table varchar(100)            
        declare @cols table (datatype varchar(50))          
        insert into @cols values('bit')          
        insert into @cols values('binary')          
        insert into @cols values('bigint')          
        insert into @cols values('int')          
        insert into @cols values('float')          
        insert into @cols values('datetime')          
        insert into @cols values('text')          
        insert into @cols values('image')          
        insert into @cols values('uniqueidentifier')          
        insert into @cols values('smalldatetime')          
        insert into @cols values('tinyint')          
        insert into @cols values('smallint')          
        insert into @cols values('sql_variant')          
    
        set @sql='' 
    
        Select 
            @sql=@sql+             
            case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type + 
            case when Column_name='id' then ' IDENTITY ' else '' end +            
            case when Data_Type in (Select datatype from @cols) then '' else  '(' end+
            case when data_type in ('real','money','decimal','numeric')  then cast(isnull(numeric_precision,'') as varchar)+','+
            case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end
            when data_type in ('char','nvarchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end+
            case when data_type ='varchar' and Character_Maximum_Length<0 then 'max' else '' end+
            case when data_type ='varchar' and Character_Maximum_Length>=0 then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end+
            case when Data_Type in (Select datatype from @cols)then '' else  ')' end+
            case when Is_Nullable='No ' then ' Not null ' else ' null ' end + 
            case when Column_Default is not null then 'DEFAULT ' + Column_Default else '' end + ','
        from 
            Information_Schema.COLUMNS where Table_Name=@tableName            
    
        select  
            @table=  'Create table ' + table_Name 
        from 
            Information_Schema.COLUMNS 
        where 
            table_Name=@tableName            
    
        select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'            
    
        select @sql  as DDL         
    
    End            
    
    Else        
        Select 'The table '+@tableName + ' does not exist'           
    

    PHP

    function cloneAccountDatabase($new_id){
    
        $srcDatabaseName = "AccountDatabase_1"; //The Database we are cloning
        $sourceConn = openDB($srcDatabaseName);
    
        $destDatabaseName = "AccountDatabase_".(int)$new_id;
        $destConn = openDB($destDatabaseName);
    
        //ENSURE DATABASE EXISTS, OR CREATE IT      
        if ($destConn==null){
            odbc_exec($sourceConn, "CREATE database " . $destDatabaseName);
            $destConn = openDB($destDatabaseName);
        }
    
        //BUILD ARRAY OF TABLE NAMES
        $tables = array();
        $q = odbc_exec($sourceConn, "SELECT name FROM sys.Tables");
        while (odbc_fetch_row($q))
            $tables[]=odbc_result($q,"name");
    
    
        //CREATE TABLES
        foreach ($tables as $tableName){
            $q=odbc_exec($sourceConn, "exec GenerateScript '$tableName';");
            odbc_fetch_row($q);
            $sql = odbc_result($q, 'ddl');
            $q=odbc_exec($destConn, $sql);
        }
    
        //BUILD ARRAY OF VIEW NAMES AND CREATE
        $q = odbc_exec($sourceConn, "SELECT * FROM Information_Schema.Views");
        while (odbc_fetch_row($q)){
            $view=odbc_result($q,"table_name");
            $sql = odbc_result($q, "view_definition");
            odbc_exec($destConn, $sql);
        }           
    
        return(true);   
    }
    

    UPDATE

    The CLONEDATABASE function is available in newer versions of MSSQL.

    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-clonedatabase-transact-sql?view=sql-server-2017

    //Clone AccountDatabase_1 to a database called AccountDatabase_2
    
    DBCC CLONEDATABASE (AccountDatabase_1, AccountDatabase_2) WITH VERIFY_CLONEDB, NO_STATISTICS;
    ALTER DATABASE AccountDatabase_2 SET READ_WRITE WITH NO_WAIT;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 arduino控制ps2手柄一直报错
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 求chat4.0解答一道线性规划题,用lingo编程运行,第一问要求写出数学模型和lingo语言编程模型,第二问第三问解答就行,我的ddl要到了谁来求了
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题