Top 10 things Stellar Phoenix SQL Recovery user needs to know

Recover your corrupt SQL Server Database with a dedicated software application is a SQL Recovery Software provided by Stellar. The application runs directly from your system after installing software on specified location. Here I am sharing useful information of SQL Recovery Software. 

There are two types of files, mdf and ndf which manage your data in proper format. The ndf file is a secondary file, if you have a large amount of data; you probably have a secondary file (ndf) to save your data. The new feature is to recover large ndf files. Now you have no need to worry about large file recovery, Stellar SQL Recovery can also recover large corrupt ndf files. The SQL Recovery Software should run on copy of the corrupt file.

In any version of SQL Database, if you find any problem to find the corrupt mdf file then you can easily search it by using the "Find Option". Just click on this button & the application find into your system that appears immediately in list view format. You can choose the file that you want to recover from this list. 

The new version of SQL Server Database launched in 2011. Another new feature is- supports to latest version of SQL Server Database that is SQL Server 2008 R2. 

Fast scanning algorithms — you can recover database quickly & accurately without completing the whole recovery. 

In addition to the whole process information, the new set includes separate log report after scanning database which shows all recovery process. I am eager to see how this apps sharing this process in the software. 

You can also recover XML data types and XML indexes with this software.

You cannot save preview of your database in demo version. For this you have to purchase full version of the software.

You can check the preview more than one objects at a time. Click on either one to multiple objects to recover it. Save it quickly and easily on location as you like.

You should perform software updates as soon as they become available. The easiest way for users to update your software is through update button. If you are using software, when an update is available, you can see it by clicking update button the top fifth right button on the software screen. Just click on this button to install the new updates.

If any of option isn’t used by you just yet, don’t worry use it now & give your precious comments about it.

Source : http://data-base-recovery.blogspot.in

SQL Database Recovery Terms

As we know in SQL Database recovery we have these three options which we use commonly:

• Restore from backup
• DBCC Command & Repair command or 
• Third Party SQL Database Recovery Software

So, I would like clear most of the doubts related to above three options:

Is “REPAIR_ALLOW_DATA_LOSS” command not cause data loss:  It all totally depends on corruption level. When you are using this command, you should make up your mind that you can also lose you data. That's why the option is named that - seriously.

