备份& RESTORE DATABASE STORE PROCEDURE [英] BACKUP & RESTORE DATABASE STORE PROCEDURE

问题描述

使用数据库 SQL SERVER 2014



我想要 BACKUP 并恢复代码  WITH
商店程序

解决方案

您好，

有两种方法可以使用存储过程或使用SMO（SQL-Server管理对象） ）。

存储过程的示例（取自
docs ）

非存储过程

 USE AdventureWorks2012; 
 GO 
 BACKUP DATABASE AdventureWorks2012 
 TO DISK ='Z：\ SQLServerBackups\AdventureWorks2012.Bak'
 WITH FORMAT，
 MEDIANAME ='Z_SQLServerBackups'，
 NAME ='AdventureWorks2012的完全备份'; 
 GO 

作为存储过程

 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 CREATE PROCEDURE dbo.usp_BackupDatabase AS 
 BEGIN 
 DECLARE @FileName AS NVARCHAR（50）; 
 DECLARE @MediaName AS NVARCHAR（50）; 
 DECLARE @Name AS NVARCHAR（50）; 
 BACKUP DATABASE AdventureWorks2012 
 TO DISK = @FileName 
 WITH FORMAT，
 MEDIANAME = @MediaName，
 NAME = @Name; 
 END 

VB.NET代码，创建类的实例，使用所需参数调用方法。

 Imports System.Data.SqlClient 
 
 Public Class ForumQuestion 
 Public Sub BackUpDatabase（pFileName As String，pMediaName As String，pName As String）
 using cn As New SqlConnection With {.ConnectionString =" TODO"} 
使用cmd作为新的SqlCommand使用
 {
 .Connection = cn，
 .CommandType = CommandType.StoredProcedure 
} 
 cmd.CommandText =" usp_BackupDatabase" 
 cmd.Parameters.AddWithValue（" @ FileName"，pFileName）
 cmd.Parameters.AddWithValue（" @ MediaName"，pMediaName）
 cmd.Parameters.AddWithValue（" @ Name" ;，pName）
 cn.Open（）
 cmd.ExecuteNonQuery（）
结束使用
结束使用
结束次级
结束类
 

使用提供更多控制但代码更多的SMO。

可以从NuGet安装SMO

https： //docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/installing-smo?view=sql-server-2017

< pre class ="prettyprint lang-vb"> Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.VisualBasic.MyServices

Module SM O_VBBackup3

Sub Main（）
'连接到SQL Server的本地默认实例。
Dim srv As Server
srv = New Server

'参考AdventureWorks2012数据库。
Dim db As Database
db = srv.Databases（" AdventureWorks2012"）

'将当前恢复模型存储在变量中。
Dim recoverymod As Integer
recoverymod = db.DatabaseOptions.RecoveryModel

'定义备份对象变量。
Dim bk As New Backup

'指定要备份的备份类型，描述，名称和数据库。
bk.Action = BackupActionType.Database
bk.BackupSetDescription =" AdventureWorks2012的完整备份"
bk.BackupSetName =" AdventureWorks 2012 Backup"
bk.Database =" AdventureWorks2012"

'通过在构造函数中提供备份设备文件名来声明BackupDeviceItem，并且设备类型是文件。
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem（" Test_Full_Backup1"，DeviceType.File）

'将设备添加到备份对象。
bk.Devices.Add（bdi）

'将Incremental属性设置为False以指定这是完整数据库备份。
bk.Incremental = False

'设置到期日期。
Dim backupdate As New Date
backupdate = New Date（2006,10,5）
bk.ExpirationDate = backupdate

'指定日志必须截断后备份完成。
bk.LogTruncation = BackupTruncateLogType.Truncate

'运行SqlBackup以在SQL Server实例上执行完整数据库备份。
bk.SqlBackup（srv）

'通知用户备份已完成。
Console.WriteLine（"完全备份完成。"）

'从备份对象中删除备份设备。
bk.Devices.Remove（bdi）

'对数据库进行更改，在这种情况下，添加一个名为test_table的表。
Dim t As Table
t = New Table（db，"test_table"）
Dim c As Column
c = New Column（t，"col"，DataType.Int）
t.Columns.Add（c）
t.Create（）

'为差异备份创建另一个文件设备并添加备份对象。
Dim bdid As BackupDeviceItem
bdid = New BackupDeviceItem（" Test_Differential_Backup1"，DeviceType.File）

'将设备添加到备份对象。
bk.Devices.Add（bdid）

'将差异备份的Incremental属性设置为True。
bk.Incremental = True

'运行SqlBackup以在SQL Server实例上执行增量数据库备份。
bk.SqlBackup（srv）

'通知用户差异备份已完成。
Console.WriteLine（"差异备份完成。"）

'从备份对象中删除设备。
bk.Devices.Remove（bdid）

