更新时间:2022-11-24 22:49:53
如果您的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