问题描述
我在服务器中有超过50个数据库
如何从sql server 2008 R2备份所有数据库。
谢谢，
i have more than 50 databases in server
how to backup all the databases form the sql server 2008 R2.
Thanks ,
推荐答案
SELECT name, database_id, create_date
FROM sys.databases ;
GO
这将备份数据库：
This will backup a database:
BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'
我不知道你是否可以使用select进行多次备份，但是如果其他所有方法都失败了，你可以使用CURSOR和'EXEC'来创建和运行支持所有数据库的SQL 。
实际上，简单的方法是运行：
I don't know if you can use a select to do multiple backups, but if all else fails, you could use a CURSOR and 'EXEC' to create and run SQL that backs up all your databases.
Actually, the easy way is to run this:
select 'BACKUP DATABASE ' + NAME + ' TO DISK=''d:\' + NAME + '.bak'''
from sys.databases;
将创建一个包含所需备份命令的表。然后，您可以在Management Studio或proc中逐个或按顺序运行它们。
which will create a table full of the backup commands you need. Then you can just run them, one by one, or in sequence, in Management Studio or in a proc.
declare @db_name as nvarchar(max);
declare @id as int;
declare @end_id as int;
declare @filename as nvarchar(max);
begin
select @id=1
select @end_id= max(database_id) from sys.databases
while(@id < @end_id)
begin
select @db_name=name from sys.databases where database_id=@id
set @filename='D:\BK\'+@db_name+'.bak'
backup database @db_name to disk =@filename;
set @id=@id+1
end
end
这篇关于从sql server 2008 R2备份整个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！