'在恢复之前删除AdventureWorks2012数据库。
srv.Databases（" AdventureWorks2012"）。Drop（）

'定义一个Restore对象变量。
Dim rs As Restore
rs = New Restore

'将NoRecovery属性设置为true，因此不会恢复事务。
rs.NoRecovery = True

'将包含完整数据库备份的设备添加到Restore对象。
rs.Devices.Add（bdi）

'指定数据库名称。
rs.Database =" AdventureWorks2012"

'恢复完整数据库备份而无需恢复。
rs.SqlRestore（srv）

'通知用户完整数据库还原已完成。
Console.WriteLine（"完整数据库还原完成。"）

'从Restore对象中删除设备。
rs.Devices.Remove（bdi）

'将te NoRecovery属性设置为False。
rs.NoRecovery = False

'将包含差异备份的设备添加到Restore对象。
rs.Devices.Add（bdid）

'恢复差异数据库备份。
rs.SqlRestore（srv）

'通知用户差异数据库恢复已完成。
Console.WriteLine（"差异数据库恢复完成。"）

'删除设备。
rs.Devices.Remove（bdid）

'将数据库恢复模式设置回原始值。
srv.Databases（" AdventureWorks2012"）。DatabaseOptions.RecoveryModel = recoverymod

'删除已添加的表。
srv.Databases（" AdventureWorks2012"）。Tables（" test_table"）。Drop（）
srv.Databases（" AdventureWorks2012"）。Alter（）

'从硬盘中删除备份文件。
My.Computer.FileSystem.DeleteFile（" C：\Program Files \ Microsoft Microsoft SQL Server \ MSSQL.12 \ MSSQL \ Backup \ Test_Full_Backup1"）
My.Computer.FileSystem .DeleteFile（" C：\Program Files \ Microsoft SQL Server \ MSSQL.12 \ MSSQL \ Backup \ Test_Differential_Backup1"）
End Sub
End Module

无论哪种方式，都知道限制和限制

显式或隐式事务中不允许使用BACKUP语句。

创建备份最新版本的SQL Server无法在早期版本的SQL Server中恢复

通过SMO恢复。

通过T-SQL恢复（这是备份示例的完成）以上）。

更改以下内容，创建一个存储过程并执行create，然后测试它wi与上面的备份示例相似的代码。对于下面的示例，我建议不使用参数，有一个专用于执行一个
数据库的存储过程。

 RESTORE DATABASE AdventureWorks2012 
来自AdventureWorks2012Backups; 

Use a database SQL SERVER 2014

I want the BACKUPand restore code  WITH STORE PROCEDURE

解决方案

Hello,

There are two ways to go, using a stored procedure or using SMO (SQL-Server Management Objects).

Example for a stored procedure (taken from the docs)

Non-Stored procedure

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'  
   WITH FORMAT,  
      MEDIANAME = 'Z_SQLServerBackups',  
      NAME = 'Full Backup of AdventureWorks2012';  
GO  

As a Stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.usp_BackupDatabase AS
BEGIN
DECLARE @FileName AS NVARCHAR(50) ;
DECLARE @MediaName AS NVARCHAR(50) ;
DECLARE @Name AS NVARCHAR(50) ;
BACKUP DATABASE AdventureWorks2012  
TO DISK = @FileName
   WITH FORMAT,  
      MEDIANAME = @MediaName,  
      NAME = @Name;
END

VB.NET code, create an instance of the class, call the method with desired parameters.

Imports System.Data.SqlClient

Public Class ForumQuestion
    Public Sub BackUpDatabase(pFileName As String, pMediaName As String, pName As String)
        Using cn As New SqlConnection With {.ConnectionString = "TODO"}
            Using cmd As New SqlCommand With
                {
                    .Connection = cn,
                    .CommandType = CommandType.StoredProcedure
                }
                cmd.CommandText = "usp_BackupDatabase"
                cmd.Parameters.AddWithValue("@FileName", pFileName)
                cmd.Parameters.AddWithValue("@MediaName", pMediaName)
                cmd.Parameters.AddWithValue("@Name", pName)
                cn.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub
End Class

Using SMO which provides more control but is more code.

SMO is installable from NuGet

https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/installing-smo?view=sql-server-2017

Imports Microsoft.SqlServer.Management.Common  
Imports Microsoft.SqlServer.Management.Smo  
Imports Microsoft.VisualBasic.MyServices  

