Database Compare & Synchronize

Hi to all
I'm new on this forum, i don't have any problems (for now, and crossing my fingers :-D)..i just want to inform all of you of a nice a usefull program that i've have developed and that can help to compare and sychronize (in future) the schema of two databases. (SQL Server 2000 supported only)

It's released under GPL, and it's hosted on sourceforge and GotDotNet. I'm telling you this just because many people told me that it's very usefull and i should spread it as much as possibile :-D

Ok, that's all just let me know if you like it:

http://www.davidemauri.it/dabcos/

PS
This tool is still under developing, anyway it can compare two database schema to check difference in tables, views, functions and stored procedures.
For any who cares it's completely written in C# and it's code is freely downloadable.You mean like sqlcompare?

I usually advocate that people should write there own in t-sql.|||Originally posted by nigelrivett
You mean like sqlcompare?

I usually advocate that people should write there own in t-sql.

Yes it's similar. Though now the synchronize function has not been implemented yet, the idea is that this function will create the T-SQL script necessary to synchronize the database, and eventually run it directly. The main concern of the project is to give the dba the FULL control over what he's doing.

Database Backups & Compression

Hi All

I have a database which is 72GB, which is backed up every night as part of the maintenance plan. I have plenty of storage space, and the server that runs the database is fairly powerful (quad-processor 3.2ghz, 64bit, 48GB RAM) and is part of an active-passive cluster. The database backup is also copied to a SAN location.

My issue is with the size of the backup file. As part of the Disaster Recovery plan, I need to copy this database backup file accross the network to a remote site, so that in the event of a disaster at the site, business can continue at the remote site after restoring the database backup file. However, my database backup file is so big that I cannot copy it accross the network in time for the next morning. I have tried using WinRar and have managed to achieve a file about 20% of its original size, but it takes 2 hours to produce this file.

Is there any recommended reeading for this type of issue? Log shipping / mirroring has been investigated and will be part of the DR model but the 'powers that be' insist on having a full copy performed to the remote site.

Any suggestions? Thanks in advance guys n gals :-)Log shipping IS a full copy, in the truest sense of the word. It is not a monolithic file, but that is a feature instead of a problem in my opinion.

-PatP|||okay for a moment let;
machine A = live, machine B = warm standby server on a remote site

I believed that when restoring log shipments to machine B, there are inherent problems, due to the nighly backups that take place on machine A.

When the log shipping from machine A continues after it has performed a nightly backup, the transcation log entries that were processed and removed during the backup will not be included in the next log shipment to machine B, thus losing a porion of transactions during that given period. Therefore, to successfully restore on machine B, the monolithic database backup file from machine A would be required initially to restore the database and then applying any log shipments that have been shipped after that night's backup.

Is that incorrect...anybody?

The problem is getting that monolithic file to copy accross the network in a given timeframe - it's too big to achieve but 'they' are insisting that it is done.|||Is that incorrect...anybody?


Bzzzzzzzz ... thank you for playing ... you will get your consolation prize as you head backstage :)

When databases are in full recovery mode, the backup does not mark the transaction log for re-use. That only occurs after the transaction log backup.

Prove it to yourself and the PTB (powers that be) by setting up log shipping with a copy of Northwind on the target box ... that way you aren't fighting the database size issue. GO thru the full backup, and tran backup cycles, then make database mods and see if any are lost (hint: they won't be).

I have, in the past, restored a backup from three months prior and then brought it current by applying log backups from that point forward, even across weekly full backups (storage team issues ... grrrrrrrrrrr!).

And to make the uneducated happy, you could even slowly copy the full backup across the wire to the failover machine weekly.|||The database backup will not truncate the log, so the subsequent log backups will contain all the log entries.

I have not done this with MS SQL backups yet but
To speedup the transfer of your 72 GB database backup;
Consider using rsync (http://www.google.com/search?num=100&hl=en&q=rsync&meta=)
Also consider using an rsyncable gzip (http://www.google.com/search?num=100&hl=en&q=rsyncable+gzip&meta=) to compress the file
At minimum compression it should reduce it to 14 GB at acceptable speed (assuming you don't have images inside your database)
And will allow rsync to only copy the portion inside the gzip file that changed.
You would probably see that only 1.4 GB of the 14 GB is actually transferred across the line (10 times faster).

PS. I can understand why they want full backups. You only need a problem with one log backup (missing or damaged file) and you won't be able to recover past that point. A full nightly backup and 15 min log backups make sense to me.

data type DateTime

Hi all!!
I'm developing a project using VS 2005 (C# code) and SQL Server 2005.
I have some problems with the data type 'DateTime'.

In some parts of my project, I have used DataSets and DataAdapters and I can insert in a dataRow a data type 'DateTime' and it is inserted as dd/mm/yyyy hh:mm:ss. That's OK.

But in other parts I'm not using Datatables and DataAdapters and I insert new rows in the database using ExecuteNonQuery, but the data type 'DateTime' in the format dd/mm/yyyy hh:mm:ss is not valid. It only allows formats like mm/dd/yyyy hh:mm:ss, but I want the other format.

Why is dd/mm/yyyy hh:mm:ss working with DataAdapters? Why is dd/mm/yyyy hh:mm:ss not working with ExecuteNonQuery? Is there any way to specify in the Database that I want the format dd/mm/yyyy hh:mm:ss??

Thanks in advance folks,
Javier.

Try to add a setting for DATEFORMAT just before you insert statement, for example:

SET DATEFORMAT dmy; insert into ...

|||Thanks Iori_Jay,
But I've checked that it is necessary to write "SET DATEFORMAT dmy" every time I want to insert a new row in the table that has DateTime attribute. Is there another way of keeping this value within the DB's life??

thanks again|||Since the DATEFORMAT option is a session(means connection to SQL) option, you have to set it in each connection. And within the connection, the option is effective.