How to Copy or Move MS SQL Database

I was struggling and wasted couple of hours trying to move a MS SQL database to a new server. Reason was that I kept on getting some error messages that I really did not understand properly. Let us see how to copy a MS SQL database in couple of simple steps.

You can use Database Back Up and Restore function for this task without any problem. However, there are couple of error messages that you have to aware.

1. Back your database from the Original location (Server A)
2. Copy it to the new location (Server B)
3. Create a instant of a database in Server B and restore the backup copy that you took from server A

Yes, it is a quite simple process. Lets handle couple of error messages you might have to face while you trying to copy the database to new location.


Error Message: The backup set holds a backup of a database other than the existing database. (Microsoft.SqlServer.Express.SMo)

Solution: Go to Options page (Restore Database) and check the Overwrite the existing database option.

Error Message 02: System.Data.SqlClient.SqlError: Directory lookup for the file failed with the operating system error 3(The System cannot find the path specified.) (Microsoft.SqlServer.Express.Smo)

Solution: In the options Page (Restore Database) check the Restore the database files as: pane. If your new server is 64 bit it might still looking the files in Program Files folder but actually SQL is installed in Program File (x86). Select the correct path.

Error Message 03: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Express.Smo)

Solution: Open the SQL Server Configuration Manager and restart the SQL Server.

KB970892 update failed

SQL Server 2005 Service Pack 3 update KB970892 has been failed in Windows 2003 for some reason and it is keep on stopping the SQL services every time it tries to install the update. Lets see how it can be solved in details.


Windows update related to SQL server Service pack 3 (KB970892) is been failing to install in Windows XP, Windows 2003 Environments. If you have a close look at the Event Viewer you can notice couple of Error messages related to this.

Product: Microsoft SQL Server 2005 Express Edition - Update 'GDR 4053 for SQL Server Database Services 2005 ENU (KB970892)' could not be installed. Error code 1603. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB970892_sqlrun_sql.msp.log.

Annoying part is if the Windows Update is set to Automatic them every time it tries this update will stop the SQL services which cause SQL Database driven websites to go offline.

Simple Registry Hack will fix this issue. I tried it on Windows 2003 Server and it should work on Windows XP in the same way around. Source NetScanTool Pro Inside Out Comment.


1. Go to Run, type regedit, and click ok, or press Enter.
2. Back up your registry, if you don't know how to do this, google it...
3. In Regedit, go to [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\Setup]
4. find the Resume REG_DWORD, and change its decimal value from 1, to 0.
5. Check MSSQL.2 and MSSQL.3 for the Resume key and change the value to 0 from 1.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Setup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.3\Setup

5. install the update, and restart the machine.

This worked like a magic and solve the KB970892 installation problem.

Add or Update your SQL databse with Excel

Today's hack is to show quick way to update or add thousands of SQL Database record using Excel. Best part of this hack is that you do not have to have much SQL skills or programming skills to update your database. This method is really helpful when you do not find any method to bulk update or add through your application.

Lets See how you going to update or add thousands of records with in couple of minutes using Excel.

Requirements

1. Exact SQL Query to Update or Add the SQL table.
2. String Concatenation in Excel

1. Exact SQL Query to Update or Add the SQL table

If you are familiar with SQL queries writing an query for add or update for a given table is not that hard. But I am too lazy to do that as more than query skills it requires to get all the column names and give the values for each column accordingly. Most of the present database management tools (MSSQL management tools or MySQL managements tools) gives add, update and other SQL queries for a given table. Most of the cases you just have to right click on the table and select the query you want and BINGO it will generate the query for you. Copy the SQL Query that was generate or you have written by your self.

2. String Concatenation in Excel
This is the tricky part and I have couple of secrets to revealed here. So it is better to go through a example and you can apply it to any SQL Query.
1. Open up new Excel work sheet and paste your query there. It will be in single cell.

In this case I will take a simple MS SQL query that will add the item type for set given item in ItemType table.

INSERT INTO [MyDB].[dbo].[ItemType] ([ItemNo],[Type]) VALUES ('1000','S1');
2. Then Identify the areas that will change from record to record and the areas that will stay the same for all the records.

I will bold all the area that are dynamic.
INSERT INTO [MyDB].[dbo].[ItemType] ([ItemNo],[Type]) VALUES ('1000','S1');

3. Now start taking each section of the query and paste it in different cells.

Sections for our example will be like this

Section 1 - INSERT INTO [MyDB].[dbo].[ItemType] ([ItemNo],[Type]) VALUES ('
Section 2- 1000
Section 3 - ','
Section 4 - S1
Section 5 - );
So each section should go into a different cell in the same row.





