Can not change index INCLUDE fields order....

Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch

Can not change index INCLUDE fields order....

Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], &
#91;Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], &
#91;Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:

> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] ha
d
been dropped and re-created at some point, although my tests on a SP2 machin
e
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch

Can not change index INCLUDE fields order....

Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
Forch
Hi
"Forch" wrote:

> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John

Can no longer open my SSIS packages

I am in the process of migrating DTS 2000 packages via DTS Migration Wizard.

I ran all of the packages through the wizard. Then, I successfully completed changing the remaining issues in two of my packages to SSIS. Everything was fine. The second package I completed is called from a SQL Agent Job. I managed to get the job step converted to use SSIS package. It ran successfully.

Now, I am trying to open other packages to work on them and I get "Microsoft Visual Studio is unable to load this document: Value does not fall within the expected range." and a message window that says "Object reference not set to an instance of an object." This actually happens with the two that I migrated, also. I can no longer access them.

What happened?!?!?!? Does anyone have any experience with this?

Well, the solution I had the files in must have been corrupted. I can open individual files using BIDS. I just can't do anything with them from the project. Seems as though a collection might have gone willy.

Can Lazy Processing process partitions in parallel ?

Hi,
we are processing a large number od small(ish) partitions, and I was
wondering if using Lazy Processing would process the partitions in parallel.
The partitions are built in serial. If Lazy Processing is set, would AS2000
process each partition in turn on the background thread? Or would it process
multiple partitons in parallel, making use of all available CPUs?
The partitions are being processed rather than the Cube because not all the
partitions need to be updated.
Thanks in advance.
To process partitions in parrallel you can use utility like this one:
http://www.microsoft.com/downloads/d...DisplayLang=en
Edward.
This posting is provided "AS IS" with no warranties, and confers no rights
"Al" <Al@.discussions.microsoft.com> wrote in message
news:9B6065A9-CCB5-42DA-B82D-263DCE45E683@.microsoft.com...
> Hi,
> we are processing a large number od small(ish) partitions, and I was
> wondering if using Lazy Processing would process the partitions in
> parallel.
> The partitions are built in serial. If Lazy Processing is set, would
> AS2000
> process each partition in turn on the background thread? Or would it
> process
> multiple partitons in parallel, making use of all available CPUs?
> The partitions are being processed rather than the Cube because not all
> the
> partitions need to be updated.
> Thanks in advance.
|||the question is about the lazy aggregation process ;-)
this process is a background process which evaluate each partition 1 after
1.
you can't change this behavior. so there is no parrallel lazy aggregation
process.
"Edward Melomed [MSFT]" <edwardm@.norely.micrsoft.com> wrote in message
news:OYNArBlMGHA.2992@.tk2msftngp13.phx.gbl...
> To process partitions in parrallel you can use utility like this one:
> http://www.microsoft.com/downloads/d...DisplayLang=en
> Edward.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:9B6065A9-CCB5-42DA-B82D-263DCE45E683@.microsoft.com...
>

Can Lazy Processing process partitions in parallel ?

Hi,
we are processing a large number od small(ish) partitions, and I was
wondering if using Lazy Processing would process the partitions in parallel.
The partitions are built in serial. If Lazy Processing is set, would AS2000
process each partition in turn on the background thread? Or would it process
multiple partitons in parallel, making use of all available CPUs?
The partitions are being processed rather than the Cube because not all the
partitions need to be updated.
Thanks in advance.To process partitions in parrallel you can use utility like this one:
http://www.microsoft.com/downloads/...&DisplayLang=en
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Al" <Al@.discussions.microsoft.com> wrote in message
news:9B6065A9-CCB5-42DA-B82D-263DCE45E683@.microsoft.com...
> Hi,
> we are processing a large number od small(ish) partitions, and I was
> wondering if using Lazy Processing would process the partitions in
> parallel.
> The partitions are built in serial. If Lazy Processing is set, would
> AS2000
> process each partition in turn on the background thread? Or would it
> process
> multiple partitons in parallel, making use of all available CPUs?
> The partitions are being processed rather than the Cube because not all
> the
> partitions need to be updated.
> Thanks in advance.|||the question is about the lazy aggregation process ;-)
this process is a background process which evaluate each partition 1 after
1.
you can't change this behavior. so there is no parrallel lazy aggregation
process.
"Edward Melomed [MSFT]" <edwardm@.norely.micrsoft.com> wrote in message
news:OYNArBlMGHA.2992@.tk2msftngp13.phx.gbl...
> To process partitions in parrallel you can use utility like this one:
> http://www.microsoft.com/downloads/...&DisplayLang=en
> Edward.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:9B6065A9-CCB5-42DA-B82D-263DCE45E683@.microsoft.com...
>

