我有一个MS SQL Server 2008 Express系统,其中包含一个数据库,我想“复制和重命名”(用于测试目的),但我不知道一个简单的方法来实现这一点。

我注意到在R2版本的SQL Server中有一个复制数据库向导,但遗憾的是我无法升级。

我们讨论的数据库大约是1g。 我试图恢复我想复制到一个新数据库的数据库的备份,但没有运气。


当前回答

Install Microsoft SQL Management Studio, which you can download for free from Microsoft's website: Version 2008 Microsoft SQL Management Studio 2008 is part of SQL Server 2008 Express with Advanced Services Version 2012 Click download button and check ENU\x64\SQLManagementStudio_x64_ENU.exe Version 2014 Click download button and check MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe Open Microsoft SQL Management Studio. Backup original database to .BAK file (db -> Task -> Backup). Create empty database with new name (clone). Note comments below as this is optional. Click to clone database and open restore dialog (see image) Select Device and add the backup file from step 3. Change destination to test database Change location of database files, it must be different from the original. You can type directly into text box, just add postfix. (NOTE: Order is important. Select checkbox, then change the filenames.) Check WITH REPLACE and WITH KEEP_REPLICATION

其他回答

这里提到的解决方案都不适合我-我使用的是SQL Server Management Studio 2014。

相反,我不得不取消“选项”屏幕中的“在恢复前进行尾日志备份”复选框:在我的版本中,默认情况下它是选中的,并阻止恢复操作完成。 取消检查后,还原操作继续进行,没有出现问题。

原来我试图从备份中错误地进行恢复。

最初我创建了一个新数据库,然后尝试在这里恢复备份。 我本应该做的,也是最终有效的,是打开恢复对话框,并在目标字段中输入新数据库的名称。

因此,简而言之,从备份中恢复是有效的。

谢谢大家的反馈和建议

<!doctype html >

<head>
    <title>Copy Database</title>
</head>

<body>
    
    <?php
    
    $servername = "localhost:xxxx";
    $user1 = "user1";
    $pw1 = "pw1";
    $db1 = "db1";
    
    $conn1 = new mysqli($servername,$user1,$pw1,$db1);
    
    if($conn1->connect_error) {
        die("Conn1 failed: " . $conn1->connect_error);
    }
    
    $user2 = "user2";
    $pw2 = "pw2";
    $db2 = "db2";
    
    $conn2 = new mysqli($servername,$user2,$pw2,$db2);
    
    if($conn2->connect_error) {
        die("Conn2 failed: " . $conn2->connect_error);
    }
    
    $sqlDB1 = "SELECT * FROM table1";
    $resultDB1 = $conn1->query($sqlDB1);
    
    if($resultDB1->num_rows > 0) {
        while($row = $resultDB1->fetch_assoc()) {
            $sqlDB2 = "INSERT INTO table2 (col1, col2) VALUES ('" . $row["tableRow1"] . "','" . $row["tableRow2"] . "')";
            $resultDB2 = $conn2->query($sqlDB2);
        }
    }else{
        echo "0 results";
    }
    
    $conn1->close();
    $conn2->close();
    
    ?>
    
</body>

您可以只创建一个新数据库,然后转到任务,导入数据,并将所有数据从您想要复制的数据库导入到刚刚创建的数据库。

使用MS SQL Server 2012,您需要执行3个基本步骤:

First, generate .sql file containing only the structure of the source DB right click on the source DB and then Tasks then Generate Scripts follow the wizard and save the .sql file locally Second, replace the source DB with the destination one in the .sql file Right click on the destination file, select New Query and Ctrl-H or (Edit - Find and replace - Quick replace) Finally, populate with data Right click on the destination DB, then select Tasks and Import Data Data source drop down set to ".net framework data provider for SQL server" + set the connection string text field under DATA ex: Data Source=Mehdi\SQLEXPRESS;Initial Catalog=db_test;User ID=sa;Password=sqlrpwrd15 do the same with the destination check the table you want to transfer or check box besides "source: ..." to check all of them

你完成了。