Tag Archives: database

sql server 2005 express file database readonly

当开发环境里的sql server 2005 express数据库文件copy到发行环境里,往往面临权限更改的问题。
比如开发环境里是 nt authority/xxx windows集成帐号,但发行环境里不可能保证这个帐号和密码与开发环境一致。一个较好的方法是将之设置为 sql server帐号,比如使用sa和相关的密码(更安全自然是使用专门的帐号,而不用sa)
但即便如此,copy到发行环境里,还会出现数据库文件只读的问题。这一般是由于在sql server 05 express使用的network service权限不足够更改数据库文件。一个方便的解决方法是将数据库文件所在的目录权限增加network service,使之安全控制(可能修改和写入也足够)。重启sql server(配置管理器里)。到这一步按我的实验仍是只读。打开management studio express,先将该只读的数据库文件detach掉(好像会提示某个信息说detach失败,没关系),关闭对象链接器然后再重新打开,这时表明已经成功detach掉了,重新attach进来,发现已经可用。
权限的问题有时候整起来十分麻烦,这问题足足花了我三、四个小时。

转移sql server 2k数据库

在转移sql server数据库时,往往使用attach/detach来实现
在记得数据库里用户名/密码的情况下,这是种很简易的方法。
首先将数据文件和日志文件attach到新sql server上
会发现原库里的用户的登录为空,即形成所谓的孤立用户。
在登录里建立原有用户名和密码的登录
通过以下语句将Heihe里的名为Heihe的孤立用户连接到新建的Heihe登录上。
use Heihe
EXEC sp_change_users_login ‘Update_One’, ‘Heihe’, ‘Heihe’
多个用户执行多次即可。

发现log事务日志文件很大,数据文件2G左右,日志超过15G,决定压缩。
使用以下命令:

–截断事务
BACKUP LOG with no_log
use Heihe
DBCC SHRINKFILE (heihe_log)
将之收缩到默认大小

恢复sql server 2k 数据

1. 新安装sql server 2k+sp4,建named instance,注意位置与被恢复前的位置一致,同样的盘符
2. 打开企业管理器,新建sde, westdc, dnnWestdc三个新库,关闭企业管理器
3. 打开sql 查询分析器,执行
restore database westdc from disk = ‘E:SQL_BACKUPwestdcwestdc_db_200609140200.BAK’

3.1 注意 sde生成时的命名方式是 sde_dat (instead of sde_Data generated by sql svr 2k by default) -> sde.mdf (instead of sde_Data.mdf by default), and sde_Log (same as default) -> sdelog.ldf (instead of sde_Log.ldf by default),所以需要在生成sde时注意修改对应的名字
4. 恢复 model, msdb, pubs三个数据库
restore database model from disk = ‘E:SQL_BACKUPmodelmodel_db_200609210200.BAK’
restore database msdb from disk = ‘e:sql_backupmsdbmsdb_db_200609210200.BAK’
restore database pubs from disk = ‘e:sql_backuppubspubs_db_200609210200.BAK’

5. 恢复master,必须在单用户模式下进行
5.1 停止现有的sql server 服务
5.2 open command window, come to the named instance directory, for example, E:WESTDC-sqldbMSSQL$WESTDCBinn
5.2. run sqlservr -c -m -s WESTDC. WESTDC is the name of instance going to run
5.3 打开sql查询分析器,运行 restore database master from disk = ‘e:sql_backupmastermaster_db_200609210200.BAK’
结果提示:
已成功地还原了 master 数据库。正在关闭 SQL Server。
SQL Server 正在终止此进程。
DONE!

注,如果位置不一致,可以通过detach和attach函数移动数据库(包括用户数据库和pubs, model, msdb, tempdb, master)到新位置,具体操作见微软支持:http://support.microsoft.com/kb/224071/

Oracle的自动备份

将以下内容保存为 OracleExportAutomata.bat,运行即可。可以与Windows计划任务联合使用,比如每天3:00am运行一次。