Can Kill Command undo changes outwith a transaction?

In SQL 2000 I have a problem whereby it appears that a process successfully
saved data (inserts followed by updates), hung, and when I killed the proces
s
some of the data disapeared. I had understood that only declared transaction
s
would be rolled back, but in this case it appears that untransacted changes
were undone.
When I say that the process successfully saved data, the data was read from
the database in a separate program (Crystal Reports) and was printed out.
By "the data disappeared", I mean that the inserted rows are no longer in
the database, and the rows that were updated (according to the hard copy) ar
e
in a previous state. The tables involved in this have insert/update/delete
triggers that record all changes; they do not record the changes that are
involved, and have been working without fault for several months.
The updates were grouped in transactions, whereas the inserts were not.
There was no over-all transaction.
Sure, at first glance you might think it's just a transaction that did not
complete and that I'm overlooking something, however I have the print out of
the missing data to show the data was in there.
Can someone confirm or refute that my interpretation of what happened is
possible, or suggest an alternative explanation?My guess is the report was run under the Read Uncommitted isolation level
(or used NOLOCK). As such it would see the changes if run while the
transaction was in progress and they would have been rolled back when you
killed it.
Andrew J. Kelly SQL MVP
"woodk" <woodk@.discussions.microsoft.com> wrote in message
news:F6E9C8E0-BD1C-49BF-BDB8-094BFC68C964@.microsoft.com...
> In SQL 2000 I have a problem whereby it appears that a process
> successfully
> saved data (inserts followed by updates), hung, and when I killed the
> process
> some of the data disapeared. I had understood that only declared
> transactions
> would be rolled back, but in this case it appears that untransacted
> changes
> were undone.
> When I say that the process successfully saved data, the data was read
> from
> the database in a separate program (Crystal Reports) and was printed out.
> By "the data disappeared", I mean that the inserted rows are no longer in
> the database, and the rows that were updated (according to the hard copy)
> are
> in a previous state. The tables involved in this have insert/update/delete
> triggers that record all changes; they do not record the changes that are
> involved, and have been working without fault for several months.
> The updates were grouped in transactions, whereas the inserts were not.
> There was no over-all transaction.
> Sure, at first glance you might think it's just a transaction that did not
> complete and that I'm overlooking something, however I have the print out
> of
> the missing data to show the data was in there.
> Can someone confirm or refute that my interpretation of what happened is
> possible, or suggest an alternative explanation?|||Thank you for your suggestion. The operations were not wrapped in a single
transaction, and some of them should have been atomic in their own right.
However as we've found an indication that Crystal does indeed read
uncommitted, we're investigating the possibility that an earlier transaction
by that user may not have been properly committed. If this is the case, it
may be that this was also what led to the situation where I had to issue the
Kill command in the first place.
"Andrew J. Kelly" wrote:

> My guess is the report was run under the Read Uncommitted isolation level
> (or used NOLOCK). As such it would see the changes if run while the
> transaction was in progress and they would have been rolled back when you
> killed it.
> --
> Andrew J. Kelly SQL MVP

Can Incremental Backup be done with Simple recovery Model?

