经过一上午的努力,终于实现了通过Procedures将数据库自动备份(再加到Job中即可)。备份后的文件名中,包含当天日期,保证每次备份都是单独的。
paste出来与大家共享。
set
ANSI_NULLS
ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <skywind>
-- Create date: <2006.8.9>
-- Description: <backup a database auto>
-- =============================================
CREATE PROCEDURE [ dbo ] . [ prcd_BackupTmp ]
AS
BEGIN
DECLARE @return_value char ( 17 )
select @return_value = ' D:\ ' + convert ( char ( 10 ), getdate (), 120 ) + ' .BAK '
BACKUP DATABASE rose TO DISK = @return_value
END
另外,还可以用.net 2005的 sql server project 形式写procedure
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <skywind>
-- Create date: <2006.8.9>
-- Description: <backup a database auto>
-- =============================================
CREATE PROCEDURE [ dbo ] . [ prcd_BackupTmp ]
AS
BEGIN
DECLARE @return_value char ( 17 )
select @return_value = ' D:\ ' + convert ( char ( 10 ), getdate (), 120 ) + ' .BAK '
BACKUP DATABASE rose TO DISK = @return_value
END
[Microsoft.SqlServer.Server.SqlProcedure]
public static void BackupAspnet()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
string sSql = "", sEmail = "", sTo = "", sSubject = "", sBody = "", sHtml = "";
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.CommandText = "select convert(char(10), getdate(),120)";
object obj = cmd.ExecuteScalar();
cmd.CommandText = "backup database rose to disk='D:\\wwwroot\\rosedb\\" + obj.ToString() + "rose.bak'";
cmd.ExecuteNonQuery();
conn.Close();
}
}
public static void BackupAspnet()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
string sSql = "", sEmail = "", sTo = "", sSubject = "", sBody = "", sHtml = "";
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.CommandText = "select convert(char(10), getdate(),120)";
object obj = cmd.ExecuteScalar();
cmd.CommandText = "backup database rose to disk='D:\\wwwroot\\rosedb\\" + obj.ToString() + "rose.bak'";
cmd.ExecuteNonQuery();
conn.Close();
}
}
本文转自Jack Niu博客园博客,原文链接:http://www.cnblogs.com/skywind/archive/2006/08/09/471939.html,如需转载请自行联系原作者