How To Migrate Database From Microsoft SQL Server 2000 To Microsoft SQL Server 2014

It is easy to Migrate Database From Microsoft SQL Server 2000 To Microsoft SQL Server 2014

Before you start make sure that you have administrative accounts on both servers.
This is 2 step process.
I would recommend reading this post for user export
Also read this document from Microsoft knowledge base.
1. we are going to export database using SQL Scripts from our old MsSQL 2000 server and then use that SQL Script file to create new version of Same database on our New MsSQL2014 server.

Ok on SQL 2000 server right click database you want to migrate then click on Generate SQL Script… as shown in figure 1
figure1

Figure 1

Then click on Show All button as shown in Figure 2
mssqlexport_figure2

Figure 2

Now choose objects to create scripts for:
mssqlexport_figure3

Figure 3

Next we decide on Formatting as shown in Figure 4
mssqlexport_figure4

Figure 4

Finally we need to setup some Security and Table Scripting Options and as well one or multiple files, I prefer to use single file per database as shown in Figure 5.
mssqlexport_figure5

Figure 5

Click Ok and wait for server to do its thing.
Now To the 2014 server…

Open Microsoft SQL Server Management Studio and connect to your 2014 server
click on open file or Ctrl + O (letter o not zero)
find your Exported SQL Script file from 2000 server.
Check your CREATE DATABASE directive and make sure that FILENAME is pointing to the place where you want new database to be stored.
2. Once all this is done we can do data Import using SQL Server Import and Export Wizard as explained in this post.

moving mysql databases using mysqldump & ssh

moving mysql databases using mysqldump & ssh

Moving MySQL Databases Using mysqldump

On old server:

Check the /etc/mysql/my.cnf file and make note of the address listed in bind-addresses.

On the new server:
When you install mysql, define a temporary password to root. This password will get overwritten during the transfer, after a restart of the mysql service.

Add a new interface eth0:x in /etc/network/interfaces with the ip address noted in the old server’s /etc/mysql/my.cnf file. LEAVE THIS INTERFACE DOWN UNTIL THE FINAL SWITCH.

Edit the /etc/mysql/my.cnf file

bind-addresses = <address of the new server> or, less specific, 0.0.0.0

Restart the service mysql. NOTE: reload doesn’t load the changes in my.cnf.

Service mysql restart

Use this command to move the databases:

ssh (your username)@(old-server’s FQDN or IP) “mysqldump -u (db-username, probably root) –all-databases > /(dirpath)/(filename)” | “mysql -u root -p (temp pw designated at mysql install on the new server) -h (ip address of new server) < /(dirpath)/(filename)”

NOTE: After the restoration of the databases on the new server, your current credentials will work until the mysql service is restarted.

If, for any reason,  you need to do a complete re-install of MySQL, use this procedure to remove MySQL completely from server:

service mysql stop #or mysqld
deluser mysql
delgroup mysql
killall -9 mysql
killall -9 mysqld
apt-get remove –purge mysql-server mysql-client mysql-common
apt-get autoremove
apt-get autoclean
rm -rf /var/lib/mysql

Then re-install:

apt-get install mysql-server
ssh-keygen -R (FQDN)

Matt Long

3/31/2015

 

Connecting to a database with PHP

Connecting to a database with PHP

Install these packages:

#apt-get install apache2
#apt-get install mysql
#apt-get install php
#apt-get install php5-mysql

Create a test user, password and database

At the sql server, Log into mysql:

#mysql -u root -p

Issue the following commands to create a user “test” and a password “password”:

CREATE USER ‘test’@’localhost’ IDENTIFIED BY ‘password’;
CREATE USER ‘test’@’%’ IDENTIFIED BY ‘password’;

GRANT ALL ON *.* TO ‘test’@’localhost’;
GRANT ALL ON *.* TO ‘test’@’%’;

CREATE DATABASE instruments

 

Exit mysql:

q

Log back in as the user you just created, attaching to the new database:

mysql -u test -p instruments

Execute a

s

to see the status. Verify the user and database.

Test PHP Functionality:

Create a file named “something”.php and insert the following text:

<?php echo ‘hello world’.time();
/* <?php echo ‘mysqli_connect(); print_r(mysqli_query(‘select now()’)) ; ?> */
?>

Place this file in the /var/www directory

Open a browser and point to that file:

http://<your server>”something”.php

You should see hello world and the date.

To test your connection to the database via PHP:

Create a file with the following text and name it “something”.php

Edit the line “$db = mysql_connect(“206.207.94.34″,”test”,”password”);” to reflect your server & user.

<?php
$db = mysql_connect(“206.207.94.34″,”test”,”password”);
if (!$db) {
die(“Database connection failed miserably: ” . mysql_error());
}
else

