Recovery Of A Table From SQL SERVER 2000
Jul 28, 2007Unfortunately a table is Deleted by me from my database.
Now How I can Recover that
I have no data and structure of that table Now.
It Was Very IMP. Table .
So Plz Help.......
ADVERTISEMENT
Disaster Recovery: SQL Server 2000 On Win 2000 Server.
Sep 13, 2007This past Monday, our Raid 5 array failed on our SQL 2000 server (standard edition). Long story short, we were able to get the data back, mostly. In SQL server Ent. Mgr., I had to restore 4 databases that were 'Suspect' and "greyed out". No problems there... I was able to restore them.
What was interesting was that the SQL Server Agent would not start. As it turned out, a few files were either corrupt or missing after we got the machine back up. We had to restore a few files from tape backup and now the SQL Server Agent starts... but now I have a new problem.
For some reason, my default SQL Server Agent "Job" that is scheduled to run once per day fails. It appears that all of the databases get backed up, but for some reason, the msdbdata.mdf database is NOT being backed up.
I have a few questions. Could my msdbdata.mdf be corrupt? I have a successful backup of the msdbdata.mdf table from 9/10/2007. Can I just restore from that? Will there be any repercussions to any of the other database tables if I do?
Furthermore, should I just restore the msdbdata.mdf, master.mdf and the model.mdf? I really have no idea what these tables do and the repercussions of modifying them. I am not a DBA (obviously) and I just don't want to make an mistakes.
Thanks, CFDev.
SQL SERVER 2000 Legato Tape Recovery
Sep 25, 2007
I have SQL Server 2000 Standard Version backups going to Disk then we have Legato Networker Software to make backups of these backup directories to TAPE.
When we retrieve the file from the TAPE and use to recover the database we get the following errors: (Notice some work and some do not. This is from command 'restore verifyonly from disk='
Server: Msg 3242, Level 16, State 2, Line 1
The file on device '\backupfilersqlbackupsqlserver
ecovered_iis_mainsqlbackupsqlserveriss_mainiss_main_tlog_200709210130.TRN' is not a valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
The backup set is valid.
The backup set is valid.
Server: Msg 3242, Level 16, State 1, Line 4
The file on device '\backupfilersqlbackupsqlserver
ecovered_iis_mainsqlbackupsqlserveriss_mainiss_main_tlog_200709180130.TRN' is not a valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 4
VERIFY DATABASE is terminating abnormally.
The backup set is valid.
The backup set is valid.
If I take the original backup file that are on disk they are all valid.
Thanks for any help that can lead me to the solution
Mike
SQL 2000 Test Server Database Recovery Bombed
Feb 3, 2008I am a SQL lightweight and I tried to update the data on the test SQL server with the data on the production server and now the test SQL server won't start.
I was trying to move a sql database from our production server to our test server and things got mucked up. I think where I went wrong is that I tried to backup the database on the Production server and then restore it to the test server. I think I should have backup the production server and then detached the databases from the test server and then attached the backups. I got in a hurry and had an attitude that SQL is so good that if I mess up, I can recover.
These are the databases I recovered to test server:
Builder
CSales
CIB
Master
Model
MSDBdata
Now SQL server won€™t start and I get the error message that the database Model is in the middle of being restored. We are on SQL 2000. I tried to start from a command line with the €“f switch and it still can€™t come up.
Should I reinstall SQL and then attach the databases?
Do I need to bring over the Master, Model and MSDBdata data bases from the production servers.
SQL 2000 Server, Need Help Creating Documentation/disaster Recovery Plan...
Sep 27, 2006I currently work for a company that has had a "family business" mentality for many years. The only trouble is, it has grown into a fairly large company over the years and that mentality just doesn't cut it anymore (We have around 200 employees in 2 offices, and 25-50 remote users).
I have recently taken over the role of System Administrator, and I'm having a lot of trouble trying to get everything under wraps. There are a LOT of un-documented things happening with our database/web server, and in order to keep things safe & secure, I need to try & flesh out what those mystery settings would be. I feel that NOW is the time to get some solid documentation down, to protect my ass before some critical failure happens.
I'm wading into unknown waters here, and I'm hoping that somebody who's "been there" might have some tips to make my life a little easier.
I have an IBM Bladecenter, and a spare blade with which I can use to re-produce as close to a mirror copy of our system as possible. I have installed windows 2003 /w SQL2000 and restored a full backup of our databases and imported all database user accounts, and logins. What about Maintenance Jobs? Error Logs? Patches/Hotfixes? Service accounts? Membership roles? System databases? ODBC Sources? Services? Startup Scripts? I'm sure the list could go on & on... What are some of the crucial details I should look into?
Has anybody been in this boat before having little knowledge of what was built before you arrived, and have any insight on how to get through this without too much headache?
FAQ: Are There Any Whitepapers About Building A Disaster Recovery Site At A Remote Location For SQL Server 2000
Sep 9, 2006Hi,
Sorry for the wide distribution.
I'm trying to find any useful whitepapers about how to effectively build and operate a disaster recovery site at a remote location for SQL Server 2000. Does anyone know where to find such information?
I also know that one good option for my customer is using the Mirroring feature of SQL Server 2005. What are the other options? Is Replication an effective one for a mission-critical database (online banking)?
Thanks in advance
Recovery :: Server Local High Availability Using Failover Cluster And Disaster Recovery Using AlwaysOn
Aug 17, 2015We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies View RelatedSQL Server Admin 2014 :: Is Bulk Logged Recovery Model Support Point In Time Recovery
Dec 23, 2014is bulk logged recovery model support point in time recovery
View 9 Replies View RelatedMaximum Capacity Specifications Comparison Table For Access, SQL Server 7, 2000 And MSDE 2000
May 27, 2008
Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000
Number of instances per server
n/a
n/a
16
16
Number of databases per instance / server
n/a
32,767
32,767
32,767
Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647
Number of users per database
n/a
16,379
16,379
16,379
Number of roles per database
n/a
16,367
16,367
16,367
Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB
Number of columns per table
255
1024
1024
1024
Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage
Number of bytes per row
(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB
Number of columns per query
255
4,096
4,096
4,096
Number of tables per query
32
256
256
256
Size of procedure / query
64 KB
250 MB
250 MB
250 MB
Number of input params per procedure / query
199
1,024
2,100
2,100
Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB
Depth of subquery nesting
50
32
32
32
Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)
Number of columns per index
10
16
16
16
Number of characters per object name
64
128
128
128
Number of concurrent user connections
255
32,767
32,767
5
Recovery Model Code In SQL 2000
Jun 13, 2007In SQL 2005, sys.databases has a column named recovery_model that stores a code for the type of recovery model used by the database. Where is the recovery_model column in the SQL 2000 master database?
Thanks,
Craig
Will Rebooting The SQLServer 2000,Is It Change Recovery Model Of MSDB Database
Apr 3, 2008Hi All
Will Rebooting the SQLServer 2000,Is it change recovery model of MSDB Database
Please help...
Updating A Table Data From Another Table Using Sql Server 2000
Jun 4, 2008Hi All,
I have a Problem while updating one table data from another table's data using sql server 2000.
I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms)
I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
Table Recovery
May 21, 2008
Hi,
I want to drop my emp table,and get back my emp table.
i've used this following query to backup my database 'test1'
backup database test1 to disk='e: est1.bak'
drop database test1
restore database test1 from disk='e: est1.bak'
Processed 160 pages for database 'test1', file 'Test1' on file 1.
Processed 1 pages for database 'test1', file 'Test1_log' on file 1.
RESTORE DATABASE successfully processed 161 pages in 0.041 seconds (32.168 MB/sec).
If i use this query i can get back my original database.
main thing is i want to drop my particular table like emp or dept
but i couldnt recover my particular emp table.the following error will come when i try to
execute drop table test1.dbo.address
backup database test1 to disk='e: est1.bak'
drop table test1.dbo.address
restore database test1 from disk='e: est1.bak'
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "test1" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Recovery :: Pages On A Full Recovery Model Database Corrupted
Sep 17, 2015Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
View 4 Replies View RelatedTransact SQL :: Change Db In Recovery To No-recovery And Restore Transaction Log?
May 5, 2015in the process of migrating a big db from server 1 to server 2, we had to roll back the change. I started with taking a full db backup and restoring it on server 2 with norecovery, and then a couple logs with norecovery, and then the last log with recovery.
Is there some way to continue this chain now, I mean to change the db to norecovery, or other way to restore logs.Â
I dont want to do a new full backup.
If I try to do a log restore now i get the message:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Recovery :: AG Databases Went Into Synchronizing / In Recovery After Upgrading To 2014
Sep 19, 2015We have 3 replica AG setup. 2 replicas are in sync/automatic failover, the other(DR Server, different subnet) in asynchronous/manual mode…All these replicas were on sql server 2012, Recently we upgraded DR server to 2014. Since then we have a problem, the AG databases in 2014 instance went into ‘Synchronizing/ in recovery’ state…The SQL server error log has message, the recovery couldn’t start for the database ‘XYZ’…We tried to create a new database and add it to AG , it works for fine for other two 2012 replicas, but on 2014 we see the same issue
View 3 Replies View RelatedRecovery Model Indicator - Which System Table?
Jan 3, 2008Hi
I hope you can help as I am really scratching my head on this one. I am pulling together an assessment of the Disaster Recovery readiness for an organisation I am working at. Part of the assessment I am doing is the recovery model of each of the databases.
I have scripts that are already pulling lots of data from 40+ servers and 500+ databases. However, I cannot seem to find anywhere within the MASTER or MSDB or the database itself where the Recovery Model flag is held. Obviously I can right click on the database and click properties and it is there, but I need to automate this task (as it will probably be a weekly assessment).
I have checked sysdatabases and almost every other table, but nothing obvious as to where this flag is.
Any ideas?
Truncate Table Precludes Point-in-time Recovery?
Dec 18, 2001Hi All,
If I perform a truncate table (non-logged operation) in my application, will this preclude me from being able to do a partial recovery (point-in-time) of my app. using the transaction logs?
Thanks in advance,
Liz
Recovery :: Retrieving Deleted Data In Production Table?
Jul 8, 2015I 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;
Recovery :: AG Database In Reverting / In Recovery State
Nov 1, 2015We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
Recovery :: UPDATE Table And OUTPUT Clause To Capture Some Of Columns
May 28, 2015I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.
Copying Table Data From SQL Server 2005 To SQL Server 2000 - Very Slow When Using OLEDB Source And Destination Sources?
May 8, 2006An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Any inputs will be much appreciated.
Thanks,
MShah
Table Sizes In SQL Server 2000
Nov 14, 2001How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?
I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.
Table Sizes In SQL Server 2000
Nov 14, 2001How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?
I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.
Table Constraints To XML In SQL-Server 2000
Aug 4, 2006Hi,
I am DB programmer, How can i get Table Constraints to XML in SQL-Server 2000 with out data.
How To Copy Table In SQL Server 2000?
Jan 13, 2005I want to make an exact copy of a table in SQL Server 2000.
If I right click on the table I can select copy, but paste does not show up?
SQL SERVER 2000 - Table SYSPROCESSES
Mar 8, 2004Hello friends,
Despiste what it is mentioned in B.O.L., in the Table SYSPROCESSES, the column KPID has almost always the value 0 !!!
If anyone knows how I can find the right link between a SPID and the corresponding KPID, I shoud be very happy to know to.
It concerns SQL SERVER 2000 under WINDOWS 2000 SP3.
Thanks in advance for any help about that ...
Sql Server 2000 Table Maintenance
Jul 23, 2005sql server 2000I am currently maintaining a table that contains 30 Million+ records,30 columns, and 11 indexes and will double within the next six mouths.The application that accesses this table, mainly for read onlypurposes, runs without any problems. We have begun using Crystalreports and are now having problems. When we create reports thataccesses the large table our server has significant performance dip.The application begins to time out and the reports take a very longtime, even with simple selects on indexed field.I have began looking into partitioning the large table on its key fieldand creating a partition view. But from what I have read this willonly help if we key on the partitioned field. And all other searcheswill actually take a little longer.Archiving old data is not an option. All the data is being usedAny suggestions will be appreciated. Thanks in advance.Rick
View 1 Replies View RelatedTable Qualification In SQL Server 2000
Jul 20, 2005Is there any way to set up a User in SQL Server so they DO NOT have toqualify a table name with the owner name in a Select statement?
View 3 Replies View RelatedCan We Use DTS To Import PDF And MP3 Into SQL Server 2000 Table?
Aug 30, 2006I've got a situation in whcih I have to import PDF and MP3 files into SQL Server 2000 table.
Thanks,
YL
Table Partition In SQL Server 2000
Aug 4, 2007I have created the T1 table which will accept US records and T2 table for any other country.
CREATE TABLE T1(I INT unique ,name varchar(10) CHECK(name = 'US') PRIMARY KEY(I,name))
CREATE TABLE T2(I INT unique ,name varchar(10) ,CHECK(name NOT IN ('US')) PRIMARY KEY(I,name))
Then Created the partitioned View using below script
CREATE VIEW V AS
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
I am able to insert the US records using view
insert into V values (1,'us')
Problem:
I am not able to insert the non US records like UK and JN...
It is thorwing the exception like partition column not find.
eg : insert into V values (1,'uk')
PLease help me to insert the non US records into the View
How Track Changes Of A Table In Sql Server 2000 Database
Jan 4, 2006Hi
anyone please help!
I have created the database driven web application with asp.net and sql server 2000. now I want keep track three operation(insert, updata and delete) that have been made on tables in a SQL Server 2000 database. what i did is:
1, create a audit table with columns: auditTable, actions, actionUser, actionTime
2, create three trigger(insert, update and delect respectivily) for every table
my problem is that i can not get right user name. I use form authentication and i stored user login information in the database. every time, no matter who is logining to the web application, the action user is always SA. I user user-name() function to get userName(actionUser).
Please anyone can help me to get current login user name, or tell the best way to track operations on a table.
Thanks
jili
How Do I Retrieve Table Properties In MS SQL Server 2000
Nov 29, 2004I have looked on google and haven't found a query (as of yet) to perform this function.
Essentially I am using VB.NET with Excel and have a mapping between a worksheet and a table in my database. I wrote an import function to pull the data out of excel and put it into SQL Server but I want to try catching errors before i do that.
What is the SQL query to get column sizes from a table. Meaning in a table I have column1 that is allowed a size of int(5). How do i retrieve that information from a query opposed to just looking at it in SQL Server EM??
any help would be appreciated
thanks