Hello All,
Sometime back we had scheduled Differential database backups on our OLTP
system. The backup is scheduled to run once at 6AM, once at 12Noon and
7PM. This backup is apart from the half hourly transaction log backup
and the daily full database backup.
Normally, for a day, the Transaction log backup does not exceed 300MB and
the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
exceed 500MB and the 7PM Diff backup does not exceed 600MB.
However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
but the Transaction log backup still 300MB.
I would expect the Transaction Log backup also to show around 1500MB.
Does anyone know why the differential database backup is so large when
compared to the Transaction Log.
Thanks,
RgnOne explanation would be that you move to bulk logged recovery mode, and
bcp'd in a lot of data ( which is minimally logged).
Another explanation would be that you dropped/re-created a bunch of indexes,
only the drop/create statement would be in the log, but the entire index
would be copied in the differential backup.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rgn" <anonymous@.discussions.microsoft.com> wrote in message
news:9B20C782-042B-4D0C-857C-F68787C5C45C@.microsoft.com...
> Hello All,
> Sometime back we had scheduled Differential database backups on our OLTP
> system. The backup is scheduled to run once at 6AM, once at 12Noon and
> 7PM. This backup is apart from the half hourly transaction log backup
> and the daily full database backup.
> Normally, for a day, the Transaction log backup does not exceed 300MB and
> the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
> exceed 500MB and the 7PM Diff backup does not exceed 600MB.
> However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
> the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
> but the Transaction log backup still 300MB.
> I would expect the Transaction Log backup also to show around 1500MB.
> Does anyone know why the differential database backup is so large when
> compared to the Transaction Log.
> Thanks,
> Rgn|||Wayne
You are very true in saying that the bulk-logged operation generates a large transaction log backup and/or differential DB backup. However, my question is that, if the differential Db backup is 1.5GB then even the transaction log backup shoul
be of simillar size, right
In my case, the transaction log seems to be only 300MB where as the differential db backup is 1.4GB
rgn
Showing posts with label scheduled. Show all posts
Showing posts with label scheduled. Show all posts
Sunday, March 11, 2012
Differential Backup Files
When a new scheduled job is created for a Differential backup, the file specified in the Destination folder is automatically created by SQL Server. After the first time the job runs, is there a way to configure SQL Server to give each Differential file a unique name, including the timestamp (i.e. similar to Full Backup jobs)? I noticed my only options are 'Append to File" and "Overwrite Existing File." If I choose to enable "Backup Set Expiration," the backup job will not run, because it wants to append/overwrite the filename specified.pretty easy to do via T-SQL statement. The following script creates a backup with date and hour as timestamp in file name.
declare @.hour varchar(2), @.date varchar(8)
set @.hour = substring(convert(char(2), getdate(), 108), 1, 2)
set @.date = convert(varchar(8), getdate(), 112)
exec ('use master backup database xxx to disk = ''D:\backup\xxx_db_' + @.date + @.hour + '.bak '' with init')
declare @.hour varchar(2), @.date varchar(8)
set @.hour = substring(convert(char(2), getdate(), 108), 1, 2)
set @.date = convert(varchar(8), getdate(), 112)
exec ('use master backup database xxx to disk = ''D:\backup\xxx_db_' + @.date + @.hour + '.bak '' with init')
Friday, March 9, 2012
Differential Backup
I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>|||>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>
Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.
>AK
>
>.
>|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK
>--Original Message--
>I wouldn't figure the differential should ever be larger
than the full DB
>backup. Is the differential backup writing to the same
file each time? If so
>then you're probably adding to the file each time, look
up the WITH FORMAT
>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
>> I have the following scheduled for backup:
>> a) Full back up (daily) which creates a *.bak of 1.5GB
in
>> size.
>> b) Transaction log (every 2 hours) which creates a *.TRN
>> of 20 MB
>> c) Differential back up which creates a *.bak of 9GB and
>> continues to grow.
>>
>> Couple of questions:
>> 1) Is the size normal for Differential? What am I doing
>> wrong? Why is it so big?
>> 2) Is there only one file that gets created for
>> differential?
>>
>> AK
>>
>
>.
>|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
>--Original Message--
>>--Original Message--
>>I have the following scheduled for backup:
>>a) Full back up (daily) which creates a *.bak of 1.5GB
in
>>size.
>>b) Transaction log (every 2 hours) which creates a *.TRN
>>of 20 MB
>>c) Differential back up which creates a *.bak of 9GB and
>>continues to grow.
>>
>>Couple of questions:
>>1) Is the size normal for Differential? What am I doing
>>wrong? Why is it so big?
>>2) Is there only one file that gets created for
>>differential?
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>>AK
>>
>>.
>.
>|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
> >--Original Message--
> >I wouldn't figure the differential should ever be larger
> than the full DB
> >backup. Is the differential backup writing to the same
> file each time? If so
> >then you're probably adding to the file each time, look
> up the WITH FORMAT
> >option of the BACKUP command.
> >
> >Mike Kruchten
> >
> >
> >"AK" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> >> I have the following scheduled for backup:
> >>
> >> a) Full back up (daily) which creates a *.bak of 1.5GB
> in
> >> size.
> >>
> >> b) Transaction log (every 2 hours) which creates a *.TRN
> >> of 20 MB
> >>
> >> c) Differential back up which creates a *.bak of 9GB and
> >> continues to grow.
> >>
> >>
> >> Couple of questions:
> >>
> >> 1) Is the size normal for Differential? What am I doing
> >> wrong? Why is it so big?
> >>
> >> 2) Is there only one file that gets created for
> >> differential?
> >>
> >>
> >> AK
> >>
> >>
> >
> >
> >.
> >
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>|||>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>
Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.
>AK
>
>.
>|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK
>--Original Message--
>I wouldn't figure the differential should ever be larger
than the full DB
>backup. Is the differential backup writing to the same
file each time? If so
>then you're probably adding to the file each time, look
up the WITH FORMAT
>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
>> I have the following scheduled for backup:
>> a) Full back up (daily) which creates a *.bak of 1.5GB
in
>> size.
>> b) Transaction log (every 2 hours) which creates a *.TRN
>> of 20 MB
>> c) Differential back up which creates a *.bak of 9GB and
>> continues to grow.
>>
>> Couple of questions:
>> 1) Is the size normal for Differential? What am I doing
>> wrong? Why is it so big?
>> 2) Is there only one file that gets created for
>> differential?
>>
>> AK
>>
>
>.
>|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
>--Original Message--
>>--Original Message--
>>I have the following scheduled for backup:
>>a) Full back up (daily) which creates a *.bak of 1.5GB
in
>>size.
>>b) Transaction log (every 2 hours) which creates a *.TRN
>>of 20 MB
>>c) Differential back up which creates a *.bak of 9GB and
>>continues to grow.
>>
>>Couple of questions:
>>1) Is the size normal for Differential? What am I doing
>>wrong? Why is it so big?
>>2) Is there only one file that gets created for
>>differential?
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>>AK
>>
>>.
>.
>|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
> >--Original Message--
> >I wouldn't figure the differential should ever be larger
> than the full DB
> >backup. Is the differential backup writing to the same
> file each time? If so
> >then you're probably adding to the file each time, look
> up the WITH FORMAT
> >option of the BACKUP command.
> >
> >Mike Kruchten
> >
> >
> >"AK" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> >> I have the following scheduled for backup:
> >>
> >> a) Full back up (daily) which creates a *.bak of 1.5GB
> in
> >> size.
> >>
> >> b) Transaction log (every 2 hours) which creates a *.TRN
> >> of 20 MB
> >>
> >> c) Differential back up which creates a *.bak of 9GB and
> >> continues to grow.
> >>
> >>
> >> Couple of questions:
> >>
> >> 1) Is the size normal for Differential? What am I doing
> >> wrong? Why is it so big?
> >>
> >> 2) Is there only one file that gets created for
> >> differential?
> >>
> >>
> >> AK
> >>
> >>
> >
> >
> >.
> >
Differential Backup
We have an sql database which is about 50GB. Everyday I do 2 different
backups from 2 different programs. The first backup is a full backup
scheduled in sql and runs at 8pm. The second backup is online backup and runs
at 7 pm from Monday to Thursday as defferential and every Friday as full. My
question is: Because of the daily full backup, is my differential backup
correct or shall I stop the local daily full?Fedias
Your DB is pretty big, so I'd start with FULL backup once a day and
DIFFERENTIAL backup starting from 6AM till 22PM every two/three hours.
Remmeber that DIFF backup contains all data since last FULL backup , thus
every DIFF backup will get bigger than previous one.
Another strategy you might want to create a LOG backup without doing DIFF
one. It gives possibility to recover your dtabase at point of time in cas
of crashing/
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
"Fedias Papadopoulos" <Fedias Papadopoulos@.discussions.microsoft.com> wrote
in message news:D2A26087-0483-4473-B4DB-3E79BAE05FE4@.microsoft.com...
> We have an sql database which is about 50GB. Everyday I do 2 different
> backups from 2 different programs. The first backup is a full backup
> scheduled in sql and runs at 8pm. The second backup is online backup and
> runs
> at 7 pm from Monday to Thursday as defferential and every Friday as full.
> My
> question is: Because of the daily full backup, is my differential backup
> correct or shall I stop the local daily full?|||Thanks Uri,
Is very difficult to make daily full backup using my online backup solution
(www.cybackup.com) because the compressed full backup is about 6.5gb and
takes about 30 hours to go offsite. This solution makes only full and
differential backups. So we do daily dif backups and every friday a full. So
my wonder is, does the daily second full backup we do from sql affects the
dif backup of the online backup
"Uri Dimant" wrote:
> Fedias
> Your DB is pretty big, so I'd start with FULL backup once a day and
> DIFFERENTIAL backup starting from 6AM till 22PM every two/three hours.
> Remmeber that DIFF backup contains all data since last FULL backup , thus
> every DIFF backup will get bigger than previous one.
> Another strategy you might want to create a LOG backup without doing DIFF
> one. It gives possibility to recover your dtabase at point of time in cas
> of crashing/
> http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> --administaiting best practices
>
>
>
>
>
>
>
>
> "Fedias Papadopoulos" <Fedias Papadopoulos@.discussions.microsoft.com> wrote
> in message news:D2A26087-0483-4473-B4DB-3E79BAE05FE4@.microsoft.com...
> > We have an sql database which is about 50GB. Everyday I do 2 different
> > backups from 2 different programs. The first backup is a full backup
> > scheduled in sql and runs at 8pm. The second backup is online backup and
> > runs
> > at 7 pm from Monday to Thursday as defferential and every Friday as full.
> > My
> > question is: Because of the daily full backup, is my differential backup
> > correct or shall I stop the local daily full?
>
>|||Fedias
Yes it does
"Fedias Papadopoulos" <FediasPapadopoulos@.discussions.microsoft.com> wrote
in message news:46AC2224-4B54-413C-A9B9-00C85968C793@.microsoft.com...
> Thanks Uri,
> Is very difficult to make daily full backup using my online backup
> solution
> (www.cybackup.com) because the compressed full backup is about 6.5gb and
> takes about 30 hours to go offsite. This solution makes only full and
> differential backups. So we do daily dif backups and every friday a full.
> So
> my wonder is, does the daily second full backup we do from sql affects the
> dif backup of the online backup
> "Uri Dimant" wrote:
>> Fedias
>> Your DB is pretty big, so I'd start with FULL backup once a day and
>> DIFFERENTIAL backup starting from 6AM till 22PM every two/three hours.
>> Remmeber that DIFF backup contains all data since last FULL backup ,
>> thus
>> every DIFF backup will get bigger than previous one.
>> Another strategy you might want to create a LOG backup without doing
>> DIFF
>> one. It gives possibility to recover your dtabase at point of time in
>> cas
>> of crashing/
>> http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
>> --administaiting best practices
>>
>>
>>
>>
>>
>>
>>
>>
>> "Fedias Papadopoulos" <Fedias Papadopoulos@.discussions.microsoft.com>
>> wrote
>> in message news:D2A26087-0483-4473-B4DB-3E79BAE05FE4@.microsoft.com...
>> > We have an sql database which is about 50GB. Everyday I do 2 different
>> > backups from 2 different programs. The first backup is a full backup
>> > scheduled in sql and runs at 8pm. The second backup is online backup
>> > and
>> > runs
>> > at 7 pm from Monday to Thursday as defferential and every Friday as
>> > full.
>> > My
>> > question is: Because of the daily full backup, is my differential
>> > backup
>> > correct or shall I stop the local daily full?
>>|||In order to restore a diff backup, you need to first restore *the most recent* full backup. In 2005,
you have a new option, though. Check out the COPY_ONLY option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Fedias Papadopoulos" <Fedias Papadopoulos@.discussions.microsoft.com> wrote in message
news:D2A26087-0483-4473-B4DB-3E79BAE05FE4@.microsoft.com...
> We have an sql database which is about 50GB. Everyday I do 2 different
> backups from 2 different programs. The first backup is a full backup
> scheduled in sql and runs at 8pm. The second backup is online backup and runs
> at 7 pm from Monday to Thursday as defferential and every Friday as full. My
> question is: Because of the daily full backup, is my differential backup
> correct or shall I stop the local daily full?
backups from 2 different programs. The first backup is a full backup
scheduled in sql and runs at 8pm. The second backup is online backup and runs
at 7 pm from Monday to Thursday as defferential and every Friday as full. My
question is: Because of the daily full backup, is my differential backup
correct or shall I stop the local daily full?Fedias
Your DB is pretty big, so I'd start with FULL backup once a day and
DIFFERENTIAL backup starting from 6AM till 22PM every two/three hours.
Remmeber that DIFF backup contains all data since last FULL backup , thus
every DIFF backup will get bigger than previous one.
Another strategy you might want to create a LOG backup without doing DIFF
one. It gives possibility to recover your dtabase at point of time in cas
of crashing/
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
"Fedias Papadopoulos" <Fedias Papadopoulos@.discussions.microsoft.com> wrote
in message news:D2A26087-0483-4473-B4DB-3E79BAE05FE4@.microsoft.com...
> We have an sql database which is about 50GB. Everyday I do 2 different
> backups from 2 different programs. The first backup is a full backup
> scheduled in sql and runs at 8pm. The second backup is online backup and
> runs
> at 7 pm from Monday to Thursday as defferential and every Friday as full.
> My
> question is: Because of the daily full backup, is my differential backup
> correct or shall I stop the local daily full?|||Thanks Uri,
Is very difficult to make daily full backup using my online backup solution
(www.cybackup.com) because the compressed full backup is about 6.5gb and
takes about 30 hours to go offsite. This solution makes only full and
differential backups. So we do daily dif backups and every friday a full. So
my wonder is, does the daily second full backup we do from sql affects the
dif backup of the online backup
"Uri Dimant" wrote:
> Fedias
> Your DB is pretty big, so I'd start with FULL backup once a day and
> DIFFERENTIAL backup starting from 6AM till 22PM every two/three hours.
> Remmeber that DIFF backup contains all data since last FULL backup , thus
> every DIFF backup will get bigger than previous one.
> Another strategy you might want to create a LOG backup without doing DIFF
> one. It gives possibility to recover your dtabase at point of time in cas
> of crashing/
> http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> --administaiting best practices
>
>
>
>
>
>
>
>
> "Fedias Papadopoulos" <Fedias Papadopoulos@.discussions.microsoft.com> wrote
> in message news:D2A26087-0483-4473-B4DB-3E79BAE05FE4@.microsoft.com...
> > We have an sql database which is about 50GB. Everyday I do 2 different
> > backups from 2 different programs. The first backup is a full backup
> > scheduled in sql and runs at 8pm. The second backup is online backup and
> > runs
> > at 7 pm from Monday to Thursday as defferential and every Friday as full.
> > My
> > question is: Because of the daily full backup, is my differential backup
> > correct or shall I stop the local daily full?
>
>|||Fedias
Yes it does
"Fedias Papadopoulos" <FediasPapadopoulos@.discussions.microsoft.com> wrote
in message news:46AC2224-4B54-413C-A9B9-00C85968C793@.microsoft.com...
> Thanks Uri,
> Is very difficult to make daily full backup using my online backup
> solution
> (www.cybackup.com) because the compressed full backup is about 6.5gb and
> takes about 30 hours to go offsite. This solution makes only full and
> differential backups. So we do daily dif backups and every friday a full.
> So
> my wonder is, does the daily second full backup we do from sql affects the
> dif backup of the online backup
> "Uri Dimant" wrote:
>> Fedias
>> Your DB is pretty big, so I'd start with FULL backup once a day and
>> DIFFERENTIAL backup starting from 6AM till 22PM every two/three hours.
>> Remmeber that DIFF backup contains all data since last FULL backup ,
>> thus
>> every DIFF backup will get bigger than previous one.
>> Another strategy you might want to create a LOG backup without doing
>> DIFF
>> one. It gives possibility to recover your dtabase at point of time in
>> cas
>> of crashing/
>> http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
>> --administaiting best practices
>>
>>
>>
>>
>>
>>
>>
>>
>> "Fedias Papadopoulos" <Fedias Papadopoulos@.discussions.microsoft.com>
>> wrote
>> in message news:D2A26087-0483-4473-B4DB-3E79BAE05FE4@.microsoft.com...
>> > We have an sql database which is about 50GB. Everyday I do 2 different
>> > backups from 2 different programs. The first backup is a full backup
>> > scheduled in sql and runs at 8pm. The second backup is online backup
>> > and
>> > runs
>> > at 7 pm from Monday to Thursday as defferential and every Friday as
>> > full.
>> > My
>> > question is: Because of the daily full backup, is my differential
>> > backup
>> > correct or shall I stop the local daily full?
>>|||In order to restore a diff backup, you need to first restore *the most recent* full backup. In 2005,
you have a new option, though. Check out the COPY_ONLY option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Fedias Papadopoulos" <Fedias Papadopoulos@.discussions.microsoft.com> wrote in message
news:D2A26087-0483-4473-B4DB-3E79BAE05FE4@.microsoft.com...
> We have an sql database which is about 50GB. Everyday I do 2 different
> backups from 2 different programs. The first backup is a full backup
> scheduled in sql and runs at 8pm. The second backup is online backup and runs
> at 7 pm from Monday to Thursday as defferential and every Friday as full. My
> question is: Because of the daily full backup, is my differential backup
> correct or shall I stop the local daily full?
Differential Backup
I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK
than the full DB
file each time? If so
up the WITH FORMAT
message
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...[QUOTE]
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
>
> than the full DB
> file each time? If so
> up the WITH FORMAT
> message
> in
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
quote:|||
> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>
quote:
>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>
Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.
quote:|||Mike:
>AK
>
>.
>
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK
quote:
>--Original Message--
>I wouldn't figure the differential should ever be larger
than the full DB
quote:
>backup. Is the differential backup writing to the same
file each time? If so
quote:
>then you're probably adding to the file each time, look
up the WITH FORMAT
quote:
>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||How would I NOT append and rather overwrite?
>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
in[QUOTE]
>
>.
>
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
quote:|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
>--Original Message--
>
in[QUOTE]
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>.
>
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...[QUOTE]
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
>
> than the full DB
> file each time? If so
> up the WITH FORMAT
> message
> in
Differential and Transaction backup restore.
Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
Steve
Why are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>
|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent[vbcol=seagreen]
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
restore
>
|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WITH NO_LOG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transaction
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> you
> kicked
> it
> recent
> restore
>
|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>
|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
am[vbcol=seagreen]
Are[vbcol=seagreen]
last
>
|||Shrink doesn't break the log backup sequence. This assumes, of course that EM doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> 8am,
> in
> am
> Are
> last
>
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
Steve
Why are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>
|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent[vbcol=seagreen]
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
restore
>
|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WITH NO_LOG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transaction
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> you
> kicked
> it
> recent
> restore
>
|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>
|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
am[vbcol=seagreen]
Are[vbcol=seagreen]
last
>
|||Shrink doesn't break the log backup sequence. This assumes, of course that EM doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> 8am,
> in
> am
> Are
> last
>
Differential and Transaction backup restore.
Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
SteveWhy are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
restore[vbcol=seagreen]
>|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WIT
H NO_LOG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transactio
n
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> you
> kicked
> it
> recent
> restore
>|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
am[vbcol=seagreen]
Are[vbcol=seagreen]
last[vbcol=seagreen]
>|||Shrink doesn't break the log backup sequence. This assumes, of course that E
M doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> 8am,
> in
> am
> Are
> last
>
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
SteveWhy are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
restore[vbcol=seagreen]
>|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WIT
H NO_LOG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transactio
n
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> you
> kicked
> it
> recent
> restore
>|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
am[vbcol=seagreen]
Are[vbcol=seagreen]
last[vbcol=seagreen]
>|||Shrink doesn't break the log backup sequence. This assumes, of course that E
M doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> 8am,
> in
> am
> Are
> last
>
Differential and Transaction backup restore.
Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
SteveWhy are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
--
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> > Differential and Transaction backup restore.
> >
> >
> >
> > I have scheduled differential backup to run at 3PM and the transactional
> > backup to run at 3pm. In the event of data restore work, should I
restore
> > the data from differential as well as transactional backups.
> >
> > My questions, is it required to run data restore from the 3Pm
> > transactional
> > backup?
> >
> > Thanks,
> >
> > Steve
> >
> >
>|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WITH NO_LOG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transaction
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> Why are you doing a Differential and a Log backup at the same time? Are
> you
>> only issuing one Log backup during the entire day? Depending on which
> kicked
>> in first or finished first and if there was any activity during that time
> it
>> could be either one or both. Transaction logs keep a running chain of all
>> transactions since the first FULL backup assuming you never truncated the
>> log outside of a log backup. The Diff has all the changes since the last
>> FULL backup. You always apply the last FULL backup and then the most
> recent
>> Diff if you have one. Then any logs since the last Diff. Since it is
>> imposable to tell the real order from this post it is hard to say if
>> restoring the log would actually do anything or not in regards to
>> transactions that the Diff may not have caught. If you attempt to restore
>> the log and it is out of sequence it will warn you. I suggest you stagger
>> your backups and ensure you have a proper schedule of Log backups to cover
>> your needs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> > Differential and Transaction backup restore.
>> >
>> >
>> >
>> > I have scheduled differential backup to run at 3PM and the transactional
>> > backup to run at 3pm. In the event of data restore work, should I
> restore
>> > the data from differential as well as transactional backups.
>> >
>> > My questions, is it required to run data restore from the 3Pm
>> > transactional
>> > backup?
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> >
>>
>|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>>I will rethink bout my backup scheduling. Thanks for the suggestions.
>>
>> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
>> truncated the log file. SQL Server crashes at 10am, then all my
>> transaction
>> log files are not restorable since 9.00 onwards. Is it correct?
>> Steve
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> Why are you doing a Differential and a Log backup at the same time? Are
>> you
>> only issuing one Log backup during the entire day? Depending on which
>> kicked
>> in first or finished first and if there was any activity during that
>> time
>> it
>> could be either one or both. Transaction logs keep a running chain of
>> all
>> transactions since the first FULL backup assuming you never truncated
>> the
>> log outside of a log backup. The Diff has all the changes since the last
>> FULL backup. You always apply the last FULL backup and then the most
>> recent
>> Diff if you have one. Then any logs since the last Diff. Since it is
>> imposable to tell the real order from this post it is hard to say if
>> restoring the log would actually do anything or not in regards to
>> transactions that the Diff may not have caught. If you attempt to
>> restore
>> the log and it is out of sequence it will warn you. I suggest you
>> stagger
>> your backups and ensure you have a proper schedule of Log backups to
>> cover
>> your needs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> > Differential and Transaction backup restore.
>> >
>> >
>> >
>> > I have scheduled differential backup to run at 3PM and the
>> > transactional
>> > backup to run at 3pm. In the event of data restore work, should I
>> restore
>> > the data from differential as well as transactional backups.
>> >
>> > My questions, is it required to run data restore from the 3Pm
>> > transactional
>> > backup?
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> >
>>
>>
>|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> > WITH NO_LOG?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> > news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
> >>I will rethink bout my backup scheduling. Thanks for the suggestions.
> >>
> >>
> >>
> >> By the way, I have taken one complete backup at 8 am, some one at 9.00
am
> >> truncated the log file. SQL Server crashes at 10am, then all my
> >> transaction
> >> log files are not restorable since 9.00 onwards. Is it correct?
> >>
> >> Steve
> >>
> >>
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> >> Why are you doing a Differential and a Log backup at the same time?
Are
> >> you
> >> only issuing one Log backup during the entire day? Depending on which
> >> kicked
> >> in first or finished first and if there was any activity during that
> >> time
> >> it
> >> could be either one or both. Transaction logs keep a running chain of
> >> all
> >> transactions since the first FULL backup assuming you never truncated
> >> the
> >> log outside of a log backup. The Diff has all the changes since the
last
> >> FULL backup. You always apply the last FULL backup and then the most
> >> recent
> >> Diff if you have one. Then any logs since the last Diff. Since it is
> >> imposable to tell the real order from this post it is hard to say if
> >> restoring the log would actually do anything or not in regards to
> >> transactions that the Diff may not have caught. If you attempt to
> >> restore
> >> the log and it is out of sequence it will warn you. I suggest you
> >> stagger
> >> your backups and ensure you have a proper schedule of Log backups to
> >> cover
> >> your needs.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> >> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> >> > Differential and Transaction backup restore.
> >> >
> >> >
> >> >
> >> > I have scheduled differential backup to run at 3PM and the
> >> > transactional
> >> > backup to run at 3pm. In the event of data restore work, should I
> >> restore
> >> > the data from differential as well as transactional backups.
> >> >
> >> > My questions, is it required to run data restore from the 3Pm
> >> > transactional
> >> > backup?
> >> >
> >> > Thanks,
> >> >
> >> > Steve
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>|||Shrink doesn't break the log backup sequence. This assumes, of course that EM doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
>> If someone actually truncates the log at 9am, you can only restore til
> 8am,
>> as you suspect.
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
>> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
>> > WITH NO_LOG?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> > news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>> >>I will rethink bout my backup scheduling. Thanks for the suggestions.
>> >>
>> >>
>> >>
>> >> By the way, I have taken one complete backup at 8 am, some one at 9.00
> am
>> >> truncated the log file. SQL Server crashes at 10am, then all my
>> >> transaction
>> >> log files are not restorable since 9.00 onwards. Is it correct?
>> >>
>> >> Steve
>> >>
>> >>
>> >>
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> >> Why are you doing a Differential and a Log backup at the same time?
> Are
>> >> you
>> >> only issuing one Log backup during the entire day? Depending on which
>> >> kicked
>> >> in first or finished first and if there was any activity during that
>> >> time
>> >> it
>> >> could be either one or both. Transaction logs keep a running chain of
>> >> all
>> >> transactions since the first FULL backup assuming you never truncated
>> >> the
>> >> log outside of a log backup. The Diff has all the changes since the
> last
>> >> FULL backup. You always apply the last FULL backup and then the most
>> >> recent
>> >> Diff if you have one. Then any logs since the last Diff. Since it is
>> >> imposable to tell the real order from this post it is hard to say if
>> >> restoring the log would actually do anything or not in regards to
>> >> transactions that the Diff may not have caught. If you attempt to
>> >> restore
>> >> the log and it is out of sequence it will warn you. I suggest you
>> >> stagger
>> >> your backups and ensure you have a proper schedule of Log backups to
>> >> cover
>> >> your needs.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> >> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> >> > Differential and Transaction backup restore.
>> >> >
>> >> >
>> >> >
>> >> > I have scheduled differential backup to run at 3PM and the
>> >> > transactional
>> >> > backup to run at 3pm. In the event of data restore work, should I
>> >> restore
>> >> > the data from differential as well as transactional backups.
>> >> >
>> >> > My questions, is it required to run data restore from the 3Pm
>> >> > transactional
>> >> > backup?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Steve
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
SteveWhy are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
--
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> > Differential and Transaction backup restore.
> >
> >
> >
> > I have scheduled differential backup to run at 3PM and the transactional
> > backup to run at 3pm. In the event of data restore work, should I
restore
> > the data from differential as well as transactional backups.
> >
> > My questions, is it required to run data restore from the 3Pm
> > transactional
> > backup?
> >
> > Thanks,
> >
> > Steve
> >
> >
>|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WITH NO_LOG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transaction
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> Why are you doing a Differential and a Log backup at the same time? Are
> you
>> only issuing one Log backup during the entire day? Depending on which
> kicked
>> in first or finished first and if there was any activity during that time
> it
>> could be either one or both. Transaction logs keep a running chain of all
>> transactions since the first FULL backup assuming you never truncated the
>> log outside of a log backup. The Diff has all the changes since the last
>> FULL backup. You always apply the last FULL backup and then the most
> recent
>> Diff if you have one. Then any logs since the last Diff. Since it is
>> imposable to tell the real order from this post it is hard to say if
>> restoring the log would actually do anything or not in regards to
>> transactions that the Diff may not have caught. If you attempt to restore
>> the log and it is out of sequence it will warn you. I suggest you stagger
>> your backups and ensure you have a proper schedule of Log backups to cover
>> your needs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> > Differential and Transaction backup restore.
>> >
>> >
>> >
>> > I have scheduled differential backup to run at 3PM and the transactional
>> > backup to run at 3pm. In the event of data restore work, should I
> restore
>> > the data from differential as well as transactional backups.
>> >
>> > My questions, is it required to run data restore from the 3Pm
>> > transactional
>> > backup?
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> >
>>
>|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>>I will rethink bout my backup scheduling. Thanks for the suggestions.
>>
>> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
>> truncated the log file. SQL Server crashes at 10am, then all my
>> transaction
>> log files are not restorable since 9.00 onwards. Is it correct?
>> Steve
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> Why are you doing a Differential and a Log backup at the same time? Are
>> you
>> only issuing one Log backup during the entire day? Depending on which
>> kicked
>> in first or finished first and if there was any activity during that
>> time
>> it
>> could be either one or both. Transaction logs keep a running chain of
>> all
>> transactions since the first FULL backup assuming you never truncated
>> the
>> log outside of a log backup. The Diff has all the changes since the last
>> FULL backup. You always apply the last FULL backup and then the most
>> recent
>> Diff if you have one. Then any logs since the last Diff. Since it is
>> imposable to tell the real order from this post it is hard to say if
>> restoring the log would actually do anything or not in regards to
>> transactions that the Diff may not have caught. If you attempt to
>> restore
>> the log and it is out of sequence it will warn you. I suggest you
>> stagger
>> your backups and ensure you have a proper schedule of Log backups to
>> cover
>> your needs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> > Differential and Transaction backup restore.
>> >
>> >
>> >
>> > I have scheduled differential backup to run at 3PM and the
>> > transactional
>> > backup to run at 3pm. In the event of data restore work, should I
>> restore
>> > the data from differential as well as transactional backups.
>> >
>> > My questions, is it required to run data restore from the 3Pm
>> > transactional
>> > backup?
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> >
>>
>>
>|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> > WITH NO_LOG?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> > news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
> >>I will rethink bout my backup scheduling. Thanks for the suggestions.
> >>
> >>
> >>
> >> By the way, I have taken one complete backup at 8 am, some one at 9.00
am
> >> truncated the log file. SQL Server crashes at 10am, then all my
> >> transaction
> >> log files are not restorable since 9.00 onwards. Is it correct?
> >>
> >> Steve
> >>
> >>
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> >> Why are you doing a Differential and a Log backup at the same time?
Are
> >> you
> >> only issuing one Log backup during the entire day? Depending on which
> >> kicked
> >> in first or finished first and if there was any activity during that
> >> time
> >> it
> >> could be either one or both. Transaction logs keep a running chain of
> >> all
> >> transactions since the first FULL backup assuming you never truncated
> >> the
> >> log outside of a log backup. The Diff has all the changes since the
last
> >> FULL backup. You always apply the last FULL backup and then the most
> >> recent
> >> Diff if you have one. Then any logs since the last Diff. Since it is
> >> imposable to tell the real order from this post it is hard to say if
> >> restoring the log would actually do anything or not in regards to
> >> transactions that the Diff may not have caught. If you attempt to
> >> restore
> >> the log and it is out of sequence it will warn you. I suggest you
> >> stagger
> >> your backups and ensure you have a proper schedule of Log backups to
> >> cover
> >> your needs.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> >> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> >> > Differential and Transaction backup restore.
> >> >
> >> >
> >> >
> >> > I have scheduled differential backup to run at 3PM and the
> >> > transactional
> >> > backup to run at 3pm. In the event of data restore work, should I
> >> restore
> >> > the data from differential as well as transactional backups.
> >> >
> >> > My questions, is it required to run data restore from the 3Pm
> >> > transactional
> >> > backup?
> >> >
> >> > Thanks,
> >> >
> >> > Steve
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>|||Shrink doesn't break the log backup sequence. This assumes, of course that EM doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
>> If someone actually truncates the log at 9am, you can only restore til
> 8am,
>> as you suspect.
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
>> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
>> > WITH NO_LOG?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> > news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>> >>I will rethink bout my backup scheduling. Thanks for the suggestions.
>> >>
>> >>
>> >>
>> >> By the way, I have taken one complete backup at 8 am, some one at 9.00
> am
>> >> truncated the log file. SQL Server crashes at 10am, then all my
>> >> transaction
>> >> log files are not restorable since 9.00 onwards. Is it correct?
>> >>
>> >> Steve
>> >>
>> >>
>> >>
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> >> Why are you doing a Differential and a Log backup at the same time?
> Are
>> >> you
>> >> only issuing one Log backup during the entire day? Depending on which
>> >> kicked
>> >> in first or finished first and if there was any activity during that
>> >> time
>> >> it
>> >> could be either one or both. Transaction logs keep a running chain of
>> >> all
>> >> transactions since the first FULL backup assuming you never truncated
>> >> the
>> >> log outside of a log backup. The Diff has all the changes since the
> last
>> >> FULL backup. You always apply the last FULL backup and then the most
>> >> recent
>> >> Diff if you have one. Then any logs since the last Diff. Since it is
>> >> imposable to tell the real order from this post it is hard to say if
>> >> restoring the log would actually do anything or not in regards to
>> >> transactions that the Diff may not have caught. If you attempt to
>> >> restore
>> >> the log and it is out of sequence it will warn you. I suggest you
>> >> stagger
>> >> your backups and ensure you have a proper schedule of Log backups to
>> >> cover
>> >> your needs.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> >> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> >> > Differential and Transaction backup restore.
>> >> >
>> >> >
>> >> >
>> >> > I have scheduled differential backup to run at 3PM and the
>> >> > transactional
>> >> > backup to run at 3pm. In the event of data restore work, should I
>> >> restore
>> >> > the data from differential as well as transactional backups.
>> >> >
>> >> > My questions, is it required to run data restore from the 3Pm
>> >> > transactional
>> >> > backup?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Steve
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>
Different versions of SSMS?
Hi.
Does it exist different versions of SSMS?
I'm now working with SSMS Express and SQL Server 2005 Express. How can I
make a scheduled backup from SSMS?
___________________________
Best regards - Anders
Sql Server Express does not include a SQL Agent, so you will have to
use an external solution for scheduling your backups. My suggestion
would be to run a script with SQLCMD on a scheduled basis (e.g. with
the at command).
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Or use below two:
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180341388.388646.126450@.u30g2000hsc.googlegr oups.com...
> Sql Server Express does not include a SQL Agent, so you will have to
> use an external solution for scheduling your backups. My suggestion
> would be to run a script with SQLCMD on a scheduled basis (e.g. with
> the at command).
> Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
Does it exist different versions of SSMS?
I'm now working with SSMS Express and SQL Server 2005 Express. How can I
make a scheduled backup from SSMS?
___________________________
Best regards - Anders
Sql Server Express does not include a SQL Agent, so you will have to
use an external solution for scheduling your backups. My suggestion
would be to run a script with SQLCMD on a scheduled basis (e.g. with
the at command).
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Or use below two:
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180341388.388646.126450@.u30g2000hsc.googlegr oups.com...
> Sql Server Express does not include a SQL Agent, so you will have to
> use an external solution for scheduling your backups. My suggestion
> would be to run a script with SQLCMD on a scheduled basis (e.g. with
> the at command).
> Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
Wednesday, March 7, 2012
Different Schema
Hi Is it possible to use SQL 2000 replication with two databases that
have different schema.
At the momenet I am using scheduled scripts to create export files ,
ftp them and then import them, its complex and I need a better way.
Thanks.
Yes - you could use transactional replication with
(a) Transformable subscriptions (DTS integration)
(b) Indexed Views on the publisher
(c) a custom sync object on the publisher
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks - in terms of failover and recovery if either server goes down
or if theres poor connectivity , is a) or c) the better solution ?
Paul Ibison wrote:
> Yes - you could use transactional replication with
> (a) Transformable subscriptions (DTS integration)
> (b) Indexed Views on the publisher
> (c) a custom sync object on the publisher
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||(c) would be less strain on the publisher's transactions and holds more
support going forward to new versions of SQl Server.
However I'm a little confused - in terms of failover it is very strange to
change the schema - normally the apps running on the publisher require the
same tables/fields on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
have different schema.
At the momenet I am using scheduled scripts to create export files ,
ftp them and then import them, its complex and I need a better way.
Thanks.
Yes - you could use transactional replication with
(a) Transformable subscriptions (DTS integration)
(b) Indexed Views on the publisher
(c) a custom sync object on the publisher
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks - in terms of failover and recovery if either server goes down
or if theres poor connectivity , is a) or c) the better solution ?
Paul Ibison wrote:
> Yes - you could use transactional replication with
> (a) Transformable subscriptions (DTS integration)
> (b) Indexed Views on the publisher
> (c) a custom sync object on the publisher
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||(c) would be less strain on the publisher's transactions and holds more
support going forward to new versions of SQl Server.
However I'm a little confused - in terms of failover it is very strange to
change the schema - normally the apps running on the publisher require the
same tables/fields on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Subscribe to:
Posts (Atom)