DB updates when limited to bak/trn files only

Hi,
I couldn't make a clear title without writing 2 lines.

My problem (I tried to find out through the archives):
this scenario will be used for several DBs on severals servers.

The remotes servers are not mine, I have only access to the backups files, I have no rights to setup a replication relationship.

I'm using a repository server with SQL 2005, and daily, I need to get the latest Full backup from an SQL2000 server, copy it to the repository server, restore it then delete the .bak file.
This is possible by using many different scripts (like .vbs to copy and rename the latest Full backups) then I use SQL job for daily restore.
Process is too long and the time estimated to start next task (and the backup keep growing).

Is there a way to do everything via SQL2005 script (job)?

Initially, the problem is that I have to do this with the Full backups every days (around 5 Dbs 8 Go in average). So if I can use the latest transaction log files (that would eliminate my first question).
The best way is to use log shipping as well but sql2005 is needed on both sides.

Hope I'm clear.

Thanks for any help!Do you need the databases in read only mode, or fully recovered?

It would be difficult to use the tran log backups, because you could never recover the database.

However, saying that, I wonder if you could keep the receiving database in an unrecovered Read-Only mode, and transactionally publish that database to a third database that would be recovered?


Source Receiving Recovered
Server Full Backup Server DB Server
[----] ------> [----] [----]
[----] Tran Log backups [----] Transactional [----]
[----] ------> [----] Replication [----]
[----] [----] ------> [----]
[----] [----] [----]|||Database in read only mode should be enough as they are used for extraction.
The best and simpler solution again is having the rights on the remotes server but I need to do with it.

So as I won't be able to restore the DB with Trn log only, I still need to copy the Full Bak. I'm fine with that as having the full from 12 h ago is fine.
I guess the intermediary server solution in not needed for that. I know this is a widely used solution but maybe not for what I need (until SQL 2005 migration on remotes servers).

So now, saying I'll only use the .bak, is here a way I can automate from my local server the copy and restore?

Backups are done on same Remote servers, then picked up from there.

Thanks|||So now, saying I'll only use the .bak, is here a way I can automate from my local server the copy and restore?


Since I don't know all the info regarding your provider as regards backup retention period, etc ... here is an overview:

1. Download robocopy - it's free and very good - will auto-restart a file copy if interrupted

2. create a sql script that will be executed by sqlcmd (command line interface) - script builds the filename to download (assumes provider is building filenames including date of backup in filename) and uses robocopy to perform the copy to your local storage. Also have that script create a small file called "CopyComplete.txt" after robocopy successfully finishes (you could copy it from another locatiopn"

3. Create a scheduled job that checks periodically for the "CopyComplete.txt" file. When it finds it, it deletes it and then does the database restore using the replace option.

This is one method ... there are many variations of this ... you chose one and debug it until it works, then use that as a template for all your databases.|||Bkp retention is 2 days (2 full and 6 trn) (so need to grag the last ones, always same time)

I'll try Robocopy with your steps then and will let u know how i is.

thanks again