如何备份和还原SQL Server数据库 [英] how to backup and restore sql server database
本文介绍了如何备份和还原SQL Server数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
hi
我一直在尝试备份sql数据库，但我一直收到此错误
服务器"BG11-A07"的备份失败.
这是我的代码
hi
i''ve been trying to backup sql database but i keep getting this error
Backup failed for Server ''BG11-A07''.
here is my code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using Microsoft.Win32;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;
namespace TIM_System_Backup
{
public partial class frmBackup : Form
{
SqlConnection connections = new SqlConnection(@"");
Server srv;
ServerConnection conn;
public frmBackup()
{
InitializeComponent();
}
private void frmBackup_Load(object sender, EventArgs e)
{
RegistryKey rk = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");
String[] instances = (String[])rk.GetValue("InstalledInstances");
if (instances.Length > 0)
{
foreach (String element in instances)
{
if (element == "MSSQLSERVER")
lstLocalInstances.Items.Add(System.Environment.MachineName);
else
lstLocalInstances.Items.Add(System.Environment.MachineName + @"\" + element);
}
}
Thread threadGetNetworkInstances = new Thread(GetNetworkInstances);
threadGetNetworkInstances.Start();
}
private void txtFileName_TextChanged(object sender, EventArgs e)
{
}
public void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
{
this.progressBar1.Value = e.Percent;
}
delegate void SetMessageCallback(string text);
private void AddNetworkInstance(string text)
{
if (this.lstNetworkInstances.InvokeRequired)
{
SetMessageCallback d = new SetMessageCallback(AddNetworkInstance);
this.BeginInvoke(d, new object[] { text });
}
else
{
this.lstNetworkInstances.Items.Add(text);
}
}
private void GetNetworkInstances()
{
DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
AddNetworkInstance(dr["Name"].ToString());
}
}
}
private void btnConnect_Click_1(object sender, EventArgs e)
{
}
private void btnBrowse_Click_1(object sender, EventArgs e)
{
}
private void btnBackupDB_Click_1(object sender, EventArgs e)
{
}
private void btnRestore_Click_1(object sender, EventArgs e)
{
}
private void btnBackupLog_Click_1(object sender, EventArgs e)
{
Backup bkp = new Backup();
Cursor = Cursors.WaitCursor;
dataGridView1.DataSource = "";
try
{
string strFileName = txtFileName.Text.ToString();
string strDatabaseName = txtDatabase.Text;
bkp.Action = BackupActionType.Log;
bkp.Database = strDatabaseName;
bkp.Devices.AddDevice(strFileName, DeviceType.File);
progressBar1.Value = 0;
progressBar1.Maximum = 100;
progressBar1.Value = 10;
bkp.PercentCompleteNotification = 10;
bkp.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
bkp.SqlBackup(srv);
MessageBox.Show("Log Backed Up To: " + strFileName, "SMO Demos");
}
catch (SmoException exSMO)
{
MessageBox.Show(exSMO.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
Cursor = Cursors.Default;
progressBar1.Value = 0;
}
}
private void btnVerify_Click_1(object sender, EventArgs e)
{
}
private void lstLocalInstances_SelectedIndexChanged(object sender, EventArgs e)
{
}
private void btnConnect_Click(object sender, EventArgs e)
{
//try
//{
//ddlDatabase.Items.Clear();
string sqlSErverInstance;
if (this.tabServers.SelectedIndex == 0)
{
sqlSErverInstance = lstLocalInstances.SelectedItem.ToString();
}
else
{
sqlSErverInstance = lstNetworkInstances.SelectedItem.ToString();
}
if (chkWindowsAuthentication.Checked == true)
{
conn = new ServerConnection();
conn.ServerInstance = sqlSErverInstance;
}
else
{
conn = new ServerConnection(sqlSErverInstance, txtLogin.Text, txtPassword.Text);
}
srv = new Server(conn);
//foreach (Database db in srv.Databases)
//{
// ddlDatabase.Items.Add(db.Name);
//}
txtDatabase.Text = "CONTACTMANAGEMENTSYSTEM.MDF";
//}
//catch (Exception err)
//{
// MessageBox.Show("Failed to connect");
// Console.WriteLine(err);
//}
}
private void btnBackupDB_Click(object sender, EventArgs e)
{
Backup bkp = new Backup();
this.Cursor = Cursors.WaitCursor;
this.dataGridView1.DataSource = string.Empty;
//try
//{
string fileName = this.txtFileName.Text;
string databaseName = this.txtDatabase.Text;
bkp.Action = BackupActionType.Database;
bkp.Database = databaseName;
bkp.Devices.AddDevice(fileName, DeviceType.File);
bkp.Incremental = chkIncremental.Checked;
this.progressBar1.Value = 0;
this.progressBar1.Maximum = 100;
this.progressBar1.Value = 10;
bkp.PercentCompleteNotification = 10;
bkp.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
bkp.SqlBackup(srv);
MessageBox.Show("Database Backed Up To: " + fileName, "SMO Demos");
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.ToString());
//}
//finally
//{
// this.Cursor = Cursors.Default;
// this.progressBar1.Value = 0;
//}
}
推荐答案
执行BACKUP DATABASE语句以创建完整的数据库备份，并指定:
要备份的数据库的名称.
写入完整数据库备份的备份设备.
完整数据库备份的基本Transact-SQL语法为:
Execute the BACKUP DATABASE statement to create the full database backup, specifying:
The name of the database to back up.
The backup device where the full database backup is written.
The basic Transact-SQL syntax for a full database backup is:
BACKUP DATABASE databasename
TO backup_device [ ,...n ]
[ WITH with_options [ ,...o ] ] ;
恢复
For restore
RESTORE DATABASE databasename
FROM Restore_device [ ,...n ];
例子
Example
BACKUP DATABASE MyDB TO DISK='C:/MyDBBackup.bak';
RESTORE DATABASE MyDB FROM DISK='C:/MyDBBackup.bak';
只需执行以下脚本即可在SqlConnection上备份数据库:
Just execute the following script to backup your database onSqlConnection:
BACKUP DATABASE [database name]
TO DISK = 'c:\backup.bak'
WITH NAME = 'Full Backup',FORMAT
这篇关于如何备份和还原SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
