Restoration Time Reqd In Mssql2k
Apr 7, 2004
Q--Is there any feature of sqlserver-2000 by which we can restore the database with minimum time by using the parallel processor?
if the answer is yes wht is the statistics with example
For example
Time reqd for restoring 20GB of database 25minutes.
with configuration of Xeon processor,1GB memory and no users are using the server.
Basic purpose of my question is i need to give a solution to the client by using which he will be able to restore 20GB of data in 1 minutes .Is there any 3rd party utility which is available in market or can we achive this in mssqlserver2000, itself by increasing the resources like memory,cpu etc.
View 1 Replies
ADVERTISEMENT
May 7, 2008
Hi Gurus,
Every time when i want to restore database Iam going with SqlLight restoration scripts.
Here iam unable to get estimation time while restoration time.
How can i get time to complete restoration when database is in middle of restoration.
Please advice...
Thanks,
ServerTeam
View 3 Replies
View Related
May 27, 2004
Hello,
I'm testing "Point In Time" restoration for my system using both Database & Log backup files. (Database backup once a day; Log backup every 4 hours)
When I use T-SQL to perfrom the restoration, I can specify one .BAK file with numerous .TRN files and restore to any 'point of time' with no issue.
However, if I use EM to perform the same restoration, I can only specify one .BAK file with a maximum of two .TRN files (although I can see all the .TRN files) in order to restore the database properly. If I specify more .TRN files, after restoration, my DB will be in 'LOADING' status and can't be used.
Does anyone encounter the same problem before and know what is going on?
Thank You.
View 3 Replies
View Related
May 29, 2002
I currently have replication setup from a MSSQL2k machine to Oracle 8.1.5 and while the replication works, it times out every time there is a hiccup in the network. It acts as if it won't retry like it does from MSSQL2k to MSSQL2k. I'm using Microsoft ODBC for Oracle for my connection. Any ideas?
View 1 Replies
View Related
Jul 23, 2005
Hi,I'm converting an Access 2000 database to Sql Server and must bemissing something obvious.Using the Import utility in Sql Server, the Access queries seem to getexecuted and the resultant data imported as tables. Oops!Using the Upsize lizard in Access 2003, the queries aren't even in theselection list of "tables" to upsize. It looks like the Upsize wizardisn't supposed to do queries.How does one automate the migration of standard conforming queries toSql Server?Of course, I expect to spend some time addressing the stickiernon-compliant queries, but there must be a way to do the easy ones.What am I missing?-Dave
View 4 Replies
View Related
Jul 23, 2005
Hi All,I'm trying to track down a mysterious problem we're experiencing inwhich updates and inserts to tables in our mssql2k server appear to be'disappearing.'To explain our situation:We have a web page (written in ASP, if that's relevant) on which weaccept enrollment information.When that page is submitted, the form data is passed to a storedprocedure on our mssql2k server, which performs several operations,all of which are wrapped in a transaction.In particular, the stored procedure performs an update operation on arecord in one table (i'll call it TableA) and an insert into anothertable (TableB).If the procedure encounters a problem (ie after each update / insertoperation in the procedure we test for IF @@Error<>0) it performs arollback, performs a select similar to the one immediately below, andthen RETURNs.SELECT '1' as error, 'Unable to update TableA' as errormsgIf the procedure doesn't fail any of the @@Error tests, thetransaction is committed, and a membership number is SELECTed to bereturned.SELECT '0' as error, @memnum as membershipnumberThe @memnum variable is populated within the transaction.Back in the ASP page we test both for the proc returning an emptyrecordset, or for it passing an explicit value in the error field, andpush the page to an error page if either of these conditions are met.If, on the other hand, none of these conditions are met, and themembershipnumber field in the recordset is populated with a validmembership number, we push to a confirmation page.This confirmation page receives the membership number in a sessionvariable, performs a SELECT against TableB (the table that receivedthe insert during the proc) using that membership number in the WHEREclause, and the resultant recordset is used to populate theconfirmation details on that page. That recordset is also then used topopulate the details of a confirmation email, which is automaticallysent by the confirmation page.And now here's our problem: we've become aware of a handfull of peoplewho have gone through the enrollment process, have received theconfirmation email containing the information they supplied asexpected, but the data appears to be entirely missing from our tables.By that I mean that the record in TableA does not appear to have beenupdated (under normal circumstances that record should have hadseveral flags set, and several other fields updated with informationsupplied by the person enrolling), and the record in TableB does notappear to have been inserted.In essence, looking at our tables, it *feels* like the transaction inthe stored procedure for that particular enrollment hit a problem andwas rolled back. However, the evidence that we have in the form of theconfirmation email argues strongly that the data must have existed inour tables (particularly in TableB), if only for an unknown period oftime.We're kind of at our wit's end to work out what is going wrong withthese enrollments. From my understanding of transactions (and I couldwell be wrong) any changes to data (ie updates, inserts etc) containedwithin are essentially 'invisible' to any other operation (ie theSELECT that happens in the confirmation page) until the transaction iscommitted, implying that the effect of the update and insert shouldhave been 'permanently' successful if no error code is received and ifa valid membership number was returned. I ask, because someone in ourteam has suggested that maybe the operations in the transaction'lasted long enough' in the tables to have been visible for the SELECTon the confirmation page to have worked, but were then subsequentlyrolled back, explaining why the confirmation email is appropriatelypopulated and why the data then appears to be missing. However, as Isaid, this doesn't match my understanding of how transactions behave.Sorry for the length of this post, but I felt it was best to explainthis as best as I could.Does anyone have any advice they can give us on this situation? ie,are there any known problems with operations in transactions 'bleedingover' into tables, but then being rolled back at some later point?Does anyone have any thoughts or suggestions on how we can furtherdiagnose this issue?Truly, any help will be immensely appreciated...Thanks in advance,M Wells
View 2 Replies
View Related
Sep 7, 2007
Hey all, 2 questions.
Is there a way to 'refresh' the value of adodb.connection.state? and When my ado class reconnects after a dropped connection i still get connection failure errors.
I'm trying to make the connection b/n the my work's app and mssql server more 'robust'.
Our ado class tests the connection in the 'execute' method ie if connect() execute db task
.connect() is
IF this.loConnection.State = adStateOpen
*-- The connection is already open.
ELSE
create connection
ENDIF
return (this.loConnection.State = adStateOpen)
Now, this works fine when starting up the app, and everything runs fine, but *in theory* it should be able re-establish the connection at any time. So i kill the connection @ on the sql server while the app is running and try to perform a task that requires a db connection.
problem is that 'this.loConnection.State' is still equal to 1 even though the connection is no longer there.
It gets changed to 0 when the .execute fails but the failure is less than graceful... and although it reconnects (sp_who shows a new connection), it seems like the ado object is trying to connect using the old connection rather than the new one. command.activeconnection is set to the new connection in the .connect method so i dunno what's up.
Any ideas?
View 5 Replies
View Related
Nov 26, 2004
Hey there everyone, hopefully someone can shed some light on this problem.
Here is the stored procedure:
CREATE Procedure sp_getRequestorwCredentials
@LoginID varchar(50)
AS
SELECT Users.Name as name, Users.Email as email, Users.Manager as manager, Users.Mgremail as mgremail, Users.Empid as empid, Users.Phone as phone, Users.Dept as dept, Users.Busunit as busunit, Users.Segment as segment, Users.EmpPosition as position, Users.Region as region,
Requests.RequestDate, Users.EmpPosition, Users.Reason as reason, Users.District as district, Users.NetId, Users.Status as status
FROM Users INNER JOIN
Requests ON Users.Empid = Requests.UserId
WHERE (Users.NetId like RTRIM(@LoginID))
ORDER BY Requests.RequestDate DESC
return
GO
Now this query will work fine when using the like operator however if a user with a similar name to another user already in the table then the user may get the other persons record instead of thier own. Of course the solution here is to use the = operator instead of LIKE. This works fine when querying the DB directly however when executed within the stored proc no records are returned using the = operator even if they exact same query text works fine in a query window with a hardcoded var.
Yes I know there are SQL injection issues but the LoginID is being grabbed from the users domain login DOMAINNAME and is grabbed from the authenciation module. Since Active Directory names have a limited char set and theres not a way to pass an invalid name with text that allows for an injection attack.
View 1 Replies
View Related
Jul 23, 2005
Hi All,Further to my previous long-winded question about a situation in whichwe appear to be mysteriously losing data from our mssql2k server.We discovered an update statement, in the stored procedure we believeis at fault, after which no error check was being performed.Under certain conditions, this update is fired against the same recordin the same table as the immediately preceding update statement withinthe transaction. We are now suspecting that under some circumstances,these two updates get into a locking conflict that is eventuallyforcing the transaction to be rolled back.However, I'm still left with three questions.1) Where an update in a transaction gets locked, and an error isn'ttested immediately afterwards (ie no 'IF @@Error<>0' test is made),would the transaction proceed as normal?2) Most critically, would statements in the stored procedure thatappear after the COMMIT TRAN statement also be executed, even if anunresolved lock existed within the transaction?3) Assuming that (2) does happen, would a SELECT made on anotherconnection with a 'WITH(NOLOCK)' locking hint be able to see thechanges made in the locked transaction even if the server is set toREAD COMMITTED, and the SELECT takes place some time after the COMMITTRAN is issued? More to the point, given (2), how long would thelocked transaction survive before being rolled back after the COMMITTRAN has been issued? Is it possible that the COMMIT TRAN takes place,the transaction is flagged for potential rollback while a lockresolution is attempted, the stored procedure exists as thougheverything was fine, a subsequent SELECT (ie performed as one of thenext operations in the same application) using WITH(NOLOCK) 'sees' thechanges made by the transaction, reinforcing the impression that thetransaction succeeded, and then at some point thereafter the lock isdetermined to be unresolvable and the transaction is rolled back,making it seem as though the data disappeared, even though it had beenSELECTable via a different connection to the server?Thanks, by the way, to Simon and Erland for your advice on my previousquestions about this problem.Much warmth,M Wells
View 1 Replies
View Related
May 19, 2001
Hi Every1 there,
I have 3 tables
Items
-----
Item_code
item_desc
gp_code
cat_code
and
inv_header
---------
invhkey
trnno
trndate
tot_amount
tot_discount
inv_Details
----------
invdetkey
invhkey
item_code
qty
rate
discount
i want a way in which i should b able to view the information in this format.
Gp_code Item_code item_Description
eg: 01aa abc abcdedf
Sales
May2001 Apr2001 Mar2001 Feb2001 Jan2001 Dec2000 till May2000(i.e 1 year)
if possible i want a flexibility wherein i should b able to give the gp_code and all the items in tht gp_code should b displayed in above format where in the starting monthh will b the current month .
Any help will b highly appreciated.
Thanx.
View 1 Replies
View Related
Aug 24, 1999
Hi !!
We are using Ms SQLSERVER 6.5 on NT 4.0 service pack 3
I have joined in as a new DBA and don't know much about MS-SQLSERVER & it's replication.
What I have observed out here is that one of the databases was being replicated betnn Tokyo and
Kawasaki and suddenly after 22nd Feb stop replicatiing without giving any errors.
I have checked the max job_id in MSsubscriber_status on distribution database as well as max_job_id
in MSlast_job_info on subscription db.They seem to match.I have also tried to drop and recreate
the unique clustered index on the subscribing database.But nothing seems to work.
can anyone help me with this ?
Thanking in anticipation.
Regards,
Sapna
View 3 Replies
View Related
Jul 19, 2004
Hi Every body,
I have newly joined this group. I am new to DB administration.
I wanted some information as to if my Server crashes (which has) & i reinstall SQL server, will restoring master database restore all my permissions & security which was set before crash. It would be great in anybody can help me on this.
Regards,
Krishna
View 1 Replies
View Related
Oct 15, 2004
Pls consider the following table:---
Table Name: ChemoAdmin
Fields:
Field Name: Field Type: Typical Value:
patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg
I require the following output using one query:---
DATE 09/17/2004 09/21/2004
Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg
Pls help.
View 1 Replies
View Related
Oct 15, 2004
Pls consider the following table:---
Table Name: ChemoAdmin
Fields:
Field Name: Field Type: Typical Value:
patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg
I require the following output using one query:---
DATE 09/17/2004 09/21/2004
Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg
Pls help.
View 11 Replies
View Related
May 20, 2001
Hi there,
I want to display month name from a date field.
Eg:
May from 5/21/2001
without using Case statement.As i want to group by data using month name.
Thanx for Reading it.
View 1 Replies
View Related
Jun 14, 2002
Just a query, I have several tables that have bigint identity columns, I need to know what will happen when it reaches the upper limit. It will be most likely that earlier id fields have been deleted by the time it reaches the upper limit, so will it reuse numbers?
Cheers
Karyn
View 1 Replies
View Related
Nov 1, 2004
hi all,
i have installed mssql2k in win2000 server. since it was for my personal use, i did not give a password for the 'administrator' user in win2k. later on i cahnged the admin password,but my sqlserver did not start due to login failure.
is ther any solutions to tackle this?
View 5 Replies
View Related
May 7, 2001
i am restoring newdatabase(test) with the backup from other server.
The problem is the restoring process is hanging and its not restoring the
database .when i stopped and refreshed the databases the database is not showing up
could some body Give patch for this problem
Thanks
Prasad
View 2 Replies
View Related
May 16, 2000
Please excuse my lack of understanding as I am not a SQL DBA.
I have just received a database backup: "myDB_20005034400.bak" and have been
asked to put a copy on my server. I am told that this was made from the Enterprise Manager's DB Maintenance Utility. The backup Administrator has left the company with the server in a crashed condition.
How can I find out what "myDB_20005034400.bak" pertains to? (No msdb, etc. available). I am RESTORING TO A NEW SERVER.
MAIN: HOW DO I RESTORE THIS FILE TO A NEW DATABASE; ie TestDB (TestDB.mdf;
TestDB.ldf)?
Thanks a trillion.
Shelly
View 2 Replies
View Related
Jan 11, 2000
Hi,
Please give the retoration procedure:
I have a production server and I want test it on the other machine to check whether my backups are working or not?(Please give me step by step so that I can do easily)
Your answer will be highly appreciated.
Thanks,
dindu
View 1 Replies
View Related
Dec 7, 2000
Hi !
when i am trying to restore MSDB database from a disk backup it is giving an error saying
"Database in use.System administrator must have exclusive use of the databse to run the restore operation".
But i am not using msdb anywhere.when i see the processes under current activities i see an application named sqlagent-alert engine using the database.even after killing this process it reppaears and stops the restore operation.
so i stopped sql server agent and did the restore operation.It worked.
so is it necessary to stop sql server agent while restoring msdb?may i know the logical reason for it?
View 1 Replies
View Related
Apr 21, 2008
Hi Gurus,
While one of my DB through job i got following error message.
Failed to uncompressing data block. RESTORE DATABASE is terminating abnormally. Read on "VDI_2A04E518-0B70-41F8-BA22-48A63F9AC65C_0" failed: 1223(The operation was canceled by the user.) [SQLSTATE HY000] (Error 52008). The step failed.
Can someone help in this.
Thanks,
ServerTeam
View 5 Replies
View Related
Feb 14, 2006
VijayaKumar writes "We have SQL Server 7.0 & that machine was crahesd out. We took the .ndf & .ldf files and restore the DB in onther machine.
In that machine I am able to Login but during the time of retrival (select query or stored procedure) it gives the following error....
Server: Msg 823, Level 24, State 1, Line 1
I/O error (bad page ID) detected during read of BUF pointer = 0x1174f100, page ptr = 0x217f8000, pageid = (0x1:0x55b1), dbid = 12, status = 0x801, file = E:MYSTS_CWS_DB_PRIMARY.ndf.
Connection Broken"
View 1 Replies
View Related
Mar 29, 2007
Problem in restoring new database with the backup file stored in tape drive
tape doesnnot contain sql server backup set
View 2 Replies
View Related
Oct 16, 2007
Hello all,
I operate a website which runs on Server 2003 and SQL 2005 Express. Unfortunately my server experienced a hard drive failure, however fortunately I have a full backup using 2003's NT Backup.
My new server is slightly different specification wise to my last server - but my question is, what are my options for restoring the database and how would I go about this? I do administor 2003 servers, but I'm not a database administrator.
My initial thoughts are to update Windows and its components (such as IE) and SQL 2005 to the same equivalent versions, and to perform a full restore, but what if it breaks Windows?
My second thought is to somehow extract the database from my backup file, but then I wouldn't know where to start how to re-import everything.
All comments would be welcome! Many thanks, Mike
View 5 Replies
View Related
Nov 6, 2007
Hi all,
Please find the following details and give me appropriate solution.
Full backup: everyday :00:30 hrs
Differential backup: everyday 20:00 hrs
Transction log backup: every 30 mins
Database size is 150+ GB
Lastday one of our disk got crashed which had contains data and log file of the database at 22:25. Now my client wants all of the data till 22:25.
I can recover data till 22:00 but how can I recover last 25 minutes data.
Please help ASAP
View 5 Replies
View Related
Jun 5, 2006
I am working on a site redesign which attaches to an MS SQL backend. The hosting company sent me a backup copy of the DB. I am trying to restore the db into my SQL Server but keep getting an error. I created an empty DB and then backed up that DB. I then replaced the .bak from my db with the .bak file he sent to me in a zip document. I then tried to do a DB restore through enterprise manager and recieved this error:
Device activation error. The physical file name 'd:sqldatabasemssqldatastklaweb_db_data.mdf' may be incorrect. File 'stklaweb_db_data' cannot be restored to 'd:sqldatabasemssqldatastklaweb_db_data.mdf'. Use With Move to identify a valid location for the file. Device activation err. The physical file name 'd:sqldatabasemssqldatastklaweb_db_data.ldf' may be incorrect. File 'stklaweb_db_data' cannot be restored to 'd:sqldatabasemssqldatastklaweb_db_data.ldf'. Use With Move to identify a valid location for the file. RESTORE DATABASE is terminating abnormally.
I need to get this db restored asap. Any suggestions/help would be GREATLY appriciated!!!
Jason
View 12 Replies
View Related
Jul 24, 2002
SQL Server 7 or 2000.
I restore a database on a different server from a device file. This database has a user defined for it. In this case, 'privuser' is the name. No login has been defined.
I can add the privuser login but cannot grant db access because privuser already exists in the db. I cannot drop 'privuser' because the user owns all the user objects in the db.
How can I make the privuser login I create access the db using the privuser username in the db? Would having the login created before restoring the db as a new db work?
View 4 Replies
View Related
Sep 22, 2000
Hi Folks:
This seems like a pretty straightforward question, but since I've been trying to find this in BOL and not having any luck, I thought I'd ask some folks more experienced than I.
How much room do I need on a SQL box to restore a 6.45 gig database? (it's
going from a huge machine to a backup tape, then from the backup tape to a
much smaller machine for testing an app, and we're trying to find out what we need to do to make it all work out.)
I'm assuming that I would need significantly more than the 6 gigs of the db for the work of actually doing the restore, but can't find out how much more...
Any ideas would be most appreciated.
Many thanks in advance,
Tom
View 1 Replies
View Related
Dec 5, 2000
Hi,
This is the problem which we face now and then. There is an automated job that run everyday night to restore the Database for reporting purposes. Before running that job an NT Task Schedular stop starts the sql server. This is to make sure that all the connections are closed. Nobody uses it at Night. But some times the job fails with bellow error message...
"Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed."
Should I make the DB option set to single user mode. I don't think that is the problem.. because it runs fine most of the time..but sometime it fails.
Please help.. Thanks in advance :-)
Shyam.
View 2 Replies
View Related
Dec 16, 2005
Hello friends,
Kindly guide, if you are aware of any mechanism for restoring the differential backup only.
This can save a lot of time other than restoring complete database.
Thanks in advance.
Shrirang
View 2 Replies
View Related
Feb 16, 2007
pls tell me how to solve the database in use problem while restoring database using query.
View 2 Replies
View Related
Sep 10, 2001
It appears that in SQL 2000 restoring a single table is a bit more tedious and less user friendly as in earlier versions (6.5). Can anyone explain in a nutshell the easiest/quickest way to restore a single table from a backup?
Thanks,
Christine
View 1 Replies
View Related
