Database

30-Apr-13 19:47

Hi I have just changed the db for a small app from using an MS ACCESS db (terrible thing!) to SSE 2008 R2 DB. Previously, they used to just copy the ACCESS db file each evening to a flash drive, and keep that off site. They have been using the app for a year now, and the SSE backup file is only about 20MB - in other words not that much data to backup. The server instance only has one database, and one additional user, no views or stored procedures or anything like that

1) What exactly do I need to back up? The database, system databases (master, model, msdb etc) log files?

2) How should I do the backup? (Been thinking along the lines of a backup command that can be run from the app (SMO), or possibly having a table in the DB with last backup date/time, and if a user logs on, and the last backup is older that a day, it makes a new backup. Or is there a better way? Can you schedule a job on SSE to do this daily?

3) I'm also not sure what options to use with respect to:
a) Backup Type (Full or Incremental?)
b) Copy-only Backup (No?)
c) Backup to existing medias set (yes) - Overwrite or Append?

4) The code I've tested using SMO
Backup bkp = new Backup();
bkp.Action = BackupActionType.Database;
bkp.Database = databaseName;
bkp.Devices.AddDevice(fileName, DeviceType.File);
bkp.Incremental = chkIncremental.Checked;
bkp.SqlBackup(srv);

Basically I want to end up with a backup (that can possibly be copied to some or other storage device as well for off site storage - flashdisk or portable drive) that in that case of the sever crashing completely, that I could restore to at worst a new instance of SSE?

Am i on the right track here?

Any other suggestions welcome!

modified 1-May-13 2:23am.

GuyThiebaut30-Apr-13 21:56

30-Apr-13 21:56

Without going into details of which backup strategy you should chose - there are lots of articles out there that can do a much better job than I can - do make sure that you test your backups.

I do a weekly restore to check that the backups are working correctly - I even went as far as to deliberately corrupt the database(not an easy thing to do I can assure you) to check that a restore worked ok.

Remember - you backups are only as good as the state they return your system to should you experience a catastrophic hardware failure.

I would also recommend taking an image of your server every month - as in the case of a failure it might be easier to restore an image then backups on top of that rather that rebuild a Windows server from scratch...

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Richard.Berry10030-Apr-13 22:16

30-Apr-13 22:16

Hi Guy

Thanks for your reply. I'm not working for the company, I only developed an app for their stock control. They don't have IT staff on site, and I'm really not sure what sort of backups they do on the servers. (They have two boxes both running Small Business Server - and I installed and instance of SSE for my app on one of them) All I want is to be able to restore the data for my application. Even if that means I install a new instance of SSE on another machine, and restore the data that was backed up.

When you say you restore a backup weekly to test, where do restore the backup to? Have you got a duplicate database that you use to test the restore? What sort of things do you check to see that restore was successful - If it restores without Errors, check row counts on the tables?

GuyThiebaut30-Apr-13 22:43

30-Apr-13 22:43

I restore the backup to a test system(so yes a duplicate database) then I test a series of .Net apps on the restored data as a sort of sniff test.

SQL Server is 'generally' good at telling you if a backup restored with corruption - you could run a select query on the major tables as a sniff test, just to ensure that what comes back looks ok.

As a DBA you want to make sure that when the SHTF you can get the systems back to working within an agreed SLA(which is another point - make sure that you have an agreed SLA on how long a restore of a broken system will take and double the time you think it will take you, I can't stress how important this is because when things go wrong people behave irrationally and you need to be able to hold up that piece of paper to give yourself some space, in order to get on with fixing the systems).

A few useful sites worth reading.
Backups[^]
Validating a backup[^](if you are not subscribed it is well worth joining this site)
Database integrity[^]

I would strongly recommend an image backup every month with daily incremental image backups - disk space is cheap and rebuilding systems is expensive.

Another tip - if you are not running this all on a virtual machine, ensure that the SQL Server install is on a different disk to the software accessing it(this will further ensure its integrity and speed up access).

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Richard.Berry10030-Apr-13 23:18

30-Apr-13 23:18

GuyThiebaut wrote:
I restore the backup to a test system(so yes a duplicate database) then I test a series of .Net apps

This should be easy enough to implement... I think.... (Well at least a duplicate database on the same server instance)

GuyThiebaut wrote:
As a DBA you want to make sure that when the SHTF you can get the systems back to working within an agreed SLA

Good point - the guy is a mate, but he should know what is a reasonable recovery time...

GuyThiebaut wrote:
I would strongly recommend an image backup every month with daily incremental image backups - disk space is cheap and rebuilding systems is expensive.

I will suggest that he implements this (I think he brings in a consultant to help out with servers etc)

GuyThiebaut wrote:
Another tip - if you are not running this all on a virtual machine, ensure that the SQL Server install is on a different disk to the software accessing it(this will further ensure its integrity and speed up access).

Not entirely sure what you mean here? The app is a standalone windows forms app, installed on the workstations, so I guess that should be fine.

Thanks for the links - will have a look shortly

GuyThiebaut30-Apr-13 23:34

30-Apr-13 23:34

Richard.Berry100 wrote:
the guy is a mate,

Even more important to have an agreed SLA as it is good to be clear on where the boundaries of being a mate and being an employer lie Wink | ;)

