小白求助 SQL serve 一个问题

阿呆哥

SQL server 2008 怎么做批量备份数据库
本机上有60多个数据库 一个一个去备份 太麻烦了  

有没有办法批量备份

格式：数据库名.bak  全部备份到D盘bak文件夹

在线等  十万火急

EnterpriseSolu

写个脚本，循环所有数据库，备份就好了啊
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

EnterpriseSolu

再来个例子
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

莫利花

大佬很强，虽然刚学看不懂

janny82

https://www.laoliang.net/cwrj/youyou/5499.html  我这里有个用友的批量备份，你可以瞅瞅~~

miocaro507

加我QQ：2781890985

onlyw

好多大神，

yingfeng

装了SSMS，直接建一个备份的维护计划就行了，免敲代码。

lorzl

8楼说的对，还能设置周期定期执行

qanholas

新建维护计划，选数据库，选路径，保存，执行，不用关心脚本