Backup Questions for SQL - Newbie questions: 1

[Apollo21]

Gentlepeople,
Question 1.
On a standard SQL Server 2000 on a Windows 2003 Server. We have a large database that can span from 90 to 180 gigs. This is a problem for our tape backup procedures (because of the size). I run a maintenance plan (which I have seen in here isn't the best option, but it is what I have for now). It does a nightly full back up of all the databases under this instance.(actually we have 4 instances under this SQL Server but this one is the biggie). I want to do differential or incremental backups every so often during the day. Say I do 4 differentials. On a restore, do I restore the full backup, its transaction log and then the latest differential? I am wondering if I have to stack the differentials on the restore (restore them 1 by 1 until I get to the last one).

Question 2.
I know I have an LDF(log) and an MDF(data). When I perform the restore am I restoring to these two files or can I start restoring the full backup to nothing. I am afraid I am mixing up the purpose of the LDF and MDF. Can anyone help me? Please explain the LDF and MDF to me.

Thank you very much in advance.
Apollo21.

 

[mrdenny]

1. To do a restore with differential backups you first restore the full. Then the most recent differential, than all transaction logs from the time the differential you restored until current.

2. You can start the restore without first creating the database and files.

The mdf is the actual database objects and data. All your tables, indexes, procedures, etc are in this physical file. The ldf is the transaction log. Each time a transaction is done it is logged in the transaction log before the change is committed to the mdf file.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com