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]
GOUSE [fszjzdbs]
GO
EXEC sp_addrolemember N'db_owner', N'sysdba'
GOUSE [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,我建的用户
连接数据库采用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]
GOUSE [fszjzdbs]
GO
EXEC sp_addrolemember N'db_owner', N'sysdba'
GOUSE [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,我建的用户
解决方案 »
- 请问delphi 2010的无update1的那个安装程序在那儿下载?
- 刚毕业的新人求助!一个很菜的问题!!
- Excel被意外关闭的问题
- 最近在公司实习,没什么事,就泡CSDN向前辈们学习,又有水分了,散分吧,增强一下节日气氛!!
- 请问在用Edit输入数据的时候,怎样改变光标的宽度和颜色?使它看起来更为明显。
- 请问高手:一个SHOW显示子窗体的问题。
- 如何将封装在DLL中的MDI子窗口的主菜单与MDI父窗口融合?
- delphi6 编译的EXE调用BAT文件报错,求指导!!!!!!
- 窗体背景一问!
- xe7 android程序太大,如何解决?
- 急求:用vb如何查找oracle的时间段
- 动态交叉表的问题,请高手解决
比如,我SQL装在C盘,,而我备份路径选D,E,F.....都会报错,,,,如果我就想在其它盘里备份,应该怎么设置,或怎么写这个备份SQL,,
,如果可以手动改一下,比如E:\yyyy,,erveyone用户改成完全控制,是可以备份的,现在我要想完全用程序实现,应该怎么做,