How To Reset A Database
Jul 4, 2007
We are about the deploy our project. We made many testing such as Random data insertion etc. Some tables have autoincrement column, and their numbers reached 3,000,000. After testing, we delete all data in database. Now question is how can we truncate all tables, and make fresh start. We may use TRUNCATE query but it is boring and giving errors some time. Is there way to reset whole database, and make a fresh start, like no data entered before.????
Thanks for help, Happy Coding
View 7 Replies
ADVERTISEMENT
Oct 23, 2006
Hi,
I have red this thread while I was searching an answer to my problem. Basically when you develop an app. you do some testing to check if everything is fine. When the testing is done you have primary key seeds starting from a number x which is not 1, as it is when the db is newly created.
I read about the truncate instruction (which I knew from mysql) but I can't perform the query because of the presence of a foreign key constraint in another table.
So I thought, maybe there is a way to tell the DB engine to disable FK checks while I do the truncation, but I can't find it in the documentation.
So the question is: using SQL EXPRESS what is the quickest way to reset the db state to its initial state, where all the primary key seeds were 1?
thanks in advance and sry if this question is already answered somewhere else, I searched but I didn't find a solution.
sfabriz
View 7 Replies
View Related
Sep 30, 2006
I'm writing a web application that saves data to a Microsoft SQL database. As I build my application, I continually dirty my database with test data and refine my data schema. Is there a tool that can quickly remove all of the data from 4 of my 5 tables? Right now I'm using MS SQL Server Management Studio Express to open each table and manually deleting the rows of data and it's a pain in the ass. Playing around with the Management Express, I was able to generate a create SQL script (I'm afraid to test it though) that looks like it will rebuild my table. The problem is, one of my tables is a list (index?) of types...and I'd need that data saved
ebuilt each time...but I cannot figure out how to tell SQL that the rows in this table needs to be rebuilt (and is not really data per say).How do you guys "clear" or reset your database during development?-=STZ=-
View 2 Replies
View Related
Jan 11, 1999
I'm having a problem with my database, can any of you sql gurus out there help me?
after I use sp_resetstatus to unmark the database as suspect I get this:
error: 602, Severity: 21, State: 15
Could not find the row in sysindexes for dbid '6', object '9', index '2'. Run DBCC CHECKTABLE on Sysindexes
then this one:
error: 3414, Severity: 21, State: 1
Database 'maillist' (dbid 6): Recovery failed. Please contact Technical Support for further instructions.
I can't do checktable because it's suspect, and I can't reset it's suspect status, any ideas?
View 1 Replies
View Related
Oct 28, 2006
I designing a continuous integration process, and I need to be able to "clean" the SQL Server 2005 database. Locally, I just drop the database and re-create it. That's the simplest way.
Yet, I would like to do the same thing with an "hosted" database online. The problem being that I do not have the rights to drop/recreate the database. Thus I need to operation within the database.
Does anyone has an idea how to drop everything in a database except the database itself?
Thanks in advance,
Joannès
http://www.peoplewords.com
View 2 Replies
View Related
Jul 18, 2006
I have a database surrogate key that increments so rapidly (+5000 every 30 mins). I need my SSIS package to reset this database surrogate key to avoid reaching an upper limit value for that field.
How can I do that using SSIS package?
thanks,
Aref
View 1 Replies
View Related
Jul 15, 2005
Hello! I like to reset my database (all tables in the database) to
default starting index values. I tried to use the command "TRUNCATE
TABLE" but it does't work becuase of the referenced foreign keys. I get
always the error message: Cannot truncate table 'T_MyTable' because it
is being referenced by a FOREIGN KEY constraint.
You know a solution for that problem?
Perhaps save all constraints, then delete them, truncate the tables and
then create the old constraints again.But how can I save all
constraints?
Thanks to help me in this urgent problem! FOX
View 3 Replies
View Related
Jan 18, 2008
I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:
Server: Msg 3415, Level 16, State 2, Line 1
Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.
What command(s) are needed to make these files read_write?
thanks
View 7 Replies
View Related
Nov 27, 2007
I have a table in my db that has an identity column as the primary key. I have deleted all the data and have tried to use truncate table on it to reset the identity column. The table has a number of foreign key constraints so it will not truncate.
Is there any way to reset the indentity column without removing the constraints?
View 1 Replies
View Related
Dec 10, 2007
My UDF
FUNCTION [dbo].[fn_concat_boxes](@item varchar(10), @week int, @type char(1))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week AND firm_or_commission = @type
GROUP BY quantity
ORDER BY quantity
RETURN @Output
END
gives the same output every time it is called, despite different parameters passed. @Output does not change as expected.
Any ideas guys?
View 16 Replies
View Related
Nov 27, 2006
Hi,
consider my table,
s.no priority status
1 2 pending
2 3 pending
3 0 completed
4 1 pending
5 0 completed
Now, if I insert a record as,
6 1 pending
the priority must be reset as follows,
s.no priority status
1 3 pending
2 4 pending
3 0 completed
4 2 pending
5 0 completed
6 1 pending
Im using c# and sql server. How can i do it?
Thanks,
Jasmeeta.
View 2 Replies
View Related
Jul 8, 2007
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?
View 2 Replies
View Related
Jul 17, 2007
Hi Forum, I have a .mdf that I have used to test SQL data app. I want to reuse the .mdf; delete all added data and reset Customer_ID primary colomn back to 1. All good info appreciated thanks Paul
View 1 Replies
View Related
Apr 18, 2008
I have a SQL data source triggered with this code: string searchstring = TextBox2.Text;
SqlDataSource1.SelectCommand = "mod_search_all_sel";
SqlDataSource1.SelectParameters.Add("search_str", searchstring);
SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; The second time this fires however, the SelectParameters.Add function creates another paramter and the stored procedure blows up. How do I do a reset like in old Classic ASP where you would set the DS = Nothing?
View 2 Replies
View Related
Jun 16, 2008
I have a table where I delete all the records, then reload. can I reset the identity to (1,1) Is there a SQL command for that?
-smcirish
View 3 Replies
View Related
Mar 11, 2004
In my application , DB has a large table. I write a small program to clear the table whenever the size of table is over 50 MB. At that time , I want to reset identity as 1. How can I do that?
Currently , my program delete old table and generate a new one with the same schema when the table is too large.But this is kind of ugly.
View 1 Replies
View Related
Nov 1, 2000
Can I reset the Auto_ID column in a table to start from 1 again?
Thanks
View 1 Replies
View Related
Apr 7, 2003
How do reset identity seed on table, for example I have table that has gap in the identity such as (1,2,3,5,6, etc..) and I want reset the entire table.
Thank You,
John
View 2 Replies
View Related
Jun 27, 2001
Can a column of Identity type reset after it reaches its maximum value?
Thanks,
Ben
View 4 Replies
View Related
May 15, 2006
This morning I made a change to two tables in my live system. All I did was add a new column in each called 'suspense' with a data type of bit and a default value of '0'.
When the users started using the system they noticed that the record ID numbers had reset and were starting from 1 and going up 2, 3, 4 etc. The last good id number was about 20500. The id columns are set as primary key with identity and increment of 1.
This shouldn't happen should it? Is it a bug? I never touched the ID column, just added the new one.
View 2 Replies
View Related
Aug 9, 2006
I have a DTS package that deletes all the records in a table and repopulates from an ACCESS table. All's well except the primary key of the target table doesn't go back to 1.
What's the syntax in T-SQL to accomplish that?
I imagine it's dropping the column and adding it back - but since it's a primary key field, regular drop/add syntax isn't working for me.
Any help here is appreciated.
Thanks,
Peter
View 4 Replies
View Related
Mar 23, 2007
Hi all
I have build an application on top of SQL Server 2005 and i am using microsoft driver. My application stops periodically becoz of Database Connection Loss and I get the following exception
com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.transmit operation. Exception:Socket closed. Context:(5) [Thread[Thread-55,5,main], IO:7e2dc, Dbc:null].
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDriverError(Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.DBComms.transmit(Unkn own Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.IOBuffer.sendCommand( Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerStatement.se ndExecute(Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerStatement.do ExecuteQuery(Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.executeQuery(Unknown Source)
Unable to find the exact reason for the above problem. Pl advice
Thanks & Rgds
Roshini
View 1 Replies
View Related
Jul 12, 2012
I'm trying to create a trigger that will clear the "checked" field when all items with the same classification have been checked.
The purpose of this is to do cycle counting of inventory, which is when you count the stock levels of a proportion of stock that have the same classification.
The checked field will be used to record the date it was lasted checked and will cleared once all stock have counted, putting back into the pool of stock to be counted.
Therefore, the trigger is suppose to count the number of rows that share the same classification as the amended row, and compare that with the number of rows in that classification, that have a date in the checked date field. When the two values are the same, reset all the checked dates to blank.
But when I tested it, nothing happened ...
ALTER TRIGGER [dbo].[UDEF_Last_Checked_Reset] ON [dbo].[ASC_PMA_TBL]
AFTER INSERT
AS
BEGIN
DECLARE @PartOnly varchar(16) -- Part Number
DECLARE @Rev varchar(4) -- Part Revision
DECLARE @PartCode as varchar(2) -- Part Classification
DECLARE @Last_Checked as datetime -- Checked Date
[Code]...
View 1 Replies
View Related
Oct 6, 2007
Hello All:
i am inserting a counter from a procedure based on the year/month.
I would like to reset the counter to 1 every month.
example of data:
count year/month
_______________
16200711
17200711
18200711
19200711
20200711
21200712 this counter should be reset to 1
22200712
23200712
MY PROCEDURE
____________
DECLARE @i INT
SET @i = 1
DECLARE @DateID INT,
@YMN CHAR(6)
DECLARE c CURSOR
FOR
SELECT DateID,YMN FROM TEST
WHERE WkDayIn = 'Yes' AND HolidIn = 'No'
OPEN c
FETCH NEXT FROM c INTO @DateID,@YMN
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE TEST
SET BusDay = @i
WHERE DateID = @DateID
SET @i = @i + 1
FETCH NEXT FROM c INTO @DateID,@YMN
END
CLOSE c
DEALLOCATE c
Thank You
F.
View 4 Replies
View Related
Nov 9, 2007
Hello everyone:
this procedure resets the business day to one for every month. It works fine except for the month of October. any idea or suggestions?
DECLARE @i INT
SET @i = 1
DECLARE @DateID INT,
@DtTimeD DATETIME,
@LASTDAY DATETIME
DECLARE c CURSOR
FOR
--
-- LASTDAY is the last day of the month
-- the counter will reset to 1 on the first of each month
--
SELECT DateID, DtTimeD, DATEADD(dd, -DAY(DATEADD(m,1,DtTimeD)), DATEADD(m,1,DtTimeD)) 'LASTDAY'
FROM D_DATE
WHERE WkDayIn = 'Yes' AND HolidIn = 'No'
OPEN c
FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY
WHILE @@FETCH_STATUS = 0
--
-- update the business day in D_DATE
--
BEGIN
UPDATE D_DATE
SET BusDay = @i
WHERE DateID = @DateID
--
-- reset the counter to 1 if it's the last day of the month
--
IF @DtTimeD = @LASTDAY
SET @i = 1
ELSE
SET @i = @i + 1
--
IF @@ROWCOUNT = 500
COMMIT
--
FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY
END
CLOSE c
DEALLOCATE c
GO
View 10 Replies
View Related
Dec 3, 2007
Hi experts, I would like to ask if there is a way to reset the SA password of SQL 2005 without having to know the current password?
Im using SQL 2005 Express
Thanks!
View 3 Replies
View Related
Dec 22, 2006
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?
View 5 Replies
View Related
Aug 9, 2007
Sorry if this is a dumb question, but I am new to SQL Server and trying a simple cursor. Works fine the first time I execute, but then @@Fetch_Status stays at -1 and it will not work again. If I disconnect and then connect it will work once again. What I am doing wrong?
declare @entityid int, @datafrom nvarchar (255),
@datato nvarchar (255), @updatedate datetime
declare statustime1 cursor
local scroll static
for
select audit.entityid, audit.datafrom, audit.datato, audit.updatedate
from audit
, orders
where audit.entityid = orders.orderid
and audit.entitytypeid = 4
order by orders.orderid, audit.updatedate desc
open statustime1
While @@fetch_status = 0
Begin
fetch next from statustime1
into @entityid, @datafrom, @datato, @updatedate
print @entityid
end
close statustime1
deallocate statustime1
go
View 13 Replies
View Related
Dec 10, 2007
Hi to all,
I'd like to know how reset a table, that is I've build an applicationa with VB express, added a DataBase (DB.mdf) and created a table. In this program I add and remove records to the table. There is also a Primary Key (auto-increment).
I'd like to know how I can reset the table, that is delete all record and restart from 1 the primary key.
Thanks...
View 1 Replies
View Related
Nov 15, 2006
I want a reset-button which clear my formular....
I have the following code in mypage.aspx.vb:
Sub Reset(ByVal sender As Object, ByVal e As System.EventArgs)
Me.Server.Transfer("Mypage.aspx") 'reloads the page without postback?
End Sub
And a button on mypage.aspx:
<asp:Button ID="Button2" OnClick="Reset" runat="server" Text="Rensa" />
But when I click the button....the validation-controls executes before...so instead for "clearing" the page (reload it without postback) the validation-messages shows that I have forgott to write in som textboxes....
What should I do?
View 1 Replies
View Related
Sep 12, 2007
Regarding this post: http://forums.microsoft.com/TechNet/showpost.aspx?postid=2111183&siteid=17
This could be the follow up of that post, but I thought it would be a good idea to open a new post to discuss the ReportViewer.Reset() method.
This is the background:I was getting a "Execution '' not found" error when generating reports, VDeepak wrote something about ReportViewer.Reset() as a solution for this, so I tried with it.
At my workstation, running the project with VS2005 it worked flawlessly. Nevertheless, when I updated the files on the server I got an exception: "method ReportViewer.Reset() not found". Apparently, this was because of an outdated dll: Microsoft.ReportViewer.WebForms.dll. In other post, somebody told this could be solved installing the SQL Server 2005 SP1, I did it with no success... I found a SP2 was launched recently, so I downloaded installed it... Now another exception: a "http bad request exception". Desperate, I removed the ReportViewer.Reset() line from the code and uploaded the files to the server again. This time, everything went ok again, actually, I havn't found the freaking "execution '' not found" exception.
There are two questions here:
1 - What exactly does ReportViewer.Reset() method do? and why the same code could be failing on the server when it was working ok in the workstation (believe me, I tried everything, the only diference is this method).
2 - By chance, could the "Execution '' not found" error be fixed with the SP2??? I searched the change log and found nothing about it.
Thanks!
View 1 Replies
View Related
Dec 28, 2005
I'm a programm designer, when using SQL Express Server,
one of my codesigner accidently changed the name of sysadmin account "sa" into somthing else, even disabled the login of this account.
since that's the only one account we can login, the database is now closed to any further operation above basic level.
looking for any possiable means to regain the control of database.
like how to reset "sa" account or reset the "master" database.
we sure make a "security database now" *_*
View 4 Replies
View Related
Feb 22, 2008
Hi,i m using vwd2005 express and sql express.i've created a table in sql express and set id fied to primary key and set identity to yes.now when i add a record a the id is auto generated this way 1,2,3,4....but when i delete these records and add the new record.the id column now adds a new id as 5 then 6,7 etc.but i want the id to generate from 1,2,3 again..how can this be done?any idea?thanks. jack.
View 6 Replies
View Related
