Restore from backup on development system

Hello,

I have read Article Q10431 and I need to install my live database to a sql 2005 server locally. I am unable to do this. After creating a directory called e:\sqldata, I have tried the following:

1) restoring to a new database with the same name as my db on your
server

2) creating a db with the same name as the one on your server and restoring to that

Both times I got an error message. I have information on this database that I would like to use for local development and restoration seems like the best way to do this.
Has anyone been able to do this?

Thank you,

David

 

These are my notes of dealing with the same problem:

I wish to copy my database backup file from the root of my website to my localhost to do some work with it (DotNetNuke upgrade).

My database name is SQL2005_210513_albigen1 accordng to DASP.

Since there is no .mdf extension, this obviously an alias name used by DASP.

The backup file generated by the DASP backup procedure and saved in the root of my site is named SQL2005_210513_albigen1_backup.bak

WhenIFTP the database backup file to my local server, and then attempt to restore it with SQL Server 2005, I get an SQL Server error message - the message says SQL Server can't find the following file:

E:\SQLData\SQL2005_210513_albigen1_data.mdf

Here are some observations:

The documentation from MSFT includes the following (new features in SQL 2005):

"The Restore Database dialog allows for the editing of the backup location path so that nonmapped network drives can be used for restoring backups. This feature is supported in SQL Server 2005 Express Edition SP2."

So you go through the Restore procedure: You can't leave the "To datbase" box blank but you're not going to use it. So just put anything in it.Selectthe "From Device" radio button and click the elipse on the right to locate your backup file. (Click "Add" when the applet opens, and wait, because the process is very slow - like 3 minutes. Click on Ok.)

Checkthe Restore box at the bottom of the Restore screen and then click on "Options" in the upper left menu of the Restore screen. This opens a tab that gives the originalfile name and path - i.e., the full path to the data file on drive E of the DASP server and the path to the log file on drive F. There are two boxes for each file: 1) original name and 2) restore as.

This is where you will put your file names.

Click on the elipse to the right of the "restore as" file name -and wait a few minutes - I get a dialog box called "Locate Database File" that shows drives on my local server (this box is also very slow to navigate).

There are a couple ways to handle this: 1) type in the path and file name manually or 2) click on another file name in the directory you want - and BEFOREyou click Ok - make sure you change the file name manually (while preserving the path).

For example, I put the data and log files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and then gave it the file name SQL2005_210513_albigen1 - this is the same file name (the alias)used by DASP. The reason I do this is to avoid confusion when I edit the connection string in my web.config file to point it to localhost. (I copied the web.config from my website to localhost also.)

Change the path of the Log file as well.

Make sure this is the last thing you have to do, because when you click Ok - the restore process will begin. You get a message saying the restore wassuccessful when it is done.

Lee Sykes at DNNCreative (issue 22) advocates this procedure in his video tutorial series on DNN. Well worth the subscription price, it has saved me untold hours.