Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
megabyte
Starting Member
2 Posts |
 Posted - 2007-10-16 : 20:14:22
|
| Hello all,I operate a website which runs on Server 2003 and SQL 2005 Express. Unfortunately my server experienced a hard drive failure, however fortunately I have a full backup using 2003's NT Backup.My new server is slightly different specification wise to my last server - but my question is, what are my options for restoring the database and how would I go about this? I do administor 2003 servers, but I'm not a database administrator.My initial thoughts are to update Windows and its components (such as IE) and SQL 2005 to the same equivalent versions, and to perform a full restore, but what if it breaks Windows?My second thought is to somehow extract the database from my backup file, but then I wouldn't know where to start how to re-import everything.All comments would be welcome! Many thanks, Mike |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-16 : 20:55:49
|
Did you have an actual database backup through SQL Server itself or just a file system backup? If you just have the file system you can try restoring the MDF and LDF file and do an attach.
Future guru in the making. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 01:13:11
|
| If you have file system backups ONLY and SQL Server Service was running at the time the backup was made you may be in for some problems with the restore.You need to retrieve all MDF and LDF files from your backup.SQL Server has some system databases, these will be installed by default (presuming you have made a fresh install on the new machine). You don't need to overwrite these, but the others you can stick in a suitable folder and then ATTACH to your new SQL Server installation.However, the MASTER and MSDB system databases may contain useful information. You can't just overwrite these UNLESS your new installation is identical to the old one - same service pack level, same drives and folders available and the install was made defining those same choices.However, you can rename the MASTER and MSDB files that you retrieved from tape and Attach them as databases in their own right. Then you can examine the data they contain and recreate it. The data will be things like:LoginsBackup plansJobsHopefully you had precious few of these, so just being able to see the "raw data" will be enough to remind you what was there.For the future: Set up a maintenance plan to make database backups, and then backup those *.BAK files with NTBackup. Once you have that going you can consider excluding the .MDF / .LDF files from your NTBackup. |
|
|
|
megabyte
Starting Member
2 Posts |
Posted - 2007-10-17 : 16:11:14
|
| Thank you for the replies, much appreciated. I attempted to perform a full restore on my new server, however it became very problematic... I eventually got into SQL 2005 Express, however the databases that were there are not there after performing the restore.I had a few databases and I regularly performed a full file system backup. Could you tell me where (by default), these MDF and LDF files are located and how many there should be? Is it one pair per database created?And in simple terms, how would I go about attaching these files to a new instance of SQL 2005 Express? Thanks again! |
|
|
|
Kristen
Test
22859 Posts |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-19 : 22:10:00
|
| Right click on the db in ssms and open properties, you can find file location in file tab. |
|
|
|
|
|
|
|
|
SQL 2005 Restoration