Backup/Recover of row data in a multi-Company database - Database Discussion Boards

David Mujica9-Oct-12 9:13

9-Oct-12 9:13

Check this guy's article out on High CPU usage on SQL server, sounds like it might help.

http://jeffstevenson.karamazovgroup.com/2008/09/identifying-high-cpu-sql-processes.html[^]

Re: High CPU

cjb11010-Oct-12 21:30

cjb110

10-Oct-12 21:30

Grr, not getting very far.

Copied the db back to our dev server which hasn't had the 8gb upgrade, so its still 4gb.

Ran the same dataset through, the dev one finished in a reasonable time, the other is still going and now slowed down on yet another part of the process... Frown | :(

Re: High CPU

cjb11010-Oct-12 23:15

cjb110

10-Oct-12 23:15

Might have found something, a high number of CXPACKET waits. We've set the MAXDOP to 1 to see if we now get consistent performance, if we're lucky it will consistent performance that matches the old server...

MySQL Server timestamp changed!

Jassim Rahma2-Oct-12 21:53

Jassim Rahma

2-Oct-12 21:53

Hi,

I don't know why mySQL timestamp was change?! it' 3rd today but it's showing 4th when I try SELECT NOW or SELECT SYSDATE?

How can I fix it?

Re: MySQL Server timestamp changed!

Shameel3-Oct-12 4:05

Shameel

3-Oct-12 4:05

IMO, MySQL does not have its own date/time storage. Check your system's date/time.

Connection String for Ms access 2010 to Windows 7 64 Bit

Satyam Tripathi2-Oct-12 21:33

Satyam Tripathi

2-Oct-12 21:33

I not have Connection String for Ms access 2010 to Windows 7 64 Bit
there find an error on "provider=Microsoft.Jet.OLEDB.4.0" is
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Re: Connection String for Ms access 2010 to Windows 7 64 Bit

Richard MacCutchan2-Oct-12 22:00

Richard MacCutchan

2-Oct-12 22:00

See here[^].

One of these days I'm going to think of a really clever signature.

Backup/Recover of row data in a multi-Company database

DeDawg2-Oct-12 10:35

DeDawg

2-Oct-12 10:35

We have a web application using SQL Server 2008 R2 that contains data for tracking time that another company has expressed interest in using, we initially set up the database such that all end-user tables have a foreign key to what company that row belongs to. Is there a good way to backup/restore the data for a specific company across the tables, such that if the new company says tomorrow that they want their data restored to yesterday; it can be restored without impacting all the other companies data as well? I'm trying to avoid writing some form of stored procedure that selects and then have to do an insert/update/delete to put the data back. Thanks for any insight.

Dennis Daugherty

Re: Backup/Recover of row data in a multi-Company database

Wendelius2-Oct-12 11:17

Wendelius

2-Oct-12 11:17

Could you simply use bcp[^] to 'export' or 'import' the data. For your case, when exporting, you should define a custom query for the bcp to run.

The need to optimize rises from a bad design.My articles[^]

Re: Backup/Recover of row data in a multi-Company database

DeDawg2-Oct-12 11:22

DeDawg

2-Oct-12 11:22

I guess I could if I turned identity insert on and then just delete all for a company and then import ... I'll think on that one ...

Re: Backup/Recover of row data in a multi-Company database

Wendelius2-Oct-12 18:22

Wendelius

2-Oct-12 18:22

Or if you have foreign keys between several tables, you could set the identity insert off to ensure that the keys remain the same as they originally were.

Another mechanism you could consider is SSIS where you could break the import/export to proper blocks, define a workflow and if necessary add logic.

The need to optimize rises from a bad design.My articles[^]

Re: Backup/Recover of row data in a multi-Company database

Mycroft Holmes3-Oct-12 7:39

Mycroft Holmes

3-Oct-12 7:39

Is it possible to supply a separate environment for their needs. Or are they really trying to reset their production environment back to a prior date.

We often have a need for a department to want a restore of a previous date for reporting/investigation reasons, this does not need to impact on the production environment as they get another database environment created specifically for their needs.

Never underestimate the power of human stupidity
RAH

Re: Backup/Recover of row data in a multi-Company database

DeDawg3-Oct-12 7:43

DeDawg

3-Oct-12 7:43

This is more for a restore after they do something dumb ... like "I just deleted all the invoices for customer A when I meant B; can you get them back?" My answer would be I can put your data back to 3am last night, you'll loose everything you did today but your invoices will be back.

Re: Backup/Recover of row data in a multi-Company database

Mycroft Holmes3-Oct-12 8:02

Mycroft Holmes

3-Oct-12 8:02

Ugh, nasty, especially when you mix other clients into the data, I would suggest this could only be fixed by a custom script, charge them a fortune as a lesson and hope like hell you can deliver.

I would then implement a logging/audit strategy on the database or separate each client to their own environment. If you screw up another clients data b/c of this issue your whole business case just went out the window!

Never underestimate the power of human stupidity
RAH

cannot find column

sindhuan1-Oct-12 0:19

sindhuan

1-Oct-12 0:19

Hi I am trying to execute this query but its giving me an error

"Cannot find either column "D" or the user-defined function or aggregate "D.bonus", or the name is ambiguous.
"
Query:

MERGE INTO bonuses D
USING bonuses1 E
ON D.employee_id = S.employee_id)
WHEN MATCHED THEN
UPDATE SET
D.bonus = E.bonus
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES ES.employee_id, E.salary*0.1)
WHERE (E.salary <= 8000);