die(“Database Success!!!: ” . mysql_error());
$db_select = mysql_select_db(“instruments”,$db);
if (!$db_select) {
die(“Database selection also failed miserably: ” . mysql_error());
}
?>
<html>
<head>
<title>Step 3</title>
</head>
<body>
<?php
$result = mysql_query(“SELECT * FROM mytable”, $db);
if (!$result) {
die(“Database query failed: ” . mysql_error());
}
?>
</body>
</html>

Place this file in the /var/www directory

Open a browser and point to that file:

http://<your server>”something.php

Success!!!

HANDY MYSQL COMMANDS:

Note that all text commands must be first on line and end with ‘;’
? (?) Synonym for `help’.
clear (c) Clear the current input statement.
connect (r) Reconnect to the server. Optional arguments are db and host.
delimiter (d) Set statement delimiter.
edit (e) Edit command with $EDITOR.
ego (G) Send command to mysql server, display result vertically.
exit (q) Exit mysql. Same as quit.
go (g) Send command to mysql server.
help (h) Display this help.
nopager (n) Disable pager, print to stdout.
notee (t) Don’t write into outfile.
pager (P) Set PAGER [to_pager]. Print the query results via PAGER.
print (p) Print current command.
prompt (R) Change your mysql prompt.
quit (q) Quit mysql.
rehash (#) Rebuild completion hash.
source (.) Execute an SQL script file. Takes a file name as an argument.
status (s) Get status information from the server.
system (!) Execute a system shell command.
tee (T) Set outfile [to_outfile]. Append everything into given outfile.
use (u) Use another database. Takes database name as argument.
charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (W) Show warnings after every statement.
nowarning (w) Don’t show warnings after every statement.

For server side help, type ‘help contents’

Matt Long
01/27/2015

Getting Started with Mysql and Heidi SQL

Getting Started with Mysql and Heidi SQL

At the target server prompt, become root and type:

apt-get install mysql-server

provide a a password for the root mysql user

Verify that mysql is running:

netstat -tap | grep mysql

you should see something like this:

tcp 0 0 localhost:mysql *:* LISTEN 21921/mysqld

Verify that you can log in:

mysql -u root -p

 

You should be prompted for the root password and your prompt should change to:

mysql>

Type “help” or “?” to review the commands.

Type to exit:

q

To configure Mysql for remote connections edit /etc/mysql/my.cnf

Verify the port number. The default is 3306
Set the bind-address to 0.0.0.0

Log in to mysql as above and execute the following commands.

CREATE USER ‘myuser’@’localhost’ IDENTIFIED BY ‘mypass’;
CREATE USER ‘myuser’@’%’ IDENTIFIED BY ‘mypass’;

GRANT ALL ON *.* TO ‘myuser’@’localhost’;
GRANT ALL ON *.* TO ‘myuser’@’%’;

Note: the ‘localhost’ and ‘%’ are the correct syntax. Only change myuser and mypass.

q

Restart mysql:

/etc/init.d/mysql restart

You should be able to install Heidi SQL and log in now.

Matt
01/23/2015

Working with GB Large mysql dump files -splitting insert statements

Working with GB Large mysql dump files -splitting insert statements

Recently I had to restore a huge database from a huge MySQL dump file.
Since the dump file was had all of the create statements mixed with insert statements, I found the recreation of the database to take a very long time with the possibility that it might error out and rollback all of the transactions.

So I came up with the following script which processes the single MySQL dump file and splits it out so we can run the different parts separately.

This creates files that can be run individually called

  • mysql.tblname.beforeinsert
  • mysql.tblname.insert
  • mysql.tblname.afterinsert
cat mysql.dump.sql| awk 'BEGIN{ TABLE="table_not_set"}
{
	if($1=="CREATE" && $2=="TABLE")
	{ 
		TABLE=$3
		gsub("`","",TABLE)
		inserted=false
	}
	if($1!="INSERT") 
	{
		if(!inserted)
		{
			print $0 > "mysql."TABLE".beforeinsert";
		}
		else
		{
			print $0 > "mysql."TABLE".afterinsert";
		}
	} else {
		print $0 > "mysql."TABLE".insert"; 
		inserted=true
	}
}
'

Great SQL Formatting Tool

Great SQL Formatting Tool

We often deal with very complex, dynamically generated SQL Statements which run from our applications.

If we need to debug them for any reason we often have to display them to the screen and then copy and paste them in to an SQL Query window. The problem is that those SQL Statements are not always formatted to be very readable. Sometimes they might even be on a single line. This requires a bunch of time going through and reformatting the sql statement, making it legible for debugging.

I have used this tool SQLinFORM several times in the past but I keep forgetting about when I dont have to use it very often.

I just copy and paste the SQL into the window and click Format.
It does a great job formatting code quickly and even has some options for how you would like to see the output, I then select the output and paste it into my SQL Query window.

If you use it often they do have a version for sale.

 

Call Now Button(208) 344-1115

SIGN UP TO
GET OUR 
FREE
 APP BLUEPRINT

Join our email list

and get your free whitepaper