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 |
|
elementalwindx
Starting Member
5 Posts |
 Posted - 2007-11-06 : 00:26:57
|
| Ok guys, I do a regular daily ntbackup of my entire server. I thought I had sql backups going daily but I think a admin password change stopped that and well those backups are a LONG time ago. The ntbackup is just 1 lost half day.Windows crashed and had to be reinstalled, so I have all the files in the database folder and what I've done so far was reinstall windows, sql, and all that stuff so now windows is back up and going. I used sql admin and created the database ncdsm. Then I took the folder from the backup of the database for ncdsm and copied it to the new sql install (after stopping the service) and then turned it back on, and it shows that the tables do not exist, but it shows the list of tables in red.How can I fix this and get all my databases back to normal? Thanks. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-06 : 03:53:47
|
| I would:Stop SQL ServerRename all MDF/LDF (system) databases - so you can get them back if this doesn't work!Put all the System and User databases back (from your backup)Try starting SQL Service.Your biggest problem is that the ntbackup will have copied files that were in use, and I would think there is only a very slim chance that the files will be any use at all.Kristen |
 |
|
|
elementalwindx
Starting Member
5 Posts |
Posted - 2007-11-06 : 08:12:18
|
| thats what i said I did in my last post.So ntbackup wont back up those files correctly? :( So theres no way to get that data back? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-06 : 10:29:40
|
| "thats what i said I did in my last post."Where did you say that you renamed the system databases?Where did I say that you should "created the database ncdsm"?What you say you did and what I am proposing are different.But there is no guarantee that this will work if the files were in use when the backup was made, but there is also a slim chance that all data was flushed to the files at that time ...... and there are also other steps that we can recommend if you get somewhere close to a working systemKristen |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-11-06 : 10:45:24
|
| If data is important, then the sql backups should be monitored daily to ensure they are working. If the data isn't that big of a deal, then you don't have to monitor the sql backups and if a disaster happens, it's ok to start over with a new database with no data. Kristen is right. Stop the server. Put everything back (mdf and ldf) in the exact same place it was before (same path and folder and all). That might work. I'm not sure how it does with master though. I've always restored master from a backup in this situation (rebuilding a server from an existing server) rather than put the mdf and ldf files for master on the server. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-06 : 11:07:47
|
| You can just put the MASTER MDF back. it needs to be a clean copy though, no guarantees if the file is in use when you make your copy.Its a common method when migrating to a new server - install SQL, Stop service, rename all System database files, copy all database files (system & user) to the new server, re-start service.Drives, paths and Versions all need to be the same of course.Kristen |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-11-06 : 11:25:18
|
| I gottcha. I think I did have to do that once...years ago, and it worked (SQL7 though). I actually thought that if sql server was running and you tried to copy/backup the mdf/ldf file(s) that you would get an error saying the file is in use. I thought you had to stop sql server in order to do that. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-06 : 13:34:32
|
| Ordinarily yes, but I think backup tools can copy in-use files - but no point if the files are not then in "usable" state of course.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-06 : 21:51:39
|
| And ensure new sql installation has same sp level as previous one. |
|
|
|
|
|
|
|
|
Help me restore lost databases! :(