Help!!!! Restore File Mdf After Exec Drop Table... To Previous State.
Aug 8, 2006
Hi all.
First, sorry about my poor english.
I have a database which above 6 gb data and i have droped all table in my database.
I try to restore from log file but my database is set to simple backup and auto shrink so log file doesn't help anymore.
I have used some software to recovery from log file too but useless.
My only hope right now is mdf file.
Please help me. How could i restore my mdf file to state before i droped tables.
Thanks
View 14 Replies
ADVERTISEMENT
Sep 6, 2007
Hi
I am new to Forum. So not sure if i am posting my problem uner the right topic.
We have a sql server 2005 enterprise edition 4 way cluster on windows 2003 advance server.
I am logshipping these database to a different server at a different location.
My logshipping went fine until one the cluster server failed and the server instance failed over to another node.
The backup that happened around that time got copied over to the secondary by the copy job.
The log file that got copied to the secondary server tried restoring and i think it failed int he middle of restoring it.
(You would think that the sql would knoe if the backup is in complete and will move on to the next file. Not sure what happened there.)
There is no indication of the *.TUF file in the directory where i have the log files.
I tried restoring it manually and i got the following error
Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete processing on file 'sessionlog1'. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I looked in the msdb..log_shipping_secondary_databases and looked for the last file that it restored and tried restoring it again with the following restore command by removing and adding some of the keywords that you see after the "WITH" clause.
MSFT do not recommand to use continne_after_error unless its absolutley necessary. I stilll get the above error.
restore log sessiondata
from disk = 'I:sql13qasmlogssessiondatasessiondata_20070901124516.trn'
with restart, CONTINUE_AFTER_ERROR, norecovery
When i add the restart int he with clause,
The restart-checkpoint file 'J:Microsoft SQL ServerMSSQL.5MSSQLBackupsessiondata.CKP' was not found. The RESTORE command will continue from the beginning as if RESTART had not been specified.
Msg 4319, Level 16, State 1, Line 1
A previous restore operation was interrupted and did not complete processing on file 'sessionlog1'. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I checked it the backup directory and i can't locate the .CKP file.
Does anyone ever come accross this issue?
Is there anyother way i could recover this DB in a standby or norecovery mode.
Any kind of help to resolve this issue (beside copy the full backup and redo the whole log-shipping process again) would be appreciated. sicne my primary and secondary server are totally ina different location, i need to ship a tape, if i need a full backup. This is the 3rd time its happening on that cluster. its frustrating to ship a tape everytime this happens.
View 4 Replies
View Related
Dec 31, 2014
In Windows Server 2012. How do I do a System Restore to a previous restore point?I need to install the 64 bit and 32 bit Oracle Client Install for connections in SSIS and to create Oracle Linked Servers.
If you make a mistake it is not fun removing it. Sometimes it corrupts the machine and it is difficult to uninstall since there is not an Oracle Universal installer for Oracle 11g.If you install the 32 bit before the 64 you mess up the machine.how to create a restore point.
View 6 Replies
View Related
Aug 5, 2007
I have the following query in sql 2005:
PROCEDURE [dbo].[uspInsert_Blob] (
@fName varchar(60),
@fType char(5),
@fID numeric(18, 0),
@bID char(3),
@fPath nvarchar(60)
)
as
DECLARE @QUERY VARCHAR(2000)
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
Any ideas how to do this?
View 1 Replies
View Related
Oct 16, 2007
Hi all,
In Object Explorer of my ComputerNameSQLEXPRESS, I have a Database "shcDB" with Table "dbo.MyFriends" which has fields "PersonID" (Int), "FirstName", "LastName", "StreetAddress", "City", "State", "ZipCode", "E-MailAddress" set up. When I executed the following query:
/////----shcSP-1.sql---////
USE shcDB
GO
EXEC sp_insertRecord 3, "Mary", "Smith", "789 New St.", "Chicago", "Illinos", "12345", "M_Smith@Yahoo.com"
GO
/////////////////////////////////////////////////////////////
I got the following error message:
Msg 8144, Level 16, State 2, Procure sp_insertRecord, Line 0
Procedure or Function sp_insertRecord has too many arguments specified.
(1) Why did I get this error?
(2) Where can I get the documents for explaining the procedures like 'sp_insertRecord', 'sp_delecteRocord', 'sp_getRecord', 'sp_updateRecord', etc. and error messages?
Please help and give me the answers for the above-mentioned questions.
Thanks in advance,
Scott Chang
P. S.
I used my PC at home to post the above message. I have not been able to post it by using my office PC in our LAN system. In my office LAN system, I have been struggling to figure out and solve the following strange thing: I log in http://forums.microsoft.com and "Sign Out" is on all the time. If I click on "Sign Out", I get "Logout: You have been successfully logged out of the the forums." page all the time. What is the cause to log me out? How can I get it resolved? Our Computer Administrator? Or Microsoft? What Department in Microsoft can help me to solve this problem? Please advise on this strange thing. Thanks.
View 9 Replies
View Related
Nov 5, 2005
Hello,Using Enterprise Manager, I deleted from my database the only tablethat contained records (Right-Click on Table, Choose "Delete Table").My expectation was that the LARGE .mdf file would be reduced to minimalsize (at least as small as the Northwind MDF). However, it's still 4+Gig in size!! (Northwind is 2.62 MB). This is a problem, bc I deletedthe table in order to recapture hard drive space.NOTE: I already tried using Shrink Database in EM. This significantlyreduced the size of the .mdf file from its original (i.e. pre-delete)size of 38 Gig to present size of 4 Gig.What can I do to further reduce the size of the MDF file?Thank you.
View 1 Replies
View Related
Jun 19, 2008
Dear All,
replication is not working for me now, how can i find the old snapshots and resore them to the present snapshot?
Arnav
Even you learn 1%, Learn it with 100% confidence.
View 3 Replies
View Related
May 16, 2007
Hello,
I am using a SQL Database.
I want to know if there is a way to restore a database to an early state.
i made some changes on the entries and now the relations between tables are all messed up.
Need your help.
Thanks in advance.
View 1 Replies
View Related
Jun 23, 2015
I've a SQL Server Express 2012 DB that I need to backup and restore on a different machine.I know that in the past someone performed full db and logs backup with sqlcmd.exe and I found some of this backup files but not all of them.In the last 6 months no backups has been taken.What is the right procedure I need to follow in order to save a backup of this DB and restore it on different machine withou losing data?
View 9 Replies
View Related
Oct 9, 2006
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies
View Related
Nov 24, 2006
Hi,
i have a backup file , and i saw the wizard to restore the file in sql2005.
But i need to restore only a table.
Is there a wizard for that?
View 11 Replies
View Related
May 6, 2008
hi
can you posible one tabel restore in database using full backfile
thanks
View 1 Replies
View Related
Nov 25, 2005
Third question:
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.
Why this happens??
Regards,
Manolis
View 1 Replies
View Related
Nov 15, 2006
Hello,i am in great trouble. I want to revert back to original state ofdatabase before i performed restore database on my sql server 2KDatabase. Accidently i didn't take backup of my Database and i didrestore, so is there any way to get the original state back of myDatabase?Any suggestion will be highly appriciated.Regards,S. Domadia.
View 2 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
May 23, 2007
I have an 19 gig database that somehow has a 100gig log file. The DB MUST BE in full recovery mode, I backup the transaction logs EVERY hour and shrink nightly. but for some reason my logfile WILL NOT SHRINK.
HELP,
I've used both the DBCC Shrinkfile (xxxxxx) and DBCC ShrinkDatabase (xxxxx) and these don't seem to work. I Have No current backup, I have Not capacity for addtional 100 gig worth of backup drive or off-site tape.
View 1 Replies
View Related
Mar 21, 2007
Hi,
I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.
However, I now need to send the output of each stored stored procedure to a flat file.
I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.
However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.
But I get this error when I try to save:
Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C
An OLE DB Recored is available. Source: blah
"Command text was not set for the command object"
What to do??
Thanks
View 5 Replies
View Related
May 21, 2007
I have a table that holds images. I want to get the previous image id and the next image id based on the current ID:
ImageID
4
21
56
74
99
So if the current image ID is "21" I'd like to return 4 and 56.
Thanks!!
View 3 Replies
View Related
Apr 16, 2001
How can I call a batch file from within Query Analyzer, which is the same batch I'm using with isqlw command.
Thanks
View 3 Replies
View Related
May 13, 2008
I am not sure if we can execute SSIS package using t-sql, but I want to do the following:
Step1. Execute SSIS package1
Step2. Check for the file1.txt in c drive using the below code:
Declare @result int
exec xp_fileexist 'c:file1.txt', @result output
IF (@result = 1) --if file exists exec ssis package
begin
Execute SSIS package2
end
If we can't use t-sql, please let me know how I can do that as part of sql job. Thanks!!
View 1 Replies
View Related
Feb 22, 2007
How do I do this ? Or is there any way to bundle Package with Config file so that they both are deployed in MSDB?
View 5 Replies
View Related
Sep 13, 2007
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
View 2 Replies
View Related
Jul 9, 2004
Hi...
Is there any way to check previous row in SQL Query?
I have a table with these column :
Name1
Name2
Audit_Time (datetime)
Changes
I want to delete record from database in which the Audit_time is <'01/05/2004'.
However before deletion, I want to check, if the Changes value is 'OLD' And the previous value is 'NEW', I will check the Audit_time of the NEW instead of OLD.
Table :
Row Name1 Name2 Audit_Time(mm/dd/yyyy) Changes
1 ABCD EFGH '01/01/2004' ADD
2 ABCD EFGHIJ '01/04/2004' NEW
3 ABCD EFGH '01/04/2004' OLD
4 Klarinda Rahmat '02/08/2004' NEW
5 Klarinda Rahmat '01/04/2004' OLD
In this case, I want to delete row 1,2,3 Where the audit_time are < '01/05/2004'.
Row 5 the audit_time also < '01/05/2004', however the changes='OLD' and the previous value changes='NEW', so I will check the Audit_Time of row 4 which is not < '01/05/2004'.
So I can't delete row5.
Is there any way to check previous row or the row before a specific row in SQL.
Any suggestion is welcomed.
Thank you in advanced.
View 2 Replies
View Related
Jan 9, 2008
I have got a table with GUID (PK), COMPANY, CONTACT. There are going to be instances where the company name is the same on multiple records.
What i am trying to do is work out what is the next and previous record
The data is going to be sorted, by COMPANY then GUID.
I am think a stored procedure would be the best to combact this, but very usure how to go about writing it, so i passed the present company and GUID values to it.
Any help would be appreciated, and thanks in advance.
View 20 Replies
View Related
Apr 9, 2008
Hello
just wondering, if I can get help with a table (Table A) , where there are few dates and numbers, I have to relate this table to another table (Table B) , where I have whole year working dates
I want to write an expression while making dataset, that if date in table B matches table A, it just get the number from the next column from table A, if it does not matches - it should go back to the date (in table A) - where there is a number.
Like table B has working dates for march, but there are only two dates in table A , for 3/2 (and number is 300) and 3/20 (number 200). So In dataset I want all dates from table B and from 3/2 to 3/19 300 in number column and 200 on 3/20.
Thanks in advance for your help
View 3 Replies
View Related
May 19, 2008
I'm creating a report in SSRS where we want to do a week-over-week or month-over-month analysis. We have each month and the metrics in a table. By sorting the table ascending, I can grab the previous record using the Previous function in a cell. However, when I order the table descending the previous record is not the correct record to get. In that case I want to grab the next record rather than the previous record. How can I invert the previous function? Or how can i grab the next record in the table rather than the previous?
Thanks!
View 3 Replies
View Related
Sep 10, 2013
Need getting a query which I will get previous year, previous month first day everytime i run the query.
Ex: If i run the script on 9/10/2013 then result should be 8/1/2012. (MM/DD/YYYY)
View 4 Replies
View Related
Dec 6, 2006
I need to create a file that removes (drops) all of your database objects.
View 12 Replies
View Related
Aug 12, 2015
I wonder how do I shrink log file in a specific database which in Mirror/Synchronized/Restoring state..So that database is in Mirror server ( High availability ).
View 4 Replies
View Related
Sep 16, 2014
I need to compare the next row with the previous row of same table and produce nonidentical column.for eg... say my table has
Row 1 => 1001 Abhas 120 150 180
Row 2 => 1001 Abhas 150 150 180
then my output would be as below:
StudId Name fee1 fee2 fee3
1001 120
1001 Abhas 150 150 150
i.e in first row of resultset, i want to show only those values which are changed alongwith studID and next row should display all values.
View 9 Replies
View Related
Jan 16, 2001
How to drop transaction log file and create another one
or change logical name of transaction log file?
Thank for any help
View 1 Replies
View Related
Dec 11, 2014
I am not able to to drop few file groups that has been created to add partition range.
Steps taken so far:
--Empty files started
DBCC ShrinkFile(YEAR2015_FG,EmptyFile);
GO
--Removing files
Alter DATABASE ETL_MART REMOVE FILEGROUP YEAR2015_FG;
GO
-- Remove parition scheme depednecy
[code]...
View 2 Replies
View Related
Jan 30, 2008
I have a folder that gets 2 to 3 files dropped everyday. I want to create a package that loads only the new files into the database. The old files (Processed files) remain in the same folder and cannot be moved. How do i accomplish this in ssis?
I found something about the file watcher task with the for loop, but just can't put it all together.
Any help would be appreciated.
Thanks
View 6 Replies
View Related
