Sybase IQ 16 Backup and Restore (interactive sql)

[former_member217141]

Dear IQ Gurus,

We want to backup and restore via Interactive SQL

Database name : iqdemo

Backup directory C:\backup

Could you please help me on syntax ?

Best Regards

[markmumy · Accepted solution]

Here's the manual on the BACKUP DATABASE command:

SyBooks Online

Here's a link with examples:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01759.1604/doc/html/wil13...

Something like this in its simplest form:

BACKUP DATABASE

  TO 'c:\\backup\\iq_backup.full.2015-01-13'

WITH COMMENT 'Jan 13 2015 full backup'

Mark

[SybDBA · Accepted solution]

Hi Mark,

from where can i get IQ_LABS.zip file.....???

---

Pankaj

[markmumy · Accepted solution]

IQ_LABS.zip?  I am confused as to how it applies to backups.  I know of an IQ_LABS zip file that I used in training for partners and internal customers.

Mark

[SybDBA · Accepted solution]

sry, Mark,

Actually i was talking about that IQ_LABS zip file that used for training.

Thanks,

Pankaj

[markmumy · Accepted solution]

This was SAP content that was delivered mostly to internal employees and partners.  For partners, the labs were given out in class.  Did you attend one of the sessions?

Beyond that, I am not aware of any public distribution of the course materials.

Mark

[Former Member · Accepted solution]

Hi Mark

If an IQ database with say 350GB each dbspaces is restored on to a new database with DBSPACES ON raw partitions of size  say 500GB each is there a way to extend the dbspace on the 500GB raw partition to use up the additional space?

-Eric

[markmumy · Accepted solution]

Yes and no...

If the dbfile was initially added with the RESERVE clause, then it can be extended out to the current size PLUS the amount specified in the RESERVE clause.  This clause, during creation, tells IQ to set aside a freelist for the future growth of that dbfile.  If you had IQ create the dbfile with a reserve of 150gb, then the 350gb device could be extended out as far as 500gb, or something less than that.

If you did not create the dbfile with that clause, there is no way to extend the file from 350gb to 500gb.

Unfortunately, there is no way to go back to an existing dbfile and add space to the free list.

The only other method would be to restore the database to smaller devices, add new devices of the larger size, mark the older, smaller devices as read-only, then run sp_iqemptyfile to move the data fro the smaller devices to the larger devices.  Once complete, you can then drop the smaller devices and redeploy them elsewhere.

Mark

[Former Member · Accepted solution]

Thanks for your prompt response.

Please clarify the following:  Let's say we have database A with dbspaces created without reserve (350GB each file)    backed up and restored to database B with dbspaces created without reserve (500GB each file).    From your answer above, which of the dbspaces would have had the dbspaces files created with reserve.  Files on from database (A) or to database (B)?

[markmumy · Accepted solution]

When IQ does a restore, the restored database has the exact same attributes as the source.  Both with have files of 350gb each.  IQ is unlike some other databases in which you create the database then restore your database into that container.  With IQ, your restore process creates the database with the exact same number of devices, each with the same size as their source counterpart.

Mark

[SybDBA · Accepted solution]

Dear Mark,

Please help me out....from here

I am unable to get this.

Regards,

Pankaj

[markmumy · Accepted solution]

Can you send the output of sp_iqfile, sp_iqdbspace, and possibly sp_iqdbspaceinfo?  Maybe include your IQ MSG file, too.

It sounds like your dbspace is either offline or was put into read only mode for some reason.

Mark

[SybDBA · Accepted solution]

sp_iqfile

sp_iqdbspace

sp_iqdbspaceinfo

part of labs.iqmsg

I. 01/22 20:41:10. 0000000015 _EntriesPerLink: 9972

I. 01/22 20:41:10. 0000000015 Exception Thrown from slib\s_blockmap.cxx:5273, Err# 0, tid 183 origtid 183

I. 01/22 20:41:10. 0000000015 _FirstAvailLink: 0

I. 01/22 20:41:10. 0000000015 _FirstAvailLink: 0

