Backup Question For Newbie

We use SQL for a few minor applications, and currently, we just down the
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>sql