quote:

---

Originally posted by gypo

Thanks sodeep. Because of the size of the full backup, I can't move it to the remote server. Also the idea is that I have a backup server waiting to go in the case of a failure.

Any other ideas?

---

quote:

---

Originally posted by gypo

I am currently truncating the trans logs before the full backup, but I can stop that if I need to.

What I would like to do is: after each trans backup restore the trans backup to a backup DB (the backup DB is not located on the same network so I will need to write scripts to FTP files about - unless there is another way to do it?).

---

quote:

---

Originally posted by gypo


I suppose the main question is:
Can I just load the trans backups even though a full backup has occured? Or, each time a full back up occurs i need to restore from the full backup?

---

quote:

---

Originally posted by tfountain

gypo,

If you're looking to create a standby or failover server, you would be better off looking into the technologies to accomplish this, whether it be an active/active cluster, active/passive cluster or data mirroring (to a standby).

On another note, we have a concept like this for a reporting server that we do a full backup and restore of 4 major databases from our live production server (that is on an active/passive cluster) to a read only server. This is for reporting purposes and the backup is done once a day. We copy about 80GB of data files nightly and the entire process takes about 1 hour. This is for the backup, file copy, restore and database shrink statements for all 4 databases. So copying 17GB of files is nothing. The only thing you will need to make sure is that your network has the bandwidth to handle this. I had to have our network guys upgrade the entire switch our servers where on to a full 1GB switch to make this practical.

---

quote:

---

Originally posted by tkizer

quote:

---

Originally posted by gypo

I am currently truncating the trans logs before the full backup, but I can stop that if I need to.

What I would like to do is: after each trans backup restore the trans backup to a backup DB (the backup DB is not located on the same network so I will need to write scripts to FTP files about - unless there is another way to do it?).

---

There is no point in truncating the transaction log before a full backup. Please explain what your intention is for that step. It makes no sense as you are breaking the transaction log backup chain.

quote:

---

Originally posted by gypo


I suppose the main question is:
Can I just load the trans backups even though a full backup has occured? Or, each time a full back up occurs i need to restore from the full backup?

---

Yes that is possible but not with your current setup. You must stop truncating the transaction log.

You must start with a full backup though, so you'll need to copy it once and then restore that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

-- Do full backup
BACKUP DATABASE [MyDB]
	TO DISK = 'F:\DB.BAK'
	WITH NOFORMAT, INIT,  
	NAME = N'Full Database Backup',
	SKIP, NOREWIND, NOUNLOAD,  STATS = 10

-- Do transactional backup
BACKUP LOG [MyDB]
	TO DISK = N'F:\DB.TRN'
	WITH NOFORMAT, INIT, 
	NAME = N'Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

-- Restore full backup
RESTORE DATABASE [MyDB]
	FROM DISK = N'F:\DB.BAK'
	WITH
		FILE = 1,  
		NORECOVERY,
		NOUNLOAD,  
		REPLACE,  
		STATS = 10
GO

-- Restore logs
RESTORE LOG [MyDB] FROM  DISK = N'F:\DB_1.TRN' WITH  FILE = 1,NORECOVERY,NOUNLOAD,STATS = 10
GO
RESTORE LOG [MyDB] FROM  DISK = N'F:\DB_2.TRN' WITH  FILE = 1,RECOVERY,NOUNLOAD,STATS = 10
GO