小白求助 SQL serve 一个问题
本帖最后由 阿呆哥 于 2020-10-30 00:18 编辑SQL server 2008 怎么做批量备份数据库
本机上有60多个数据库 一个一个去备份 太麻烦了
有没有办法批量备份
格式：数据库名.bak全部备份到D盘bak文件夹
在线等十万火急
写个脚本，循环所有数据库，备份就好了啊
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 READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')-- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
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 再来个例子
DECLARE
@database NVARCHAR(100),
@backupoptions NVARCHAR(MAX),
@dynamic_sql NVARCHAR(MAX),
@backuppath NVARCHAR(MAX)
DECLARE backup_cursor CURSOR FAST_FORWARD FOR
SELECT name
FROM sys.databases
where database_id>4
AND state_desc='ONLINE'
SET @backupoptions = ' WITH STATS=10,INIT '
SET @backuppath='E:\Ahmad\'
OPEN backup_cursor
FETCH NEXT FROM backup_cursor INTO @database
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @fullbackuppath nvarchar(max)=''
SET @fullbackuppath= @backuppath + @database + '_' + REPLACE(convert(varchar(12),getdate(),110),'-','_') + '.bak'
SET @dynamic_sql=' BACKUP database ' + @database + ' TO DISK= ''' + @fullbackuppath + '' + '''' + @backupoptions + ''
EXEC(@dynamic_sql)
FETCH NEXT FROM backup_cursor INTO @database
END
CLOSE backup_cursor
DEALLOCATE backup_cursor 大佬很强，虽然刚学看不懂 https://www.laoliang.net/cwrj/youyou/5499.html我这里有个用友的批量备份，你可以瞅瞅~~ 加我QQ：2781890985 好多大神，{:1_921:}{:1_921:}{:1_921:} 装了SSMS，直接建一个备份的维护计划就行了，免敲代码。 8楼说的对，还能设置周期定期执行 新建维护计划，选数据库，选路径，保存，执行，不用关心脚本
页:
[1]
2