Resoring transaction logs

We believe we lost data yesterday in one of our tables, but only have a full
backup from 7/12 and have transaction logs from 7/14. Is there anything we
can do here? Don't you need the transaction logs from the last full backup
time in order to restore from the logs?
Help!Yes, you should be able to apply those transaction logs to that full backup
since you have a break in the sequence.(please someone correct me if I'm
wrong). You don't have logs backed up to tape anywhere?
If not, then you might want to look into Lumigent's Log Explorer to examine
the transaction logs. www.lumigent.com
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||hi Mark. First you need to backup the transaction log (should be available
regardless of the state of the failed db) before you do anything else. Then
you can restore the db from the full specifying no recovery to allow you to
continue restoring. Finally you can restore the transaction logs with a
recovery so that transactions will be rolled forward or back and the db will
then become available.
Please refer to books online for this procedure
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||Yes and No.
Yes - If 7/14 logs are the immediate logs backed up after 7/12 full backup.
You can apply logs all the way up to the most recent backup.
No - You backed up logs two days later? Logs should be backed up much more
frequently. That's what log backup is meant. If at least one log backup was
missing between 7/12 and 7/14, then you can not use the 7/14 logs at all.
Richard
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||..agreed log backup should be performed at LEAST daily, however there should
be no reason why you should be missing any logs since your last full backup,
assuming of course you are not using the simple recovery model -in which
case you can kiss your logs goodbye.
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:eRtXVqsUDHA.1484@.TK2MSFTNGP12.phx.gbl...
> Yes and No.
> Yes - If 7/14 logs are the immediate logs backed up after 7/12 full
backup.
> You can apply logs all the way up to the most recent backup.
> No - You backed up logs two days later? Logs should be backed up much more
> frequently. That's what log backup is meant. If at least one log backup
was
> missing between 7/12 and 7/14, then you can not use the 7/14 logs at all.
>
> Richard
> "Mark" <maxmann@.kc.rr.com> wrote in message
> news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > We believe we lost data yesterday in one of our tables, but only have a
> full
> > backup from 7/12 and have transaction logs from 7/14. Is there anything
> we
> > can do here? Don't you need the transaction logs from the last full
> backup
> > time in order to restore from the logs?
> >
> >
> > Help!
> >
> >
>|||Well, just to explain the situation a little further.
1. We are using Full Recover mode, which should be a good thing as far as
I'm concerned.
2. The reason we were missing the two days of logs is because we have two
jobs set in sql server.
One to do a daily full backup and 1 to do hourly trans log backups,
which is cool. The problem is
that on the 12th, somehow the job to do the full backups go disabled,
and we only have trans log backups
on tape back to the 14th. So that's how the gap happened. It's not
that we were doing trans log backups
every two days. It was just the issue w/ that job being disabled.
3. We have contacted lumigent and they said since we have the db in full
recovery mode that we should
be able to completely restore the database table that got deleted simply
by using the transaction log
that we have of when it happened.
Thanks for all the help.
Mark
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||James, firstly just because Mark states "full
backup from 7/12 and have transaction logs from 7/14" doesnt mean to suggest
that there is a break in the sequence only that he had a one day gap before
had did transaction log backups.
Secondly it doesnt matter whether he has got subsequent logs to tape or not
since the transaction log is *still* available for backup before he starts
the recovery. The Log Explorer tool (very good) is probably unnecessary in
this scenario).
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"James Luetkehoelter" <james.luetkehoetler@.NOSPAMspyglassllc.com> wrote in
message news:eEF4jHtUDHA.964@.TK2MSFTNGP09.phx.gbl...
> Er, this should have read "Shouldn't be able to apply". If there's a
break
> in the logs, you shouldn't be able to do a point-in-time recovery.
> "James Luetkehoelter" <james.luetkehoetler@.NOSPAMspyglassllc.com> wrote in
> message news:%23OK$bcsUDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Yes, you should be able to apply those transaction logs to that full
> backup
> > since you have a break in the sequence.(please someone correct me if I'm
> > wrong). You don't have logs backed up to tape anywhere?
> >
> > If not, then you might want to look into Lumigent's Log Explorer to
> examine
> > the transaction logs. www.lumigent.com
> >
> >
> > "Mark" <maxmann@.kc.rr.com> wrote in message
> > news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > > We believe we lost data yesterday in one of our tables, but only have
a
> > full
> > > backup from 7/12 and have transaction logs from 7/14. Is there
anything
> > we
> > > can do here? Don't you need the transaction logs from the last full
> > backup
> > > time in order to restore from the logs?
> > >
> > >
> > > Help!
> > >
> > >
> >
> >
>|||Hi Mark, as you state your gap is literary that you "missed a backup". This
doesnt mean that you have missing backup data -just less backups. please
refer to and follow my previous posts, there is no reason why you should not
be able to recover point in time of your choosing.
***REMEMBER TO TAKE A FINAL TRANSACTION LOG BACKUP BEFORE ATTEMPTING YOUR
RECOVER***
You should read BOL, section Administering SQL Server/ Backing up and
Restoring Databases/ Using Recovery Models/ Full Recovery
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mark" <maxmann@.kc.rr.com> wrote in message
news:ethVT$8UDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Well, just to explain the situation a little further.
> 1. We are using Full Recover mode, which should be a good thing as far as
> I'm concerned.
> 2. The reason we were missing the two days of logs is because we have two
> jobs set in sql server.
> One to do a daily full backup and 1 to do hourly trans log backups,
> which is cool. The problem is
> that on the 12th, somehow the job to do the full backups go disabled,
> and we only have trans log backups
> on tape back to the 14th. So that's how the gap happened. It's not
> that we were doing trans log backups
> every two days. It was just the issue w/ that job being disabled.
> 3. We have contacted lumigent and they said since we have the db in full
> recovery mode that we should
> be able to completely restore the database table that got deleted
simply
> by using the transaction log
> that we have of when it happened.
> Thanks for all the help.
> Mark
>
> "Mark" <maxmann@.kc.rr.com> wrote in message
> news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > We believe we lost data yesterday in one of our tables, but only have a
> full
> > backup from 7/12 and have transaction logs from 7/14. Is there anything
> we
> > can do here? Don't you need the transaction logs from the last full
> backup
> > time in order to restore from the logs?
> >
> >
> > Help!
> >
> >
>

Resolver Issues

I'm not able to resolve conflicts using the conflict viewer because I have
timestamp columns in my tables. Can I manually resolve these conflicts by
making the necessary changes to the data?
I'm using filters on my articles and I'm noticing that replication is
changing rows in the table that don't meet my filter clause. Well, all
the data didn't change in the row just the Last_Update_Date and
Last_Update_By columns changed. I have a trigger on my tables that
updates these columns. For some reason my trigger is getting fired.
These two columns get updated so replication thinks it needs to push these
changes along to the subscriber. I end up with conflicts if the
subscriber has also changed these rows.
The first thing to figure out is what is causing teh triggers to fire.
As far as manually doing this, you can. The conflict viewer is just another
application which sits over a set of data. You can easily do this yourself.
You can freely read/write to the conflict tables if you choose since the
only things which reads from them is the conflict viewer. If you choose,
you can write your own application to replace the conflict viewer in order
to fit it into your environment and tailor it to your needs.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I figured out my trigger issue.
I appreciate your help.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:O4wRnlLKEHA.620@.tk2msftngp13.phx.gbl...
> The first thing to figure out is what is causing teh triggers to fire.
> As far as manually doing this, you can. The conflict viewer is just
another
> application which sits over a set of data. You can easily do this
yourself.
> You can freely read/write to the conflict tables if you choose since the
> only things which reads from them is the conflict viewer. If you choose,
> you can write your own application to replace the conflict viewer in order
> to fit it into your environment and tailor it to your needs.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>

resetting master.mdf

Hey! I downloaded the sql engine and made a bunch of tables in visual basic. They were called new1, new2, new3, and new4. So I deleted new1.mdf, new1.ldf, new2.mdf, etc... but I notice I still can't recreate them. So I'm thinking there's remnants in master.mdf or something?? How would I go about getting rid of all the old tables, even ones whose names I've forgotten, so I can have a clean slate again? (btw I don't have sql server, just the desktop engine)mdf's are not tables..they are databases...

Did you DROP the databases?

If so they are gone....|||thanks for the quick reply. I didn't drop the databases. In the VB code all I did was 'create database... etc'. So, how would I drop all the databases, even if I don't remember the names of all of the ones that I created? Like is there a way to reset all that stuff so it will be like I just installed it?|||SELECT * FROM master..sysdatabases

Just don't touch master, msdb, model, pubs or northwind

And you probably should do some maintenance

Check out

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx|||thanks! I'll check that site out.

Resetting Key Column Numeration from SQL Tables

I used a lot of fictitious data in order to test the code of my web site. But I noticed that when I deleted all fictitious data from the tables, the primary key columns keep creating automatically numbers that are above the last one before all data was erased instead of starting again from number 1. How do I reset this automatic numeration in order to restart from 1 again ?

You have to use the DBCC Checkpoint command

Refer this: http://www.sqlteam.com/item.asp?ItemID=8003

|||Check out BOL for DBCC CHECKIDENT command.|||

Another way to do this is to use Truncate Table Command rather than delete.

This will reset identity.

Resetting / deleting all the users in aspnet_* tables

Hi,

How can i reset to zero, deleting all the users who are in the aspnet_* tables in my production 2003 server?

Thanks

It has been a while since I did it, so I can't remember if deleting a user cascades and deletes all related records in other tables. In any event, you can do individual truncates on each of the tables:

TRUNCATE TABLE aspnet_Members;

|||

Assuming you don't have any other tables in the database that have foreign key constraints to the aspnet_* tables, you could simply rerun the T-SQL scripts that create those tables (since those scripts first drop the tables, if they exist). You can find the T-SQL files in %WINDOWS%\Microsoft.NET\Framework\v2.0.50727 or you can execute them using the aspnet_regsql.exe command line tool.

UPDATE: To provide more clarity... the T-SQL scripts in that folder are named like Install*.sql. InstallMembership.sql, for example, contains the scripts for the membership-related tables; InstallRoles.sql for the roles. InstallCommon.sql has them all.

|||

Sweeperq:

It has been a while since I did it, so I can't remember if deleting a user cascades and deletes all related records in other tables. In any event, you can do individual truncates on each of the tables:

TRUNCATE TABLE aspnet_Members;

The only difficulty with this approach is since the aspnet_* tables have a number of foreign key constraints among themselves, if there is data in the tables you have to truncate (drop) the tables in the correct order, otherwise you will get foreign key constraint errors and the truncate will fail.

|||

So TRUNCATE does not trigger cascading deletes?

|||

The stored procedure aspnet_users_deleteuser takes care of the relationships and constraints - it's what the Membership.DeleteUser method itself calls. You can call it manually. Here's anarticle on deleting users from membership using the stored procedure.

But this deletes one user at a time. You want to delete all users? You can use SQL to cursor through the aspnet_users table, calling the stored procedure each time.

Otherwise, you need to truncate in the correct order which would be this: aspnet_usersinroles, aspnet_profile, aspnet_personalizationperuser, aspnet_membership, aspnet_users.

|||

Sweeperq:

So TRUNCATE does not trigger cascading deletes?

It does, if I'm not mistaken. Problem is, the foreign key constraints on the aspnet_* tables do not cascade deletes. So you have to manually delete the "child" tables first before you can delete the data from the parent table.

Reseting the Auto-Number for a table back to zero and Compact/Repair or Unload/Reload for

After deleting all the test data from all tables in a SQL 2000 database, is there a way to reset all the auto-incrementing fields back to zero in one shot? In Access, you can run the Compact and Repair option. Also, in Sybase SQL, there was an "unload/reload" option to reduce the database size. Is there a similar function in SQL2000? Thanks for all the help

Try to use truncate instead of delete to remove records from your table, it will remove records and reset identity fields to starting values.

Thanks

Reseting Select Permissions for Public Role

Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.

What can I do so that users are able to select from these tables after they are created?

Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?

All help is appreciated.I'd use GRANT SELECT ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp) within the script.

-PatP|||That did the trick. Thanks for all the help.sql

Reseting automatic increments on primary keys

Hello
I've a SQL database that has several tables and relathionships between them.
Most of my tables have
the primary key set to automatic increments using identity on table properti
es.
It all did well in testing fases...
My problem is that when going to production how can I reset those identities
values on several primary keys to have my keys strat on 1 instead of the la
st number used in testing...
my procedure, that didn't worked, was first erasing all tables, second conve
rt all primary keys with identity set to no identity, third save table, fort
h put primary key back to identity increments, fifth save table again
The first record inserted, instead of having primary key with 1 brought prim
ary key with 80 or so...
how can I reset those values?!?!
best regards
Jorge RibeiroHi Jorge,
You can reset the current value of the identity column with:
DBCC CHECKIDENT('<table name>', RESEED, 1)
However, you shouldn't attach any meaning to the value of the identity
column for a number of reasons, so whether it starts at 1 or 80 shouldn't
make any difference.
Jacco Schalkwijk
SQL Server MVP
"Jorge Ribeiro" <jorge.ribeiro@.irsocial.mj.pt> wrote in message
news:99FB747F-DAED-4264-886C-FF5D28FDF426@.microsoft.com...

quote:

---

> Hello
> I've a SQL database that has several tables and relathionships between

---

them. Most of my tables have

quote:

---

> the primary key set to automatic increments using identity on table

---

properties.

quote:

---

> It all did well in testing fases...
> My problem is that when going to production how can I reset those

---

identities values on several primary keys to have my keys strat on 1 instead
of the last number used in testing...

quote:

---

> my procedure, that didn't worked, was first erasing all tables, second

---

convert all primary keys with identity set to no identity, third save table,
forth put primary key back to identity increments, fifth save table again

quote:

---

> The first record inserted, instead of having primary key with 1 brought

---

primary key with 80 or so...

quote:

---

> how can I reset those values?!?!
> best regards
> Jorge Ribeiro

---

|||Jacco is correct. You can use the CHECKIDENT function to reseed the
identity. However this will not change any of the current values. To do
that you will have to move the data to a different table, reseed the
identity and then import the data back in.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Reset tables

I

use Microsoft SQL Server and also I have several tables. One table has

ID like PK, and name. ID use autoincrement number mode, and now when I

want start form one but not from 156 or like that. I don't know how?

And also when I delete all data from table an run my apllication and

add something in table, it starts form last number, not from one.

Maybe can I do it from SQL menagement studio?

How I can reset table and start from 1?Make sure the table is empty or else this won't work. Go to SQL management studio. Go to modify table. Select the identity column. Look in the column properties window under identity specification. There you see the Identitiy Seed. Change the value (for instance to 2) and save it. Change it back to 1 and save is again. This should do the trick.|||Hi,

have a look in the BOL for the following command:

DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

reset next identity value

For a set of tables many of which contain FK constraints related to identity
cols in the foreign tables:
When deleting all data (dropping FK constraints first) and then repopulating
data from scripts , how to instruct SS to reset the next identity value to
the identity seed ?TRUNCATE TABLE, or check out DBCC CHECKIDENT (TableName, RESEED)
Roy
On Mon, 27 Feb 2006 17:14:18 -0800, "John A Grandy"
<johnagrandy-at-yahoo-dot-com> wrote:

>For a set of tables many of which contain FK constraints related to identit
y
>cols in the foreign tables:
>When deleting all data (dropping FK constraints first) and then repopulatin
g
>data from scripts , how to instruct SS to reset the next identity value to
>the identity seed ?

Reset Increment Seed

I'm still in the development stage and am frequently deleting all data from all tables and then filling those tables anew. However, the increment seed for identifying fields doesn't reset to 1 (or 0--not sure which). While not important for operation of the database, I would prefer that the field identifiers start with 1 when I am ready to release the database for operation. Is there a way to do this?

I can generate scripts to rebuild the database structure to do this, but scripts aren't generated for database diagrams and the graphical representation of the table structure is very useful.

Hi Joe,

Truncate table reset the indentity. Truncate table is faster and need no transaction logs.

regards

Laurent

|||

You should take a look at DBCC CHECKINDENT in Books Online:

eg DBCC CHECKIDENT(YourTable, RESEED, 0)

HTH!

|||

Exception is that TRUNCATE TABLE will not work for table with reference to foreign keys. Almost all the table have references to foreign keys. Even when none of the tables have data in them, you still can not truncate the table.

Will try

DBCC CHECKIDENT(myTableName,RESEED,0) WITH NO_INFOMSGS

Reset Increment Seed

I'm still in the development stage and am frequently deleting all data from all tables and then filling those tables anew. However, the increment seed for identifying fields doesn't reset to 1 (or 0--not sure which). While not important for operation of the database, I would prefer that the field identifiers start with 1 when I am ready to release the database for operation. Is there a way to do this?

I can generate scripts to rebuild the database structure to do this, but scripts aren't generated for database diagrams and the graphical representation of the table structure is very useful.

Hi Joe,

Truncate table reset the indentity. Truncate table is faster and need no transaction logs.

regards

Laurent

|||

You should take a look at DBCC CHECKINDENT in Books Online:

eg DBCC CHECKIDENT(YourTable, RESEED, 0)

HTH!

|||

Exception is that TRUNCATE TABLE will not work for table with reference to foreign keys. Almost all the table have references to foreign keys. Even when none of the tables have data in them, you still can not truncate the table.

Will try

DBCC CHECKIDENT(myTableName,RESEED,0) WITH NO_INFOMSGS

sql

reset IDENTITY after table data import?

I have a remote DB I am wokring with at present. The DBA has provided me with a non owner LOGIN so I can't copy tables from the live to the staged DB as objects I can only copy tables and data.

The PKEY and IDENTITY COLUMNS get reset to just regular columns on each table. I can restore the PKEY constraint and have come across the DBCC CHECKIDENT to get the new ident value. I just can't figure out how to set a column to be an identity. The ALTER TABLE command isn't having any of it.

I am obviously missing the right bit on Books online

any suggestions?

many thanks

SteveThere isn't any way I know to add the IDENTITY property to an existing column. I'm pretty sure that it must be done at the time of the table creation.

My advice would be to script the table from the production DB, change the script to use IDENTITY, then copy the data from production to your working table.

-PatP

Reset Identity

Hi, there is a way to reset identity field of many tables via storeprocedure
?
i try with the scripts below, but they don't work !
BACKUP LOG test_dbWITH TRUNCATE_ONLY
DBCC shrinkdatabase (test_db)
and also with
create table #table(
idTabella int,
nome varchar(4000)
)
insert into #table
SELECT dbo.sysobjects.id, dbo.sysobjects.name
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id =
dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype =
dbo.systypes.xtype
WHERE (dbo.syscolumns.status = 128)
declare @.NomeTabella as varchar(4000)
declare @.TabellaID int
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
while exists(select idTabella from #table)
begin
DBCC CHECKIDENT(@.NomeTabella, RESEED)
delete from #table where idTabella = @.TabellaID
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
end"Alessandro" schrieb:
> Hi, there is a way to reset identity field of many tables via storeprocedu
re
> ?
> i try with the scripts below, but they don't work !
> BACKUP LOG test_dbWITH TRUNCATE_ONLY
> DBCC shrinkdatabase (test_db)
> and also with
> create table #table(
> idTabella int,
> nome varchar(4000)
> )
> insert into #table
> SELECT dbo.sysobjects.id, dbo.sysobjects.name
> FROM dbo.sysobjects INNER JOIN
> dbo.syscolumns ON dbo.sysobjects.id =
> dbo.syscolumns.id INNER JOIN
> dbo.systypes ON dbo.syscolumns.xtype =
> dbo.systypes.xtype
> WHERE (dbo.syscolumns.status = 128)
> declare @.NomeTabella as varchar(4000)
> declare @.TabellaID int
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> while exists(select idTabella from #table)
> begin
> DBCC CHECKIDENT(@.NomeTabella, RESEED)
> delete from #table where idTabella = @.TabellaID
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> end
The follwing procedure reseeds all ID-cols in the db. Tables without an
ID-col return an error that you can ignore ...
declare @.table varchar(256)
declare cu cursor for select [name] from sysobjects where xtype = 'U'
open cu
fetch next from cu into @.table
while @.@.fetch_status = 0
begin
dbcc checkident (@.table, RESEED)
fetch next from cu into @.table
end
close cu deallocate cu

Reset Id field

I have a few SQL tables that use an auto incrementing integer key field, ie it has 'is identify' set to yes

The tables have been used for testing while the application was developed.

I plan to delete all data from these tables when the application goes live. Is there a way to start SQL counting from 1 again without deleting and re-creating thr tables?

Check out Books on line for DBCC CHECKIDENT.

|||

Excellent -thanks for that - searched using those terms and found what I needed.

Regards

Clive

Reset autoincrement number

I use Microsoft SQL Server and also I have several tables. One table has ID like PK, and name. ID use autoincrement number mode, and now when I want start form one but not from 156 or like that. I don't know how? And also when I delete all data from table an run my apllication and add something in table, it starts form last number, not from one.

Maybe can I do it from SQL menagement studio?

How I can reset table and start from 1?Hi,

have a look in the BOL for the following command:

DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

One way to delete the data AND also reset the IDENTITY field is to TRUNCATE the table. (Check in Books Online, topic: Truncate.)

However, this may not be available if the table has PK-FK relationships with other tables.

|||Hmmm, but my table has PK-FK relationship. What now? Really I don't know how!|||If you want to truncate the table you will HAVE TO break the FK-PK first, othwerwise you will have to delete in the logical parent-child order.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

reset all identity seed in table

I mad a clone of my db and delete all info that I don't need.
how can I reset all identity seed in tables to the beginning numbers?
Tnx in advanced
Use this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus
|||Use this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus
|||Have a look at DBCC CHECKIDENT in SQL Books OnLine
"John" <john@.smith.com> wrote in message
news:doe4gc$n4j$1@.news2.netvision.net.il...
> I mad a clone of my db and delete all info that I don't need.
> how can I reset all identity seed in tables to the beginning numbers?
> Tnx in advanced
>
|||Use this
DBCC CHECKIDENT (tablename, RESEED,1)
|||this only work for one table
I want it all
Tnx
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1135253936.827451.60600@.f14g2000cwb.googlegro ups.com...
> Use this
> DBCC CHECKIDENT (tablename, RESEED,1)
>
|||If your tables are not referenced by foreign keys you could give this a try
:-
TRUNCATE TABLE tablename
this will remove all data and reset the identity column to it's seed value.
To run this against every table in your database make use of exec
sp_MSforeachtable
"John" <john@.smith.com> wrote in message
news:doe5om$na6$1@.news2.netvision.net.il...
> this only work for one table
> I want it all
> Tnx
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1135253936.827451.60600@.f14g2000cwb.googlegro ups.com...
>

reset all identity seed in table

I mad a clone of my db and delete all info that I don't need.
how can I reset all identity seed in tables to the beginning numbers?
Tnx in advancedUse this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus|||Use this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus|||Use this
DBCC CHECKIDENT (tablename, RESEED,1)|||Have a look at DBCC CHECKIDENT in SQL Books OnLine
"John" <john@.smith.com> wrote in message
news:doe4gc$n4j$1@.news2.netvision.net.il...
> I mad a clone of my db and delete all info that I don't need.
> how can I reset all identity seed in tables to the beginning numbers?
> Tnx in advanced
>|||this only work for one table
I want it all :)
Tnx
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1135253936.827451.60600@.f14g2000cwb.googlegroups.com...
> Use this
> DBCC CHECKIDENT (tablename, RESEED,1)
>|||If your tables are not referenced by foreign keys you could give this a try
:-
TRUNCATE TABLE tablename
this will remove all data and reset the identity column to it's seed value.
To run this against every table in your database make use of exec
sp_MSforeachtable
"John" <john@.smith.com> wrote in message
news:doe5om$na6$1@.news2.netvision.net.il...
> this only work for one table
> I want it all :)
> Tnx
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1135253936.827451.60600@.f14g2000cwb.googlegroups.com...
> > Use this
> > DBCC CHECKIDENT (tablename, RESEED,1)
> >
>

