emergency help, our server1 is down, we need to restore everything from tape using server2, but when i put the tape in the server2, it said "unreadble media", what should i do to restore everything? How can the tape to be readble? Thanks, thanks.
View 14 RepliesHi all , hope my post finds u all well.
Currently I was reading about how to create a backup and i have some questions and i hope to find helpful answers..
what is back up media ,device and tape ?
how to initialize the media and what is the need for something like that ?
what about media headers that must be written ?
thanks in advance
Davids Maguire
Please direct me to detailed causes of SQL server crashing after a 17805 Invalid Buffer Received From Client. Our production server goes down, following this msg.
We applied service pack 5a, and it is still crashing. It crashes using either NAMED PIPES or TCP/IP protocol (via ODBC) The driver versions are older, ODBC 3.0 and SQL Server 2.65, respectively.
I need some direction on this. I'm leaning towards the possibility that this is an application problem.
Any help, any configuration setting changes, upgrades, workarounds, will be accepted. Thank you much!
Our SQL SERVER seems to be sick,all queries run very very slow since three days ago. SQL query analyzer can't select a efficient index to produce execute plan and the CPU is idle and lazy even if many proccess threads waiting for handle,it is very strange!! Otherwise,there are too many deadlock existing when the 2 third users use system at same time.
I have use DBCC REINDEX to re-build all indeies.
Total data used space :11.22G:
reserved index_size data unused
--------------------------------------------------
11220056 2053432 8945432 221192
data rows in some table up to 16009830,details of some table as follow:
Table_name Rows reserved data index unused
------------------------------------------------------------------------
Worksheet_Fabric 4629414 1073568 KB 782504 KB 290360 KB 704 KB
Fab_Input_Detail 16009830 804512 KB 616896 KB 187248 KB 368 KB
Product_In 4385485 774048 KB 525896 KB 247880 KB 272 KB
Dw_note 9109810 741336 KB 736600 KB 72 KB 4664 KB
Instructions_Detail 1906960 663432 KB 592800 KB 69288 KB 1344 KB
Instructions_M_Detail 2328862 94128 KB 504824 KB 88656 KB 648 KB
Product_Out 4323939 575256 KB 364880 KB 210208 KB 168 KB
Worksheet_QC_Detail 3404309 561800 KB 507552 KB 54040 KB 208 KB
SQL SERVER VERSION:
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
result of sp_configure:
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ---------
affinity mask 0 2147483647 0 0
allow updates 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 197 197
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1028 1028
user connections 0 32767 0 0
user options 0 4095 0 0
Does anybody know of a way to rollback SQL Server 2005
databases back to SQL Server 2000? Is there a way of doing it without
resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database
to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I
want to rollback to SS 2000, which was a lot more stable. I am having
several major issues that are affecting my whole company's day-to-day
operations and the managers are not happy. Some of the issues include
night time batch running very sluggish for no apparent reason. This is a
biggest problem because it only occurs once or so a week and causes a disturbance
with the daily activities when the night time processing isn��t completed on
time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I
don't believe it is a matter of my application needing to be retuned because if
that was the case, then why isn't it running sluggish every night? Also,
it's never the same day that the sluggish behavior occurs. If it was occurring
on the same night, then I would have something to investigate within our
application, but it doesn't. Another issue that I am having involves a
night time job that restores a copy of the production database to the Data
Warehouse server to be used for updating the data warehouse. Again, most
of the time it runs great (~2 1/2 hours), but once or twice a week, it goes
stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening
the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the
database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended
state. What's a suspended state and how can I get it out of suspended
state? I can find no information about suspended state in BOL. I
did a Google and nothing came up. If this suspended state was put
in for security reasons, great, but then tell me how I can remove the suspended
state. I am also not happy with the
fact that I can't get accurate information about the queries that are actively
running at that particular moment. In SS 2000, when I noticed high CPU
usage on the server, I would run the sp_who2 active stored proc and it would
show me all the active thread and how much CPU it was consuming. I would
then find the running threads with the highest CPU numbers and investigate the
query and see if we could improve it. Now in SS 2005, I get in the same
situation and run the sp_who2 stored proc, and there is no smoking gun.
All of the active threads are showing very little CPU usage, which I am very
suspect of. What the heck happen to sp_who2? I looked at some of
the other ways of looking at running processes (i.e... sys.sysprocesses) and
they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of
years before I upgrade to SS 2005.
Dave Brown
is there a roll back function for sql server 2005 that would change data back to what it was before the execution of last update query?
View 8 Replies View RelatedWe are currently trying to restore a copy of our production database to a different server. Unfortunately SQL does not see the tape drives and we are not able to add them. The tape drives are installed and visible in device manager. I have updated all drivers and firmware but still no luck. Any ideas would be appreciated. Thanks
View 1 Replies View RelatedDoes anyone know if it is possible to have a tape device another server as a tape dump device on SQL. Instead on removing the tape drive and installing it locally ?!?
Regards
Jasper
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
I have schedule backup job from SQL Server 2000 to Tape. Each Jobs consist of 4 steps which backup different database.
Sometimes I encounter problem. The Message is
Executed as user:dbo. The file on device '\.Tape0' is not a valid Microsoft Tape Format backup set. [SQLSTATE 42000] (Error 3242) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
There is no problem at the command, as it will run normally when i manually trigger the schedule job. Sometimes the error occur only after few days the job run.
Dear sir/madam,
I am from NTU, Singapore and we have a windows 2003 server edition installed on a DELL server and it also runs MS SQL server 2000 for a project of ours. We have installed a HP tape drive on the dell server and it works fine if ntbackup is used at the command prompt but then the SQL enterprise manager does not recognize the tape drive and backing up of our database is of high priority as it contains sensitive information. But the tape drive is installed properly as the device manager does not show any error and ntbackup also recognizes it. I cant seem to figure out why SQL server 2000 cannot recognize it. It would be very helpful if you could kindly guide me on this issue.
Thanking you
Regards
Lavanya Janardhanan
Hello,
I would like to know if anyone has a recommended method for having backups to tape while running log shipping. For example, is it possible to copy the transaction log backups used for log shipping to tape and apply them to a Full Database Restore from a 3rd Party Backup tool such as Veritas?
My goal is to be able to do a point-in-time restore from tape and still be able to use SQL Server 2005 Log Shipping.
Thanks,
Erik
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies View RelatedNew to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies View RelatedHello All,I am having trouble with a stored procedure I am working on. Just a little background, I am trying to do a GridView inside another GridView but the trouble I am having is that each row in the Parent GridView is display the entire Child GridView. What I want to do is basically for GridView1 (Parent) Row 1 should display GridView2 (Child) Row1. So I thought let me just pass three parameters.Any Help would be greatly apperciated Field – The Column I wantProject – The Project IDTPC – Name This is the stored procedure I am working on… CREATE PROCEDURE dbo.Portal_FinancialGetJanuary( @TPC nvarchar(50), @Project int @Field varchar(1000)) AS SET NOCOUNT ON SELECT @Field, MonthFROM Portal_FinancialForecastingWHERE(((Portal_FinancialForecasting.TPC) = @TPC) AND (Portal_FinancialForecasting.Month = 'January') AND (Portal_FinancialForecasting.Project = @Project))
View 2 Replies View Relatedhi,
i am getting problem in sql65, service pack5a.
for some tables in my database it is showing null. I cannot able to see the records in a table. I can see these tables in sysobjects table.
owner of the some tables is "null".
I executed following query to get the table name as well as owner.
select owner = user_name(uid), name , type
from sysobjects where name = 'published_docs'
Result:
owner name type
----- ----- -----
(null) published_docs u
--Pls give any ideas.
Dont say go to 65 discussion board(sorry i am in urgent)
thank you very much
--Devi
Hi all,
My window xp pro suddenly cannot reboot any more.
luckily, I can get into safe mode with command prompt.
So before doing any repair or even reinstallation of the op.
I need to back up my database using transact-sql in order to restore later on completely if have to reinstall my OP system.
I have MSDE installed on my computer. so osql.exe is available
for me to back up the database. I did before with the backup command, but it's a while ago. since this time the backup is so precious to me. I want you guys to back me up.
I will go to osql dir in C:program filesmicrosoft SQL server...
then issue:
backup database CMCTIS to disk='D:sqlbackupcmcbackup.dat'
CMCTIS is the database name and d:sqlbackupcmcbackup.dat is the file I want to use later on
to restore. Since if I will probably do repair or reinstallation, the information on C: will be wiped out or damaged. that's why I will use D: drive for the backup.
Please confirm.
So later on I can restore database completely including data and store procedures, database changes, right?
I have several databases and two server instances. I have to back up database one by one right?
Please reply at your earliest convenience. Otherwise I will lose all three month database work.
Betty
our one of our website is down. I try to reboot the server and restart the services. It didn't help. One of our user try to ftp. But also it said he has no permission on it. Do you know what can i do?
Thanks.
I have just deleted a clients database by mistake ( working too many hours this weekend!). I have recovered the actual files with Ontrack file recovery software.
I have tried to attach the mdf but SQL says it's not a valid sql database file. I have also created a new database of the same name , stopped sql, copied over the original files and re-started sql . SQL ( 2000) then marks the datbase as suspect.
Does anyone have a possible
thx
Stressed & Desperate.
No backups available . No lectures please... i've just learn't the VERY hard way !!
<edit> Moved to Data Corruption forum </edit>
Hi all!
I need an emergency help, I will have to give this assignment tomorrow...
what is the name of the function which makes this view:
I 1
F 2
I 4
C 2
F 3
I 1
C 1
into this:
I 6
C 3
F 5
Thanks,
Balázs
I don't know why the last topic is locked, but here is the situation more detailed.
I know that the group by is the answer for my last question.
But the situation is sadly not this easy:
We have a Donortype table and it looks like this:
D DONORTARGETQ1 DONORTARGETQ2 DONORTARGETQ3 DONORTARGETQ4
- ------------- ------------- ------------- -------------
C 400 400 500 500
F 300 350 400 500
I 125 150 200 275
3 rows selected.
The first column is called donortype. That's what we have to use.
The contribution table looks like this:
CONTRIBUT DONORID CONTRIBUTIONAMOUNT PROGRAMNAME MEMBERNUMB
--------- ---------- ------------------ ------------------------------ ----------
03-JAN-06 2 200 Basic Research 5
07-JAN-06 3 150 Applied Research 3
27-JAN-06 7 50 Teaching Programs 1
05-FEB-06 6 100 International Programs 2
23-FEB-06 2 50 International Programs 1
03-MAR-06 1 100 Basic Research 6
30-MAR-06 5 75 Basic Research 1
15-APR-06 6 50 Teaching Programs 4
09-MAY-06 4 100 Applied Research 2
14-MAY-06 7 100 International Programs 5
29-MAY-06 7 100 Applied Research 5
11-JUN-06 5 50 Teaching Programs 5
18-JUN-06 6 75 Applied Research 2
23-JUN-06 3 50 Basic Research 3
10-JUL-06 1 100 Teaching Programs 3
16-JUL-06 4 100 International Programs 2
22-JUL-06 6 75 Applied Research 4
15-AUG-06 5 60 Applied Research 1
23-AUG-06 2 150 International Programs 1
09-SEP-06 3 175 Applied Research 6
18-SEP-06 6 150 Teaching Programs 2
01-OCT-06 2 80 International Programs 5
13-OCT-06 1 50 Basic Research 6
30-OCT-06 5 25 Applied Research 1
11-NOV-06 4 100 Teaching Programs 4
24-NOV-06 2 100 Applied Research 5
10-DEC-06 7 200 Basic Research 1
20-DEC-06 3 85 International Programs 6
28 rows selected.
We have the DonorID and the ContributionAmount here.
We also have a Donor table:
DONORID DONORNAME D DONORPROJECTION
---------- ------------------------------ - ---------------
1 Crabtree I 50
2 Genetix C 100
3 Marlo I 55
4 Mervis F 60
5 Orange Computers C 50
6 Roper Foundation F 70
7 Keltron, Inc. C 85
7 rows selected.
The third column is DonorType as well.
What I have to create is something like this
DonorType ContributionAmount
I 100
F 300
C 250
Where the 100 is the sum of ContributionAmounts performed by DonorID-s which have a DonorType I
and 300 is the sum of ContributionAmounts performed by DonorID-s which have a DonorType F
and so on...
(the numbers are made up, they are probably not correct...)
My idea was that let's create a view with DonorID and sum(ContributionAmount) grouped by DonorID.
Than join this view with the DonorType table, and create a view which contains DonorId, DonorType and sum(ContributionAmount) from the first view.
And what now?! HOw do you group this by DonorType?
dear:
can anyone tell how to connect to Microsoft Mobile Server 2005 using System.Data.SqlceConnection or using System.oledbConnection connect to Microsoft Access DataBase in Pocket Pc or windows CE program?
dose there any minimum requirements i need to install?would you like to list a list for me
thanks alot.
I am trying to replicate between two servers. I am pushing a subscription to the other server. Of course this has to write to a file on the Servers hard drive before the actual replication. (It's actually trying to write a shared drive on the server) I have checked the permissions all day and I keep getting the same thing; 'The process could not create file [path to file] the network name cannot be found' What should I check? I have checked the permissions on the drive all the way down to the folder and It hasnt helped. I have checked the host files to make sure the ip address is correct in the file. I dont understand the network name error because it's trying to write to itself. Is there anyone out there that can help me???? PLEASE????
View 2 Replies View RelatedDear All,
We have a big concern in our Database system. We have 2000 transactions daily in our database. We need to replicate some how the database for our fail over setup. I tried transactional replication at midnight but our all systems locked and we had a lot of complaints from the customers and It was taking a lot of time to snapshot part and I had to abort it because of these reasons.
I need an advice how I can create a replication or is there any other way to replicate or is there any way to do this process without bothering the system? Please give me some advice and help..
I have a 6.5 DB in which I lost a hard drive and have to recreate the Databases. I do have the original and latest device file ***.DAT which contains all of my application Data. Is there a way I can re-create the DB and point to use this device and keep my data???
A quick response is greatly appreciated!
Hi all - had a bit of a nightmare at work with order accounts application. Basically - all the orders have been set to uninvoiced and I need to set the ones which have been invoiced back to invoiced. Problem is that orders can be part invoiced.
Tables as follows:
orders
orderid PK int
invoiced int
orderline
orderid int
qty int
productid int
invoices
invoiceid PK int
orderid int
invoiceline
invoiceid int
qty int
productid int
There can be multiple invoices for each order
Question is - how can I write a query which updates the order table - set invoiced to 1 where there are the same amount of items in the order as in the related invoices?
Real emergency this one folks!
Hopefully someone can help me out!
Stephen.
I absolutely agree with you.
I managed to find the error, it was a typo.
Here is the code, if somebody is interested about it. It is oracle, not SQL server, but the idea is the same.
drop view a;
drop view b;
drop view figure5_9;
create view a
as
select d.donorID, dt.donortype, dt.donortargetq1
from donortype dt, donor d
where d.donortype=dt.donortype;
select * from a;
create view b
as
select a.donortype, sum(c.ContributionAmount) as amount
from Contribution c, a
where c.ContributionDate between '01-JAN-06' and '31-MAR-06' and a.donorID=c.donorID
group by a.donortype;
select * from b;
create view figure5_9
as
select b.donortype, a.donortargetq1, b.amount, b.amount/a.donortargetq1 as percentage
from a, b
where a.donortype=b.donortype
group by b.donortype, a.donortargetq1, b.amount;
select * from figure5_9;
Thanks for the help anyway ;-)
Dear All,We have a big concern in our Database system. We have 2000 transactionsdaily in our database. We need to replicate some how the database forour fail over setup. I tried transactional replication at midnight butour all systems locked and we had a lot of complaints from thecustomers and It was taking a lot of time to snapshot part and I had toabort it because of these reasons.I need an advice how I can create a replication or is there any otherway to replicate or is there any way to do this process withoutbothering the system? Please give me some advice and help..Thanksasa.
View 9 Replies View RelatedI have successfully restored my old MSSQL7.0 database from a computer that crashed - but the database is several months old. I do, however, have the most recent log files (*.ldf) - the actual log file and NOT a backup log file. Is there any way i can update my restored databse with the most recent .ldf files?
View 1 Replies View RelatedHi, I don't have any backup file for database. But I do have *.mdf and *.ldf files on tape backup for all the databases on server. Our server has crashed and we are trying to rebuild it. Our DBA is not accessible right now.
Can please anyone help how to put sql server back with all the databases?
I will really appreciate your help...
Thanks a lot in advance..
Dear All:
I have a text file that need to use bulk insert to do the import, but after I run the command line as follows, an error message do come out at once:
my command line is:
BULK INSERT im_data
FROM 'c:
pt_kioskex051107.txt' WITH (FIELDTERMINATOR = ' ')
and the error is "unexpected end-of-file(eof) encountered in data file. Now i attached one of the file and hope someone could help me out and let me know what's wrong with it. Thanks for all.
Best rgds,
Ivan Vong
Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
View 2 Replies View Related