For SQL2000 - Just wondering if the incremental backup requires transaction
logs.
We have a database upgrade process that performs a lot of updates. In order
to keep the transaction log from growing out of control - I would like to be
able to set the recovery model as "simple". I am hoping that I could do the
following:
1. Perform a full DB backup
2. Set the recovery model to Simple
3. Run the upgrade
4. Set the recovery model back to Full
5. Run an incremental backup
Does this sound reasonable? Should steps 4 and 5 be reversed?
Thanks in advance.
No, Incremental backups (which are called transaction log backups in SQL server) are based on the
transaction log. If you are missing log records in the transaction log, you wouldn't be able to
restore from the transaction log backups.
Consider differential backups, perhaps?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - Just wondering if the incremental backup requires transaction
> logs.
> We have a database upgrade process that performs a lot of updates. In order
> to keep the transaction log from growing out of control - I would like to be
> able to set the recovery model as "simple". I am hoping that I could do the
> following:
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run an incremental backup
> Does this sound reasonable? Should steps 4 and 5 be reversed?
> Thanks in advance.
>
|||"TJT" <TJT@.nospam.com> wrote in message
news:OWT5nJ#eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - Just wondering if the incremental backup requires
transaction
> logs.
> We have a database upgrade process that performs a lot of updates. In
order
> to keep the transaction log from growing out of control - I would like to
be
> able to set the recovery model as "simple". I am hoping that I could do
the
> following:
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run an incremental backup
> Does this sound reasonable? Should steps 4 and 5 be reversed?
> Thanks in advance.
>
If this is a one-time upgrade, I would suggest the following...
1. Perform Full DB Backup
2. Set to Simple
3. Run upgrade
4. Perform Full DB Backup
5. Test your upgraded information. If all is OK
6. Set recovery back to full.
I'm not sure why you need the incrementals.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi Tibor,
Actually - I really meant to ask the question about Differential backups
(and not Incremental). I just had my terminology messed up.
So - would I be able to do this for Differential backups...
1. Perform a full DB backup
2. Set the recovery model to Simple
3. Run the upgrade
4. Set the recovery model back to Full
5. Run a differential backup
Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4zfMN%23eFHA.3012@.tk2msftngp13.phx.gbl...
> No, Incremental backups (which are called transaction log backups in SQL
server) are based on the
> transaction log. If you are missing log records in the transaction log,
you wouldn't be able to
> restore from the transaction log backups.
> Consider differential backups, perhaps?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "TJT" <TJT@.nospam.com> wrote in message
news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
transaction[vbcol=seagreen]
order[vbcol=seagreen]
to be[vbcol=seagreen]
the
>
|||Hi Rick,
The database is rather large (90GB) and we are trying to minimize downtime.
This is why I was hoping to perform a differential backup.
If the database were small and downtime were not a problem - I would perform
another Full backup after the upgrade as you suggest.
Thanks,
Tom
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uvuhVO%23eFHA.1448@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> "TJT" <TJT@.nospam.com> wrote in message
> news:OWT5nJ#eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> transaction
> order
to
> be
> the
> If this is a one-time upgrade, I would suggest the following...
> 1. Perform Full DB Backup
> 2. Set to Simple
> 3. Run upgrade
> 4. Perform Full DB Backup
> 5. Test your upgraded information. If all is OK
> 6. Set recovery back to full.
>
> I'm not sure why you need the incrementals.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Yes, that should work. Just test on a test server that the steps are fine first (possibly with less
data volume). Also, be aware that you have now broken your chains of log backups. The log backup
taken after this will not be able to apply after a log backup taken before all this. Only from the
last db backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:eySKSR%23eFHA.2244@.TK2MSFTNGP15.phx.gbl...
> Hi Tibor,
> Actually - I really meant to ask the question about Differential backups
> (and not Incremental). I just had my terminology messed up.
> So - would I be able to do this for Differential backups...
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run a differential backup
> Thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u4zfMN%23eFHA.3012@.tk2msftngp13.phx.gbl...
> server) are based on the
> you wouldn't be able to
> news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> transaction
> order
> to be
> the
>
|||I can backup 90GB in just a few minutes using no third party tools
How long is "Too Long" ?
What kind of IO subSystem do you have ? (RAID Array, SAN, NAS)
Greg Jackson
PDX, Oregon

Can Incremental Backup be done with Simple recovery Model?

For SQL2000 - Just wondering if the incremental backup requires transaction
logs.
We have a database upgrade process that performs a lot of updates. In order
to keep the transaction log from growing out of control - I would like to be
able to set the recovery model as "simple". I am hoping that I could do the
following:
1. Perform a full DB backup
2. Set the recovery model to Simple
3. Run the upgrade
4. Set the recovery model back to Full
5. Run an incremental backup
Does this sound reasonable? Should steps 4 and 5 be reversed?
Thanks in advance.No, Incremental backups (which are called transaction log backups in SQL ser
ver) are based on the
transaction log. If you are missing log records in the transaction log, you
wouldn't be able to
restore from the transaction log backups.
Consider differential backups, perhaps?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...[vbco
l=seagreen]
> For SQL2000 - Just wondering if the incremental backup requires transactio
n
> logs.
> We have a database upgrade process that performs a lot of updates. In ord
er
> to keep the transaction log from growing out of control - I would like to
be
> able to set the recovery model as "simple". I am hoping that I could do t
he
> following:
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run an incremental backup
> Does this sound reasonable? Should steps 4 and 5 be reversed?
> Thanks in advance.
>[/vbcol]|||"TJT" <TJT@.nospam.com> wrote in message
news:OWT5nJ#eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - Just wondering if the incremental backup requires
transaction
> logs.
> We have a database upgrade process that performs a lot of updates. In
order
> to keep the transaction log from growing out of control - I would like to
be
> able to set the recovery model as "simple". I am hoping that I could do
the
> following:
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run an incremental backup
> Does this sound reasonable? Should steps 4 and 5 be reversed?
> Thanks in advance.
>
If this is a one-time upgrade, I would suggest the following...
1. Perform Full DB Backup
2. Set to Simple
3. Run upgrade
4. Perform Full DB Backup
5. Test your upgraded information. If all is OK
6. Set recovery back to full.
I'm not sure why you need the incrementals.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Tibor,
Actually - I really meant to ask the question about Differential backups
(and not Incremental). I just had my terminology messed up.
So - would I be able to do this for Differential backups...
1. Perform a full DB backup
2. Set the recovery model to Simple
3. Run the upgrade
4. Set the recovery model back to Full
5. Run a differential backup
Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4zfMN%23eFHA.3012@.tk2msftngp13.phx.gbl...
> No, Incremental backups (which are called transaction log backups in SQL
server) are based on the
> transaction log. If you are missing log records in the transaction log,
you wouldn't be able to
> restore from the transaction log backups.
> Consider differential backups, perhaps?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "TJT" <TJT@.nospam.com> wrote in message
news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
transaction[vbcol=seagreen]
order[vbcol=seagreen]
to be[vbcol=seagreen]
the[vbcol=seagreen]
>|||Hi Rick,
The database is rather large (90GB) and we are trying to minimize downtime.
This is why I was hoping to perform a differential backup.
If the database were small and downtime were not a problem - I would perform
another Full backup after the upgrade as you suggest.
Thanks,
Tom
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uvuhVO%23eFHA.1448@.TK2MSFTNGP14.phx.gbl...
> "TJT" <TJT@.nospam.com> wrote in message
> news:OWT5nJ#eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> transaction
> order
to[vbcol=seagreen]
> be
> the
> If this is a one-time upgrade, I would suggest the following...
> 1. Perform Full DB Backup
> 2. Set to Simple
> 3. Run upgrade
> 4. Perform Full DB Backup
> 5. Test your upgraded information. If all is OK
> 6. Set recovery back to full.
>
> I'm not sure why you need the incrementals.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Yes, that should work. Just test on a test server that the steps are fine fi
rst (possibly with less
data volume). Also, be aware that you have now broken your chains of log bac
kups. The log backup
taken after this will not be able to apply after a log backup taken before a
ll this. Only from the
last db backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:eySKSR%23eFHA.2244@.TK2MSFTNGP15.phx.gbl...[vbco
l=seagreen]
> Hi Tibor,
> Actually - I really meant to ask the question about Differential backups
> (and not Incremental). I just had my terminology messed up.
> So - would I be able to do this for Differential backups...
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run a differential backup
> Thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:u4zfMN%23eFHA.3012@.tk2msftngp13.phx.gbl...
> server) are based on the
> you wouldn't be able to
> news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> transaction
> order
> to be
> the
>[/vbcol]|||I can backup 90GB in just a few minutes using no third party tools
How long is "Too Long" ?
What kind of IO subSystem do you have ? (RAID Array, SAN, NAS)
Greg Jackson
PDX, Oregon

