如何完整的备份一个SQL SERVER服务器中的数据库

[项目管理PMO]

我想把一个SQL SERVER服务器中的数据库中的所有信息包括表、创建用户、各SQL SERVER用户对数据库的存取权限等等内容完整备份下来，同时在我重新安装SQL SERVER后可以恢复到新的服务器中？

[项目管理PMO]

谢谢各位的大力支持，我先试试，完成后一定加分。

[txlicenhe]

用数据库维护计划可以选择备份哪些数据库，全向导操作，一试就会了。

[LoveSQL]

sql200企业管理器
--右键任意数据库
--备份数据库
然后就可以按照提示来做了。。。。。。。。。

[aiailove]

和我遇到的问题一样，帮你UP，

[shuiniu]

--邹建的
/*--备份所有数据库

备份的文件名为数据库名+.bak

--邹建 2003.10--*/

/*--调用示例

--备份所有用户数据库
exec p_backupdb @bkpath='c:\',@dbname=''

--备份指定数据库
exec p_backupdb @bkpath='c:\',@dbname='客户资料,xzkh_new'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_backupdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_backupdb]
GO

create proc p_backupdb
@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@dbname nvarchar(4000)='' --要备份的数据库名称列表,不指定则备份所有用户数据库
as
declare @sql varchar(8000)

--检查参数
if isnull(@bkpath,'')=''
begin
select @bkpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
select @bkpath=substring(@bkpath,charindex('\',@bkpath)+1,4000)
,@bkpath=reverse(substring(@bkpath,charindex('\',@bkpath),4000))+'BACKUP\'
end
else if right(@bkpath,1)<>'\' set @bkpath=@bkpath+'\'

--得到要备份的数据库列表
if isnull(@dbname,'')=''
declare tb cursor local for
select name from master..sysdatabases where name not in('master','tempdb','model','msdb')
else
declare tb cursor local for
select name from master..sysdatabases
where name not in('master','tempdb','model','msdb')
and(@dbname like '%,'+name+',%' or @dbname like name+',%' or @dbname like '%,'+name)

--备份处理
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
set @sql='backup database '+@dbname
+' to disk='''+@bkpath+@dbname
+'.bak'' with init'
exec(@sql)
fetch next from tb into @dbname
end
close tb
deallocate tb
go

[pengdali]

拷贝你的data目录下的数据库文件，步骤：

停止sql服务或脱机-->拷贝你的data目录下的数据库文件

粘贴你的数据库文件到目标机上，步骤：

右键数据库-->所有任务-->附加数据库

[pengdali]

--备份
backup database sys to disk='c:\目录\a.bak' with init


--还原
USE master
restore database 数据名 from disk='c:\test.bak' with REPLACE

[magnetmoon]

用 shuiniu(飞扬的梦) 的

pengdali(大力 V3.0)的不能備份以下內容：
创建用户、各SQL SERVER用户对数据库的存取权限等等

[zjcxc]

--备份用户信息

--生成安全性中的用户
sql200企业管理器
--右键任意数据库
--所有任务
--生成SQL脚本
--<选项>中,"安全性选项",选择"包含创建数据库及用户权限","编写 SQL Server 登录（Windows NT 和 SQL Server 登录）脚本"
--其他所有的选项保持默认值
--然后确定,将其保存成一个.sql文件

[chao778899]

直接数据库－》右键－》备份数据库－－－完全备份

[zjcxc]

/*--备份所有数据库

备份的文件名为数据库名+.bak

--邹建 2003.10--*/

/*--调用示例

--备份所有用户数据库
exec p_backupdb @bkpath='c:\',@dbname=''

--备份指定数据库
exec p_backupdb @bkpath='c:\',@dbname='客户资料,xzkh_new'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_backupdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_backupdb]
GO

create proc p_backupdb
@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@dbname nvarchar(4000)='' --要备份的数据库名称列表,不指定则备份所有用户数据库
as
declare @sql varchar(8000)

--检查参数
if isnull(@bkpath,'')=''
begin
select @bkpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
select @bkpath=substring(@bkpath,charindex('\',@bkpath)+1,4000)
,@bkpath=reverse(substring(@bkpath,charindex('\',@bkpath),4000))+'BACKUP\'
end
else if right(@bkpath,1)<>'\' set @bkpath=@bkpath+'\'

--得到要备份的数据库列表
if isnull(@dbname,'')=''
declare tb cursor local for
select name from master..sysdatabases where name not in('master','tempdb','model','msdb')
else
declare tb cursor local for
select name from master..sysdatabases
where name not in('master','tempdb','model','msdb')
and(@dbname like '%,'+name+',%' or @dbname like name+',%' or @dbname like '%,'+name)

--备份处理
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
set @sql='backup database '+@dbname
+' to disk='''+@bkpath+@dbname
+'.bak'' with init'
exec(@sql)
fetch next from tb into @dbname
end
close tb
deallocate tb
go

[zjcxc]

--恢复--恢复用户--查询分析器中执行上面生成的用户脚本.

--恢复数据库,执行下面的存储过程:
/*--恢复指定目录下的所有数据库

恢复的数据库名为备份文件名(不含扩展名)
备份文件的扩展名固定为.bak

--邹建 2003.10--*/

