Copying MS SQL 2016 Database on AWS RDS

A common task on a SQL Server might be to copy a database into a new database. This could be on the same server or a different server.

On Amazon Web Service’s (AWS) Relational Database Service (RDS) this task becomes a little more complex to plan and execute. There are a few reasons for this:

  1. You don’t have access to the local file system outside of creating a database in the defaulted path.
  2. You do not have the needed permissions to run “Copy Database Wizard”
  3. Doing a “native” backup/restore using S3 would create a duplicate “family_guid” which is impossible to restore on your RDS instance without deleting the source database.

If you use MS SQL 2016 you can export/import a “Data-tier Application” which is stored as a “.bacpac” file. This is a schema and data native format package (not human readable). In reality it’s a .zip file, so if you open it in something like 7-Zip you can see the package contents. This package is going to be lacking items such as the physical index (the index configuration is intact) and the transaction logs. Therefore it tends to be smaller than an actual native backup.

Keep in mind all data is transmitted from the source machine (AWS RDS) to your workstation. If this is a larger database, you may wish to consider doing this from an EC2 instance to create a faster connection, provide for a larger drive workspace and potentially save on network costs.

Here are the steps to take to backup and then restore a “.bacpac” file. This example is done using AWS RDS, but the same steps would be taken for about any environment including local.

  1. Open Microsoft SQL Server Managment Studio (MSSMS) 2016 or better and connect to your database server.
  2. Right click the source database, go to tasks and “Export Data-tier Application”.
    1
  3. Press “Next” to go to “Export Settings”. Enter a path on your local workstation to save the package to and press “Next”.
    2
  4. After you press “Finish”, it will then begin the copy process from the source machine to your local machine.
    3
  5. If you wish to import the database to another server, connect to it now via the MSSMS.
  6. Right-click the “Databases” group object and select “Import Data-tier Application”. Press “Next”.
    4
  7. Specify the path on your local machine to the “.bacpac” file being imported. Then press “Next”.
  8. Specify a new database name. It can not be the name of an existing database. Press “Next” and then “Finish”.
    5
  9. The new database will be created and imported.
    6
Advertisements

#aws, #export, #import, #microsoft, #rds, #sql

Manual Windows 2008 Registry Restore

After a Windows Update the lovely “Blue Screen of Death” appeared on one of our servers. Frantic to find a solution, “Boot to the last known working configuration” wasn’t working. A system restore was a last resort option.

Here’s what the error consisted of:

STOP: c0000218 {Registry File Failure}
The registry cannot load the hive (file):
\Systemroot\System32\Config\SOFTWARE
or its log or alternate.
It is corrupt, absent, or not writable.

To resolve the issue I:

  1. Boot to the Windows 2008 Server Install DVD
  2. Click “Repair Computer” on the second screen
  3. Open a command prompt on the second or third prompt
  4. Change directory to C:\Windows\System32\Config\
  5. Rename “SOFTWARE” to “SOFTWARE.BAK”
  6. Copy “RegBack\SOFTWARE” to that directory
  7. Reboot

This restored the SOFTWARE registry to its previous state before the Windows Update. I then had a pending list of Windows Updates to install again. But I’ll leave that for another day for now to see if anyone else is having issues.

#blue-screen-of-death, #microsoft, #registry, #update, #windows, #windows-2008-server

jQuery Templates

I decided to dig into the new official jQuery Template Plugin developed from Microsoft yesterday. After I finally got the darn thing to work it makes inserting formatted HTML populated with data way easier.

After doing some searches on Google I kept finding articles that announce that Templates would be built into the version 1.5 core. I continued down the path coding by example and researching the API Docs. However I kept running into “tmpl is not a function”. After some continued research I finally found a tiny little comment made by John Resig that it in fact did not make it into the 1.5 release. So now that error makes sense.

To resolve this issue you must still load the jQuery plugin. It appears the plugin is still in beta stages and is available for download from Github or the Microsoft CDN.

Before I’ve been unable to use Microsoft’s CDN because it did not have SSL. But I went ahead and tested to see if they now have it included, and they do!

Here’s how I use it:

<script src="//ajax.microsoft.com/ajax/jquery.templates/beta1/jquery.tmpl.min.js"></script>

This will automatically call it via http or https depending upon the current browser protocol being used.

See a related blog for jQuery on Google’s CDN here.

#cdn, #google, #jquery, #microsoft, #plugin, #template