August 15, 2013 at 4:23 am
Hi all,
I recently had to move some datafiles from one LUN to another in order to separate.mdf and .ldf datafile types. So I executed the following:
alter database test modify file (name = 'test1', filename = 'F:\Tlogs\log.ldf');
The problem is that I mistyped the filename (more specifically the path) and didn't spot the error.
The issue of course was when I attempted to start the instance after moving the datafiles, the start failed. Naturally, I couldn't reissue the alter database command with the correct path because the instance wasn't started!
In end effect, I had to create a temporary pseudo-path on the filesystem and move the datafile affected to it before I could start the instance and reisue the command to place it in the correct location. Somewhat long-winded!
Has anyone else had this problem and how did you resolve it? Surely there must be an easier way!
Regards,
Kev
August 15, 2013 at 4:34 am
After you noticed that the database couldn't come online and it was due to a mistype, you could just execute another ALTER DATABASE command with the correct filepath. That would be enough to get the database back online.
Edit: Oops, didn't read the whole post. I didn't notice you could not start the instance. I assumed you could not bring the database online.
August 15, 2013 at 4:36 am
Kev
That's curious. Are you sure they were user databases you moved and not system databases? I would expect absence of user database files to lead to the database being marked Suspect, but not to SQL Server failing to start.
John
August 15, 2013 at 5:05 am
Mis-named user database files won't stop the instance from coming online. They'll only lead to the database in question being marked recovery_pending (not suspect)
It's master, model and TempDB that can result in the instance not starting if the file names are incorrect. In all except master, you'd bring SQL up in master-only configuration and run the alter database. With master, you'd change the file names in the start up parameters.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2013 at 5:10 am
Hi all and thanks for the answers!
It was a system database (not master) that had the problem. I have just recreated the problem and realised that.
Still, it was a tricky number to recover from!
Kev
August 15, 2013 at 6:47 am
http://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply