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

Revision as of 17:38, 24 February 2015

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

How to Backup a MSSQL Database

Backup with myLittleBackup

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 "Backup Databases" from the left menu
6. Select your MSSQL database
7. Click "Ok" under the "Check the information about your database" heading
8. Complete the name and description fields
9. Click "Backup"
10. Click the link to the database to download, note the backup will also stay archived for 30 days within myLittleBackup - for easy restore

Manual Backup

This method applies to those managing their own servers or for our Windows Based VPS.

1. Open SQL Management Studio
2. Expand Databases and right click on the database and select: Tasks > Backup
3. Select "Disk" for the destination. Backup Type should be "Full".
4. Click "Add" and specify a path, and file name and .bak extension.
5. Click "OK" to complete the backup.

How to Restore a MSSQL Database

Restore with myLittleBackup

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

Manual Restore

This method applies to those managing their own servers or for our Windows Based VPS.

FOR WINDOWS VPS, create the database within WCP before proceeding.

1. Open SQL Management Studio
2. Expand Databases and right click on the database and select: Tasks > Restore
3. Select "Device" and choose the location of your backup file.
4. Select "Options" from the left panel and check the box to "Overwrite the existing database (WITH REPLACE)".
   • Also check the box to "Close existing connections" on newer versions of SQL.
5. Click "OK" to complete the restore.

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.