We have a development server that has a job that runs overnight which restores backed up databases. Our primary database server backs up the databases overnight and our development server then restores these databases to keep them up-to-date with one another on a daily basis (not transactional basis).
As our databases are pushing many gigabytes in size we now face the problem that restores take many hours overnight. Instead we're wanting to push out the use of differential database backups and restores.
The problem I have is that the development server does a restore, but how can I tell which file to pick out in the differential backup? You have to specify a file i.e. FILE = 9 in the restore statement, but what if I don't know which file it is? What if I just want the latest one?
So how do you specify the latest file from a differential database backup?