Module SMO_VBBackup3  

    Sub Main()  
        'Connect to the local, default instance of SQL Server.  
        Dim srv As Server  
        srv = New Server  

        'Reference the AdventureWorks2012 database.  
        Dim db As Database  
        db = srv.Databases("AdventureWorks2012")  

        'Store the current recovery model in a variable.  
        Dim recoverymod As Integer  
        recoverymod = db.DatabaseOptions.RecoveryModel  

        'Define a Backup object variable.   
        Dim bk As New Backup  

        'Specify the type of backup, the description, the name, and the database to be backed up.  
        bk.Action = BackupActionType.Database  
        bk.BackupSetDescription = "Full backup of AdventureWorks2012"  
        bk.BackupSetName = "AdventureWorks 2012 Backup"  
        bk.Database = "AdventureWorks2012"  

        'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.  
        Dim bdi As BackupDeviceItem  
        bdi = New BackupDeviceItem("Test_Full_Backup1", DeviceType.File)  

        'Add the device to the Backup object.  
        bk.Devices.Add(bdi)  

        'Set the Incremental property to False to specify that this is a full database backup.  
        bk.Incremental = False  

        'Set the expiration date.  
        Dim backupdate As New Date  
        backupdate = New Date(2006, 10, 5)  
        bk.ExpirationDate = backupdate  

        'Specify that the log must be truncated after the backup is complete.  
        bk.LogTruncation = BackupTruncateLogType.Truncate  

        'Run SqlBackup to perform the full database backup on the instance of SQL Server.  
        bk.SqlBackup(srv)  

        'Inform the user that the backup has been completed.  
        Console.WriteLine("Full Backup complete.")  

        'Remove the backup device from the Backup object.  
        bk.Devices.Remove(bdi)  

        'Make a change to the database, in this case, add a table called test_table.  
        Dim t As Table  
        t = New Table(db, "test_table")  
        Dim c As Column  
        c = New Column(t, "col", DataType.Int)  
        t.Columns.Add(c)  
        t.Create()  

        'Create another file device for the differential backup and add the Backup object.  
        Dim bdid As BackupDeviceItem  
        bdid = New BackupDeviceItem("Test_Differential_Backup1", DeviceType.File)  

        'Add the device to the Backup object.  
        bk.Devices.Add(bdid)  

        'Set the Incremental property to True for a differential backup.  
        bk.Incremental = True  

        'Run SqlBackup to perform the incremental database backup on the instance of SQL Server.  
        bk.SqlBackup(srv)  

        'Inform the user that the differential backup is complete.  
        Console.WriteLine("Differential Backup complete.")  

        'Remove the device from the Backup object.  
        bk.Devices.Remove(bdid)  

        'Delete the AdventureWorks2012 database before restoring it.  
        srv.Databases("AdventureWorks2012").Drop()  

        'Define a Restore object variable.  
        Dim rs As Restore  
        rs = New Restore  

        'Set the NoRecovery property to true, so the transactions are not recovered.  
        rs.NoRecovery = True  

        'Add the device that contains the full database backup to the Restore object.  
        rs.Devices.Add(bdi)  

        'Specify the database name.  
        rs.Database = "AdventureWorks2012"  

        'Restore the full database backup with no recovery.  
        rs.SqlRestore(srv)  

        'Inform the user that the Full Database Restore is complete.  
        Console.WriteLine("Full Database Restore complete.")  

        'Remove the device from the Restore object.  
        rs.Devices.Remove(bdi)  

        'Set te NoRecovery property to False.  
        rs.NoRecovery = False  

        'Add the device that contains the differential backup to the Restore object.  
        rs.Devices.Add(bdid)  

        'Restore the differential database backup with recovery.  
        rs.SqlRestore(srv)  

        'Inform the user that the differential database restore is complete.  
        Console.WriteLine("Differential Database Restore complete.")  

        'Remove the device.  
        rs.Devices.Remove(bdid)  

        'Set the database recovery mode back to its original value.  
        srv.Databases("AdventureWorks2012").DatabaseOptions.RecoveryModel = recoverymod  

        'Drop the table that was added.  
        srv.Databases("AdventureWorks2012").Tables("test_table").Drop()  
        srv.Databases("AdventureWorks2012").Alter()  

        'Remove the backup files from the hard disk.  
        My.Computer.FileSystem.DeleteFile("C:\Program Files\Microsoft SQL Server\MSSQL.12\MSSQL\Backup\Test_Full_Backup1")  
        My.Computer.FileSystem.DeleteFile("C:\Program Files\Microsoft SQL Server\MSSQL.12\MSSQL\Backup\Test_Differential_Backup1")  
    End Sub  
End Module  

Either way, know the limitations and restrictions

The BACKUP statement is not allowed in an explicit or implicit transaction.
Backups created by more recent version of SQL Server cannot be restored in earlier versions of SQL Server

Restoring via SMO.

Restoring via T-SQL (which is what the backup example is done with above).

Alter the following, create a stored procedure and execute the create followed by testing it with similar code as done with the backup example above. For the example below I recommend not using parameters, have one stored procedure dedicated to doing one database.

RESTORE DATABASE AdventureWorks2012   
   FROM AdventureWorks2012Backups; 

这篇关于备份&amp; RESTORE DATABASE STORE PROCEDURE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！