Backup & Restore Databases - MS SQL

How to restore an MS SQL database (import MS SQL database) from a backup file.

Steps

Steps to restore a database using myLittleAdmin through the control panel.

NOTE: You will need to create your MSSQL database in the WCP before doing these steps

1. Log into the WCP at http://wcp.hostek.com/
2. Click "MSSQL Tools"
3. Select your MSSQL database and database user from the dropdown menu
4. Click "MyLittleBackup"
5. Click "Restore Databases" from the left menu
6. Select your MSSQL database
7. Click "Ok" under the "Check the information about your database" heading
8. Click "Browse" and navigate to the .bak file of your database on your local system
9. Click "Ok"
10. Select the database file you uploaded in the list and click "Ok"
11. Click "Restore" and then click "Ok" on the alert message that pops up

Errors and Solutions

Solutions to common errors when restoring database backups.

The backup file you selected exceeds the limit size of your database

Error Message:

The backup file you selected exceeds the limit size of your database. Restore has been cancelled.

This error indicates that the database that created the backup file is larger than the database you are restoring to. It is important to note that the size of the actual backup file does not matter. The size that is important is the size of both of the data file and log file of the database on the original SQL Server where the backup was created. If those file sizes are too large, you will need to reduce them before creating the backup file.

The most common issue is that the transaction log file is too large. To reduce the size of the transaction log file before creating the backup, you can perform a transaction log backup (Tasks -> Backup -> set Backup type = Transaction Log) and then shrink the transaction log (Tasks -> Shrink -> Files -> set File type = Log).

Invalid Object Name errors after database restore

This error can occur when a table is prefixed with the schema of a user other than dbo (ex myuser.table).

This will generate the SQL to transfer your tables to the 'dbo' schema:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name  
FROM sys.tables p INNER JOIN  
sys.Schemas s on p.schema_id = s.schema_id  
WHERE s.Name = 'EXISTING_OWNER_USERNAME'

If you wish to transfer all object such as stored procedures to the 'dbo' schema, as well as tables, use the following to generate the needed SQL:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name  
FROM sys.objects p INNER JOIN  
sys.Schemas s on p.schema_id = s.schema_id  
WHERE s.Name = 'EXISTING_OWNER_USERNAME'