SQL DB到DB副本

Question

嘿，我在使用。是否有任何SQL命令将整个db (包括sp、视图、函数)复制到另一个空白db。表数据可以传输到另一个db表，但是整个db如何？

Answer 1

Copy All tables with Data in Another Database

 CREATE PROCEDURE [dbo].[uspCreateCopyTables]-- GOALTEST,GOAL     
        -- parameters for the stored procedure here    
        @toDatabase VARCHAR(100)    
        ,@fromDatabase VARCHAR(100)    
    AS    
    BEGIN    
        -- SET NOCOUNT ON added to prevent extra result sets from    
        SET NOCOUNT ON;    

        DECLARE @fullTableList VARCHAR(8000);    
        DECLARE @idx INT;    
        DECLARE @tableName VARCHAR(8000);    
        DECLARE @SQLQuery NVARCHAR(500);    
        DECLARE @ParameterDefinition NVARCHAR(100);    

        -- this  query gives the list of table name existing in the database.    
        SELECT @fullTableList = ISNULL(@fullTableList + ',' + TABLE_NAME, TABLE_NAME)    
        FROM INFORMATION_SCHEMA.TABLES    
        WHERE TABLE_TYPE = 'BASE TABLE';    

        SELECT @idx = 1    

        /* this section splits the table name from comma separated string and copies that table name from      
            one database to another database*/    
        IF LEN(@fullTableList) > 1    
            OR @fullTableList IS NOT NULL    
            WHILE @idx != 0    
            BEGIN    
                SET @idx = CHARINDEX(',', @fullTableList)    

                IF @idx != 0    
                    SET @tableName = LEFT(@fullTableList, @idx - 1)    
                ELSE    
                    SET @tableName = @fullTableList    

                IF (LEN(@tableName) > 0)    
                    SET @SQLQuery = 'SELECT  * INTO [' + @toDatabase + '].[dbo].[' + @tableName + '] FROM [' + @fromDatabase + '].[dbo].[' + @tableName + ']'    

                EXEC (@SQLQuery)    

                SET @fullTableList = RIGHT(@fullTableList, LEN(@fullTableList) - @idx)    

                IF LEN(@fullTableList) = 0    
                    BREAK    
            END    
    END    
    GO   

Answer 2

您有以下选择：

• 备份和恢复：备份整个数据库，然后在另一个实例上还原它。任务->备份。
• 复制数据库文件(.mdf、.ldf文件)，然后在另一个实例中复制附在。
• 使用生成脚本向导在.sql文件中生成整个数据库对象(模式和数据)，然后在其他实例上运行这些文件。