高分求教sql server 2005 数据备份与还原,在线等,第一个正确答案给全分!!!!
sql server 2005 做一个数据备份和还原的功能.
连接数据库采用delphi ado控件
1.备份操作如下
loc_str_sql :='BACKUP DATABASE [fszjzdbs] TO DISK = N'''+loc_str_Rec1+''' WITH NOFORMAT, NOINIT, NAME = N''TrialBK-完整 数据库 备份'', SKIP, NOREWIND, NOUNLOAD, STATS = 10';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
这部份应该没啥问题.
2.还源操作如下
loc_str_sql :='USE [master] ALTER DATABASE [fszjzdbs] SET SINGLE_USER WITH ROLLBACK IMMEDIATE';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='use master RESTORE DATABASE [fszjzdbs] FROM DISK = N'''+loc_str_rec+''' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='USE [fszjzdbs] ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [guest]';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='USE [fszjzdbs] DROP USER [sysdba]';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='USE [fszjzdbs] CREATE USER [sysdba] FOR LOGIN [sysdba]';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='USE [fszjzdbs] ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [sysdba]';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='USE [fszjzdbs] EXEC sp_addrolemember N''db_owner'', N''sysdba''';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='USE [fszjzdbs] ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [guest]';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='USE [fszjzdbs] DROP USER [sysdba]';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='USE [fszjzdbs] CREATE USER [sysdba] FOR LOGIN [sysdba]';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='USE [fszjzdbs] ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [sysdba]';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
loc_str_sql :='USE [fszjzdbs] EXEC sp_addrolemember N''db_owner'', N''sysdba''';
DM.Query1.Close;
DM.Query1.SQL.Clear;
DM.Query1.SQL.Add(loc_str_sql);
DM.Query1.ExecSQL;
问题:
1.还原操作时,出现SCHEMA语法错误.
2.奇怪的是,我在sql server 2005的查询里执行,,sql 如下,没有报错
USE [master]
go
ALTER DATABASE [fszjzdbs] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
use [master]
go
RESTORE DATABASE [fszjzdbs] FROM DISK = N'e:\yyyyy\asd.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
go
USE [fszjzdbs]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [guest]
GO
USE [fszjzdbs]
GO
/****** 对象: User [sysdba] 脚本日期: 09/02/2007 09:24:30 ******/
DROP USER [sysdba]
GO
USE [fszjzdbs]
GO
CREATE USER [sysdba] FOR LOGIN [sysdba]
GO
USE [fszjzdbs]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [sysdba]
GO
USE [fszjzdbs]
GO
EXEC sp_addrolemember N'db_owner', N'sysdba'
GO
USE [fszjzdbs]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [guest]
GO
USE [fszjzdbs]
GO
/****** 对象: User [sysdba] 脚本日期: 09/02/2007 09:24:30 ******/
DROP USER [sysdba]
GO
USE [fszjzdbs]
GO
CREATE USER [sysdba] FOR LOGIN [sysdba]
GO
USE [fszjzdbs]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [sysdba]
GO
USE [fszjzdbs]
GO
EXEC sp_addrolemember N'db_owner', N'sysdba'
GO
兄弟弄了好半天,也没搞明白,我对sql server 2005是一知半解,请高手赐教,感谢!!!!急,我ado连接,用的是sa用户
,程序里面的sysdba,我建的用户