Home > ETL Best Practices > Restore a Database to a Different Database on the Same SQL Instance

Restore a Database to a Different Database on the Same SQL Instance

During ETL process development, especially for a commercial system such as an EAM system, it’s a good idea to create a separate database (but on the server on the same SQL instance)  for ETL testing.

Here are the steps to do that (to make sure that the restoration does not overwrite the original database). The Restore operation can be done from within SQL Management Studio on a remote PC (you should have access to the share drive where the backup file is).

Step 1: create a new empty database on the same server, same SQL instance.

EAMDev: the original database where the backup files are from

EAMPC: a new empty database

image

Step 2: Right click on EAMPC, and select Tasks—>Restore—>Database

Select “From device”, on the Specify Backup window, Add the backup file location.

image

Select Restore for the backup file where the restoration will perform on.

image

Step 3: On the Option tab, select “Overwrite the existing database”, and database file grid, change the Restore As part to the new mdf and ldf files (they are just created when the new EAMPC empty database is created).

Then click OK.

It takes less than 30 seconds to restore a backup file of about 400MB.

image

Step 4: On the newly created database EAMPC, we need to change two things.

1) Change the logical database file names to EAMPC and EAMPC_log. (Otherwise the original logical data file names are used.)

image

2) Change the recovery mode to Simple, instead of Full, to save on log file size.

image

Don’t forget to shrink the log file.

USE EAMPC
GO

backup log EAMPC with truncate_only
go

dbcc shrinkfile (EAMPC_log, 1)
go

Step 5: The newly restored database should be tested against the EMA client tool to make sure that the connection is ok (with those special login names and passwords).

Note: because we are low on the D drive (where SQL server is installed), we need to move the data and log files to E drive, where plenty of disk space is available. So I did the Restore second time by following the above steps (of cause my data is overwritten, but that’s ok). But in the Restore As grid on the Option tab, I changed the D to E drive. checked the original data and log files on D drive, they are no longer there, just as expected. EAMPC_Data.mdf and EAMPC_Log.ldf are created on E drive instead.

Categories: ETL Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: