Database became suspect while logshipping and adding a datafile

Hello,
We do loshipping between two servers. Our database in primary server
grew, so we had to add a datafile to the primary database. However, we missed
a point. The directory, in which we created a datafile at primary site does
not exist
in the standby site. As another saying, we forgot to create the directory at
standby
site before adding the datafile to primary site. Now, the standby database
is in suspect mode, and we can not do anything. We tried sp_resetstatus,
creating the directory, and recovering the database again but nothing
changes, however the database becomes suspect again immediately, when we try
to restore the log. Please help.......
Erdinc,
Do a full backup on the primary server and restore that over your DR.
Make sure your path names are the same. Then continue as normal.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Erdinc the logshipping man wrote:
> Hello,
> We do loshipping between two servers. Our database in primary server
> grew, so we had to add a datafile to the primary database. However, we missed
> a point. The directory, in which we created a datafile at primary site does
> not exist
> in the standby site. As another saying, we forgot to create the directory at
> standby
> site before adding the datafile to primary site. Now, the standby database
> is in suspect mode, and we can not do anything. We tried sp_resetstatus,
> creating the directory, and recovering the database again but nothing
> changes, however the database becomes suspect again immediately, when we try
> to restore the log. Please help.......

Database became suspect while logshipping and adding a datafile

Hello,
We do loshipping between two servers. Our database in primary server
grew, so we had to add a datafile to the primary database. However, we missed
a point. The directory, in which we created a datafile at primary site does
not exist
in the standby site. As another saying, we forgot to create the directory at
standby
site before adding the datafile to primary site. Now, the standby database
is in suspect mode, and we can not do anything. We tried sp_resetstatus,
creating the directory, and recovering the database again but nothing
changes, however the database becomes suspect again immediately, when we try
to restore the log. Please help.......Erdinc,
Do a full backup on the primary server and restore that over your DR.
Make sure your path names are the same. Then continue as normal.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Erdinc the logshipping man wrote:
> Hello,
> We do loshipping between two servers. Our database in primary server
> grew, so we had to add a datafile to the primary database. However, we missed
> a point. The directory, in which we created a datafile at primary site does
> not exist
> in the standby site. As another saying, we forgot to create the directory at
> standby
> site before adding the datafile to primary site. Now, the standby database
> is in suspect mode, and we can not do anything. We tried sp_resetstatus,
> creating the directory, and recovering the database again but nothing
> changes, however the database becomes suspect again immediately, when we try
> to restore the log. Please help.......|||Thanks for your help. Is not there a way of saving the database before making
a full
restore?
"Mark Allison" wrote:
> Erdinc,
> Do a full backup on the primary server and restore that over your DR.
> Make sure your path names are the same. Then continue as normal.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Erdinc the logshipping man wrote:
> > Hello,
> >
> > We do loshipping between two servers. Our database in primary server
> > grew, so we had to add a datafile to the primary database. However, we missed
> > a point. The directory, in which we created a datafile at primary site does
> > not exist
> > in the standby site. As another saying, we forgot to create the directory at
> > standby
> > site before adding the datafile to primary site. Now, the standby database
> > is in suspect mode, and we can not do anything. We tried sp_resetstatus,
> > creating the directory, and recovering the database again but nothing
> > changes, however the database becomes suspect again immediately, when we try
> > to restore the log. Please help.......
>

Database became suspect while logshipping and adding a datafile

Hello,
We do loshipping between two servers. Our database in primary server
grew, so we had to add a datafile to the primary database. However, we misse
d
a point. The directory, in which we created a datafile at primary site does
not exist
in the standby site. As another saying, we forgot to create the directory at
standby
site before adding the datafile to primary site. Now, the standby database
is in suspect mode, and we can not do anything. We tried sp_resetstatus,
creating the directory, and recovering the database again but nothing
changes, however the database becomes suspect again immediately, when we try
to restore the log. Please help.......Erdinc,
Do a full backup on the primary server and restore that over your DR.
Make sure your path names are the same. Then continue as normal.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Erdinc the logshipping man wrote:
> Hello,
> We do loshipping between two servers. Our database in primary server
> grew, so we had to add a datafile to the primary database. However, we mis
sed
> a point. The directory, in which we created a datafile at primary site do
es
> not exist
> in the standby site. As another saying, we forgot to create the directory
at
> standby
> site before adding the datafile to primary site. Now, the standby database
> is in suspect mode, and we can not do anything. We tried sp_resetstatus,
> creating the directory, and recovering the database again but nothing
> changes, however the database becomes suspect again immediately, when we t
ry
> to restore the log. Please help.......

database analysis

hello!

i'm looking for resources on database analysis. my main questions are:
how does one determine the primary key of a table, and, how does one
determine the relationships between the tables?