reset all identity seed in table

I mad a clone of my db and delete all info that I don't need.
how can I reset all identity seed in tables to the beginning numbers?
Tnx in advancedUse this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus|||Use this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus|||Have a look at DBCC CHECKIDENT in SQL Books OnLine
"John" <john@.smith.com> wrote in message
news:doe4gc$n4j$1@.news2.netvision.net.il...
> I mad a clone of my db and delete all info that I don't need.
> how can I reset all identity seed in tables to the beginning numbers?
> Tnx in advanced
>|||Use this
DBCC CHECKIDENT (tablename, RESEED,1)|||this only work for one table
I want it all
Tnx
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1135253936.827451.60600@.f14g2000cwb.googlegroups.com...
> Use this
> DBCC CHECKIDENT (tablename, RESEED,1)
>|||If your tables are not referenced by foreign keys you could give this a try
:-
TRUNCATE TABLE tablename
this will remove all data and reset the identity column to it's seed value.
To run this against every table in your database make use of exec
sp_MSforeachtable
"John" <john@.smith.com> wrote in message
news:doe5om$na6$1@.news2.netvision.net.il...
> this only work for one table
> I want it all
> Tnx
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1135253936.827451.60600@.f14g2000cwb.googlegroups.com...
>

Reserved words

Is there a query that i can run to find reserved words in the tables present
in my database?
Thanks!HP wrote on Tue, 3 Jan 2006 09:21:03 -0800:

> Is there a query that i can run to find reserved words in the tables
> present in my database?
> Thanks!
Much easier to just make sure you put [] around all table and column names
in your views/stored procs/queries, that way you don't risk problems in the
future when you migrate to the next SQL Server version and find they've
added more reserved words.
Dan|||HP (HP@.discussions.microsoft.com) writes:
> Is there a query that i can run to find reserved words in the tables
> present in my database?
There is a page in Books Online with all the reserved keywords, and
two more lists of ODBC keywords and possible keywords for the future.
With some cutting and pasting, and then finding and replacing in a
text editor, you can get those words into a table.
Then you can run this query:
SELECT object_name(c.id), c.name
FROM syscolumns c
WHERE EXISTS (SELECT *
FROM keywords k
WHERE c.name = k.keyword)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql