Database schema comparison

Hello all. I am using Sql Compact Edition for a small standalone application, I create and build the initial database schema on initial startup. What I am looking for is a way to upgrade the software and on initial startup of the new version, I would like it to compare the existing database with a new schema and then update the database based on the difference. This way I can have a version that will update the database without me having to know what version it is to begin with. Is there a way to do this or am I asking too much?

Thank you,

Jim

This is something you will have to code yourself, however the key to doing this is to tap into the INFORMATION_SCHEMA views in SQL Compact Edition.

You can get metadata about all aspects of the schema of a SQL CE/SQL Mobile database using this view and then make determinations about old vs new schema as you compare two database versions. Books Online covers the INFORMATION_SCHEMA view and what it contains.

Regards,

Darren Shaffer

Database Restore with NORECOVERY

Hi All,

im trying to set up database mirroring and in the initial phase of database restoring im getting some problem.

the scenario is .

1- i create a tempDB on server1(which will be principal server) and backup full and transaction logs.

2- create tempDB in server 2( going to be used as mirror) .

3- restore the full backup of "tempDB" from server1 with NORECOVERY option.

this step results in a message box showing that the restore was successfull...

but the database explorer( management studio) shows the tempDB in (restoring ...) state. and no more operations can be performed with this Database. ( because teh databse isin restoring mode).. please help what this issue is?

Thanks In Advance for any suggestions

hi amir,

1.you have a db in principal server and it shud be in full recovery mode to configure db mirroring

2.take a full a full backup and restore it with norecovery option in the mirror prior to configuring mirroring

3.take a tran log backup in principal and restore it with norecovery option in the mirror.

then start configuring db mirroring using the wizard............

norecovery option restores the db but it will be in restoring state i.e you can apply tran logs to it but you cant use it and no objects will be visible

normally there are 3 types while performing restoration,

1.Leave the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
2.Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
3.Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)

http://msdn2.microsoft.com/en-us/library/ms177429.aspx - refer this link for restoring options.........

refer ,

http://deepakinsql.blogspot.com/2007/07/how-to-configure-database-mirroring.html - for configuring dbmirroring

http://deepakinsql.blogspot.com/2007/07/prerequisites-for-database-mirroring.html - prerequisites for mirroring

revert if you have nay concerns

thanks

|||

Hi Amir,

What Deepak xplained is right.

Follow the steps and if want to see the objects in mirror database just force a failover. The new mirror will be in restoring... state

Its quite confusing, it should have been "dont worry continue..." rather than "restoring..."

Goodluck

Vj

Database Restore with NORECOVERY

Hi All,

im trying to set up database mirroring and in the initial phase of database restoring im getting some problem.

the scenario is .

1- i create a tempDB on server1(which will be principal server) and backup full and transaction logs.

2- create tempDB in server 2( going to be used as mirror) .

3- restore the full backup of "tempDB" from server1 with NORECOVERY option.

this step results in a message box showing that the restore was successfull...

but the database explorer( management studio) shows the tempDB in (restoring ...) state. and no more operations can be performed with this Database. ( because teh databse isin restoring mode).. please help what this issue is?

Thanks In Advance for any suggestions

hi amir,

1.you have a db in principal server and it shud be in full recovery mode to configure db mirroring

2.take a full a full backup and restore it with norecovery option in the mirror prior to configuring mirroring

3.take a tran log backup in principal and restore it with norecovery option in the mirror.

then start configuring db mirroring using the wizard............

norecovery option restores the db but it will be in restoring state i.e you can apply tran logs to it but you cant use it and no objects will be visible

normally there are 3 types while performing restoration,

1.Leave the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
2.Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
3.Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)

http://msdn2.microsoft.com/en-us/library/ms177429.aspx - refer this link for restoring options.........

refer ,

http://deepakinsql.blogspot.com/2007/07/how-to-configure-database-mirroring.html - for configuring dbmirroring

http://deepakinsql.blogspot.com/2007/07/prerequisites-for-database-mirroring.html - prerequisites for mirroring

revert if you have nay concerns

thanks

|||

Hi Amir,

What Deepak xplained is right.

Follow the steps and if want to see the objects in mirror database just force a failover. The new mirror will be in restoring... state

Its quite confusing, it should have been "dont worry continue..." rather than "restoring..."

Goodluck

Vj

Database restore question

Hi,

I have a database (or better: used to have) and backup consisting of
- the initial (complete) database
- all log files since then (or so I thought)
After making a data entry error I wrote the log to the backup and
tried a point of time restore.
Unfortunately that failed with the message

"The log in this backup set begins at LSN xxx, which is too late to apply to
the database. An earlier log backup that includes LSN yyy can be restored."

and left the database in an inaccessible state.

I have tried to reproduce the error, my guess is that the recovery model was
set to 'simple'
instead of ' full' at for some time.

Is there anyway I can
- extract data from the log files (however incomplete)?
- get the database back to the point of just after the database error (just
before I tried the restore)?

Thanks for your time!

Andre"Andre" <nonamel@.nospam.org> wrote in message
news:40c0c429$0$33919$e4fe514c@.news.xs4all.nl...
> Hi,
> I have a database (or better: used to have) and backup consisting of
> - the initial (complete) database
> - all log files since then (or so I thought)
> After making a data entry error I wrote the log to the backup and
> tried a point of time restore.
> Unfortunately that failed with the message
> "The log in this backup set begins at LSN xxx, which is too late to apply
to
> the database. An earlier log backup that includes LSN yyy can be
restored."
> and left the database in an inaccessible state.
> I have tried to reproduce the error, my guess is that the recovery model
was
> set to 'simple'
> instead of ' full' at for some time.
> Is there anyway I can
> - extract data from the log files (however incomplete)?
> - get the database back to the point of just after the database error
(just
> before I tried the restore)?
> Thanks for your time!
> Andre

I'm not really sure I follow your description - do you mean that the
sequence of log backups was broken because the database was set to simple
mode, then back to full? So when you restored your logs, only some of them
restored, before giving the error? If so, then you should be able to make it
available again like this:

restore database MyDB with recovery

However you can't roll forward without a full sequence of log backups, so
you won't be able to get back to the point after the error occurred. If the
data is valuable enough, you should probably consider calling Microsoft for
support, but they may not be able to do much either, if you don't have a
valid backup set.

You might be able to recover something from your log backups using a tool
like this one, but if you don't have all the log backups then you won't know
what is missing:

http://www.lumigent.com/products/le_sql/le_sql.htm

If I've misunderstood your situation, or if this isn't helpful, please give
some more detailed information about what happened, what you've tried
(preferably the exact RESTORE commands you used), and the current status of
the database (ie. using DATABASEPROPERTYEX('MyDB', 'Status')).

Simon