Can Incremental Backup be done with Simple recovery Model?

For SQL2000 - Just wondering if the incremental backup requires transaction
logs.
We have a database upgrade process that performs a lot of updates. In order
to keep the transaction log from growing out of control - I would like to be
able to set the recovery model as "simple". I am hoping that I could do the
following:
1. Perform a full DB backup
2. Set the recovery model to Simple
3. Run the upgrade
4. Set the recovery model back to Full
5. Run an incremental backup
Does this sound reasonable? Should steps 4 and 5 be reversed?
Thanks in advance.No, Incremental backups (which are called transaction log backups in SQL server) are based on the
transaction log. If you are missing log records in the transaction log, you wouldn't be able to
restore from the transaction log backups.
Consider differential backups, perhaps?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - Just wondering if the incremental backup requires transaction
> logs.
> We have a database upgrade process that performs a lot of updates. In order
> to keep the transaction log from growing out of control - I would like to be
> able to set the recovery model as "simple". I am hoping that I could do the
> following:
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run an incremental backup
> Does this sound reasonable? Should steps 4 and 5 be reversed?
> Thanks in advance.
>|||"TJT" <TJT@.nospam.com> wrote in message
news:OWT5nJ#eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - Just wondering if the incremental backup requires
transaction
> logs.
> We have a database upgrade process that performs a lot of updates. In
order
> to keep the transaction log from growing out of control - I would like to
be
> able to set the recovery model as "simple". I am hoping that I could do
the
> following:
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run an incremental backup
> Does this sound reasonable? Should steps 4 and 5 be reversed?
> Thanks in advance.
>
If this is a one-time upgrade, I would suggest the following...
1. Perform Full DB Backup
2. Set to Simple
3. Run upgrade
4. Perform Full DB Backup
5. Test your upgraded information. If all is OK
6. Set recovery back to full.
I'm not sure why you need the incrementals.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Tibor,
Actually - I really meant to ask the question about Differential backups
(and not Incremental). I just had my terminology messed up.
So - would I be able to do this for Differential backups...
1. Perform a full DB backup
2. Set the recovery model to Simple
3. Run the upgrade
4. Set the recovery model back to Full
5. Run a differential backup
Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4zfMN%23eFHA.3012@.tk2msftngp13.phx.gbl...
> No, Incremental backups (which are called transaction log backups in SQL
server) are based on the
> transaction log. If you are missing log records in the transaction log,
you wouldn't be able to
> restore from the transaction log backups.
> Consider differential backups, perhaps?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "TJT" <TJT@.nospam.com> wrote in message
news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> > For SQL2000 - Just wondering if the incremental backup requires
transaction
> > logs.
> >
> > We have a database upgrade process that performs a lot of updates. In
order
> > to keep the transaction log from growing out of control - I would like
to be
> > able to set the recovery model as "simple". I am hoping that I could do
the
> > following:
> > 1. Perform a full DB backup
> > 2. Set the recovery model to Simple
> > 3. Run the upgrade
> > 4. Set the recovery model back to Full
> > 5. Run an incremental backup
> >
> > Does this sound reasonable? Should steps 4 and 5 be reversed?
> >
> > Thanks in advance.
> >
> >
>|||Hi Rick,
The database is rather large (90GB) and we are trying to minimize downtime.
This is why I was hoping to perform a differential backup.
If the database were small and downtime were not a problem - I would perform
another Full backup after the upgrade as you suggest.
Thanks,
Tom
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uvuhVO%23eFHA.1448@.TK2MSFTNGP14.phx.gbl...
> "TJT" <TJT@.nospam.com> wrote in message
> news:OWT5nJ#eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> > For SQL2000 - Just wondering if the incremental backup requires
> transaction
> > logs.
> >
> > We have a database upgrade process that performs a lot of updates. In
> order
> > to keep the transaction log from growing out of control - I would like
to
> be
> > able to set the recovery model as "simple". I am hoping that I could do
> the
> > following:
> > 1. Perform a full DB backup
> > 2. Set the recovery model to Simple
> > 3. Run the upgrade
> > 4. Set the recovery model back to Full
> > 5. Run an incremental backup
> >
> > Does this sound reasonable? Should steps 4 and 5 be reversed?
> >
> > Thanks in advance.
> >
> >
> If this is a one-time upgrade, I would suggest the following...
> 1. Perform Full DB Backup
> 2. Set to Simple
> 3. Run upgrade
> 4. Perform Full DB Backup
> 5. Test your upgraded information. If all is OK
> 6. Set recovery back to full.
>
> I'm not sure why you need the incrementals.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Yes, that should work. Just test on a test server that the steps are fine first (possibly with less
data volume). Also, be aware that you have now broken your chains of log backups. The log backup
taken after this will not be able to apply after a log backup taken before all this. Only from the
last db backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:eySKSR%23eFHA.2244@.TK2MSFTNGP15.phx.gbl...
> Hi Tibor,
> Actually - I really meant to ask the question about Differential backups
> (and not Incremental). I just had my terminology messed up.
> So - would I be able to do this for Differential backups...
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run a differential backup
> Thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u4zfMN%23eFHA.3012@.tk2msftngp13.phx.gbl...
>> No, Incremental backups (which are called transaction log backups in SQL
> server) are based on the
>> transaction log. If you are missing log records in the transaction log,
> you wouldn't be able to
>> restore from the transaction log backups.
>> Consider differential backups, perhaps?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "TJT" <TJT@.nospam.com> wrote in message
> news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> > For SQL2000 - Just wondering if the incremental backup requires
> transaction
>> > logs.
>> >
>> > We have a database upgrade process that performs a lot of updates. In
> order
>> > to keep the transaction log from growing out of control - I would like
> to be
>> > able to set the recovery model as "simple". I am hoping that I could do
> the
>> > following:
>> > 1. Perform a full DB backup
>> > 2. Set the recovery model to Simple
>> > 3. Run the upgrade
>> > 4. Set the recovery model back to Full
>> > 5. Run an incremental backup
>> >
>> > Does this sound reasonable? Should steps 4 and 5 be reversed?
>> >
>> > Thanks in advance.
>> >
>> >
>>
>|||I can backup 90GB in just a few minutes using no third party tools
How long is "Too Long" ?
What kind of IO subSystem do you have ? (RAID Array, SAN, NAS)
Greg Jackson
PDX, Oregon

