Add support for Microsoft SQL Server databases

I started using this module and this too would be a nice addition

I found this primarily because when looking for Sources, the module adds any MySQL databases that are registered as Destinations, and because my sites are in MS SQL, it finds none and the module is useless.

Plus one for this support

Plus two for this support!

Plus this support

Renaming so it matches the other dbms support requests.

Plus one for this support

Plus two for this support!

Good morning, there is any intention about this support?

I have no plans to write it (I'd be utterly unqualified) so someone with MS SQL Server skills will have to step up. I have not heard of anybody who is working on it at the moment.

I should get a chance to look at this shortly - I need a reliable way of doing this and I'd like to be able to handle it from within Drupal, rather than just using the MSFT tools.

@DamienMcKenna that'd be awesome. Let me know if you need any help with the plumbing or architecture. Can't help much with the actual mechanics of the MSFT db export but I'm available if you have questions.

Have started poking at this, http://msdn.microsoft.com/en-us/library/ms186865.aspx seems to be the best documentation for the SQL commands for backing up and restoring the database. The only problem is that SQL Server doesn't use "normal" SQL files for doing its backups & restorations, and I don't see it being possible to exclude tables from the backup ala MySQL, so it's going to be different. Either that or build a mysqldump-compatible exporter, but that's crazy-talk.

Another option appears to be the "bcp" command. It can be used to both import and export data into various formats, but it seems to only deal with one file and/or table at a time, i.e. one file into one table, and not one file into multiple tables. Furthermore, it requires use of a "format" file, so potentially two files for every table. I suppose post-export the integration could then zip the files into one archive, that way it only had to deal with one file?

So, the bcp option seems like the closest to how MySQL is handled, i.e. it provides table-level control, but the fact that it'll create a ton of individual files is a problem.

As a test, I created a format (i.e. schema) XML file for the "block" table using the following command:

$ bcp [table] format nul -c -x -f [table].xml -S[hostname,port] -U[username] -P'[password]' -d[databasename]

That resulted in the following:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="128" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="64" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="128" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="128" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="128" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="37"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="33"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="bid" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="module" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="delta" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="theme" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="status" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="6" NAME="weight" xsi:type="SQLINT"/>
  <COLUMN SOURCE="7" NAME="region" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="custom" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="9" NAME="visibility" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="10" NAME="pages" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="11" NAME="title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="12" NAME="cache" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="13" NAME="__pk" xsi:type="SQLUNIQUEID"/>
  <COLUMN SOURCE="14" NAME="__unique_tmd" xsi:type="SQLVARYBIN"/>
 </ROW>
</BCPFORMAT>

The following creates a tab-delimited text file with the data from a table.

$ bcp [table] out [table.csv] -c -S[hostname,port] -U[username] -P'[password]' -d[databasename]

Because of the fact that the file only contains data, I think it'd be worthwhile exporting the format XML file first.

After running into a number of problems, the client opted to change to MySQL, so I won't be pursuing this further. Sorry.

I struggled with this too. We are a complete Microsoft shop. We have Drupal running on IIS against MS SQL Server. We develop locally using WebMatrix/IIS Express. Active Directory is used for all logins. We have multiple developers working on the project so each needs their own DB so that they don't stomp on each other's changes. I was hoping to use Backup and Migrate for moving from local development to our staging environment and finally to our production environment (3 environments). But, as others have found, this module is relatively useless if you are using SQL Server. I wrote a PowerShell script that accomplishes most of what I want; namely, to copy the prod database to my local machine and sync the prod files with my local machine for development. Don't get me wrong - it's no backup and migrate, lol. But, here is what I put together and use regularly. Use at your own risk but I've used it a hundred times. I'm also posting this in case anyone decides to pick up the torch for Backup and Migrate with regard to SQL Server.

# Production Drupal site information
$productionDbName = 'MyDatabaseName'    # The production database to backup.
$productionDbLogName = ($productionDbName + '_log')    # The production database log file name.  Important because we are going to change this name during the restore process.
$productionDbServerName = 'MyProductionDatabaseServerName'    # The production database server.
$productionDrupalRoot = '\\WebServer\Share\FilePath'    # UNC path for the production drupal website.  This is what will be copied to $devDrupalRoot.  It's the folder that contains Drupal.

# Your developer identifier.  This defaults to "<domain><account>".  This will end up as part of the name of your development database.  This is important due to multiple developers working on their own local machine on the same Drupal site.  They will need their own copy of the database.  If your database is called 'DrupalDB' and your domain is 'MyCorp' and your username is 'SmithJ', then your dev database name will be 'mycorpsmithjDrupalDB'
$developerIdentifier = whoami |% { $_ -replace '\\', ''}	

# Development Drupal site information
$devDbName = ($productionDbName + $developerIdentifier)    # This development database will be DELETED! A fresh copy will be restored from the production database set in $productionDbName.
$devDbFileName = ($devDbName + '.mdf')    # The actual file name for the database on disk.  When the prod database is restored, it will be restored to this specific filename.
$devDbLogName = ($devDbName + '_log.ldf')    # The actual file name for the database log file on disk.  When the prod database is restored, the log file will be restored to this specific filename.
$devDbServerName = 'MyDevelopmentDatabaseServerName'    # The development database server.  The location of where to restore the production database.
$devDrupalRoot = 'c:\PathToYourLocalDevDrupalSite'	    # The local Drupal instance under IISExpress/WebMatrix.  This needs to be c:\***, where *** is the folder name containing your drupal instance.

# Backup file information.  This is where the production SQL Server database backup file will be created.  It is at a UNC path so that access is available by the executing computer.
$databaseBackupPath = ('\\Server\share\' + $productionDbName + $developerIdentifier + '.bak')

# Development database server file path.  This is the path where the actual SQL Server text files exist.  This is important because we need to change the names of the actual text files during the restore.
$devServerFilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'

# PHP Settings file path within the Drupal folder
$settingsFile = '\sites\default\settings.php'

# Delete the backup file if it already exists
if (Test-Path $databaseBackupPath){ Remove-Item $databaseBackupPath -Force -Verbose }

# Backup the production database.
sqlcmd -S $productionDbServerName -Q "BACKUP DATABASE [$productionDbName] TO DISK = N'$databaseBackupPath' WITH FORMAT, INIT, NAME = N'$productionDbName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

# Restore the production database to the development environment
sqlcmd -S $devDbServerName -Q "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '$devDbName') DROP DATABASE $devDbName;RESTORE DATABASE [$devDbName] FROM DISK = N'$databaseBackupPath' WITH FILE = 1, MOVE '$productionDbName' TO '$devServerFilePath$devDbFileName', MOVE '$productionDbLogName' TO '$devServerFilePath$devDbLogName', NOUNLOAD, STATS = 10"

# Create the Dev Drupal root path if it doesn't exist.
if (-Not(Test-Path $devDrupalRoot)){ New-Item -ItemType directory $devDrupalRoot }

# Copy newer files from prod to dev.  Delete files on dev that don't exist on prod.  Suppress file, directory listings.
robocopy $productionDrupalRoot $devDrupalRoot /e /purge /z /nfl /ndl

# Open the dev settings file and get it's content.  Replace the production server name with the dev server name.  Replace the production database name with the dev database name.  Write to a new file.  Delete dev settings file and then rename new file to the correct name.  
$devSettingsFile = ($devDrupalRoot + $settingsFile)
$tmp = (Get-Content $devSettingsFile) |% { $_ -replace $productionDbServerName, $devDbServerName } |% { $_ -replace $productionDbName, $devDbName } 
Set-Content $tmp -path ($devSettingsFile + '.new')
Remove-Item $devSettingsFile -force
Rename-Item ($devSettingsFile + '.new') $devSettingsFile

# Open up the settings file in notepad++ and go to line 221 to verify the database connection settings.  Don't need this line of code but it gives the warm and fuzzies.
start notepad++ ($devDrupalRoot + $settingsFile + ' -n221')

# User Feedback and the appropriate keep window open code.
Write-Host "Backup, Restore, and File Sync has completed.  Please verify the database connection settings." -foregroundcolor Red -backgroundcolor Green
Write-Host "Press any key to continue ..."
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

This feature request is 3 to 6 years old, and I'm not sure it would apply to developers going into 2018 with D8 taking over. Please open and re-file under the 8.x branch of Backup and Migrate if this is still desired.