Can we run repair without running DBCC CHECKDB: No, we can’t. Repair is an option to one of the consistency-checking commands (DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKDB

Note: DBCC CHECKFILEGROUP and DBCC CHECKCATALOG don't support repair

Is Repair fix every SQL Database corruption: No, it don’t fix, Its all depends on corruption level. There are some errors that DBCC CHECKDB cannot fix.  For more detail please check this post. 

Repair is safe to use on system databases: No, it’s not safe. We should never use it on master or tempdb because they cannot be put into single-user mode.  

Is 'REPAIR_REBUILD' resolves every SQL Database corruption: No it’s not true. REPAIR_REBUILD only fixes problem in nonclustered indexes. 

Is EMERGENCY mode repair always helps: No, it’s not repair. If there is something broken in the file-system than the command get fail. 

Can we roll back repair command?: It depends. You can roll back everything if you started an explicit transaction. But users do it very rarely.

Note: EMERGENCY mode repair can never be rolled back.

Is SQL Database Recovery Software safe to use: YES, when you are using this software, you should first make a backup of your database. I always first preferred restore from backup solution so I will also recommend you it as a first step. You should only use this option when you don't have backup.  

Before using any recovery step, you should be properly aware about their pros & cons.  It will help you to choose better solution to recover your corrupt SQL Server database. 

Source : http://data-base-recovery.blogspot.in

Changing of Server Authentication Mode in SQL Server 2008 R2

During installation, SQL Server Database Engine is set to either Windows Authentication mode or SQL Server and Windows Authentication mode. This topic describes how to change the security mode after installation.

If Windows Authentication mode is selected during installation, the sa login is disabled and a password is assigned by setup. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To use the sa login, use the ALTER LOGIN statement to enable the sa login and assign a new password.

Security Note:The sa account is a well-known SQL Server account and it is often targeted by malicious users. Do not enable the sa account unless your application requires it. It is very important that you use a strong password for the sa login.

The sa login can only connect to the server by using SQL Server Authentication.

To change security authentication mode

1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
   
   

To restart SQL Server from SQL Server Management Studio

• In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login by using Transact-SQL

• Execute the following statements to enable the sa password and assign a password.

  
  ALTER LOGIN sa ENABLE ;
  GO
  
  ER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
  GO
  AL
  
  T
  

  To enable the sa login by using Management Studio

  
  
  
  In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
  
  
  On the General page, you might have to create and confirm a password for the sa login.
  
  
  On the Status page, in the Login section, click Enabled, and then click OK.
  
  
  Courtesy : http://bnjho.blogspot.in & http://sapost.blogspot.in/
  
  
  
  

Shrink a Database SQL Server 2008 R2

This topic describes how to shrink a database by using Object Explorer in SQL Server Management Studio. The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE. For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system.

To shrink a database

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand Databases, and then right-click the database that you want to shrink.
3. Point to Tasks, point to Shrink, and then click Database.
4. Optionally, select the Reorganize files before releasing unused space check box. If selected, a value must be specified for Maximum free space in files after shrinking.
   Selecting this option is the same as specifying a target_percent value when executing DBCC SHRINKDATABASE. Clearing this option is the same as executing DBCC SHRINKDATABASE. By default, the option is cleared.
5. Enter the maximum percentage of free space to be left in the database files after the database has been shrunk. Permissible values are between 0 and 99. This option is only available when Reorganize files before releasing unused space is selected.
6. Click OK.
   
   
   Courtesy : http://bnjho.blogspot.in & http://sapost.blogspot.in/

Restore database backup of SQL Server 2008 to SQL server 2005

Step by Step Guide

1) Start convert wizard

Open SQL Server Management Studio2008. in 'Object Explorer', right click the database that you want to convert. Select 'Tasks' > 'Generate Scripts...'.

2) Next

Click 'Next'.

3) Select database and objects

Select the database that you want to convert, and check on 'Scripts all objects in the selected databases'

4) Convert Options

Set options:

'Script for Server Version' = 'SQL Server 2005'
'Script Data' = 'True'
'Scirpt Database Create' = 'True'
    

5) Output Option

Select option 'Script to file', 'Single file' and 'Unicode text'.

6) 'Finish'

View summary and click 'Finish'.

7) Result

Now you got a complete database creation script with data. It can be executed on target database server.

 

8) Amend Script

Open the generated script in SQL Server Management Studio 2005. Find the following section and amend the path to proper data folder

CREATE DATABASE [StockTraderDB] ON  PRIMARY 
( NAME = N'StockTraderDB', 
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB.mdf ,
 SIZE = 4352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'StockTraderDB_log', 
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB_log.LDF',
 SIZE = 6272KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

9) Execute the Script

When finished, You should get converted database of SQL Server 2005

Courtesy : http://potools.blogspot.in/ & http://sapost.blogspot.in/

SQL Server 2000 installation fails with "...previous program installation..."

Error:
        SQL Server 2000 installation fails with "...previous program installation..." error message

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system.

Solution:

• Restart the computer and see if you are able to run the setup. If the restart does not help, use the steps that follow.
• Perform these steps and then run the SQL Server 2000 setup again:
• Click Start, and then click Run.
• In the Open dialog box, type: "Regedit" (without the quotation marks) or "Regedt32" (without the quotation marks)
• Click OK.
• NOTE: Please make sure that you only delete the value mentioned, not the whole session manager key.
• In Registry Editor, expand the following registry subkey:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager 
• On the File menu, click Export.
• NOTE: In Microsoft Windows 2000, click Export Registry File from the Registry menu.
• In the File name text box, type: "Session Manager Key" (without the quotation marks) 
• Click Save.
• In the right-pane of the Registry Editor window, right-click PendingFileRenameOperations. On the shortcut menu that appears, click Delete.
• In the Confirm Value Delete message dialog box that appears, click Yes.
• On the File menu, click Exit.