can I restore sql server 6.5 without backup

My operation system is destroyed and I have a Sql server 6.5 on it .
I had never done the backup process , Can I using the SQL server 6.5's File
to restore it ?
thanks anyway.
Hi,
If you have all the Physical DAT files , including Master database . Then
all you have to do is..
1. Take a copy of all DAT files to a safe location
2. Install SQL 6.5 in the same folder as old installation
3. apply the same service pack as old
4. stop sql server and sql executive
5. Copy all the DAT files to the same folder
6. Start SQl server and SQL executive service.
Now your SQl server will be back as old.
Note:
Now onwards please prepare and backup strategy to backup your databases.
Thanks
Hari
MCDBA
"william.huang" <william.huang@.saturn.yzu.edu.tw> wrote in message
news:#QQ43HSeEHA.3348@.TK2MSFTNGP09.phx.gbl...
> My operation system is destroyed and I have a Sql server 6.5 on it .
> I had never done the backup process , Can I using the SQL server 6.5's
File
> to restore it ?
> thanks anyway.
>

can I restore sql server 6.5 without backup

My operation system is destroyed and I have a Sql server 6.5 on it .
I had never done the backup process , Can I using the SQL server 6.5's File
to restore it ?
thanks anyway.Hi,
If you have all the Physical DAT files , including Master database . Then
all you have to do is..
1. Take a copy of all DAT files to a safe location
2. Install SQL 6.5 in the same folder as old installation
3. apply the same service pack as old
4. stop sql server and sql executive
5. Copy all the DAT files to the same folder
6. Start SQl server and SQL executive service.
Now your SQl server will be back as old.
Note:
Now onwards please prepare and backup strategy to backup your databases.
Thanks
Hari
MCDBA
"william.huang" <william.huang@.saturn.yzu.edu.tw> wrote in message
news:#QQ43HSeEHA.3348@.TK2MSFTNGP09.phx.gbl...
> My operation system is destroyed and I have a Sql server 6.5 on it .
> I had never done the backup process , Can I using the SQL server 6.5's
File
> to restore it ?
> thanks anyway.
>