/*--调用示例
--恢复指定目录下的所有数据库
exec p_RestoreDb @bkpath='c:\'

--恢复指定目录下的指定数据库
exec p_RestoreDb @bkpath='c:\',@bkfile='客户资料,xzkh_new'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_RestoreDb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_RestoreDb]
GO

create proc p_RestoreDb
@bkpath nvarchar(1000)='', --定义备份文件的存放目录,默认为SQL的备份目录
@bkfile nvarchar(4000)='', --定义要恢复的备份文件名,不含扩展名
@dbpath nvarchar(260)='', --恢复后的数据库存放目录,不指定则为SQL的默认数据目录
@overexist bit=1, --是否覆盖已经存在的数据库,仅@retype为'DB'/'DBNOR'是有效
@killuser bit=1 --是否关闭用户使用进程,仅@overexist=1时有效
as
declare @sql varchar(8000),@dbname sysname

if isnull(@bkpath,'')=''
begin
select @bkpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
select @bkpath=substring(@bkpath,charindex('\',@bkpath)+1,4000)
,@bkpath=reverse(substring(@bkpath,charindex('\',@bkpath),4000))+'BACKUP\'
end
else if right(@bkpath,1)<>'\' set @bkpath=@bkpath+'\'

--得到恢复后的数据库存放目录
if isnull(@dbpath,'')=''
begin
select @dbpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
select @dbpath=reverse(substring(@dbpath,charindex('\',@dbpath),4000))
end
else if right(@dbpath,1)<>'\' set @dbpath=@dbpath+'\'

--得到指定目录下的所有备份文件
create table #t(fname varchar(260),depth int,isf bit)
insert into #t exec master..xp_dirtree @bkpath,1,1

if isnull(@bkfile,'')=''
declare tb cursor local for select fn=left(fname,patindex('%.bak',fname)-1) from #t
where isf=1 and fname like '%.bak' --取.bak文件
else
begin
set @bkfile=','+replace(@bkfile,',','.bak,')+'.bak,'
declare tb cursor local for select fn=left(fname,patindex('%.bak',fname)-1) from #t
where isf=1 and fname like '%.bak' and @bkfile like '%,'+fname+',%'
end

--恢复数据库处理
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
--生成数据库恢复语句
set @sql='restore database '+@dbname
+' from disk='''+@bkpath+@dbname+'.bak'''
+' with RECOVERY'
+case when @overexist=1 then ',replace' else '' end

--添加移动逻辑文件的处理
--从备份文件中获取逻辑文件名
declare @lfn nvarchar(128),@tp char(1),@i int

--创建临时表,保存获取的信息
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
--从备份文件中获取信息
insert into #tb exec('restore filelistonly from disk='''+@bkpath+@dbname+'.bak''')
declare #f cursor local for select ln,tp from #tb order by tp
open #f
fetch next from #f into @lfn,@tp
set @i=0
while @@fetch_status=0
begin
select @sql=@sql+',move '''+@lfn+''' to '''+@dbpath+@dbname+cast(@i as varchar)
+case @tp when 'D' then '.mdf''' else '.ldf''' end
,@i=@i+1
fetch next from #f into @lfn,@tp
end
close #f
deallocate #f
drop table #tb

--关闭用户进程处理
if @overexist=1 and @killuser=1
begin
declare @spid varchar(20)
declare #spid cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #spid
fetch next from #spid into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #spid into @spid
end
close #spid
deallocate #spid
end

--恢复数据库
exec(@sql)
fetch next from tb into @dbname
end
close tb
deallocate tb
go