Difference between revisions of "Backup & Restore Databases - MS SQL"

Revision as of 19:00, 26 February 2014

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).

Transfer Tables to DBO schema

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'

Transfer All Objects to DBO schema

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'

SQL Server 2000 - Transfer Table to DBO schema

To change the owner of a table in SQL Server 2000:

use MyDB
EXEC sp_changeobjectowner 'MyUser.MyTableName', 'dbo'

For the above TSQL, do the following:

1. Change MyDB to your database name.
2. Change MyUser to the current owner username.
3. Change MyTableName to the table needing modified.
4. Repeat for each table in the database, if necessary.