I. 01/22 20:41:10. 0000000015    O/S Err#: 0, ErrID: 2105 (s_offlinedbspaceexception); SQLCode: -1009173, SQLState: 'QSB69', Severity: 14

I. 01/22 20:41:10. 0000000015 _MaxLink: 1

I. 01/22 20:41:10. 0000000015 _MaxLink: 1

I. 01/22 20:41:10. 0000000015 [20227]: Operation not permitted on an OFFLINE DBSpace IQ_USER_MAIN.

-- (slib\s_blockmap.cxx 5273)

I. 01/22 20:41:10. 0000000015 _ModNum: 7

I. 01/22 20:41:10. 0000000015 _ModNum: 6

I. 01/22 20:41:10. 0000000015 _RootModNum: 7

I. 01/22 20:41:10. 0000000015 _RootModNum: 6

I. 01/22 20:41:10. 0000000015 [end s_blockmap_identity]

I. 01/22 20:41:10. 0000000015 [end s_blockmap_identity]

I. 01/22 20:41:10. 0000000015 GetBlockmapEntryInfo() Inputs & Outputs:

I. 01/22 20:41:10. 0000000015 GetBlockmapEntryInfo() Inputs & Outputs:

I. 01/22 20:41:10. 0000000015    logicalBlockId:         1

I. 01/22 20:41:10. 0000000015    logicalBlockId:         1

I. 01/22 20:41:10. 0000000015    link:                   0

I. 01/22 20:41:10. 0000000015    link:                   0

I. 01/22 20:41:10. 0000000015    serverID:               0

I. 01/22 20:41:10. 0000000015 Exception Thrown from slib\s_blockmap.cxx:5273, Err# 0, tid 184 origtid 184

I. 01/22 20:41:10. 0000000015    serverID:               0

I. 01/22 20:41:10. 0000000015    O/S Err#: 0, ErrID: 2105 (s_offlinedbspaceexception); SQLCode: -1009173, SQLState: 'QSB69', Severity: 14

I. 01/22 20:41:10. 0000000015 Exception Thrown from slib\s_blockmap.cxx:5273, Err# 0, tid 182 origtid 182

I. 01/22 20:41:10. 0000000015 [20227]: Operation not permitted on an OFFLINE DBSpace IQ_USER_MAIN.

-- (slib\s_blockmap.cxx 5273)

I. 01/22 20:41:10. 0000000015 Exception Thrown from oslib\hos_bio.cxx:694, Err# 6, tid 19 origtid 19

I. 01/22 20:41:10. 0000000015    O/S Err#: 0, ErrID: 2105 (s_offlinedbspaceexception); SQLCode: -1009173, SQLState: 'QSB69', Severity: 14

I. 01/22 20:41:10. 0000000015    O/S Err#: 0, ErrID: 518 (hos_bioexception); SQLCode: -1006062, SQLState: 'QBA62', Severity: 14

I. 01/22 20:41:10. 0000000015 [20227]: Operation not permitted on an OFFLINE DBSpace IQ_USER_MAIN.

-- (slib\s_blockmap.cxx 5273)

I. 01/22 20:41:10. 0000000015 [20300]: OS error 0 reported on file C:\IQ_LABS\IQ_USER_MAIN_3.iq.

-- (oslib\hos_bio.cxx 694)

I. 01/22 20:41:10. 0000000015    Read blk 667 for 16 blk(s) (dbBlkSz 8192)

I. 01/22 20:41:10. 0000000015 There was an s_buf::Read() error during s_bufman::Find().

I. 01/22 20:41:10. 0000000015   The following exception is from GetBlockmapEntryInfo

I. 01/22 20:41:10. 0000000015 Blockmap Member Variables for 000000006B91DD58

I. 01/22 20:41:10. 0000000015 From dblib\db_table.cxx:2757

I. 01/22 20:41:10. 0000000015   _nextPartitionForHelperFlush = 0

I. 01/22 20:41:10. 0000000015                   IsEBufNoWait = 0

I. 01/22 20:41:10. 0000000015                IsProxyFreelist = 0

I. 01/22 20:41:10. 0000000015                 _OrigRootBlock = 3136986

I. 01/22 20:41:10. 0000000015                           _Bio = 0000000020FFC598

I. 01/22 20:41:10. 0000000015                     _tempSpace = 0000000000000000

I. 01/22 20:41:10. 0000000015                        _Bufman = 0000000020E20D78 main Bufman

I. 01/22 20:41:10. 0000000015             _allocFromLastLink = 1

I. 01/22 20:41:10. 0000000015                        _isMeta = 1

I. 01/22 20:41:10. 0000000015                   _m_dbspaceID = 16387

I. 01/22 20:41:10. 0000000015                     _dbspaceID = 16387

I. 01/22 20:41:10. 0000000015                        _pidmap = 0000000000000000

I. 01/22 20:41:10. 0000000015                 _useGlobalLBNs = 0

I. 01/22 20:41:10. 0000000015            _isDistributedOwner = 0

I. 01/22 20:41:10. 0000000015                     _LBNOffset = 1

I. 01/22 20:41:10. 0000000015                       _LBNBase = 1

I. 01/22 20:41:10. 0000000015                 _nextGlobalLBN = 1

I. 01/22 20:41:10. 0000000015                      _RWAccess = 0

I. 01/22 20:41:10. 0000000015                   _gotIdentity = 0

I. 01/22 20:41:10. 0000000015      _gotIdentityWithNoEntries = 0

I. 01/22 20:41:10. 0000000015           _allocationPrivilege = 0

I. 01/22 20:41:10. 0000000015                      _pageInfo = 000000006B8E78F8

I. 01/22 20:41:10. 0000000015                  _perConnStats = 0000000020E2A200

I. 01/22 20:41:10. 0000000015                    _tmp_pidmap = 0000000000000000

I. 01/22 20:41:10. 0000000015               _nPhysicalWrites = 0

I. 01/22 20:41:10. 0000000015                _nPhysicalReads = 0

I. 01/22 20:41:10. 0000000015                  _LockRefCount = 1

I. 01/22 20:41:10. 0000000015              _PrefetchRefCount = 0

I. 01/22 20:41:10. 0000000015              _ScanModeRefCount = 0

I. 01/22 20:41:10. 0000000015                     _DirtyList = 000000006B8E7878

I. 01/22 20:41:10. 0000000015               #DirtyReferences = 0

I. 01/22 20:41:10. 0000000015                    #DirtyLinks = 0

I. 01/22 20:41:10. 0000000015                         _pnext = 0000000000000000

I. 01/22 20:41:10. 0000000015                         _pprev = 0000000000000000

I. 01/22 20:41:10. 0000000015                     _cacheSize = 0

I. 01/22 20:41:10. 0000000015                         _cache = 0000000000000000

I. 01/22 20:41:10. 0000000015                         _nBlks = 0

I. 01/22 20:41:10. 0000000015               _leaderCxtCursor = 0000000000000000

I. 01/22 20:41:10. 0000000015                   _allowToFree = 0

I. 01/22 20:41:10. 0000000015                        _psuAll = 0

I. 01/22 20:41:10. 0000000015 [s_blockmap_identity]

I. 01/22 20:41:10. 0000000015 _UniqueId: 117

I. 01/22 20:41:10. 0000000015 _RootBlockId: 3136986

I. 01/22 20:41:10. 0000000015 _NEntries: 4

I. 01/22 20:41:10. 0000000015 _LastWriterTxnId: 2392

I. 01/22 20:41:10. 0000000015 _LastWriterSavePoint: 197

I. 01/22 20:41:10. 0000000015 _FanOutAndFlags2: 0x8

I. 01/22 20:41:10. 0000000015 _Flags: 0x4d

I. 01/22 20:41:10. 0000000015 _Version: 1

I. 01/22 20:41:10. 0000000015 _NLinks: 1

I. 01/22 20:41:10. 0000000015 _EntriesPerLink: 9972