NOTE: In Windows 2000, click Exit on the Registry menu.

Restart the computer.
Using the Registry Editor, verify that the PendingFileRenameOperations registry value is not available.

Note: The PendingFileRenameOperations registry value may be re-created when you restart the computer. If the registry values are re-created, delete the PendingFileRenameOperations registry value again by completing steps a through j, and then run SQL Server 2000 Setup. Do not restart the computer before you run SQL Server 2000 Setup.

Courtesy : http://sapost.blogspot.in/

How to Repair a SQL Server 2005 Suspect database

Sometimes when you connect to your database server, you may find it in suspect mode. Your database server won’t allow you to perform any operation on that database until the database is repaired.

SQL server database can go in suspect mode for many reasons; some of them are given below:

Improper shutdown of the database server
Corruption of the database files
Unavailable device files
Unavailable database files
Database resource used by operating system
SQL Server incorrectly asserts free data page space when a row is inserted

To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give the errors in the database.

To repair the database, run the following queries in Query Analyzer,

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

and you are done. 

You should keep one thing in mind while using the above queries that the repair mode used here , REPAIR_ALLOW_DATA_LOSS, is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

Select Edit All Records In SQL Server 2008

In SQL Server 2008, the default numbers of rows for selecting and editing records are 1000 and 200 respectively. Sometimes if the records are greater than 200 and you want to edit some record then you have to change the query to get all the records. This is time consuming and you have to repeat this exercise whenever the records are greater than 200. The solution for getting all the records for selecting and editing is given below in steps:

1- Open SQL Server 2008 Management Studio
2- Goto Tools
3- Click "Options"
4- Expand the tree of "SQL Server Object Explorer"
5- Click "Commands"
6- Change "Value for edit top < n > rows command" to 0
7- Change "Value for select top < n > rows command" to 0
8- Click "OK"

SQL SERVER INTERVIEW QUESTION - PART 2

SQL SERVER INTERVIEW QUESTION - PART 2

What is normalization?

Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.

What is a stored procedure:

Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.

What is the difference between UNION ALL Statement and UNION ?
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.
Example for Stored Procedure?
They are three kinds of stored procedures,1.System stored procedure – Start with sp_2. User defined stored procedure – SP created by the user.3. Extended stored procedure – SP used to invoke a process in the external systems.Example for system stored proceduresp_helpdb - Database and its propertiessp_who2 – Gives details about the current user connected to your system. sp_renamedb – Enable you to rename your database

What is a trigger?
Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.

What is a view?
If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.

What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

What are the types of indexes available with SQL Server?
There are basically two types of indexes that we use with the SQL ServerClustered -
1. It will format the entire table, inturn physically sort the table.
2. Only one clustered index can be created for a table.
3. Data will be located in the leaf level.
4. By default, primary key will create clustered index on the table.
Non-Clustered Index
1. It wont touch the structure of the table.
2. It forms an index table as reference to the exact data.
3. A reference to the data will be located in the leaf level.
4. For a table, we can create 249 non clustered index.


Courtesy : http://mrsupport.blogspot.com/

SQL SERVER INTERVIEW QUESTIONS - PART 1

SQL SERVER INTERVIEW QUESTIONS - PART 1

What is the significance of NULL value and why should we avoid permitting null values?

Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?

The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.

The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

What is DTS in SQL Server ?

Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.

What is the difference between SQL and Pl/Sql ?

Straight forward. SQL is a single statement to finish up our work.Considering, I need some data from a particular table. “Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Procedural Logic / SQL. 