@echo off
echo ———–
echo name: Oracle data export automata utility
echo author: WANG Liangxu
echo date: 2006-8-7
echo refined by NAN Zhuotong
echo documented by NAN Zhuotong
echo Any problem please contact us via [email protected]; [email protected]

REM This program is used to export data in Oracle to external files named with current date.
REM A simple export schema is employed. Monday the data will be completely exported.
REM Tuesday through Thursday data will be exported in an incremental manner.
REM Friday, data exported include all the incremental data since the last completed export.
REM Saturday and Sunday incremental export will be employed once more.

REM This program can be used together with Windows plan and task utility. In that way,
REM the program can be enhanced with running by schedule.

echo ———–
set w=%date:~13,1%
set d=%date:~0,4%%date:~5,2%%date:~8,2%
rem echo %d%
if %w%==一 goto 1
if %w%==二 goto 2
if %w%==三 goto 3
if %w%==四 goto 4
if %w%==五 goto 5
if %w%==六 goto 6
if %w%==日 goto 7

REM exp system/systemwestdc inctype=complete file=d:/ORADB-BACKUP/20060807.dmp (complete)
REM exp system/systemwestdc inctype=incremental file=d:/ORADB-BACKUP/2006xxxx.dmp
REM exp system/systemwestdc inctype=cumulative file=d:/ORADB-BACKUP/2006xxxx.dmp
REM Mon: 完全备份(A)
REM Tue: 增量导出(B)
REM Wed: 增量导出(C)
REM Thu: 增量导出(D)
REM Fri: 累计导出(E)
REM Sat: 增量导出(F)
REM Sun: 增量导出(G)

:1
 echo Begin exporting data to file %d%-compl.dmp
 exp system/systemwestdc inctype=complete file=d:/ORADB-BACKUP/%d%-compl.dmp
 goto end
:2
 echo Begin exporting data to file %d%-incre.dmp
 exp system/systemwestdc inctype=incremental file=d:/ORADB-BACKUP/%d%-incre.dmp
 goto end
:3
 echo Begin exporting data to file %d%-incre.dmp
 exp system/systemwestdc inctype=incremental file=d:/ORADB-BACKUP/%d%-incre.dmp
 goto end
:4
 echo Begin exporting data to file %d%-incre.dmp
 exp system/systemwestdc inctype=incremental file=d:/ORADB-BACKUP/%d%-incre.dmp
 goto end
:5
 echo Begin exporting data to file %d%-cumul.dmp
 exp system/systemwestdc inctype=cumulative file=d:/ORADB-BACKUP/%d%-cumul.dmp
 goto end
:6
 echo Begin exporting data to file %d%-incre.dmp
 exp system/systemwestdc inctype=incremental file=d:/ORADB-BACKUP/%d%-incre.dmp
 goto end
:7
 echo Begin exporting data to file %d%-incre.dmp
 exp system/systemwestdc inctype=incremental file=d:/ORADB-BACKUP/%d%-incre.dmp
 goto end
:end

SQL server master库损坏的解决方案

SQL server 2k master db corruption due to power cutoff, causing sql server cannot start up. In this server, ArcSDE 9 and ArcIMS 9 are associated with Sql Server 2k sp4. and worse, no backup have been created prior to this disaster.
solution:
1. Backup possibly effected dbs, i.e., sde db, and westdc db.
2. run rebuildm.exe to reconstruct the master db. all logins are lost after that operation. sde amd westdc dbs cannot be found in the rebuilt master db.
3. attach the original sde db and westdc db using sa as the db owner.
4. create lost logins, for example, westdc, sde, etc. Fortunately, I have backed up all these information.
5. using enterprise manager, open sde and westdc dbs, isolated users can be found under the users leaf. So we have to assign the appropriate logins to those isolated users. use commands as follow:
USE sde;
GO
sp_change_users_login @Action=’Report’;
GO
USE westdc;
GO
sp_change_users_login @Action=’update_one’, @UserNamePattern=’westdc’, @LoginName=’westdc’;
GO
the first set of commands is used to report the isolated users in database sde. the second set of commands will assign an existing login named westdc to the isolated user westdc.
6. restart the arcsde service, then arcims services. DONE!
Lessons:
We always are required to backup databases in a regular interval.

