August 28, 2007 at 9:08 am
Hi All,
Please excuse this very beginner question. We've lost our SQL Server DBA and I'm a .NET developer dead in the water here.
I’ve been given a .bak file of a SQL Server 2005 database that I need to work with. I’ll be doing substantial modifications to the schema for an application I need to write so I’d like to play with it first on my local drive in SQL Server 2005 Express. (Also, I don't have direct access to our SQL Server box). Can someone point me to some good tutorials (for non-DBA’s) that could help me restore this database in SQL Server Management Studio Express?
I just need to add a new table and some new fields to existing tables so I can begin writing code. I'd like to work locally in SQL Server Express while we look for a new DBA.
Thanks so much,
- Thom Cox
August 29, 2007 at 4:33 am
In books online (the documentation for SQL Server)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/24b3311d-5ce0-4581-9a05-5c7c726c7b21.htm
or http://msdn2.microsoft.com/en-us/library/ms177429.aspx
describes step-by-step how to restore a full backup
Regards,
Andras
August 29, 2007 at 11:59 am
Hello Thom,
The advice from Andras to look up the documentation is sound. Also, here's a step-by-step that might be helpful to you.
First, some prep work: Place the backup file (.bak) on the C:\ drive of the instance where your SQL Express instance is located. Then, find the path of the existing database files used by SQL Express. Very likely this will be C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. You should find the "Master.mdf" file when you are at the correct location.
1. Open Mgmt Studio to start the process to restore the database. Select the "Databases" node and right click it. Choose "Restore database..." This opens the Restore Database dialog.
2. In the upper section of the dialog, fill in the name of your database ("MyDatabase) in the "To Database" text box. Go to the lower section of the dialog to specify the location of the backup. Select the "From Device" radio button. Since your backup file is not presently identified as a backup device, select the ellipsis button ("...") to the right of the textbox to identify the backup to SQL Server.
3. A new dialog titled "Specify Backup" will open. First, make sure that "file" is specified as the Backup Media. Then go to the Backup Location section. Use the ADD button to bring up a navigation tool. Use the tool to navigate to the location of your backup file (C:\). Then enter the complete name of the backup file, including extension ("MyDatabase.bak") in the file name textbox. Hit OK twice to get back to the Restore Database dialog.
4. In the upper section of the dialog, enter the name of your database in the "To database" textbox ("MyDatabase"). In the lower section, select the checkbox to request a Full Restore of the database. Before you start the process, switch to the Options page of the dialog. (The Options icon is in the upper left corner of the dialog).
5. Here is where you will tell SQL Server where to put the database data and log files. Ignore the checkboxes and go to the ellipsis button next to the first file path. When the navigation tool appears, navigate to the "Data" directory you found in the prep section of this process. Then enter the database name in the File Name textbox (no extension) and hit OK.
6. Repeat Step 5 for the log file. In this case, enter the name of the log file in the text box. Usually it is something like "MyDatabase_Log". Hit OK and the restore process will run.
HTH
Elliott
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply