Script Data in MS SQL Server 2008 Database Tables using Generate SQL Server Script Wizard

Open the Generate Scripts SubMenu Item from Task Menu

First of all, open the Microsoft SQL Server Management Studio which is installed from the Client Tools of a MS SQL Server 2008 installation package.

Connect to a MS SQL Server 2008 database instance using the Server Explorer or using the Connect screen.

Then open the Object Explorer window and expand the Databases node.

Here I connected to the local SQL Server 2008 instance and clicked over the Databases node and a list of existing sql server databases are visible in the object explorer window now. Later, I clicked the sql database MyWorks which owns the tables that I want to script data, rows/records of the database.

Continue by right clicking on the database name and open the context menu, chooes Tasks menu and open submenu. Select Generate Script submenu item from the displated list.



Generate SQL Server Script Wizards

When you select the Generate Scripts sub menu item the Generate SQL Server Scripts Wizard starts. SQL administrators and sql programmers can use the Script Wizard to generate t-sql scripts as a t-sql scripter to create scripts for any object (tables, views, schemas, etc). You can work in detail on the Script Wizard and find useful hint that you can benefit in your sql developments.


Select Database to Script

The first step in the Script Wizard is detemining the database to work on. You can choose a sql database among the listed all sql databases existing on the sql server instance.



Choose Script Options

Here is the screen where sql developers can configure the script details, and where developers can shape the automatic generated script according to the applications needs and requirements.

For our case, since we want to script table data which exists in the database that I have selected in the previous steps, we should set the Script Data option to True. You can see that the Script Data option is listed in the Table/View Options sections on the Choose Script Options screen. Since default Script Data option is set to false by default, we should alter this option in order to get an Insert statement for each row in the database table.

Note : Set Script Data option to True



Choose Object Types

This option in the Script Wizard is for the types of the objects we want the script generator to build scripts for. Since we deal with database tables, we will select Tables among the listed options such as Schema and User-defined table types.



Choose Tables

Since we selected Tables in the previous step, the wizard now displays all the tables that exists in the selected sql database. Here as a developer, I make a list of tables that I want to generate table data scripts for. For our sample case, I only select one table.


Output Option

Output Option screen in the Generate Script Wizard is the screen where a sql administrator or a programmer can make a selection among the existing output options. The script generator can create the desired scripts in the forms of a file, also can split the automatic generated script per object basis, or define the file as a unicode file or in ANSI text. A database developer can also select the file name and the output file folder for the script engine to create and place the script file.

Other output options are script to clipboard, just like a Copy-Paste operation and the last option is displaying the generated script on a new query window in the Microsoft SQL Server Management Studio.

I selected the New Query Window option in order to display the generated script by the MS SQL Server 2008.



Script Wizard Summary

Here is the last stop before proceeding to the script generation where database programmers can see which options they have selected and can go back to previous screen and make re-selections in the options for a desired script.


Generate Script Progress

Generate Script Progress screen displays the status of the scripting operation. If an error occurs sql developers and administrators can find the error details on this screen. If everything runs without any error and does not fail, you will see Success status for each scripting action on the progress screen



Data Script on the Query Window

Since as the output option for the scripting, the New Query Window is selected, the final script is displayed on the SQL Server Management Studio Query Editor window as shown below.

This script is formed of a CREATE TABLE script for the source table and following that, INSERT statements for each row in the selected source table.

As you see, any sql developer or any database administrator can use the below script to create a copy of any sql database tables with their data on another sql database or an other sql server instance



So as a summary, I want to say that if you frequently need to move tables from one sql database to an other with its data like me, you can use the Script Data option of the Generate SQL Server Scripts wizard. The Script Data option is a new enhancement in SQL Server Management Studio with the latest version, Microsoft SQL Server 2008. So you should have installed the Client Tools for MS SQL Server 2008 before using this option. SQL programmers can export data as sql script by using one of the existing SQL Server tools, SQL Server Generate Script Wizard.

Three methods to transfer a mysql database

This is a list of methods alog with a description and howto for each method as well as advices about when to use each method.
1. phpMyAdmin. For this example I used php phpMyAdmin 2.9.1. Older versions may be a little different.

Export your database: Go in phpMyAdmin, select your database and go to Export tab. You should see a set of options, by default both data and structure should be checked so your export will contain the database structure and the actual data in the database. You can also pick the format of your export ( sql, csv, pdf, xml ...etc). For transferring the database you should use the SQL format. At the bottom of the page there should be a for that will let you specify the file name, and compression. If you select "none" the export will be dumped in your browser, this is not recommended because you would have to copy that sql and then paste it in phpMyAdmin on the new server and if the dump is large it may not work.

Import the database on the new server: Click on the SQL button in the left sidebar in phpMyAdmin. In the pop up window that just showed up go to "Import files" tab. In there you will be able to import the sql file you get from your first server.The problem with the phpMyAdmin approach is that most web server configurations limit the maximum size of files you upload or the maximum execution time of a php script or the maximum memory that a PHP script can use. So unless you have small dumps ( 1-10 mb ) this method is not recommended.

