I am trying to setup my SQL server and I want to plan the backups
My ideas were to create a sys_dat_bak device for the full backup of
all the system databases (master, model, msdb) and a sys_logs_bak for
the transaction log backup of the model db. The backup files created
by MS SQL server would then be backupped again by our normal central
backup system in filesystem mode. Ditto for the user databases, a
app_data_bak and app_logs_bak. For the user database I would schedule
extra tlog-backups during the day.
I have successfully created the backup disk-devices with T-SQL.
When I try to automate the actual backup commands in Enterprise
Manager, I can get the backups to succeed, but when I veryfy with
FILELIST then I seem to be missing files and not getting what I want
on this device.
Here's what I do :
I have a job called Full backup systemdatabases (data and tlog).
Step 1 (= daily full backup of master and clear file before backup)
I use master
BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 2 (= daily full backup of model and append to backupdevice)
I use master
BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 3 (= Tlog backup of model and clear file before backup)
I use master
BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME =
N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
Transaction log', NOFORMAT , NO_TRUNCATE
On success goto next step
Step 4 (= daily full backup of msdb and append to backupdevice)
I use master
BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
MSDB database', NOFORMAT
All these steps finish with success
When I check with restore headeronly, then I can see that 3 session
were started and finished to device sys_data_bak and 1 session to
sys_logs_bak. That's exactly what I hoped for.
When I check with restore filelistonly, then I see on :
- sys_data_bak : two files written : master.mdf and mastlog.ldf
- sys_logs_bak : two files written : model.mdf and modellog.ldf
And now I am puzzled.
Why would this backup write only master.mdf and mastlog.ldf on
sys_data_bak ?
Why would this backup write only model.mdf and model.ldf on
sys_logs_bak ? Model.mdf shouldn't even be on that device.
And where's msdb backup ?
Can someone explain please? In need to understand what is happening
here. Also if you have suggestions how to improve the scripts please
do.
Hi
1. Use separate device for each database.
2. Use separate device for backup tasks on database.
Eg:
SAMPLE_DATABASE
Create devices:
-SAMPLE_DATABASE_FULL
-SAMPLE_DATABASE_DIFF
-SAMPLE_DATABASE_LOG_1
-SAMPLE_DATABASE_LOG_2
-SAMPLE_DATABASE_LOG_3
-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and backup the file to tape.
-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with overwrite) and backup the file to tape.
-And by day use log backup devices , also with overwrite
You can this method easy scripting. Also retore methods.
3. Configure Your database defaults on model, then backup once. This database used only when you create a new database on server, not store data.
Andras Jakus MCDBA
"citizen" wrote:
> I am trying to setup my SQL server and I want to plan the backups
> My ideas were to create a sys_dat_bak device for the full backup of
> all the system databases (master, model, msdb) and a sys_logs_bak for
> the transaction log backup of the model db. The backup files created
> by MS SQL server would then be backupped again by our normal central
> backup system in filesystem mode. Ditto for the user databases, a
> app_data_bak and app_logs_bak. For the user database I would schedule
> extra tlog-backups during the day.
> I have successfully created the backup disk-devices with T-SQL.
> When I try to automate the actual backup commands in Enterprise
> Manager, I can get the backups to succeed, but when I veryfy with
> FILELIST then I seem to be missing files and not getting what I want
> on this device.
> Here's what I do :
> I have a job called Full backup systemdatabases (data and tlog).
> Step 1 (= daily full backup of master and clear file before backup)
> I use master
> BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
> NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 2 (= daily full backup of model and append to backupdevice)
> I use master
> BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
> NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 3 (= Tlog backup of model and clear file before backup)
> I use master
> BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME =
> N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> Transaction log', NOFORMAT , NO_TRUNCATE
> On success goto next step
> Step 4 (= daily full backup of msdb and append to backupdevice)
> I use master
> BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
> NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> MSDB database', NOFORMAT
> All these steps finish with success
> When I check with restore headeronly, then I can see that 3 session
> were started and finished to device sys_data_bak and 1 session to
> sys_logs_bak. That's exactly what I hoped for.
> When I check with restore filelistonly, then I see on :
> - sys_data_bak : two files written : master.mdf and mastlog.ldf
> - sys_logs_bak : two files written : model.mdf and modellog.ldf
> And now I am puzzled.
> Why would this backup write only master.mdf and mastlog.ldf on
> sys_data_bak ?
> Why would this backup write only model.mdf and model.ldf on
> sys_logs_bak ? Model.mdf shouldn't even be on that device.
> And where's msdb backup ?
> Can someone explain please? In need to understand what is happening
> here. Also if you have suggestions how to improve the scripts please
> do.
>
|||You are seeing this because you probably did not specify the WITH FILE =
parameter for the RESTORE FILELISTONLY command. If you do not specify this
parameter, it will show you the files that were backed up for the first
backup in the device. Refer to Books Online for more information on how to
use this parameter.
However as suggested by Andras, you may want to create separate devices to
reduce the confusion.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||On Fri, 30 Jul 2004 08:45:03 -0700, Andras Jakus
<AndrasJakus@.discussions.microsoft.com> wrote:
>Hi
>1. Use separate device for each database.
>2. Use separate device for backup tasks on database.
>Eg:
>SAMPLE_DATABASE
>Create devices:
>-SAMPLE_DATABASE_FULL
>-SAMPLE_DATABASE_DIFF
>-SAMPLE_DATABASE_LOG_1
>-SAMPLE_DATABASE_LOG_2
>-SAMPLE_DATABASE_LOG_3
>-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and backup the file to tape.
>-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with overwrite) and backup the file to tape.
>-And by day use log backup devices , also with overwrite
>You can this method easy scripting. Also retore methods.
>3. Configure Your database defaults on model, then backup once. This database used only when you create a new database on server, not store data.
>Andras Jakus MCDBA
>
Since the database will be very small, we will not be making
differential backups, only full backup's. Those backupfiles will get
backuped again by our central backup system.
I don't think I will have much trouble with the app. database backup,
but I don't know why the backup of the systemdatabases is behaving
like it is now. Do you know ?
|||On Fri, 30 Jul 2004 16:49:39 GMT, pankaja@.online.microsoft.com (Pankaj
Agarwal [MSFT]) wrote:
>You are seeing this because you probably did not specify the WITH FILE =
>parameter for the RESTORE FILELISTONLY command. If you do not specify this
>parameter, it will show you the files that were backed up for the first
>backup in the device. Refer to Books Online for more information on how to
>use this parameter.
>However as suggested by Andras, you may want to create separate devices to
>reduce the confusion.
>Thank you for using Microsoft newsgroups.
>Sincerely
>Pankaj Agarwal
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
OK, found it. I must indeed specify with file =
So all the files that must be there, I have found now.
But is there no way to let sql server show ALL the files available ?
Now one must know which fileset and guess how many there are ...
if they don't know all of the backupprocedure.
