Sql server 2005 自动备份数据库

在SQL Server 2005数据库中实现自动备份的具体步骤:

1、打开SQL Server Management Studio

2、启动SQL Server代理

3、点击作业->新建作业

4、"常规"中输入作业的名称

5、新建步骤，类型选T-SQL，在下面的命令中输入下面语句

DECLARE @strPath NVARCHAR(200)

set @strPath = convert(NVARCHAR(19),getdate(),120)

set @strPath = REPLACE(@strPath, ':' , '.')

set @strPath = 'D:\bak\' + 'databasename'+@strPath + '.bak' BACKUP DATABASE [databasename] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

 （D:\bak\改为自己的备份路径，databasename修改为想备份的数据库的名称）

上面是完全备份，如果是差分备份

BACKUP DATABASE [databasename] TO DISK = @strPath WITH DIFFERENTIAL ,NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

 

用差分备份还原时有可能出错，错误信息： Restore of SQL 2005 database fails with the error message "The tail of the log for the database "TEST1" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log." 解决方法: Select the option "Overwrite the existing database" under Restore Job Properties | Settings | Microsoft SQL and run the restore job again.

参考：http://www.cnblogs.com/armyfeng/articles/617244.html  

6、添加计划，设置频率，时间等。 确定，完成。