如何实现SQL Server 2005数据库自动备份压缩
发布网友
发布时间：2022-04-07 23:54
我来回答
共2个回答
懂视网
时间：2022-04-08 04:16
<summary>
/// 数据库维护
/// </summary>
public class DatabaseMaintenance
{
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="fileName">备份文件的路径</param>
public static void Backup(string fileName)
{
//TODO SQL Server only now
string sqlConnectionString = ConfigurationManager.ConnectionStrings["HelpStoreContext"].ToString();
using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
string dbName = new SqlConnectionStringBuilder(sqlConnectionString).InitialCatalog;
string commandText = string.Format(
"BACKUP DATABASE [{0}] TO DISK = ‘{1}‘ WITH FORMAT",
dbName,
fileName);
DbCommand dbCommand = new SqlCommand(commandText, conn);
if (conn.State != ConnectionState.Open)
conn.Open();
dbCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 还原数据库 database
/// </summary>
/// <param name="fileName">要还原的数据库文件路径</param>
public static void RestoreBackup(string fileName)
{
string sqlConnectionString = ConfigurationManager.AppSettings["HelpStoreContext"];
using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
string dbName = new SqlConnectionStringBuilder(sqlConnectionString).InitialCatalog;
string commandText = string.Format(
"DECLARE @ErrorMessage NVARCHAR(4000)
" +
"ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
" +
"BEGIN TRY
" +
"RESTORE DATABASE [{0}] FROM DISK = ‘{1}‘ WITH REPLACE
" +
"END TRY
" +
"BEGIN CATCH
" +
"SET @ErrorMessage = ERROR_MESSAGE()
" +
"END CATCH
" +
"ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE
" +
"IF (@ErrorMessage is not NULL)
" +
"BEGIN
" +
"RAISERROR (@ErrorMessage, 16, 1)
" +
"END",
dbName,
fileName);
DbCommand dbCommand = new SqlCommand(commandText, conn);
if (conn.State != ConnectionState.Open)
conn.Open();
dbCommand.ExecuteNonQuery();
}
//clear all pools
SqlConnection.ClearAllPools();
}
}
2、在控制器里调用方法
//备份数据库
public string BackupData()
{
try
{
var dname = DateTime.Now.Ticks;
string filename = Server.MapPath("~/Data/" + dname + ".bak");
if (!System.IO.File.Exists(filename))
{
System.IO.File.Create(filename);
}
DatabaseMaintenance.Backup(filename);
return "备份成功";
}
catch
{
return "备份失败";
}
}
3、删除已经备份的数据库bak文件
//删除数据库备份文件
public string DelDataBase(string id)
{
try
{
string filepath=Server.MapPath("~/Data/"+id);
System.IO.File.Delete(filepath);
return "删除成功";
}
catch {
return "删除失败";
}
}
mvc项目中实现备份数据库（sqlserver2005）
标签：
热心网友
时间：2022-04-08 01:24
实现SQL server2005数据库自动备份压缩功能有两种方式，自动功能和用第三方工具，自带功能比较复杂，请直接参考百度文库教程，如下：http://wenku.baidu.com/link?url=HzqV-FOopbL8jCl1fx8Fh0E5qgS4dGrONoc3xRGcDcx6rQbS9hkobN4aq4MQdX12JdFKHvaOQSRg1lympn-L7wFynKq2p4N0za04ZoV5LpW
用第三方工具“多备份”也可以实现数据库自动备份压缩，节省存储空间
