I need to restore a single table which one of my developers has deleted two columns from that table with all datas.
The copy of that database is on another server ,if some one can show me through EM, a step by step i will appreciate.
Thank you.
Hi,
Plz help me to restore deleted records.Pl zsuggest the ways to do it.
Thanks!!
The setup is 3 sql 2000 servers, one with a number of publications, and two with transactional pull subscriptions.
The publications were accidentally deleted from the primary server when replication was removed from an older server that unfortunately had been cnamed to the primary server.
The subscriber servers now error "the subscription does not exist".
I had thought restoring the master and msdb on the publisher would bring the publications back but this has not happened. There are good backups of all the system and user databases on the publisher available, but not the scripts that created the publications for replication. Can anyone suggest how or what to restore to get the publications back on the primary server so we can restart replication?
Thank You!
[edit]
It also may be worth noting that the publisher is it's own distributor in this scenario.
First, let me say that I have already reviewed information posted by experts within the chat area.
Someone completely deleted my SQL Server 7 database. I retrieved the .LDF and .MDF files from a network backup. Now I am trying to attach the database. I have tried this without creating an instance of the database by using the attach stored procedure to attache the .LDF and .MDF files I retrieved from the network backup. Also, I have tried it by creating an instance of the database and doing a detach of the newly created db .LDF and .MDF files and an attach of the .LDF and .MDF files I retrieved from the network backup. Neither of these approaches have worked.
Here is what I have tried in the Query Analyzer, but to no avail:
For the following example, I created a database instance called 'qarun_diamond_48_brett' with brand new .ldf and .mdf files and then tried to detach and then attach the .ldf and .mdf files from the network backup:
use master
go
sp_detach_db
'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett_log.ldf'
Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]
use master
go
sp_detach_db
'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett.mdf'
Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]
I tried the following attaches of the retrieved/recoverd .ldf and .mdf from the network to the newly created db instance. That didn't work, so I tried attaching to a db that had not yet been created.
use master
go
sp_attach_db
'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2_log.ldf'
Message: Server: Msg 1801, Level 16, State 3, Line 1
Database 'qarun_diamond_48_brett' already exists.
If I use a different db name I get the following error:
Server: Msg 5105, Level 16, State 13, Line 1
Device activation error. The physical file name 'qarun_diamond_48_brett' may be incorrect.
use master
go
sp_attach_db
'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2.mdf'
Message: Server: Msg 1801, Level 16, State 3, Line 1
Database 'qarun_diamond_48_brett' already exists.
I have also tried these statements wtih the EXEC sp_attach_db and EXEC sp_detach_db commands from within the Master db in Query Analyzer.
Any help would greatly be appreciated.
Thanks,
-Brett
I want to delete some records from dbo.ABC table using where clause.
And would like to restore the deleted records back into the same table.
Hi All,
In SQL Server 2000(Sql Query Analyzer), I had opened a main program file for debugging purpose.
Somehow by mistake its entire contents get replaced/deleted.
I don't have the backup.
I would appreciate anyone who can save me by advising to restore this program.
Hi!
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Hi!
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end of it a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Mnay thanks for your help!
av
Hi
If I delete 5 or more columns from a table I get the error "Index was outside the bounds of the array", OK is the only response. I then have to close the report and then open it again.
Easy fix is don't delete more than 5 columns, but my template is setup with 15 columns as it's far quicker to delete them as it is to add them 1 by 1 each time. I've finally got around to looking for a hotfix or something similar but can find no mention of this exact error relating to table column deleting.
Anyone else experience this or know if there is a hotfix for it?
Thanks
Is there anyway to retreive deleted data (rows) of a table? Unfortunately the backups are corrupt so we can't do a restore.
Thanks
Subscription data are deleted...
We've got a merge replication. I have one Publisher and one Subscriber. I've made a pull subscription to a Publisher.
I've got into trouble few times occasionally 3-4 times a year. Today also I got problem with one table only one row among so many not carried out on at subscriber, even it is not find out at publisher.
This row was entered at subscriber in the morning, printed by users but in evening report it is not coming down in reports, so I had checkup and find out it is not available at any server. How?
I don’t understand, how inserted, printed data erase from subscriber but rarely. Is there any clue?
Which parameter of the subscription is responsible for it ?
Best regards.
Hello,
Thank you for reading this. I am faced with a problem where there is no storage of deleted or updated information in an OLTP DB. I am mostly concerned about retrieving the deleted data from a table when an update occurs so as to make the deleted data show as an error. Is there a way to retrieve these kind of data from a DB, if so how? Can these data be retrieved by dates? Also how much time do you have before the data can no longer be retrieved? Assuming that the data can be retrieved.
I know the good stuff about datawarehousing but unfortunately we have no DW. Any help will be greatly appreciated.
I have a database that is 70GB big. One of the tables has over 350million rows of data. I need to delete about 1/3 of the data in thatone table.I was going to use a simple delete command to delete the unnessacaydata.Something likeDelete from tablename where columname > '100'However, it takes SOOO LONG to run, over 8 hours, if not longer....What is the best way to delete this data???One idea was to create a Temp Table, then move the needed data to thetemp table, then Truncate the Table, and move that data back.I'm not very good with SQL Query Language, so can somone give me anexample on how to do this?? Or if you have a differant idea that wouldbe faster please let me know.thanks,Sam
View 2 Replies View RelatedI have created a asp.net web api hosted on Azure. A couple of times a day all data from my database get deleted and I see these errors in the log. I have tried to reinstall microsoft sql server spatial types from nuget, but that didnt work. Could not load assembly 'D:homesitewwwrootbinSqlServerT ypesx86SqlServerSpatial110.dll'.
Error received: 'Could not load file or assembly 'file:///D:homesitewwwrootbinSqlServerTypesx86SqlServerSpatial110.dll' or one of its dependencies. The module was expected to contain an assembly manifest.'.
I want to learn that if my query deleted all data in the table by mistake, is it possible recovery and how?
View 1 Replies View Relatedhi Guys..
i don't know i m stupid or what.. but somehow i deleted my company very improtant data.. and more over i hvn't taken the backup of database..
Please help me out if any one of you guys know how can i recover the data.. i don't have a single back of this database...
Thanks .....
hi,is it possible to retrieve deleted data from .log file in sql server 2005 ? ThanksMayur Panchal
View 1 Replies View RelatedI have an older instance of SQL Server (2000) used for an old installation of Great Plains. Over the years there have been several custom application built to interface various accounting related systems with the data on this server.
Recently there have been several instances where random transactions are being deleted. The folks in the accounting group setup Great Plains to track any deletes from the GL account and determine who did the deletes. They're telling me that the same account we use to launch SQL Server is the account deleting the data.
It's possible that someone knows the login creds for this account and is using it to do some task that's causing the problem, but that's a long shot in my opinion. I rather believe that one of these custom interface apps is causing the problem.
One of the custom interfaces includes several SQL jobs that run that do some truncating and bulk inserting. In each case these jobs are owned by the sa account. If one of these jobs deleted the data would it be reported as being deleted by sa or by the service account since that's the account that launches SQL Server Agent?
I accidentally deleted some useful data in my database. I have both .LDF and .MDF files for this database.
Is there a way to recover this database to a point of time?
Thx.
I have not used the copy objects wizard that much. I used it today to copy 4views from my dev box to production. It copied the views, but also wiped outmy data in all the tables that the views are built around!!! Inproduction!!!!Can someone provide me some insight into why this happened?Thanks,Chad
View 6 Replies View RelatedHi all
Any way to Recover truncated, deleted, or modified data . i will be appreciated for any help
Hello,
When I reinitialized a subscription from SQL2005 db (publisher) to a SQL 2000 DB (Subscriber) and had the Upload changes before reinitialization turned on, the data from the SQL 2000 db didn't get uploaded. This resulted in a loss of 2 days worth of data.
Does anyone have any idea if there's anywhere that data is kept. (unfortunately a series of errors caused our backups to not be running on the 2000 db either).
Thank You
Bob
Hi,
I have an SSIS package that runs each day from a live data source to create a data mart, which is then used for various things including SSAS and SSRS.
The problem is that certain records that will eventually go on to form fact tables are deleted from the live system (not a very robost database in the first place, hence the SSIS!) but these are not reflected in the SSIS transformation, creating plus figures when compared to the live system.
I currently use type 1 slowly changing dimension processes in each data flow (of which there are about 35) but I realise that this only updates records and does not delete.
The solution I have in place is to truncate the fact tables in the mart before the run starts using an Execute SQL task. This solves the problem though to me seems a little heavy-handed and renders the slowly changing dimension processes redundant (as it is currently only run once a day).
My question is, is there a better method of dealing with the above scenario? If there isn't, it would be a nice feature to add to future versions (*nudge nudge*).
Thanks in advance :-)
I have deleted some data from some table. I dont have any backups and the database in simple recovery Mode. How to recover that data ...
View 15 Replies View RelatedI have an SQL .bak file and I would only like to restore specific columns as one of the columns is a free text field and is substantially increasing the size of the file. I can't restore it due to disk space constraints so dropping the column isn't possible if I can't get the table into a database locally.
View 1 Replies View RelatedHi,
I need to prevent modification/update to a field.
So I created a trigger. To take the data from DELETED table then replace the field data.
However, I have problem with one field which data type is text.
MS SQL always return me this error:
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
I use the following code to take the data from deleted table
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(NOTES )
FROM DELETED
I also tried simple Select statement
Select notes from deleted
What code should I use to take the deleted data
Please help.
Thanks in advance
I am in problem that I have delete data in my production table.
Now how to retrieve it?
I have only Yesterdays Full and Transaction Backup files.
I used the following query for deleting all data.
delete from t1;
I have a table that has a primary key that is auto incremented by 1. This table's data is cleared out periodically and as data gets added the auto id primary key continues to increase in numeric value. Once the data is cleared from the table the auto id names could be used again(the eventId is not stored) Currently the eventID is at 26,581,399. I know the maximum int value is 2,147,483,647.
How should I handle this? or rebuild the table every time the data is cleared(problematically)?
Hi folks,
Table:
a int,
b int,
c int,
d text
I need to change my AFTER - Trigger from this (example!):
select * into #ins from inserted
to something like
select *(without Text / nText / image -columns) into #ins from inserted.
So I tried to build a string like this: (using INFORMATIONSCHEMES)
select @sql = 'select a,b,c into #ins from inserted'
exec(@sql)
a,b,c are not of Text, nText or Image datatype.
After executing the trigger, I get an error, that inserted is unknown.
Does anyone know how to solve this ?
Thx.
Iam using Sql Server Integration Service to transfer the data. I have to methods to transfer the data.
Method -1
Daily cleaning Dataware house Data base and transfering the data from Source database.
Method -2
Only new rows transfering from the Source database to dataware house data base.
If i use first method, Any performence issues's will come in the future?. In future my source data will have upto 6 lacks records.
If i use second method, daily date based i can transfer the data. But if they delete any previous records from the source database how can i reflect the same in Data ware house Data base.
Can you please provide the solutions.
Regards
Hanu
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
Here's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS):
Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms.
This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match.
Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear.
Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package.
Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.