如何在sql中利用已存在的資料庫複製一個一模一樣新的空資料庫

Hi, 請說明資料庫種類以方便回答. 若是 sql server 的話, 可以使用 dts 來處理即可, 如圖所示, 1. 在你要複製的來源資料庫按右鍵選匯出資料, 2. 來源就直接下一步, 出現目的, 選你的目的 server , 並選新增資料庫, 再給定一個名稱! 3. 下一步後, 最重要的要選第三個[物件和資料的複製], 4. 再來若是只要 schema 不要資料, 將[複製資料]選項取消即可, 其餘用預設值就會將原來資料庫的所有 schema 含權限等資訊複製至目的地資料庫!!

但每次執行到最後會出現這個畫面，不知道有沒有關係

timhuang 大大的方法應該可以,我提一個另外的方式,先將原來的資料庫備份出去,然後在新的資料庫用匯入的方式,然後要選取原來資料庫的備份檔,要注意的是他會有一個指定路徑,記得要把名字改成新的資料庫的名字!!這樣應該就可以!!不會會連資料一起匯過去喔!! Pillar Wang

------
Pillar Wang

pillar62大大，您的方式我不太會，就是匯入的方法覺得怪怪的，可更詳細的說明嗎？

底下是從 SQL Server 線上叢書找到的 DTS 的程式範例, 不過是 VB 版 你可以從 C:\Program Files\Microsoft SQL Server\80\Tools\Devtools\Samples\Dts\Dtstasks\DTSCopyDatabase 找到範例專案檔 .vpb 裡面引用 SQL Server 的 DTS Dll custtask.dll#Microsoft DTS Custom Tasks Object Library dtspump.dll#Microsoft DTSDataPump Scripting Object Library dtspkg.dll#Microsoft DTSPackage Object Library 如果你剛好是用 VB, 或是會把他轉成 Delphi 就可以用自己寫的程式來做 手邊沒有 VB, 不然可以直接 compile 看看    [摘錄主要片段] Private Sub GenericTaskPackage() 'Run the supplied package (*.dts) Dim oConnection As DTS.Connection Dim oStep As DTS.Step Dim oTask As DTS.Task Dim oCustomTask As DTS.TransferObjectsTask 'TaskObject    On Error GoTo PackageError    Set oPackageEvents = oPackage    'Create step, tasks 'Connections are not necessary when transfering objects. In general are needed when pumping data Set oStep = oPackage.Steps.New Set oTask = oPackage.Tasks.New("DTSTransferObjectsTask") Set oCustomTask = oTask.CustomTask    'Fail the package on errors oPackage.FailOnError = False    With oStep   .Name = "Copy Database Step"   .ExecuteInMainThread = True End With    With oTask   .Name = "GenericPkgTask" End With 'Customize the Task Object With oCustomTask .Name = "DTSTransferObjectsTask" 'SourceServer property specifies the name of the source server .SourceServer = "(local)" 'SourceUseTrustedConnection property specifies whether the Windows Authentication security mode is to be used .SourceUseTrustedConnection = True 'SourceDatabase property specifies the name of the source database .SourceDatabase = sDatabase1 'DestinationServer property specifies the name of the destination server when you transfer SQL Server objects .DestinationServer = "(local)" 'DestinationUseTrustedConnection property specifies whether Windows Authentication is used .DestinationUseTrustedConnection = True 'DestinationDatabase property specifies the name of the destination database to use .DestinationDatabase = sDatabase2 'ScriptFileDirectory property specifies the directory to which the script file and log files are written '.ScriptFileDirectory = 'path must exist 'CopyAllObjects property specifies whether to transfer all objects .CopyAllObjects = True 'IncludeDependencies property specifies whether dependent objects are scripted and transferred during a transfer .IncludeDependencies = False 'IncludeLogins property specifies whether the logins on the source are scripted and transferred .IncludeLogins = False 'IncludeUsers property specifies whether the database users on the source are scripted and transferred .IncludeUsers = False 'DropDestinationObjectsFirst property specifies whether to drop objects, if they already exist on the destination .DropDestinationObjectsFirst = True 'CopySchema property specifies whether database objects are copied .CopySchema = True 'CopyData property specifies whether data is copied and whether existing data is replaced or appended to .CopyData = DTSTransfer_ReplaceData End With oStep.TaskName = oCustomTask.Name 'Add the step oPackage.Steps.Add oStep oPackage.Tasks.Add oTask 'Run the package and release references. oPackage.Execute 'Clean up Set oCustomTask = Nothing Set oTask = Nothing Set oStep = Nothing oPackage.UnInitialize Exit Sub