Tip You will notice that when ever you try to copy Section 3 (',') the first single quote will get disappear in Excel. You have to enter another extra quote to fix this problem. So Section 3 will be rewritten as follows.

Section 3 - '','

4. Now you have to recreate the SQL Query that you copied using Excel String Concatenation.

In the same row leave one cell space after the query and select the next cell. Click on Insert Function button (fx) and it will POP up a Windows. Select Text and then Concatenate. Another window will be open with Text1 Text2 fields. Apply each section for each text field in the same order.

The final result of String Concatenation should give you the exact same SQL Query you start with.

5. You are pretty close to finish updating couple thousands of records in your database. Now you have to get all the relevant record in your database and updated fields in Excel.
Tip: Always try to do or get the updated work in Excel. You can do that by providing the existing data in a Excel Sheet or any data entry or someone who is going to update your records.

Otherwise you have to copy and paste the data from another file type to excel and clean it up a little bit.
Anyway Excel is quite powerful in sorting, grouping filtering and changing thousands of fields as the same time. Speed and the options would be much better than your application (in most of the cases). Anyway it is a different set of skill which we will go through later.
6. Copy and paste fields in appropriate columns in your excel.

In our example I have only two fields and I will copy the set of Item numbers and Item Type Code is the two columns that we bold in Step 2.




7. Select each cell including the Concatenated one (but excluding the dynamically changing cells) and click on the small black box at the bottom right hand corner ner and drag it down until the row that last dynamic field is populated. It is some thing like if you have 9000 records you need to fill other cells (SQL Query) in those 9000 cells.
Warning : Always remember to Back up your database before applying this step.

8. Finally you all individual SQL query for all your records in the Concatenated field. Updating or Adding it really dose not matter. Select all the rows and copy and paste it to the SQL Query execution window. and Execute the Query. Either it will add all the records or updated.
That's it!

Warning : Always remember to Back up your database before applying this step.

Update queries may update the whole table or wrong row base on the conditions you have provided.

You can do deleting certain rows from database tables using this method as well. Again it will have the same risk deleting the whole table or wrong rows base on the condition you specify. Always double check before you execute the query.

Use this hack make you life easy and share your experience with us.

Restore failed - Microsoft SQL Server Management Studio

Are you getting an error while trying to restore Microsoft SQL Database via Microsoft SQL Server Management Studio?Additional information (Error Message)
System.Data.SqlClient.sqlError: Exclusive access could not be obtained because the database is in use (Microsoft.SqlServer.Smo)

Lets see what you need to do to solve this problem...
Reason for this error is most of the time database will be used by a program. You just have to kill that so database will be free to restore.

Right click on the Database after login in to the Microsoft SQL Server Management Studio with right previlages. Select Rasks and go to Detach. In general page you will see the database you have right clicked with a Message saying how many active connections are. Click on the message will pop up a new window with the Process info.

Right click on the process and kill Process will free the database to restore.

How to Prevent SQL Injection

Last time we discuss the simplest logic behind the SQL injection. Even though it looks like simple it can do a real damage to your website and its valuable data. If you are a webmaster or blogmaster you can follow this simple rules to prevent your website been attacked by SQL injection.

Validate user input 

Always remember to validate user input before it send to the SQL query. Always check whether user enters special characters (= ' ') or key words from scripts. You should apply user input validation for all the user input. Any of the user 

couple of good website tutorial teach you how to validate.

Validating user input in PHP - Nice tutorial explain the importance of user input validation. Tutorial start from 'Never trust user input' and go throught basic PHP validation methods.

Secure your web applications by validating user input ith PHP is another short but nicely explained tutorial explains couple of basic user input validations. 

msdn user input validations in asp.net tutorial explains basic asp.net validation for login page. 

Regular Expressions is handy way to vlidate user input.

Change your SQL queries style. 

First Style: Select * from USERS where USERNAME = ' + usernametxt.Text + ' AND PASSWORD = ' + passwordtxt.Text +';

Second Style: 

                      SqlCommand sc = new SQLCommand("Select * from USERS where USERNAME = @username AND PASSWORD = @password;", myconnection);

                      sc.Parameters.AddWithValue("@username", usernametxt.Text);

                      sc.Parameters.AddWithValue("@password", passwordtxt.Text);

If you are following the first style you should change it to the second style. In first style support SQL injection as it creates new SQL query with the user input, where in second method it always take it as a value (string , integer) and assign it to the parameter. It never create a new SQL query with the user input. 

This is two major steps you can take to stop SQL injection. 

Related Reading

SQL Injection Crazzy way to bypass login