I. 01/22 20:41:10. 0000000015 _FirstAvailLink: 0

I. 01/22 20:41:10. 0000000015 _MaxLink: 1

I. 01/22 20:41:10. 0000000015 _ModNum: 8

I. 01/22 20:41:10. 0000000015 _RootModNum: 8

I. 01/22 20:41:10. 0000000015 [end s_blockmap_identity]

I. 01/22 20:41:10. 0000000015 GetBlockmapEntryInfo() Inputs & Outputs:

I. 01/22 20:41:10. 0000000015    logicalBlockId:         1

I. 01/22 20:41:10. 0000000015    link:                   0

I. 01/22 20:41:10. 0000000015    serverID:               0

I. 01/22 20:41:10. 0000000015 Exception Thrown from slib\s_blockmap.cxx:5273, Err# 0, tid 19 origtid 19

I. 01/22 20:41:10. 0000000015    O/S Err#: 0, ErrID: 2105 (s_offlinedbspaceexception); SQLCode: -1009173, SQLState: 'QSB69', Severity: 14

I. 01/22 20:41:10. 0000000015 [20227]: Operation not permitted on an OFFLINE DBSpace IQ_USER_MAIN.

-- (slib\s_blockmap.cxx 5273)

I. 01/22 20:41:11. 0000008982 Txn 35362 0 35362

------

Regards,

Pankaj

[markmumy · Accepted solution]

You dbspace IQ_USER_MAIN is offline for some reason.  The IQMSG file doesn't show why.  It only shows the access that we attempt after the server started.  Can you include the beginning portion of the message file?  This should show why the space is offline.  It should also show why some devices are RO and some are RW.  Or did you do that?

Also, verify the full path to each of the user main devices.

Mark

[SybDBA · Accepted solution]

Sorry Mark,

some IQ_USER_MAIN files i have removed from the folder earlier, due to that this error was;

now its fine and working.

beyond this, I have two questions for you;

Q.1. what is the mean of 'truncate table' command?

(bcause no changes i have seen after using this command)

Q.2. i have got some error during restore like 'database name not unique', what does it mean??

( i read somewhere that before restoration remove the .db & .log files, then how we will be able to start the IQ server)

Q.3. why we are changing the database name during restoration.

Regards,

Pankaj

[markmumy · Accepted solution]

The TRUNCATE command removes all data from a table.  No different than "delete from XXX".  What were you expecting to happen?  Depending on how you connect to IQ, you may need to issue a COMMIT once the TRUNCATE is done.

When IQ starts, by default it broadcasts its server and database name to the network.  To prevent this add this '-sb 0' to your cfg file.  This tells IQ not to broadcast its information on the network for auto discovery.  That broadcast requires that the server be unique not the network.

Certainly, when you restore you want to make absolutely sure that the catalog and tran log files do not exist in directory that you are restoring to.

The only reason to change the database name during restore is so that you can distinguish between the original and new systems.  If the original is going away, then there's no need to do the rename.

Mark

[SybDBA · Accepted solution]

what about removing .db and .log files before server startup (restoration)???

Regards,

Pankaj

[markmumy · Accepted solution]

Yes.  Before you attempt a restore, you want to make sure that the catalog (.db) and transaction log files are removed.  I also remove things like the IQMSG, server output, stderr files, etc.

Mark

[SybDBA · Accepted solution]

Hi Mark,

then how we will able to start the server(without .db) ???

Regards,

Pankaj

[markmumy · Accepted solution]

The restore creates them. but to do a restore they can't be there otherwise errors will be thrown.

Mark

[c_baker · Accepted solution]

start_iq @params.cfg

will start an 'empty' server.  Connect to the utility_db (this is a virtual db allowing connection to the server only).  Use a params.cfg that at least matches the memory configuration of the source db so you are not limited by caches.

Chris

[SybDBA · Accepted solution]

Thanks Chris,

Got it.

Regards,

Pankaj

[SybDBA · Accepted solution]

Thanks Mark,

Got it.

i was thinking that i'll not be able to start the server without a .db file.

Regards,

Pankaj