SQL Server Wont Restart After Hard Reboot
-
I was working within SQL-Server 2000 Service Pack 3A and my computer locked up. The only thing that I could do was reboot it. After I rebooted it the SQL Server would not auto restart. The auto restart is turned on. I also tried to restart it manually through theconsole and also throught the dos prompt. It says database is not their. I don't have the exact error because I am not in front of it but any help please. I have backed up my database that I will need, so hopefully however this gets fixed I will still be able to use the database. I'm pretty new at this but if anyone could tell me where to start It would be greatly appreciated.
Thanks In Advance
Brent
-
Brent,
The exact error will be handy for helping you out. Can you get it from the SQL errorlog? Or, at the command prompt try running "sqlservr" from the mssql\binn directory and seeing the error.
You can most likely restore from the backup you've taken and things will be ok, but given that a hard reboot should not make your database files (mdf and ldf) unavailable, it's important that you find out what has happened and why it's happened.
Look too in the Windows event log.
Cheers,
- Mark -
O.K. I am almost positive that I found what is wrong. Although not receiving any other tyoe of notice other than in my logs. It says my eval has expired. O.K. makes sense. Will I not be able to restart sql eval if it has expired?
My BIGGEST problem is can i save the database that I backed up and reinstall then reimport the database? What files do i need to save? I founf in the backup folder the file named after my database, so I take it that is the database. Is that all I will need?
Please let me know time is critical.
I will be doing a format and reinstall also.
Thanks In Advance
Brent
-
If you have recent backups, restore will be possible with "restore db" (as long as you stay with the same version or with a higher version of sqlserver).
If you still have the .MDF, .NDF and .LDF files, you can use sp_attach_db to reactivate the db. If you keep the same machine-name and install at the same locations, you can even switch the new files with you "old"-ones and startup sqlserver.
check BOL
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
the only file that i see in the backups does not have an extension, and their is only one of them. Is that all that I need?
-
in the backup-folder you'll mostly find .bak-files. These contain regular sqlserver backup-files. (these are to be user with the "restore database"-commandser)
The ones with .mdf, .ndf or .ldf are sqlserver data- and logfiles. These are most likely to be found in the \data folder. These files can be used with sp_attach_db.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
can i just add one thing, although sp_attach_db is a very useful stored proc, it should never be relied upon to successfully attach database files to a server unless those existing files have been explicitly detached using sp_detach_db, this is particularly true if the sql server has terminated in an un-timely manor.
if you experience problems with sp_attach_db, it is possible to attach just the data file (*.mdf) using sp_attach_single_file_db, this creates a new log file for the attached data file and can usually be successful as a last resort to get a database up and running again, obviously any uncommited transactions from the log would be lost.
lloyd
-
O.K. so I have the data folder and the log folder saved. If I format and reinstall sql server will i be able to recover my databases with those two folders or do I need something else. I just need to make sure before I proceed. Please let me know as soon as possible, I have 10+ websites down right now because of this crash.
Thanks In Advance
Brent
-
Brent,
I suggest you test the integrity of those files in your data folder:
a) install sql2000 on another machine
b) copy the mdf files relating to your user databases (ie. NOT master, model, northwind, pubs, tempdb, msdb) to the mssql\data folder of your new machine.
c) attach the user database/s with something along the lines of: sp_attach_db 'MyDb', '<whatever directory>\mssql\data\MyDb_Data.mdf'
d) check that the databases attach ok (new log files will be generated)
e) check the contents of those databases
f) hopefully you'll then be confident that you have saved the data files ok
Cheers,
- Mark -
make sure each db has at least one .mdf _and_ one .ldf file !
Also keep your backups !
As lholman mentioned, if sqlserver did crash, attach_db may not work !
IMO, when you have reinstalled sqlserver, first stop sqlserver services, copy the new data-folder to a save location, then depending on then # of users, and the number of dts-packages and sqlagent jobs, start with attaching the user-db, then (in case of many dts packages or sqljobs) stop sqlserver and replace the msdbdata.mdb and msdblog.ldf, start sqlserver and check your dts-packages.
When using EM, also shutdown your EM and start it again to avoid any flaws
Check some dts-packages and jobs, if they are there, it should be OK.
stop sqlagent,
Then in case of many sql-users you don't know the pwd of, follow the "restore master" procedure. You may also do that on an alternate sqlserver that can be rubbished and then create a new tmpworker-db, grant all users public rights to that tmpworker-db and then use copy-database-wizard to copy that tmpworker-db to your original server _with_ all users !!! If that went ok, you can use this script to refresh sqlserver's userlinks on the server to then new uid's. This proc has to be done for each userdb.
print 'print @@servername + '' / '' + db_name()'
print 'go'
go
declare @username varchar(128)
declare @Musername varchar(128)
declare @IsNtName bit
declare @sql_stmt varchar(500)
--cursor returns with names of each username to be tied to its respective
DECLARE user_cursor CURSOR FOR
SELECT su.name as Name, msu.name as MasterName , su.isntname
FROM sysusers su
left join master.dbo.sysxlogins msu
on upper(su.name) = upper(msu.name)
WHERE su.sid > 0x00
ORDER BY Name
--for each user:
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username NOT IN ('dbo', 'list of names you want to avoid') -- enkel aanvullen indien je een ID niet wenst de synchroniseren
BEGIN
if @Musername is null
begin
if @IsNtName = 1
begin
print 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomein**\' + @username + ''')'
print ' begin '
print ' exec sp_grantlogin N''NtDomain**\' + @username + ''''
print ' exec sp_defaultdb N''NtDomain**\' + + @username + ''', N'''+ db_name() + ''''
print ' end'
set @sql_stmt = '--Windows account gehad'
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = ''' + @username + ''''
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' + @username + ''''
end
PRINT @sql_stmt
print 'go'
print '--*** opgelet : exec stmt is commented !!! ***'
--EXECUTE (@sql_stmt)
END
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
END --of table-cursor loop
--clean up
CLOSE user_cursor
DEALLOCATE user_cursor
Print '** end User-synchronisatie **'
good luck
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply