| Author |
Topic |
|
DaveGKeller
Starting Member
10 Posts |
 Posted - 2012-05-30 : 00:04:20
|
| I have what I hope is a quick question. My server died. Motherboard issue, not HD issue. I have bought a new server. MS SQL 2008 Express was loaded on the old one. I was wondering if there is a way to use the old database on the new server. I'm *hoping* I can install SQL Server 2008 Express on the new machine. Then perhaps connect the old HD as a backup drive for the sole purpose of copying the database and log file to the new HD. Then somehow have the new SQL Server Express use that database.Is this possible? I am not a DB Admin by any stretch of the imagination. If this is possible could you please make the steps as easy to understand as possible. Thanks for any help. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-30 : 00:08:42
|
| Should be possible. You need to get the .mf and .ldf files for the old database then attach the database to the new server. Look it up in Bol.The database might be corrupt if it wasn't closed when the machine died in which case it becomes more complicated and you will have to coppy out objects.Look up backup strategies for the future.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-05-30 : 00:19:40
|
| I sincerely hope you have some backup of the database.You can attach the db files to the new server ONLY if the files aren't corrupted.But if they are then your chances of getting your data back is very limited.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
prett
Posting Yak Master
212 Posts |
Posted - 2012-05-30 : 04:07:56
|
| If you can attach your files to new server then there are three possibilities:1) You can attach sql server database files to new server if files are not corrupted. (As Sachin suggested)2) If file get corrupted then you should use your last good backup to restore file after that attach files to new server.3) If you don't have backup. DONT WORRY. You can try this solution i.e. third party sql database recovery software. These software helps users to repair corrupt files which you can easily search on Google:http://lmgtfy.com/?q=sql+database+recovery+software After that attach files to new server. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-05-30 : 04:55:52
|
quote:
Originally posted by prett...3) If you don't have backup. DONT WORRY.
This is a load of crap. If your server dies and you don't have backups you should definitely worry. A lot. It might prevent you from screwing up further. And to let anyone think that not doing backups is nothing to worry about is just outrageous.- LumbagoMy blog-> http://thefirstsql.com |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-30 : 07:38:02
|
| Think that's "don't worry" as in "you can always find another job".==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-05-30 : 09:12:55
|
| "don't worry... there is no sugar?"and probably no data...... DaveGKeller - The first thing you should do is to get those files and make copies. Put those copies somewhere safe.If you don't have a backup then make sure you at least make sure you aren't going to irreversibly screw those files.sql server uses different file extensions for different things. the .Log files are *not* simply log files in the way you may think, They are critically important for the integrity of the database.Read through something like this first:http://www.techrepublic.com/article/a-primer-on-sql-server-database-files/6096988And post back here when you are ready. There are some real recovery guru's here (not me). who can help.Transact CharlieMsg 3903, Level 16, State 1, Line 1736:The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION http://nosqlsolution.blogspot.co.uk/ |
|
|
|
DaveGKeller
Starting Member
10 Posts |
Posted - 2012-05-30 : 11:39:55
|
| Well, good news and questionable news. Turns out it was the HD and the motherboard. :(However, the system did run backups (although not automatically as the dates for different backups are not consecutive). The last one I have is from 5/19/2012. I have never done a restore from a backup before. All I have is something called "myDatabaseName.bak". That is the file that was stored on a separate machine.Is that the correct format for what a SQL Server 2008 database backup should look like? If yes, how do I go about restoring that for a new installed version of the database? I have the sql files to create the database, the tables and the sprocs. Do I create that first and then do the restore? Can the restore be done from within the SQL Server GUI?Thanks again for all the help. I was hoping to do the attach method mentioned above but that drive is just not responding. Thanks again. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-30 : 12:04:29
|
| That sounds good.Try right clicking on dtaabases and restore database.Select from device then the file. select a backup in the file.Then you might have to set the file locations to restore to if your new server doesn't have the same folders.Put in a database name and yoou should be there.The next problem you might have is if the logins might not map to the users in the database but that depends on how you have set up security.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
DaveGKeller
Starting Member
10 Posts |
Posted - 2012-05-30 : 12:58:38
|
quote:
Originally posted by nigelrivett
That sounds good.Try right clicking on dtaabases and restore database.Select from device then the file. select a backup in the file.Then you might have to set the file locations to restore to if your new server doesn't have the same folders.Put in a database name and yoou should be there.The next problem you might have is if the logins might not map to the users in the database but that depends on how you have set up security.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Well, I tried two times and it failed both times. I re-created the database, all the tables and sprocs. I had copied the *.bak file to the new HD. Then I selected the restore database. I chose the new database (same name as before). I clicked Device and then File and then pointed to the *.bak file. When I clicked OK to run it gave me this message: quote:
TITLE: Microsoft SQL Server Management Studio------------------------------Restore failed for Server 'myComputerName\myInstanceName'. (Microsoft.SqlServer.SmoExtended)------------------------------ADDITIONAL INFORMATION:System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'myDatabaseName' database. (Microsoft.SqlServer.Smo)------------------------------
So I deleted the new database and tried restoring without have created the database. That failed because you have to have a database to restore to. So I created the database again but didn't add the tables/sprocs. I got the same error.Would the fact that the new computer has a different name have anything to do with this? Is there a step I missed? Thanks again for any help guys. I'm very grateful for all the advice. |
|
|
|
DaveGKeller
Starting Member
10 Posts |
Posted - 2012-05-30 : 13:12:07
|
| Wait, maybe it's OK now. I went to the Options (left pane of SQL Server Management Studio). Then selected the first checkbox on the right (WITH_REPLACE). That initially failed until I disconnected my remote connection to the database (where I was issueing the sql files to create tables/sprocs). Then I re-ran and it finished successfully. I'm not 100% sure that it is valid but thus far it looks good. I have some checking out to do but hopefully it's finished.I'll update this post when I know more. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-30 : 13:31:50
|
| You didn't need to create the database - it's all contained in the backup. The with replace overwrites what you had created.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Use database from crashed server on new server?