Restoring Accidentally deleted or Updated Rows without Backup restore - Sql Server

In Reference to my previous post : Restoring Database to a previous point of time –Recommended for all DBA’s

have been looking some thing better to avoid restoring database and recover the deleted \ updated data for given tables.

Finally got the solution for the same. Hurray!!!

Following is the link to ultimate tool designed by Mr.Imran.

Restoring Deleted Rows - Sql Server

Restoring Modified Rows - Sql Server

Happy coding :)

Update Mutiple Tables in Single Query - Sql Server

Use Views - Simple :)

Reference: http://weblogs.sqlteam.com/brettk/default.aspx

I must say the blog mentioned above is SUPER!!!


NOT POSSIBLE to update multiple tables.

Important tools for Sql Server

http://weblogs.sqlteam.com/mladenp/archive/2007/11/20/Free-SQL-Server-tools-that-might-make-your-life-a.aspx

SSMS Tools Pack : Highly recommended

For Formating: http://www.tsqltidy.com/tsqltidySSMSAddin.aspx

 

Restoring Database to a previous point of time --Recommended for all DBA's

The scenario

A coworker calls you in a panic because he accidentally deleted some production data, and he wants you to restore the lost records.

Read further

http://www.techrepublic.com/blog/datacenter/restore-your-sql-server-database-using-transaction-logs/132

Preferable:

http://www.linglom.com/2009/07/03/solved-system-data-sqlclient-sqlerror-the-tail-of-the-log-for-the-database-dbname-has-not-been-backed-up/

Create Comma Separated List- Sql tips and tricks

Create list of Integer type column

[code]

DECLARE @listStr VARCHAR(MAX)

SELECT @listStr = COALESCE(@listStr+',' ,'') + CAST(ColumnName AS Varchar(10))
FROM TableName
SELECT @listStr
GO

[/code]

Replace ColumnName and TableName

Function to replace all special characters - Sql Sever

[code language="sql"]
CREATE FUNCTION dbo.RemoveSpecialCharacter(@Str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN

SET @Str = Replace(@str, Char(13), ' ')
--replace all enters with space
SET @Str = Replace(@str, Char(9), ' ')
--replace all tabs with space
SET @Str = Replace(@str, Char(10), ' ')
--replace all line feeds with space

RETURN @Str

END
[/code]

Useful function to be used for data export to excel sheet(Using SSIS) as the "Enter" makes the data to move to next line and the reports gets distoreted.

SSIS Container Good Examples

For Each Container Example

http://beyondrelational.com/modules/2/blogs/106/posts/11122/loop-through-each-record-in-a-text-file-recordset-destination.aspx

Mapping SP variable in OLEDB Source-->

 http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx

Useful Special Characters For Sql Server Dynamic Queries

Char(44) -- comma
Char(39) -- Single Quote
char(9) -- Tab
char(10) -- Line feed
char(13) -- Enter\Carriage return
Char(32) -- Space

Go to SSMS.. Type :

[code language="sql"]

Select Char(44)

[/code]

and see the magic :)