The parts of any repository/DAO implementation worth testing are the queries. To ensure that these queries are correct, you'd have to run it in the actual database.
Given the above facts does it make sense to unit test DAOs/Respositories? If yes what are the best practices?
Yes - with an in-memory database (HSQLDBL for example)
This blogpost of mine discusses a similar topic.
Update: about your comment - in the post I linked an ORM is used to make sure database inconsistencies aren't a problem. Working with raw SQL isn't quite a good idea in the first place (if working with OOP). Then, you could always try to use as ANSI SQL as possible (and not test the inconsistencies).
Another option might be to dedicate a testing database server + a continuous integration engine, and run the tests only within the engine (so that multiple test executions from multiple machines don't mess with each other)
I unit test my repositories pretty religiously. They are probably my most important unit tests actually.
This can actually be quite painless if you use an ORM such as NHibernate.
I use a base fixture that contains setup and teardown for creating an in memory sqlite db and then destroying it at the end of each test. This is surprisingly quick. Then for each repository I have a setup that injects my test data for my tests. This is very self contained and catches all the logic issues in my repository queries.
The only thing it doesn't catch is db provider specific cases, but when using something like NHibernate these are usually the exception.
For the special cases where you are testing database specific queries you may need to have a suite of tests that use a different setup and teardown methodology. Unfortunately these tests would be slower and likely more fragile than your other unit tests (that's why they should be grouped together).
If "express" editions of the database software you are testing are available I still recommend that the database be set up locally on the fly so that you always ensure that the database your tests run against has the schema they expect. I would change one part of the setup and teardowns of this though. I would only setup and teardown the database at the beginning and end of the entire run of tests. Then every tests setup and teardown should start a transaction, then roll it back at the end. This is a quick way of keeping things compartmentalized between the tests. The last thing you want is for data from one test to affect another test.
Yes doing unit tests for DAO layer is a must. In java there are frameworks like dbUnit helps you in doing that. Keeping a separate schema/instance in the database with some bootstrap data will help you do right unit testing and you can cover most of the scenarios.