UPDATE bonuses
Set bonus=10000
FROM bonuses D
JOIN cbonuses1 E
ON D.employee_id = S.employee_id)

Any idea??

modified 1-Oct-12 7:56am.

Re: cannot find column

Eddy Vluggen1-Oct-12 1:00

Eddy Vluggen

1-Oct-12 1:00

SQL

UPDATE bonuses 
Set bonus=10000
FROM bonuses D
JOIN cbonuses1 E
ON D.employee_id = S.employee_id)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

How to Recover Deleted rows from sql 2000 Database

Robymon30-Sep-12 20:38

Robymon

30-Sep-12 20:38

How to Recover Deleted rows from sql 2000 Database using with some period parameter

eg: from 01/01/2012 - 20/05/2012

Display all deleted records on this period.

Re: How to Recover Deleted rows from sql 2000 Database

Eddy Vluggen1-Oct-12 0:57

Eddy Vluggen

1-Oct-12 0:57

Restore the backup. AFAIK, there's no alternative for backups.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: How to Recover Deleted rows from sql 2000 Database

Shameel3-Oct-12 5:46

Shameel

3-Oct-12 5:46

There is no inbuilt-mechanism to restore or read deleted rows in SQL Server 2000. You must implement logical deletes instead of physical deletes to be able to achieve that. (For example, an IsDeleted(bit) column in every table).

As Eddy suggested, the only other ways is to work on a restored copy of the database.

Windows Run Command

jojoba201128-Sep-12 23:08

jojoba2011

28-Sep-12 23:08

Hi World!
what is the Windows Run Command for "SQLDataRoot" OR "SQLPath"
for exp:
%program files% --->open Program files.

i wanna something the open the :
c:\Program Files\SqlServer 2005 for DWebPro\MSSQL.1\MSSQL

in other word i need this path to give to my setup to copy the database to the data path of that instance is installed.
i know the instance name also but i cannot give the exact path.cause if sql server was installed in that machine there should be "MSSQL.1" and now it should change to :
c:\Program Files\SqlServer 2005 for DWebPro\MSSQL.2\MSSQL

So if i can give it something like %MSSQL$% it will solve the problem.

Re: Windows Run Command

Eddy Vluggen29-Sep-12 2:41

Eddy Vluggen

29-Sep-12 2:41

jojoba2011 wrote:
for exp:

%program files% --->open Program files.

i wanna something the open the :

c:\Program Files\SqlServer 2005 for DWebPro\MSSQL.1\MSSQL

We call those "environment variable", and there's no such thing for each subdirectory of Sql Server. It's not recommended to be doing stuff there anyway, since it's the installation-folder of the app.

What are you trying to achieve that you "need" this folder?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: Windows Run Command

Richard MacCutchan29-Sep-12 2:58

Richard MacCutchan

29-Sep-12 2:58

rem set mssqlver to the current version: 1, 2 etc
set mssqlver=1

rem set path according to the current version
set mssqlpath="C:\Program Files\SqlServer 2005 for DWebPro\MSSQL.%mssqlver%\MSSQL"

One of these days I'm going to think of a really clever signature.

Re: Windows Run Command

jojoba201129-Sep-12 5:12

jojoba2011

29-Sep-12 5:12

Is it possible to change this path when wanna install sql express 2005 ?something like :
c:\Program Files\SqlServer 2005 for DWebPro\<big>MSSQL.639</big>\MSSQL

cause i can give parameter when wanna install sql server express 2005(command prompt)

Re: Windows Run Command

Richard MacCutchan29-Sep-12 6:09

Richard MacCutchan

29-Sep-12 6:09

I have shown you what you need to do, why not try some experimentation for yourself to see how you can vary that to your own needs?

One of these days I'm going to think of a really clever signature.

Re: Windows Run Command

jojoba201129-Sep-12 9:26

jojoba2011

29-Sep-12 9:26

thanks for solution.
but i dont understand where should i run that command or how to use that.

My main Question is that :
Is it possible to change "MSSQL.<big>x</big>" In this address "c:\Program Files\SqlServer 2005 for DWebPro\MSSQL.1\MSSQL" while installing sql express 2005 in command line? So that it wont generate it Automatically.

I mean that i give that number while wanna install sql express 2005.

Or More Clearly :

I wanna change Instance Id and give my own Id while installing sql express 2005 !
Thanks in Advanced!

modified 30-Sep-12 0:51am.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.