Richard.Berry100 wrote:
Not entirely sure what you mean here?

I take it there there is one central instance of an SQL Server rather than an instance on each workstation.
If there is one central instance then try to get it off the OS drive(probably the C: drive) so that disk access is not competing between OS calls and SQL Server calls - does that make sense?

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Richard.Berry1001-May-13 0:10

1-May-13 0:10

GuyThiebaut wrote:
I take it there there is one central instance of an SQL Server rather than an instance on each workstation.

If there is one central instance then try to get it off the OS drive(probably the C: drive) so that disk access is not competing between OS calls and SQL Server calls - does that make sense?

Yes - one server instance on the server. Any yes I see what you mean. It makes sense, and I do remember reading something like that, but when I installed, I seemed to have missed where I could specify a different install location, so it installed on the C: drive (default).. At the moment there are way more serious performance hits in the app itself (populating too many datagrid rows at a time etc) - Only realized this when I got a copy of their data a year after the initial installation... I'd been testing with a bare bones DB on my machine - so yeah what those poor users have been going though - aw shame!

Generic column mapping

Ravikiran72p30-Apr-13 1:57

Ravikiran72p

30-Apr-13 1:57

Hi,

I have been asked solution for this.

I have Vendors where i get different excel file (diffrenct structure) from different vendor.

Vendor1 excel file
------------------
Name, Address, Age

Vendor2 Excel file
------------------
CandidateName, PhysicalAddress, Age

what ever Header Names but meaning is same.

Order of Headers are may vary different...

but i need to map in Sqlserver with correct mapping means i have table like (CandidateName,CandidateAddress,Age).... Here i need to store correct data columns which are mapped from above two excels.

Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly.

Thanks,
Ravikiran

Eddy Vluggen30-Apr-13 9:49

Eddy Vluggen

30-Apr-13 9:49

Ravikiran72p wrote:
but i need to map in Sqlserver with correct mapping means i have table like (CandidateName,CandidateAddress,Age).... Here i need to store correct data columns which are mapped from above two excels.

Unless you have "something" that you can use to identify the type of data in that column, there's nothing to map. If both names and order change, then the computer will no longer be able to distinguish between a first-name (string) and a last-name (string).

I'd suggest to use the table-headers to "propose" a mapping for that particular vendor, based on the current file and your best guess (based on a "contains"-call on the header to try and identify it) and to save/use that.

Bastard Programmer from Hell Suspicious | :suss:

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

Mycroft Holmes30-Apr-13 13:56

Mycroft Holmes

30-Apr-13 13:56

Oh the joys of dealing with people who use Excel as a data source Mad | :mad:

.

The only reliable way will be to maintain a mapping table for each source, Load your data into staging tables (I use bulkcopy, creating a new table based on the source), then test the structure against your map, process or spit the dummy based on the structure check.

Never underestimate the power of human stupidity
RAH

GuyThiebaut30-Apr-13 21:49

30-Apr-13 21:49

Ravikiran72p wrote:
Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly.

The only solution I know of is to load the data excel data into raw tables - stage this data then qc it with queries etc.
Once you are happy with the integrity of the data you can then load it into your database.
Basically this amounts to a datawarehousing system - you will need human input between the staging and load to warehouse. This is where the qc comes into effect.
All data that comes from a client needs to be qc'd with a human eye as invariably there will be data that breaks your business logic.

Don't trust client data, especially if it is in Excel format!

If any data is incorrect then the standard procedure is to ask the client to resend the corrected data - it should never be the software or receivers job to 'correct' raw data, since as soon as you 'correct ' the data you then become responsible for its contents.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Re: Error on SQL server join

jschell1-May-13 8:34

jschell

1-May-13 8:34

Ravikiran72p wrote:
Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly.

Create an "adaptor" (concept rather than a specific type of implementation) specific to each vendor. I suggest STRONGLY that you use extensive error checking on such an adaptor because that is the only way you will detect when they change the format.

The vendor files are delivered to a vendor specific folder and that is used as a key to determine which adaptor is used to process them.

The adaptor could be written in either SQL or C#. I would choose C# because it provides more flexibility for this sort of operation. Note that a C# solution need not go directly to the database. In can just output a file, with a fixed known format, which is then used as an import file to SQL Server.

