SQL developers are increasingly required to include tracking data changes into their custom applications. The information about what change was made – insert, update or delete – who made it, when, and also which computer and application was used, is very useful and can be essential for troubleshooting.April 4, 2013
If there is a need to quickly revert an inadvertent change such as an Update without a Where clause, to recover missing or corrupt data, to move data across development, test, integration and production environments or even just to avoid generating a report using a busy production SQL Server database as a data source, it brings the question if there is a way just to restore specific rows instead of restoring the whole database backup.April 4, 2013
Very often, initial database design does not fit the current criteria and requirements anymore. It is therefore necessary to periodically align to what our current needs and standards are. When working with a large database, any idea of changing the name of one or more SQL objects can be a real challenge.
Changing an object name is a simple operation and it could be done easily using the sp_rename system stored procedure or by utilizing the rename and alter … rename statements for indexes and triggers.April 4, 2013
Renaming a table or view column is not something a SQL developer needs to do every day. However, when the time comes, how can it be done easily and effortlessly?April 4, 2013
When trying to delete a database object, which has an object that depends on it, no problems can be caused, since it will not allow to be deleted:
Drop failed for Table ‘HumanResources.Employee’. (Microsoft.SqlServer.Smo)
Could not drop object ‘HumanResources.Employee’ because it is referenced by a FOREIGN KEY constraint.
If SQL queries perform sluggishly and severely impact SQL Server performance, the solution can be very simple – in some cases all that needs to be done is to not use the * wildcard in the SELECT statement.April 4, 2013
Many DBAs think of database documentation as “grunt work”. It’s one of those tasks that you never have enough time for, as you’re always busy with other higher priority tasks.
But what if it could be made automatic?April 4, 2013
Every production database requires maintenance and improvements in order to meet the ever-changing demands. The easiest way to test the new requirements, such as new functionalities and improvements, is to try them on a test database.
So, creating the test base from the backup of the production database would be the easiest way… at least that’s what it looks like. But, is this really the case?April 4, 2013
All of a sudden, you realize that something’s wrong with your database objects. Some stored procedures are missing, functions are there but they don’t work as expected since their code seems to be altered, and some triggers are created and fired which enhances the chaos you’re in.
After the initial shock, you start investigating. Don’t rule out a SQL injection attack, as it’s one of the most common web application security issues.
What is a SQL injection?
A SQL injection attack is an attack in which a code that attacks the database is inserted into a web site. It’s one of the most common types of web application security vulnerabilities. So it’s better to be prepared, or at least well informed
SQL injections can have an immediate (first-order attacks) and delayed effect (second-order attacks)
It seems something went awry with the SQL Server. It’s sluggish, behaves erratically, produces heavy network traffic, there is a significant increase in the server processor or memory utilization, and to top it all there are reports of or database objects and data being damaged or missing.
Where to start looking?
“I’ve mistakenly deleted/updated important records in a SQL Server table. Is there any way to get these records back?”
This is a frequently asked question, and very often a headache for DBAs. If they are careful and make regular database backups, they can recover the lost database data even where the SIMPLE recovery database model is used.
If that is the case, then database backups are the only solution to recover the lost database data. The deleted data has to be from the period prior to the database backup creation.April 4, 2013
You’ve inadvertently executed (or experienced other data loss scenarios involving) a DELETE, TRUNCATE or DROP statement against a database and lost valuable records.April 4, 2013
The SQL DBA’s worst nightmare is the loss of data due to their own mistake. It’s not uncommon for someone to improperly execute or even forget a WHERE clause entirely when executing DELETE or UPDATE queries, resulting in potentially millions of rows of compromised data
So, which SQL Server data recovery options are available?April 4, 2013
Ooops! Say you’ve performed an UPDATE statement without a WHERE condition against the database and overwritten a bunch of rows with bad data. How can you bring the right values back?April 4, 2013
Integrating SQL Server with business infrastructure often requires that developers prepare T-SQL code for use within various client codes, such as C#, PHP, and Perl. This means adding language specific code that will be inserted before and after every SQL statement, as well as escape characters for quotes and code to terminate a line, so that the other languages can parse and execute it correctly. Updating the code manually is a time-consuming process prone to mistakes.April 4, 2013