SQL Server 2000 - Copy a production database to a test server every day automatically

I have a production database running on SQL Server 2000.
I need to copy this database to a test server every night, automatically.
I'm not able to use backup/restore method because the production server uses
Tivoli Data Protection for SQL Server, and I don't know how to use this
backup and restore on a different server.
I cannot run another full backup only to copy to the test server because we
also run backups for the transaction log every hour, and SQL Server 2000
does not have the copy-only option.
What's the best method to have the production database copied every night to
the test server automatically?

Thanks,

Felipe

On Mon, 25 Aug 2008 19:10:21 -0300, "Carlos Felipe França da Fonseca"
wrote:

>I have a production database running on SQL Server 2000.
>I need to copy this database to a test server every night, automatically.
>I'm not able to use backup/restore method because the production server uses
>Tivoli Data Protection for SQL Server, and I don't know how to use this
>backup and restore on a different server.

Someone needs to know how to do that. If it is not your job, you
might do well to find the one responsible.

>I cannot run another full backup only to copy to the test server because we
>also run backups for the transaction log every hour, and SQL Server 2000
>does not have the copy-only option.

I don't know what that means. I know of not reason why you can't run
another BACKUP DATABASE, as long as you don't schedule it to run at
the same time as any of the BACKUP LOG commands.

>What's the best method to have the production database copied every night to
>the test server automatically?

BACKUP and RESTORE.

Roy Harvey
Beacon Falls, CT

wrote:
> I have a production database running on SQL Server 2000.
> I need to copy this database to a test server every night, automatically.
> I'm not able to use backup/restore method because the production server uses
> Tivoli Data Protection for SQL Server, and I don't know how to use this
> backup and restore on a different server.
> I cannot run another full backup only to copy to the test server because we
> also run backups for the transaction log every hour, and SQL Server 2000
> does not have the copy-only option.
> What's the best method to have the production database copied every night to
> the test server automatically?
>
> Thanks,
>
> Felipe
>
>
A dirty solution would be to copy it with the help of volume shadow
copies and "vshadows" from the vss sdk:
http://blogs.msdn.com/adioltean/arch...25/517599.aspx
and attach it.

Regards,
Peter

You can do this in a simple way.

a) Backup to disk and then TSM (Tivoli Storage Manager)
1) Make your test SQL Server run under a domain account
2) Share the backup folder of your prod server - grant rights to the
test-sql-server account
3) schedule a daily restore from the shared backup folder (you might have to
map it to a driveletter before trying to connect) From either SQL Agent or
Scheduled tasks in Windows.

b) TSM
It is possible to restore to different location because you need to set the
NODE correct before you connect. Ask your backup personell to help you with
this one because you need to run a script that disconnect all logins to the
database before running the restore - otherwise it will fail.

Hope this helps


"Peter Gibbons" wrote:

> wrote:
> > I have a production database running on SQL Server 2000.
> > I need to copy this database to a test server every night, automatically.
> > I'm not able to use backup/restore method because the production server uses
> > Tivoli Data Protection for SQL Server, and I don't know how to use this
> > backup and restore on a different server.
> > I cannot run another full backup only to copy to the test server because we
> > also run backups for the transaction log every hour, and SQL Server 2000
> > does not have the copy-only option.
> > What's the best method to have the production database copied every night to
> > the test server automatically?
> >
> > Thanks,
> >
> > Felipe
> >
> >
> A dirty solution would be to copy it with the help of volume shadow
> copies and "vshadows" from the vss sdk:
> http://blogs.msdn.com/adioltean/arch...25/517599.aspx
> and attach it.
>
> Regards,
> Peter
>