Restore existing database in SQL 2005

1)Open Microsoft SQL Server Studio, Login with your SQL Credentials or using Windows Authentication mode.
2)Create New Database with name “DatabaseName”, Set path for MDF and LDF files on your local computer.
3) Right click on the created databse("DatabaseName") in Object Browser Window and select Task->Restore->Database option. You will get restore database wizard opened.
4) On General Menu, select radio option of “From Device”, then select backup file from browse file option.
5) After successfully selection of Backup file, Goto Options menu from left navigation pane and Mark “Overwrite Existing Database” to true.
6) Click OK

If you want to create database diagrams for restored database, you will get the following error.

Error: Database Diagram Support Objects cann't be installed because this database doesn't have a valid owner.



Solution: By default restored database consider system login ID as database owner. We need to change the database ownership manually, back to the owner that originally created the database when diagrams created. To accomplish this run the following query in SQL query analyzer

--Replace "DatabaseName" with your database name
USE DatabaseName--(change this Database name)
GO

--Following query will change owner name
EXEC sp_changedbowner 'sa';
GO

You can look at the current wner to the database by running the following query

EXEC sp_helpdb;
GO

Debug problem in older version Visual Studio(VS2003 & VS2005) with IE 8

I had done lot of research to fix my debug problem in Visual Studio 2005. Finally, I got the solution.

Problem: Debug is not working for VS 2005 with IE8

Solution:

1. Go to Start
2. Select Run
3. Enter RegEdit (this opens Registry Editor Window)
4. Go Thru HKEY_LOCALMACHINE\SOFTWARE\Microsoft\Internet Explorer\Main
5. Check for the key TabProcGrowth, if you haven't find add new DWORD with the name TabProcGrowth
6. Set the value to 0

Reason: This problem is because of opening of multiple instances in IE8 browser. IE8 has a feature called "Loosely-Coupled Internet Explorer (LCIE)", which isolates the Internet Explorer frame and its tabs into separate processes. Because of this seperate process between IE frame and Tabs, Older versions of VS debuggers are getting confused to connect to the exact process.

Hope this will fix your problem.

Use nvarchar(max) for ntext in MS SQL 2005 and later versions

Error:
The ntext data type cannot be selected as DISTINCT because it is not comparable

Prob: Union fails, If a table contains nText datatype field

Answer: Use nVarChar(MAX)(This works only SQL 2005 and Above versions)

Similarly, use VarChar(MAX) for TEXT, varbinary(max) for image

Connecting to Databases from SSIS COnnection manager

Couple of guys asked me about conencting to ACCESS from SSIS. I thought, It's betetr to put in my blog..

1) Right click in Connection manager window(Which is appear at the bottom of the visual studio)
2) Select New OLEDB Connection
3) You will get "Configure OLEDB Connection Manger:" window. Click on NEW button
4) Select Provider, What ever database you want to connect
Ex: use OLEDB\SQL Native Client for connecting to SQL, Native OLEDB\Microsoft JEt4.0 OLEDB Provider for connecting to ACCESS, Microsoft OLEDB Provider for Oracle for Oracle e.t.c..
5)Select the file if data base is Access, Eneter server name if you want to connect to SQL, For conencting the Oracle data base you need to use Server variable which is used in tns ORA file(For creating connection string in tns ora file Click here).
6) Click on Test Conenction to amke sure whether it's conencted or not.
7) Click Ok

Compare two complete ROWS( all columns of each row) in two seperate TABLES

If you have two separate tables with same primary key and want to check the updates in the other table,using BINARY_CHECKSUM would be the best answer. But need to careful about column data types. Column Data types shouldn't be text, ntext, image, XML, and cursor.

Here is T-SQL query to compare two rows from different table:

Take ORDERS1 as one table and ORDERS2 as other table

SELECT ORDERS1.PRIMARYKEY_ID
FROM
(SELECT PRIMARYKEY_ID, BINARY_CHECKSUM(*) AS CHECKVALUE FROM ORDERS1) AS CHECKSUMTABLE1
INNER JOIN
(SELECT PRIMARYKEY_ID, BINARY_CHECKSUM(*) AS CHECKVALUE FROM ORDERS2) AS CHECKSUMTABLE2
ON CHECKSUMTABLE1.PRIMARYKEY_ID= CHECKSUMTABLE2.PRIMARYKEY_ID
WHERE CHECKSUMTABLE1.CHECKVALUE <> CHECKSUMTABLE2.CHECKVALUE

Save not permitted in SQL Server 2008 Express Management Studio

I got the following message, When I was trying to change or add new columns for tables in SQL Server 2008 Express.

Message: " Save not Permitted. The changes you have made require the following tables need to dropped and re-created. you have either made changes to a table that can't be re-created or enabled the option preventing saving changes that require table to b re-created."

The only choice I had was to click to cancel, or to choose to save the message to a text file. Which won't useful to us. I found the answer for this bug in SQL Online. here it is..

Solution:

Go to Tools -> Options -> Designers -> Uncheck "Prevent saving changes that requires table re-creation"


I hope it works for you also...