备份还原sqlserver数据库(使用程序)(50分)

1.如何备份还原sqlserver数据库，我正在做一个C/S结构的MIS,想通过程序控制sqlserver的数据库备份和还原，该如何操作，请高手指导。

备份整个数据库：

BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

备份特定的文件或文件组：

BACKUP DATABASE { database_name | @database_name_var }
< file_or_filegroup > [ ,...n ]
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

还原整个数据库：

RESTORE DATABASE { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

还原数据库的部分内容：

RESTORE DATABASE { database_name | @database_name_var }
< file_or_filegroup > [ ,...n ]
[ FROM < backup_device > [ ,...n ] ]
[ WITH
{ PARTIAL }
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] NORECOVERY ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

用SQLDMO,可以做的很專業

可是我想在我的程序里实现这个功能，应该怎样呢？

千年蟲的方法也是可以的,不過要動態生成sql語句,再用可執行sql語句的元件執行
假如用SQLDMO,你可以調用接口中提供的方法

To kevin8093:
我的那个是我从SqlServer2000的联机帮助上Copy过来的..你可以自己去那里看..搜索"BACKUP";
To shiny:
我的名字是"千年冰虫".不是"千年虫".请大哥不要弄错了.

BTW...
前段时间在网上看到过一个ADOBackup控件...不过不记得在哪里了...它应该可以实现你要的功能..
你自己去找找看吧..

在程序中加一个query控件，里面的sql语句就写：backup database 数据库名 to disk='路径'
还原就写：restore database 数据库名 from disk='路径名'
也可以把这些语句写在一个存储过程里，然后加一个storeproc控件，执行这个存储过程就好了。

备份数据库用boy1979的方法可行。
还原数据库可用下面的存储过程
create procedure cgpos_system_RESTORE
(
@p_path varchar(200),
@p_file varchar(50),
)
as
declare @filepath varchar(250),@netbz char(1),@p_fileLog varchar(200),
@SQLString NVARCHAR(500),@session_id smallint
declare session_cursor cursor for
select a.spid from master.dbo.sysprocesses a,master.dbo.sysdatabases b where (a.dbid=b.dbid) and (b.name='cgpossys')
open session_cursor
fetch next from session_cursor
into @session_id
WHILE @@FETCH_STATUS = 0
begin
SET @SQLString =convert(Nvarchar(500),('kill '+convert(varchar(10),@session_id)))
EXECUTE sp_executesql @SQLString
fetch next from session_cursor
into @session_id
end
close session_cursor
deallocate session_cursor
exec sp_dboption 'cgpossys','single_user','true'
if (select count(*) from sysdevices where name=@p_file) >0
begin
RESTORE DATABASE cgpossys
FROM @p_file
WITH replace
if @@rowcount=0
begin
print '系统错误，恢复系统数据库出错！'
return -200
end
end
return 0
go

同意boy1979
补充一下：
在程序中加一个query控件，里面的sql语句就写：backup database 数据库名 to disk='路径'
[red]再加上参数 WITH INIT
这样的话备份文件的增长速度不会太大[/red]
还原就写：restore database 数据库名 from disk='路径名'
[blue]再加上参数 REPLACE
这样的话恢复相当于 数据库还原选择强制还原[/blue]
也可以把这些语句写在一个存储过程里，然后加一个storeproc控件，执行这个存储过程就好了。

还是不太明白，有没有更简单的办法

可在mssql/data目錄下的.mdf文件備份下來,再通過sp_attach_single_file_db命令去恢復

用企业管理器导出一个备份可以吗？