if you know of any tips, books or websites, i'd love to know. my
current skills for such quests are mainly looking at field names and
data and trying to figure out visio. if there are better approaches,
i'd love to learn.

thanks,

-jOn 26 Oct 2006 12:58:04 -0700, joeyp wrote:

Quote:

Originally Posted by

>hello!
>
>i'm looking for resources on database analysis. my main questions are:
>how does one determine the primary key of a table, and, how does one
>determine the relationships between the tables?
>
>if you know of any tips, books or websites, i'd love to know. my
>current skills for such quests are mainly looking at field names and
>data and trying to figure out visio. if there are better approaches,
>i'd love to learn.
>
>thanks,
>
>-j

Hi j,

If you're using SQL Server 2005, then search Books Online for "Object
Catalog Views", or check out the various INFORMATION_SCHEMA views.

For SQL Server 2000, check out the INFORMATION_SCHEMA views or the
system tables, also in Books Online.

--
Hugo Kornelis, SQL Server MVP

Database Acting Strange

MY DATABASE FOR SHOPPING CART IS ACTING STRANGE IT HAS AUTOMATICALLY DESTOYED ALL PRIMARY KEYS. AND HAS CREATED 4 DUPLICATE RECORDS FOR EACH RECORD SO AT THIS STAGE I HAVE AROUNG 15000 RECORDS IN EACH TABLE IT MEANS 15 TIMES 4 60000 RECORDS. CAN ANYONE UPDATEME WHY THIS HAS HAPPENED AND WHAT CAN BE DONE TO DELETE EXISTING DUPLICATES. I NEED FAST REPLY IN THIS MATTER AS THIS IS URGENT. IF POSSIBLE KINDLY WRITE QUERIES TRIGGERS FOR FUTURE PROBLEMS AS WELL AS THESE DAYS I AM SICK SO MY BRAIN AINT WORKING IN THIS MATTER. URGENT URGENT URGENT THANKS ALOT I HAVE PREVIOUSLY POSTED AND HAVE GOT A GOOD RESPONSE. AND HOPE A GOOD ONE AGAIN. ONE MORE THING IS THAT I HAVE A BACKUP HERE AT MY OFFICE SYSTEM IT ALSO HAS DONE THE SAMETHING. I MADE A NEW DATABASE AND TRYED TO IMPORT DATA WITH DISTINCT KEYWORD BUUT IT IS NOT COPYING IT AS WELL. URGENT RESPONSE REQUIRED IF ANYONE CAN DO.It's that damn Miracle thing againnn....I hate when that happens...

Who else has sa rights to the server?|||Computers are like dogs. They can sense fear. You need to stop panicking and find out exactly when this occured, what was running, who was logged in, what the last code changes implemented were, etc...
Standard problem solving mode.

SQL Server will not, of its own accord, drop keys and duplicate records. Someone or some code did this.|||...and if I could write queries and triggers for future problems I'd be a wealthy man.|||Originally posted by blindman
...and if I could write queries and triggers for future problems I'd be a wealthy man.

LOL

Still working on the AI_DBA module?|||Oh, I finished that months ago. Works like a charm. But if I release it, I'd be out of a job.

If someone invented a light bulb that never burned out, who would want to manufacture them?|||What does that module do?|||AI_DBA module automatically and flawlessly performs all Database Administration and Development duties without the need for high-priced talent. It's user inteface consists of a single button that says "OK". It senses the manner and duration of the keypress to intuitively analyze the user's intentions and create a complete Requirements Document based upon the user's subconcious needs rather than just what they SAY they want, and completes the task within and regardless of all the unreasonable restrictions placed upon it by a boss who "knows better". It's "I told you so" submodule has been completely removed to spare any egos, and if you have an internet connection it automatically logs into dbforums three times each day and answers all questions containing the text words "date" and "format".|||OK-OK, I got the drift :)|||Originally posted by blindman
AI_DBA module automatically and flawlessly performs all Database Administration and Development duties without the need for high-priced talent. It's user inteface consists of a single button that says "OK". It senses the manner and duration of the keypress to intuitively analyze the user's intentions and create a complete Requirements Document based upon the user's subconcious needs rather than just what they SAY they want, and completes the task within and regardless of all the unreasonable restrictions placed upon it by a boss who "knows better". It's "I told you so" submodule has been completely removed to spare any egos, and if you have an internet connection it automatically logs into dbforums three times each day and answers all questions containing the text words "date" and "format".

Yup...that's about right...

I like the subconcious part...

Data Type

What is the standard for primary key IDs as far as data type. Integer vs
Numeric?
Thanks
Are they going to be whole numbers? If so, there is little benefit to
making them numeric, unless you know you will exceed the capacity of INT (in
which case, you can use BIGINT)...
http://www.aspfaq.com/
(Reverse address to reply.)
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:609C4EC9-10C7-43F6-BB0A-D930F90B8AE7@.microsoft.com...
> What is the standard for primary key IDs as far as data type. Integer vs
> Numeric?
> Thanks
|||>> What is the standard for primary key IDs as far as data type. Integer vs[vbcol=seagreen]
There are no standards w.r.t data types for a key.
Anith
|||"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:609C4EC9-10C7-43F6-BB0A-D930F90B8AE7@.microsoft.com...
> What is the standard for primary key IDs as far as data type. Integer vs
> Numeric?
What are you modelling?
Say it's cars... you'd want VIN. If it's books, ISBN.
etc.

> Thanks

Data Type

What is the standard for primary key IDs as far as data type. Integer vs
Numeric?
ThanksAre they going to be whole numbers? If so, there is little benefit to
making them numeric, unless you know you will exceed the capacity of INT (in
which case, you can use BIGINT)...
http://www.aspfaq.com/
(Reverse address to reply.)
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:609C4EC9-10C7-43F6-BB0A-D930F90B8AE7@.microsoft.com...
> What is the standard for primary key IDs as far as data type. Integer vs
> Numeric?
> Thanks|||>> What is the standard for primary key IDs as far as data type. Integer vs[vbcol=seagreen]
There are no standards w.r.t data types for a key.
Anith|||"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:609C4EC9-10C7-43F6-BB0A-D930F90B8AE7@.microsoft.com...
> What is the standard for primary key IDs as far as data type. Integer vs
> Numeric?
What are you modelling?
Say it's cars... you'd want VIN. If it's books, ISBN.
etc.

> Thanks

Data Type

What is the standard for primary key IDs as far as data type. Integer vs
Numeric?
ThanksAre they going to be whole numbers? If so, there is little benefit to
making them numeric, unless you know you will exceed the capacity of INT (in
which case, you can use BIGINT)...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:609C4EC9-10C7-43F6-BB0A-D930F90B8AE7@.microsoft.com...
> What is the standard for primary key IDs as far as data type. Integer vs
> Numeric?
> Thanks|||>> What is the standard for primary key IDs as far as data type. Integer vs
>> Numeric?
There are no standards w.r.t data types for a key.
--
Anith|||"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:609C4EC9-10C7-43F6-BB0A-D930F90B8AE7@.microsoft.com...
> What is the standard for primary key IDs as far as data type. Integer vs
> Numeric?
What are you modelling?
Say it's cars... you'd want VIN. If it's books, ISBN.
etc.
> Thanks

Data Trasformation Services

Hello All,

Can anyone tell me.. is that I can't create DTS if i'm using 4 field for Primary Key?

Can you explain a little more? Seems like you are using DTS to move data from one place to anotehr? You have a table with 4 columns in the primary key? what is that DTS is not letting you do?|||

Thanks Euan,

:D Sorry babe!!!

Yups.. I want to move data from tableA to tableB.. but in the same database.. I have a table with 4 columns primary keys.. I've got this :-

Violation of PRIMARY KEY constraint 'tableB'. Cannot insert duplicate key in object 'tableB'. The statement has been terminated.

|||You have invalid data in tablea, are you sure the keys are the same in the 2 tables? The error is basically saying that you have a row that violates the primary key constaint of table b.|||Yes, I'm sure the keys are the same in the 2 tables.. but if tableB doesn't have keys.. DTS running successfully.. but later my data will be duplicate..|||Are you running the load more than once, with the keys off perhaps? Can you count the rows in the 2 tables to make sure the counts are the same.

Data Trasformation Services

Hello All,

Can anyone tell me.. is that I can't create DTS if i'm using 4 field for Primary Key?

Can you explain a little more? Seems like you are using DTS to move data from one place to anotehr? You have a table with 4 columns in the primary key? what is that DTS is not letting you do?|||

Thanks Euan,

:D Sorry babe!!!

Yups.. I want to move data from tableA to tableB.. but in the same database.. I have a table with 4 columns primary keys.. I've got this :-

Violation of PRIMARY KEY constraint 'tableB'. Cannot insert duplicate key in object 'tableB'. The statement has been terminated.

|||You have invalid data in tablea, are you sure the keys are the same in the 2 tables? The error is basically saying that you have a row that violates the primary key constaint of table b.|||Yes, I'm sure the keys are the same in the 2 tables.. but if tableB doesn't have keys.. DTS running successfully.. but later my data will be duplicate..|||Are you running the load more than once, with the keys off perhaps? Can you count the rows in the 2 tables to make sure the counts are the same.