What is the significance of NULL value and why should we avoid permitting null values?

Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

SELECT STATEMENT IN SQL SERVER

 

STRING FUNCTIONS IN SQL SERVER

Substring/Len/replace/Ltrim/Rtrim

Courtesy : http://mrsupport.blogspot.com/

MANAGEMENT STUDIO IN SQL SERVER 2005

On Yesterday I got a call from one of my post office saying that no applications are working . It shows an error that server is not exists. Firstly I thought that it is problem with network connections and I asked themto restart the server machine and try again but then also this error exists. I came to the office and tries to run theapplication. But seeing the error I thought that this problem with SQL server. On tryingto open the SQL server 2005 management studio I got an error. The error is as follows

“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server wasnot found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.NetSqlClient Data Provider) “

First I wondered to see the error while connecting to server 2005. I never come up on such type of error during period as SA. While Contacting fellow Sys Admins they told that they had never come across such errors. While searching for the solutions on net But that not worked for me.

The main check points are

.Make sure your database engine is configured to accept remote connections
Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration

Click on Surface Area Configuration for Services and Connections
Select the instance that is having a problem > Database Engine > Remote Connections
Enable local and remote connections

Enable the TCP/IP in surface area configuration

Check the fire wall status and off the firewall if it is on and allow exception for programsSQL server and SQL server browser

Enable SQL Server Browser (Start the SQL server browser n service)

But then also I am not able to connect to SQL server management studio. But while restarting the server machine I found that the windows server administrator password was missing. Earlier it requires a password for opening the windows server. Then I gotto users (By right clicking computer---Manage----Local User) and set pass word for Administrator (Recreated the earlier Password) and restarted the server.

After this SQL Server is working and I can open management studio correctly.

via : saparavur.blogspot.in & http://sapost.blogspot.in/

How to Delete all records from all tables from Database

It is very serious one. Please do carefully

Following is the SQL query used to delete all records from all tables from Database.
This is useful during development scenarios where you need to get rid of all the dummy records and insert a fresh data.

SQL Query: Appropriate comments are provided to explain its operation

/* The Following set of queries is used to delete all records from all tables from your database */

/* Disables Referential integrity */

/*---------------------------------*/

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

/* Delete records from tables */

/*----------------------------*/

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1

DELETE FROM ?

else

TRUNCATE TABLE ?

'

GO

/* Enables Referential integrity */

/*-------------------------------*/

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

/*Query ot be used if you would like to reseed all table*/

/*------------------------------------------------------*/

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1

DBCC CHECKIDENT (''?'', RESEED, 0)

'

GO

Source : http://mrsupport.blogspot.in/

SQL: Query to find Duplicate Rows in a Table

If you would like to list duplicate rows with the count for a table, the following query would be helpful.

Query: Duplicate Rows

SELECT Column1,Column2,Column3,Count(*)
FROM dbo.TableName
GROUP BY Column1,Column2,Column3
HAVING ( COUNT(*) > 1 )

And there are scenarios where we need to find the combination of Columns in a table occurred only once. In such case the following query could be used.

Query: Unique combination of columns

SELECT Column1,Column2,Column3,Count(*)
FROM dbo.TableName
GROUP BY Column1,Column2,Column3
HAVING ( COUNT(*) = 1 )

Hope it helps.
Source : http://mrsupport.blogspot.in/

Search for a Text in whole Database SQL

Search for a Text in whole Database SQL

Hi,

There was a requirement to search for a text in the whole SQL database. found this Stored procedure very useful. though it is worth sharing...

Resource: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

-- SearchAllTables ''

CREATE PROC SearchAllTables

(

@SearchStr nvarchar(100)

)

AS

BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ''

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

FROM ' + @TableName + ' (NOLOCK) ' +

' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

)

END

END

END

SELECT ColumnName, ColumnValue FROM #Results

END