I just got a backup set which I need to install, I did as following
Create database FFTS on FFTSData=10000 Log on FFTSLog for load
It worked, then when I tried to make database online
Online database FFTS, it prompt me below msgs
Database 'FFTS' cannot be opened because either an earlier system termination left LOAD DATABASE incomplete or the database is created with 'for load' option. Load the database or contact a user with System Administrator (SA) role.
and also I need to know is the size in the create db statement refer to 2k pages? how do I have accurate size? I mean to calculate it to MB from 2k pages?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Yes.
There are only two statements allowed after "create database for load" - "alter database" and "load database".
"Create database for load" says "hi, I am creating a database that I am going to load from a backup. So there is no point writing out all the zeros for this empty database since I'm just going to overwrite every page when I do the restore, right? So don't bother with all that, just setup a placeholder database of the right size and shape and we'll fill in the gaps when we do the load."
When you then try to online, ASE quite reasonably says "... online what? There isn't a database there! I see nothing! Where are all my pages - oh, right, you told me during the create that I didn't have to write them!"
:)
Hi,
Ok I have successfully restored it, but I doubt why there are many device appears as below when I ran sp_helpdb, as you can see there are 3 FFTSData whereas only one created by me on this ASE server, as you can see during the initial creation of db I only specify 25M, so how it can go larger as 100M ?
1> sp_helpdb FFTS
2> go
name db_size owner dbid created status
---- ------------- ----- ---- ------------ --------------
FFTS 200.0 MB sa 4 Sep 15, 2009 no options set
(1 row affected)
device_fragments size usage created free kbytes
--------------------------
FFTSData 25.0 MB data only Sep 15 2009 5:51AM 18140
FFTSData 25.0 MB log only Sep 15 2009 5:51AM not applicable
FFTSData 50.0 MB data only Sep 15 2009 5:51AM 51000
FFTSLog 100.0 MB data only Sep 15 2009 5:51AM 102000
--------------------------
log only free kbytes = 25476
device segment
-------- ----------
FFTSData default
FFTSData logsegment
FFTSData system
FFTSLog default
FFTSLog system
(return status = 0)
Something isn't right. If the backup is of a database bigger than what you're trying to restore into, the restore fails with an error.
Show us the exact SQL you used to create and load the database?
Also, you want the new database to match the old as closely as possible else things can get messed up with segments and fragments.
You should have created new database according to segment map of existing database. Create database command should have been:
Create database FFTS on FFTSData='25M' Log on FFTSLog='25M' for load
go
alter database FFTS on FFTSData='150M' for load
go
Instead you have created by using below:
Create database FFTS on FFTSData='100M' Log on FFTSLog='100M' for load
go
The only way to fix is to drop the database and create it by using the first set of commands above.
Something doesn't add up. Databases do not self-grow unless specifically set up that way. The database you are showing us could not have loaded that database.
I think we need an actual cut and paste of your exact commands and the exact messages.
Either I'm seriously misunderstanding you or something is incorrect in what you're showing us.
Hi,
drop and recreated db as following statement: As u see when I ran sp_helpdb log became 35MB and Data somehow 25MB which does not follow my create statement
1> create database FFTS on FFTSData='30M' log on FFTSLog='30M' for load
1> load database FFTS from 'c:\sybase.bak'
1> sp_helpdb FFTS
2> go
name db_size owner dbid created status
---- ------------- ----- ---- ------------ --------------
FFTS 60.0 MB sa 4 Sep 15, 2009 no options set
(1 row affected)
device_fragments size usage created free kbytes
--------------------------
FFTSData 25.0 MB data only Sep 15 2009 4:39PM 18108
FFTSData 5.0 MB log only Sep 15 2009 4:39PM not applicable
FFTSLog 30.0 MB log only Sep 15 2009 4:39PM not applicable
--------------------------
log only free kbytes = 35676
(return status = 0)
That's a very different situation - now your database us no longer growing by magic. :)
This is now the situation we've told you about in a couple of replies. You have not created your database the same as the one you are restoring from.
Do you have access to the system the backup was taken from? It's easiest if you recreate identical to the source database.
This is entirely correct and expected. You are seeing the results of trying to load a database built one way into a database built differently. A database backup must load pages in the order contained in the dump image. That means if the next page to load is a data page, but your database was created such that the next available page is a log page, it has no choice but to write a data page onto a log segment, thus mixing data and log.
This is exactly the result you're getting. Very common when loading a backup into a database created in an incompatible way.
That's not the only way, but it's the easiest.
If you have access to the server you took the backup from, run this there:
select case when segmap & 3 = 3 then "data" when segmap & 4 = 4 then "log" else "other" end as "segment"
, size/512 as "size in Mb"
from sysusages
where dbid = db_id("[your database name here]")
order by lstart
go
That will tell you how to recreate your destination database. It will give you something like this:
data 25
log 5
data 25
(not those numbers, I'm just giving you an example).
Once you have that information you can then write a "create database" statement (possibly also with one or more "alter database" statements, we'll know once we see the output) which will allow the load to work perfectly.
If you don't have access to the source system, there are other ways of finding this information out but I'll hold off until I know if we need it as this answer is already getting complicated.
Business Accounts
Answer for Membership
by: Joe_WoodhousePosted on 2009-09-14 at 09:21:33ID: 25326984
Wait, I'm confused. Did you run
create database ... for load
go
load database ...
go
online database ...
go
or
create database ... for load
go
online database ...
go
?
The second won't work.
For your second question you can specify the units:
create database FFTS on FFTSData="10M"
Remember that despite what hard drive vendors want us to believe, there are 1024Kb in a Mb, and 1024Mb in a Gb, etc.
To convert a size in Mb to 2Kb pages, multiple by 512.
So 100Mb = 51200 pages.
Easier just specify the units I think. :)