The adaptors should also have error reporting, logging is good, to report on failed conversion attempts, as well as when successful ones ran.

MariaDB on Linux

mbadi27-Apr-13 10:57

mbadi

27-Apr-13 10:57

How can I backup my db through the terminal, what is the command?

Error on SQL server join

DeveloperLife201526-Apr-13 13:12

DeveloperLife2015

26-Apr-13 13:12

Hi All,

I am trying to run this query on my database and I am getting an error Incorrect syntax near the keyword 'JOIN'.

USE IVRCallCenterTest
Go
SELECT *
FROM dbo.OutputPromptItem WHERE SegmentId=7198
JOIN dbo.OutputPrompt
ON dbo.OutputPromptItem.PromptId=dbo.OutputPrompt.PromptID
Go

what is wrong with my code ?

Thanks in advance

s_magus26-Apr-13 17:41

s_magus

26-Apr-13 17:41

Your Where clause needs to come after From.

SQL

USE IVRCallCenterTest
Go

SELECT *
FROM   dbo.OutputPromptItem JOIN 
       dbo.OutputPrompt ON OutputPromptItem.PromptId = OutputPrompt.PromptID
WHERE  SegmentId = 7198
Go

Re: Error on SQL server join

AANIL DHAKAD8-May-13 19:17

AANIL DHAKAD

8-May-13 19:17

You should use Where Clause at End of Statement
Like

SQL

SELECT *
FROM dbo.OutputPromptItem 
JOIN dbo.OutputPrompt
ON dbo.OutputPromptItem.PromptId=dbo.OutputPrompt.Promp WHERE SegmentId=7198

SQL Server 2012 Express on Network - cannot see attached databases - can see Master, etc.

PrissySC26-Apr-13 12:37

Re: SQL Server 2012 Express on Network - cannot see attached databases - can see Master, etc.

26-Apr-13 12:37

I went through security and added. Checked the owners and added sa and admin account. I just can't seem to figure out what I have missed! DBs detached, copied, and moved from test server to actual network server seems to have not included a "setting" or "configuration". Any ideas? Using Windows Authentication.

The odd is that I can connect and see the Master, but not the attached databases that were added. And yes, they were attached and brought online. The fact that I can see the master tells me that I have the router rules set correctly. I am open to any suggestions. Throw it at me. I have to have missed something obvious.

FIXED!

The problem exists in policy, whether group or user I did not explore further. Installed SQL Server as a local machine with local admin. I completely took the server offline for the initial setup. Reconnected using the suggested username/password as well.

Not a problem at all.

modified 25-May-13 11:05am.

Mycroft Holmes26-Apr-13 13:01

Mycroft Holmes

26-Apr-13 13:01

What UI are you using to manage the databases?

Never underestimate the power of human stupidity
RAH

Re: SQL Server 2012 Express on Network - cannot see attached databases - can see Master, etc.

PrissySC27-Apr-13 1:39

Re: SQL Server 2012 Express on Network - cannot see attached databases - can see Master, etc.

27-Apr-13 1:39

Server Mgmt Studio

jschell27-Apr-13 11:32

jschell

27-Apr-13 11:32

The following are the only possibilities.
- The database has not been attached,
- You are not connecting to the database server that you think you are.
- The user permissions are wrong.

PrissySC wrote:
<layer> The fact that I can see the master tells me that I have the router rules set correctly

If you couldn't route to the database server then you couldn't connect nor log in. It has nothing to do with whether you can see databases.

Re: SQL Server 2012 Express on Network - cannot see attached databases - can see Master, etc.

PrissySC28-Apr-13 1:41

Re: SQL Server 2012 Express on Network - cannot see attached databases - can see Master, etc.

28-Apr-13 1:41

See, that is where I am puzzled!

I specifically went in and gave public access as well as set the Owner to the account.

So then, thinking aloud, I should look at the owner.

Should I look at builtin\users or default admin account?

I already set the NT. From there, I assume, properties, user mapping. Checked the DB in question and provided membership to owner and public.

I will check the server roles and grant status for the user too. Hmmm .... at least you agree with me!

Thanks, and if you think of something else, I will check back and keep this thread updated. Smile | :)

Mycroft Holmes28-Apr-13 12:49

Mycroft Holmes

28-Apr-13 12:49

If you have restored/attached the database from a different server you may need to drop and reapply the user permissions, while the name is the same the id values are different.

While I use windows authentication in SSMS I have all my apps have a SQL identity and uses that, so a connection string ALWAYS has a userid and password, never integrated.

Never underestimate the power of human stupidity
RAH

Attached not seen over network still - will try ID values but ....

PrissySC7-May-13 7:07