Database Naming and System changes help...
-
Ok,
I am running SQL Server 2000 Ent SP3a.
It is running all of our Peoplesoft environment without glitch. I'll have everyone note that I moved us from Oracle 9i r2 for both the HRMS and Finance systems (versions 8.8) and we are running faster and with smaller databases as well (you go SQL Server).
Anyway during the upgrade I had to keep dropping and renaming databases per Peoplesoft instructions. When I was all finished I had the following...
Started out with a database name of :
FSPRDCPY - The log files and MDF files matched this.
I then had to offline this, copy the entire structure for backup and then when we went live I created a new database called FSPRD. I then simply took the Log and MDF files from FSPRDCPY and renamed it to FSPRD_Data and FSPRD_Log respectively (pointing to the correct location of course) and started up.
All went perfect and runs great.
Except!
When I try to truncate logs and shrink DB sizes the system tables still report the old FSPRDCPY name instead of the new FSPRD name. I looked in the system tables and found the entries but am unable to alter them to the new FSPRD structure. Does anyone know of a way to do this?
Thanks,
SS
-
The short answer would have been to use the system procedure sp_renamedb 'oldname', 'newname' in the first place to do this.
Maybe it'll still work in this case but I doubt it. Otherwise I don't know any other way to do this (besides unlocking the systems tables which is not recommended
).sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
--make the changes MANUALLY so you don't get another one of these : The DBA Whoops
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
-
I'm sorry to say that you made a mistake. You should have rename db files and attach as FSPRD and everything would be ok. Now you have got sysfiles and master..sysaltfiles out of synch which is bad. You can rectify sysfiles entries in this way
ALTER DATABASE FSPRD
modify file (name=FSPRD,
newname=FSPRD)
go
ALTER DATABASE FSPRD
modify file (name=FSPRD_log,
newname=FSPRD_log)
go
This assumes that logical db filenames are FSPRD and FSPRD_log (change as needed). Test this before doing it in production. Of course there's no guarantee that there are no other problems that will make themselves felt in the future because of this nonstandard operation.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply