MSDE 2000 backups and restores

Hi All
I am new to MSDE (from access)
I have chosen simple recovery mode for MSDE Database and do a full backup
each day then subsequent backups within the same day using differential
backups
The problem is I don't really understand how the differential backup should
work
1. Do you use a different file name for the Full and Differential backups?
e.g
BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcars.bak' WITH INIT,
NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT, NAME = 'Tramcars Diff Backup'
GO
BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcarsdiff.bak' WITH INIT,
differential,
NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT,
NAME = 'Tramcars Diff Backup'
GO
2 Do you use different file names for each differential backups during the
day or append each diff backup to the previous?
Because the literature shows TSQL code for Restoring includes reference to a
File = 1 (or 2 etc)
Regards
Steve
hi Steve,
> The problem is I don't really understand how the differential backup
> should work
> 1. Do you use a different file name for the Full and Differential
> backups? e.g
> BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcars.bak' WITH
> INIT, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT, NAME = 'Tramcars Diff
> Backup'
> GO
> BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcarsdiff.bak'
> WITH INIT, differential,
> NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT,
> NAME = 'Tramcars Diff Backup'
> GO
correct... use 2 different files OR "init" the backup set with the full
backup and append to it the successive differential..
if you choos to have only 1 backup file, when restoring you have to specify
the
RESTORE DATABASE [xxx] FROM DISK = N'C:\xxxx.bak'
WITH FILE = n ,
......
where n is 1 for the very first (complete full backup) and then apply the
differential
WITH FILE = n , -- where n=2

> 2 Do you use different file names for each differential backups
> during the day or append each diff backup to the previous?
> Because the literature shows TSQL code for Restoring includes
> reference to a File = 1 (or 2 etc)
this is up to you... on how you "store" backup sets, how long you have to
store them, your internal policies, the actual media hosting the backups...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
Again thanks for your reliable input
Steve
Regards
Steve
"Andrea Montanari" wrote:

> hi Steve,
> correct... use 2 different files OR "init" the backup set with the full
> backup and append to it the successive differential..
> if you choos to have only 1 backup file, when restoring you have to specify
> the
> RESTORE DATABASE [xxx] FROM DISK = N'C:\xxxx.bak'
> WITH FILE = n ,
> ......
> where n is 1 for the very first (complete full backup) and then apply the
> differential
> WITH FILE = n , -- where n=2
>
> this is up to you... on how you "store" backup sets, how long you have to
> store them, your internal policies, the actual media hosting the backups...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
sql