backup strategy

[ersatz]

Hi,
I have to manage a SQL Server DB who is used by a web application - server A. We have also an other server for special situations (disaster, for example) - server B
Both have installed a 2003 Win OS and a 2000 SQL Server Standard Edition.
Q : What kind of backup /restore strategy I need to obtain the best performance?
joanne

 

[nigelrivett]

How much data can you afford to lose. How frequent are updates?

Try at least a nightly full backup.
maybe
4 hourly diffs and hourly (or 15 min) log backups sepending on system.

Make sure you test the backups.
You will need implement log backups unless you set the recovery model to simple or the system will crash (and possible corrupt the database) when the tr log fills the disk.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ersatz]

Hi,

Thanks for your prompt response.

Presently I don’t use B server.

My problem is that I don’t know how to use the second server in case of disaster or if I have a technical problem with server A.

Example: the server A crash or I must update the software in the server A, etc…….

Haw can I switch the application to use the bd from the server B on this time?

The application is 85% a read application.

Is a standby installation a good idea?

How can I send the backup from server A to server B, what type of restore I must use to have a shortly downtime?

Thanks again,

ersatz

 

[nigelrivett]

I use this for testing backups

http://www.nigelrivett.net/s_TestRestore.html

And this for taking backups

http://www.nigelrivett.net/BackupAllDatabases.html

The restore can be changed to do a restore without recovery (get rid of the checkdb.
That will leave you in a position to apply diffs and tr logs and get up and running on the standby server quickly.

Another option is to implement log shipping. Can be done in a similar way to the restore SP above - just copy and restore the logs as well.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ersatz]

Hi,

Thanks for links.
I understand that I cannot work with standby / log shipping in standard edition.
I will test these scripts.

Thanks a lot,
ersatz