sql server数据库备份自动时间 [英] sql server database backup time to time automatic

问题描述

大家好，

这是Hem Raj Thakur，我想不时地自动创建数据库备份。

如何解决这个问题。请帮我。这是紧急的。



提前致谢。

Hem Raj Thakur

hi to all,
this is Hem Raj Thakur, and I want to create a database backup automatically time to time.
How can I solve this problems. please help me. It's Urgent.

Thanks in advance.
Hem Raj Thakur

推荐答案

如果您的SQL Server版本不是快速版本，那么您可以使用SQL Server Management Studio的维护计划功能，该功能允许您使用图形UI配置和计划数据库备份。

设置维护计划以备份数据库 [ ^ ]



如果你有一个快速版本，那么你将无法访问SQL Server代理任务调度，并将不得不使用解决方案2中提供的脚本;这可以使用Windows Scheduler和 osql Utility [ ^ ]。

If your SQL Server version is not an express one, as it seems, then you can use the Maintenance Plan feature of SQL Server Management Studio, that will allow you to configure and schedule your databases backups with a graphical UI.
Setting up a Maintenance Plan to Backup Databases[^]

If you have an express version, then you will not have access to SQL Server agent tasks scheduling, and will have to use the script provided in solution 2 ; this can be scheduled with the Windows Scheduler and osql Utility[^].

1.创建以下脚本作为过程对mssql中可用的所有db进行备份，除了系统db的

1.Create the below scrip as procedure to take the backup of all the db which is available in mssql ,except system db's

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

2.在sql代理中按照规定的时间安排此过程。

或在任务计划程序中安排它如果您使用的是Express Edition

更多请参阅此

自动备份SQL Server 2008 Express数据库

你可以创建每日备份工作，请参考以下网址：



使用SQL Server Management Studio创建数据库备份作业

you can create daily backup job, please refer below URL:

Create a database backup job using SQL Server Management Studio

这篇关于sql server数据库备份自动时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！