can I restore sql server 6.5 without backup

My operation system is destroyed and I have a Sql server 6.5 on it .
I had never done the backup process , Can I using the SQL server 6.5's File
to restore it ?
thanks anyway.Hi,
If you have all the Physical DAT files , including Master database . Then
all you have to do is..
1. Take a copy of all DAT files to a safe location
2. Install SQL 6.5 in the same folder as old installation
3. apply the same service pack as old
4. stop sql server and sql executive
5. Copy all the DAT files to the same folder
6. Start SQl server and SQL executive service.
Now your SQl server will be back as old.
Note:
Now onwards please prepare and backup strategy to backup your databases.
Thanks
Hari
MCDBA
"william.huang" <william.huang@.saturn.yzu.edu.tw> wrote in message
news:#QQ43HSeEHA.3348@.TK2MSFTNGP09.phx.gbl...
> My operation system is destroyed and I have a Sql server 6.5 on it .
> I had never done the backup process , Can I using the SQL server 6.5's
File
> to restore it ?
> thanks anyway.
>

Can I pull the data to an existing table?

I am building RDA process between SQL server and SQL server CE. Since when we use the method 'SqlCeRemoteDataAccess.pull()', the data will be pulled down from SQL server to CE server and a new table is created with some addtional columns. As I only intend to pull the data and never push it back to SQL server, if I pull the data down for the second time, there will be an error saying that table is already existing. I wonder whether I can pull the data to an existing table everytime I do the RDA?
Thanks,
JustinRDA doesn't keep track of incremental changes from the server. If you want to update the client with new changes from the server, RDA requires that the table at the client be dropped and re-created via pull.

http://msdn2.microsoft.com/en-us/library/ms240401(en-US,SQL.90).aspx

can i process cube from outside analysis manager

I need to process a cube, but i want to do it using c#.. is there any way? Or any example with anyoneThere are a number of ways to process a cube from outside the manager, two come to mind:
1) Use the process cube task in Integration Services.
2) Write a .net program that does it using Analysis Managment Objects (replacement for 2000's DSO objects).

For more options and documentation visit: http://msdn2.microsoft.com/en-us/library/ms243781