Month: November 2011

Restoring a MS SQL database from one server to another

I find myself restoring databses from one server to another from time to time. I generally do this when we want to take a snap shot of a live database and use it on a development site.

The easiest way to restore the database from a backup file (.bak) is to use the Microsoft SQL Server Management Studio GUI.

  1. Once logged into the server you want to restore the database to. Expand the database folder in Object Explorer panel so you see all your databases listed.
  2. Right click on the database you want to restore then select
    Tasks -> Restore -> Database…
    restoreDB1
  3. This will bring up a window titled ‘Restore Database – yourdbname‘. Click the radio button ‘From Database’ followed the ‘…’ button.
    restoreDB2This bring up a window titled ‘Specify Backup’ click the ‘Add’ button this will allow you to browse your computer and locate the backup file (.bak), click ok twice and this will return you back to the ‘Resotre Database – yourdbname‘
  4. On the ‘Restore Database – yourdbname‘ window ensure the restore check box is checked under ‘Select the backup sets to restore:’
    restoreDB3
  5. Now we have selected where to restore our back up from we need to set a few paramerters before we can restore. Click on Options from the ‘Select a page’ panel.
  6. On the Options screen we need to check ‘Overwrite the existing database (WITH REPLACE)’ under restore section. Also we need ensure we are restoring the database to the correct files. Ensure the paths to ‘Raw Data’ and Log are pointing to the correct files. This is particularly important if you are running production and development databases of one server.
    restoreDB4
  7. Now we have everything set click on OK and it will start to restore the database.

This is pretty much all you need to do to restore a database. However, you may find you will have an orphan user that you need to link the database user to a SQL server login.

If you have created the original login name on the SQL server before the datebase restore, then run the following command against the database from the query analyser.

sp_change_users_login 'auto_fix', 'Login'

You must specify the login in the above command.

If however, you have multiple users in the databse which have their own logins, you will have to the orphan them individually as follows:

sp_change_users_login 'update_one', 'user', 'Login'

You must specify the user and login. The login must exist.

Internet Explorer form submit by enter key

Recently I was faced with an issue whereby when the user enters data into a single text input field form and submits via the enter key. No form fields would be passed to the processing page – this worked in all browsers but not Internet Explorer.

<form action="" method="POST">
    Field 1 <input name="field_1" type="text" />
    <input name="submit" type="submit" value="submit" />
</form>

The work around to this weird and wonderful bug is to add an additional disabled input field for IE, using IE conditional comment and hiding it from view using some CSS.

<form action="" method="POST">
    <!--[if IE]>
        <input type="text" style="display: none;" mce_style="display: none;" disabled="disabled" size="1" />
    <![endif]-->
    Field 1 <input name="field_1" type="text" />
    <input name="submit" type="submit" value="submit" />
</form>

This will now enable users to submit the form via the enter key in IE.