2. command line mysql client tools: if you have shell access to any of the servers ( ssh or telnet ) you can use the command line mysql client to either export (dump ) or import a mysql database .
To dump the database use mysqldump:


mysqldump -u "your_username" -p --lock-tables \\
"your_database" > your_database_dump.sql

I added --lock-tables there so that it puts a read lock on all tables while running the dump process to make sure that no one can modify the databases and create inconsistencies in the dump.
If you have more then one database that needs to be exported you can use the --databases option like this:


mysqldump -u "your_username" -p --lock-tables \\
--databases DB1 [DB2 DB3...] > your_database_dump.sql

If you want to export all of your databases you can just replace --databases DB1 [DB2 DB3...] with --all-databases
To import the databases on the new server you can try the phpMyAdmin method or if you have shell access (preferable ) you can use the mysql client. If you're going to use the mysql client you will have to transfer the dump file to the server ( use ftp or sftp/scp ).You might want to compress the file before transferring it


bzip2 your_database_dump.sql
scp your_database_dump.sql.bz2 user@newserver:~

and after the transfer finished, run this on the new server:


mysql -u "your username" -p "your_database" < database_dump.sql

or if your dump includes a "CREATE DATABASE" statement ( usually when a file contains the dump of more then one database or if you exported you databases using --all-databases or --databases options ) then you can just do:


mysql -u "your_username" -p < your_database_dump.sql

This method works with very large databases.

3. other tools:
The MySQL server stores database structure and data in regular files on disk. This means that if you can login on the server with privileges to access the folder where the databases are stored ( usually /var/lib/mysql ) then you can just copy or transfer then to another server using tools like ftp, scp, sftp, rsync. Before you use any of those tools you have to make sure no one is writing to the databases that you want to transfer so you should put a read lock on them. If you want to lock a table you will have to use the MySQL client to login to your MySQL server and then just type in :


LOCK TABLE table_name READ[, table_name2 READ, ...]

or if you want to lock all tables on the server:


FLUSH TABLES WITH READ LOCK

leave the mysql client running and then copy or transfer transfer the files. After the transfer finished, exit the mysql client or type:


UNLOCK TABLES

to release the read lock.
This method also works with large databases, and it is faster then the previous method in this case MySQL server does not have to parse and process queries or recreate indexes because the whole data including indexes is transferred from the old server.

Debugging Stored Procedures in SQL Server 2005

Pre-requisites


1. Find the .exe file under the directory, C:\Program Files\Microsoft SQL Server\90\Shared\1033\rdbgsetup.exe or similar path where the SQL Server was installed. The file rdbgsetup.exe stands for 'RemoteDeBuGsetup'. Look for the emphasis on letters. The filename reads rdbgsetup because, we are going to debug a stored procedures of a database available in some remote physical server. Either we need to sit at the server and debug them or we should be in a position to debug the stored procedure remotely. This action should be performed on the physical server where SQL Server 2005 was installed.

2. The user who debugs the stored procedure should be a member of SQL Server's fixed Server role, SysAdmin.

As a DBA, I may need to grant this privilege to the user who is in need of debugging a stored procedure. When I do that, I should trust the user so that he/she will not mess-up anything on the Server. Ok, I believe my users are trust worthy and I issue the following T-SQL command to assigning a fixer server role, SysAdmin.

The command to do so is as follows


EXEC master..sp_addsrvrolemember @loginame = N'&amp;amp;amp;lt;YourLoginName either SQL Server Authentication or Windows Authentication&amp;amp;amp;gt;', @rolename = N'sysadmin'

This can however be accomplished with SQL Server Management Studio's IDE. Perhaps, I prefer using T-SQL commands.

Note: The parameters to the stored procedure, sp_addsrvrolemember are of type, Nvarchar and it stands for (National Variable Character), which usually holds Unicode characters.

Now, we are all set for debugging stored procedures.

Process to Debug a stored procedure


1. Open the Microsoft Visual Studio 2005 IDE.

2. Select Server Explorer option from the View Menu as follows:





3. From the Data Connections node, right click and select, 'Add connection'. Fill in the details to connect to the intended SQL Server and the database using the login who has a fixed server role, SysAdmin. Click on Test connection. Once the connection succeeds, the overall screen should look like the following.



4. Expand the data connection just added, and navigate to the Stored Procedures node.

5. Expand the Stored Procedures node and select the intended SP to be debugged.

6. Right click and select open to view the source code of the selected stored procedure.

7. Place a break point on the intended line of code where debugging needs to be started its usually the way .NET Developers perform.

8. After performing the above steps the screen shot should look like the following.

9. After performing the above steps the screen shot should like the following:



10. Right click on the stored procedure from the 'Server Explorer' and select 'Step-Into Stored Procedure' as shown below.



11. This action brings up the following screen shot.



12. Enter the needful values and click Ok. The next shot will be the following.



13. From here on, its usual .NET debugging stuff. Use Step-Into and Step-Over and Step-out from the shortcuts menu or pressing F11,F10, Shift+F11

Wasn't that very simple. It made the life of DB developers much more comfortable. Had it not been available with SQL Server 2005 and VS 2005 IDE it would have been a nightmare to debug stored procedures remotely/locally.

Happy Development and concentrated debugging.