I recently attended a SQL 2012 training class, and one of the coolest features that was discussed during the class was the built-in backup compression. This feature became available in SQL 2008 R2. Most people recommend it be turned on as it saves on Disk I/O , Disk Storage and backup time at the expense of some CPU and Memory usage during the backup. The best part it is it is a simple check box either at the database level or server level to enable.
I’m glad they added this feature, it has always bothered me that backing up basically text data took up so much space.
Enable Compression of Database Backups
In Object Explorer, right-click a server and select Properties.
Click the Database settings node.
Under Backup and restore, Compress backup shows the current setting of the backup compression default option. This setting determines the server-level default for compressing backups, as follows:
If the Compress backup box is blank, new backups are uncompressed by default.
If the Compress backup box is checked, new backups are compressed by default.
Using phpMyAdmin to export data has always been very useful to me, however I found when trying to run the SQL scripts it generates for Microsoft SQL server I found that there is a variety of incompatibilities. This is further compounded by the Microsoft SQL Management Studio that is a much improvement over the previous Enterprise Manager however it seems to fair badly when trying to do bulk inserts. I ran into many out of memory issues when trying to execute large SQL scripts over 25 megabytes. I also tried using the sqlcmd to try to have it directly process the files but it also ran into memory issues. I choose to use SQL scripts rather because this makes it easy to get back to the original database if needed. phpMyAdmin Export ConfigExporting from phpMyAdmin
Select your database and then go to the Export tab. You will need to change the following options to have it export SQL file that is mostly compatible with MSSQL.
Change the export type to SQL
Under Options – Uncheck Comments, Change the SQL compatibility mode to MSSQL
Under Structure – Uncheck Add IF NOT EXISTS, ADD AUTO_INCREMENT value, Enclose table and field names with backquotes
Under Data – Uncheck Extended inserts
These options will ensure that the INSERT lines will be valid when importing.
Data Structure / Table Definitions
phpMyAdmin does not intemperate data definitions to be compatible. So you have to options here, use Microsoft Management Studio to create your tables or modify the SQL script to be compatible. Here are is an example of a definition that I fixed and what I did.
phpMyAdmin export
Microsoft SQL Compatible
CREATE TABLE transactions (
id int(11) NOT NULL,
medmgrid float NOT NULL,
medmgridpri int(11) NOT NULL,
medmgridsub int(11) NOT NULL,
“date” date NOT NULL,
department int(11) DEFAULT NULL,
“type” text NOT NULL,
detail text,
doctor int(11) DEFAULT NULL,
“procedure” text,
description text,
reference_date date DEFAULT NULL,
diagnosis text,
unit float DEFAULT NULL,
amount float DEFAULT NULL,
total float NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE transactions (
id int IDENTITY(1,1)PRIMARY KEY,
medmgrid float NOT NULL,
medmgridpri int NOT NULL,
medmgridsub int NOT NULL,
“date” datetime NOT NULL,
department int DEFAULT NULL,
“type” text NOT NULL,
detail text,
doctor int DEFAULT NULL,
“procedure” text,
description text,
reference_date datetime DEFAULT NULL,
diagnosis text,
unit float DEFAULT NULL,
amount float DEFAULT NULL,
total float NOT NULL
);
ID Field is AUTO_INCREMENT in MySQL, for MSSQL use “IDENTITY(1,1)PRIMARY KEY”
INT does not allow specificaton of lenght, remove these entries “(11)” after each INT
DATE is not a valid datatype for MSSQL, replace with DATETIME
Any Data Field name that is also a Keyword for MSSQL needs to be Double Quoted
Remove ANY comments from the phpMyAdmin export, even if you didn’t check this there still maybe some in there. Be sure to check the end of the file for them.
For other datatype issues you may need to consult – http://webcoder.info/reference/MSSQLDataTypes.html and check to see if it is even a valid data type. Most datatype errors are easily resolved by Googling the error that is returned.
Data Insertion
If you have used phpMyAdmin export specifications from earlier in this article your data should mostly script in just fine. However, you may need to encase any of your Data Field names with double quote if the word is also a keyword. For example I have used the word Procedure which requires double quote or MSSQL thinks you are calling a stored procedure. To do this I would suggest a program called TextPad that not only does really fast replacements and can do it across multiple files but also is able to handle a few hundred megabyte text file with ease.
If you are using an AUTO_INCREMENT field you will need to add “SET IDENTITY_INSERT field_name ON;” line in before inserting any entry that tries to set that field to a specific value. This happens when your AUTO_INCREMENT ID field has already been created from MySQL. If you don’t do this you will receive an error of “Cannot insert explicit value for identity column in table ‘field_name’ when IDENTITY_INSERT is set to OFF.”
When doing bulk inserts you will be required to keep your single SQL file < 25 megabytes. Microsoft SQL Management Studio will prompt you with "The operation could not be complete. Not enough storage is available to complete this operation" or "System.OutOfMemoryException". To divide up these files I used a free program appropriately called TextWedge which was able to handle up to 100MB SQL files and easily divided them up. You may have to open SQL Studio, execute one 25MB SQL script then close and repeat depending on how much data you have to move.