试着将 access的论坛升迁到 sql server

折腾了不小时间,终于是可以了。但才发现自己对sql server了解的东西太少了。比如存储过程,还要加强学习。
前面的取首字母的拼音一帖只适合于 access,不能用在sql server上。
但目前不打算真正将 论坛从access上转过来,因为性能区别不是很明显。只是以后在再开发论坛的时候,要注意写的代码要同时兼容sql server。

QODBC/Qt4

调试了很长时间,总是提示说“数据源名称过长”,后来发现问题出在将connStr里的
DRIVER写成Driver了
Qt的ODBC好象对大小写敏感,所以一定要根据DSN里的原样copy过来。

bool UserSoils::connectDb()
{

//create default conn using assigned dbPath
QString connStr=QString(
"DRIVER={Microsoft dBASE Driver (*.dbf)};FIL={dBase 5.0};DBQ=%1")

.arg(dbPath());

//for access
//QString connStr=QString(
// "DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=c:xxxx.mdb");
qDebug()<<"UserSoils::connectDb(), connStr ="<< connStr;
QSqlDatabase db=QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName(connStr);
return db.open();
//qDebug()<<"UserSoils::connectDb(),"<<db.lastError().databaseText();

}

Add ODBC support using Astrum InstallWizard

By default, Astrum Installwizard does not support ODBC. You can do it by
adding some variables. Here is an example as following:

1. Advanced/Variables, add a variable named <odbcUserDsnDir>, with a Text
type, Get variable from Registry, Default value "C:Program FilesCommon
FilesODBCData Sources", Root key: HKLM, Path:
HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIODBC File DSN, and Value name:
DefaultDSNDir. Thereafter, you can use <odbcUserDsnDir> to get file DSN’s
location.

2. Files to install, add the target dsn file, such as notesphr.dsn in my
example, specifying the target directory as <odbcUserDsnDir>. It will make
the dsn file copy from the installation packaget to the dsn default
directory. This is my dsn file’s content:

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=C:Program FilesPhraseMemodatabase
DBQ=C:Program FilesPhraseMemodatabasenotesphr.db

3. In the notesphr.dsn, the last two lines should be changed in reponse of
the installation directory. We can do this with the help of System
changes/Text files. Click Add…, select Replace text action, specify the
filename, which is, in this example, <odbcUserDsnDir>notesphr.dsn, Search
text "DefaultDir=C:Program FilesPhraseMemodatabase" (quote marks
excluded) by replaced with "DefaultDir=<InstallDir>database" (quote marks
excluded), click ok to dismiss the dialog. Same as just now what we did,
replace "DBQ=C:Program FilesPhraseMemodatabasenotesphr.db" with
"DBQ=<InstallDir>databasenotesphr.db"

4. done. The installation will automatically copy your dsn file to the
target computer’s appropriate directory according to the computer’s
settings.

-by tong#cngis.org

arcsde for sql svr 2k

安装完arcsde 9 for sql server 2000,发现启动不了iomgr服务。
查看sde etc目录下的log,发现sde用户不能登录进sql server 2000。
查google,发现是由于在安装sql server时,设定成windows用户验证的登录方式。用
企业管理器,将仅windows验证,改成sql server和windows验证选项。再启动arcsde,
成功!

安装arsde for oracle失败

arcsde for oracle 9i要求的最低oracle 9i版本是9.2.0.3,不知道小冉最早安装的时
候,如何将9.0.0.1版本装上了,居然也可以。不过我还得重新从oracle网站上下载
9.2.0.3。好象9.2.0.4只有linux版本。
从9.2.0.1升级到9.2.0.3需要OUI 2.2.0.18,不能从metalink.oracle.com获取,失败
是否要考虑用sql server 2000