December 20, 2007 at 1:00 pm
I have a windows application that uses various local SQL databases using SQL default instance (MSSQLSERVER). I want to move all the databases in the named (SQLHES) instance. Below is what I am doing using SMS Wrapper:
1) Installing the named instance (SQLHES)
2) De-attaching all the databases from SQL Default (MSSQLSERVER) instance
3) Attaching the databases with named instance
Now when I look at the Windows Services, I do see both instances but only default service is running and the named instance is stopped. When I tries to manually run the named instance, it does not start and giving some error. Then I go to default instance stop it and then try to start the named instance, it works. So basically, somehow names instance is colliding with default instance.
Even when I go to add/remove program, I just see the named instance under SQL Server 2005.
Can anyone help what is causing it? What I am doing wrong??
December 20, 2007 at 2:51 pm
Did you attach the master database?
might have a naming conflict.
January 3, 2008 at 11:53 am
When we install a new instance (SQLHES), it should have all system databases available include Master right??
January 3, 2008 at 12:08 pm
Hi,
Interesting issue with long shot feedback........
During your SMS wrapper, what location are you putting the database files you detached in earlier step?
Cheers,
Phillip Cox
January 3, 2008 at 12:52 pm
All the application databases are residing in C:\My Documents\Databases folder. Below is how I am de-attaching one of the database: (%STRING% has a value "C:\My Documents\Databases")
SET DATABASEFOLDER=%STRING%
SET DATABASETEMP=%DATABASEFOLDER%
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql" -E -Q "exec sp_detach_db @dbname='EZSalesOrder'" >> ezso_database_update.txt
:EOF
@echo on
And when SQLHES instance is created, below is how I am attaching the database to named instance:
SET DATABASEFOLDER=%STRING%
SET DATABASETEMP=%DATABASEFOLDER%
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql" -S (local)\SQLHES -E -Q "sp_attach_db @dbname = 'EZSalesOrder',@filename1 = '%DATABASEFOLDER%\EZSalesOrder_Data.mdf',@filename2 = '%DATABASEFOLDER%\EZSalesOrder_Log.mdf'" >> ezso_database_update.txt
:EOF
@echo on
Am I doing anything wrong? Please help!!!
January 3, 2008 at 2:27 pm
Where is '%DATABASEFOLDER% pointing to on attach step, as you may be pointing to original location?
January 3, 2008 at 2:53 pm
%DATABASEFOLDER% is pointing the original location (C:\My Documents\Databases)
January 3, 2008 at 3:31 pm
I don't see why moving a user database would prevent an instance starting so thats probably a red herring, check out the errorlogs to see why instance not starting
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply