Database startup

in sql server (re)start where does it find the location of Master.mdf file .

In Oracle, there is control file where the location of *.dbf file is stored and Instance find the location of dbf file from that location

In sql server where it finds the location of Databases as master, msdb etc..

The location is set duing Server installation.

That information is stored in the registry. It can also be supplied as a command line start up parameter [ -dMasterFilePath ].

The registry location 'should' be:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters

Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.

database showing suspect

I moved a database file (.mdf) from the Microsoft SQL Server data directory to another location, and moved it back into the original location. After which the database staus changed to suspect.
How do I make it live? ThanksRead up on sp_resetstatus in the Books online.

Database Restore

I have a SQL cluster I built about two weeks ago on Windows 2003, now we are
trying to restore a database to the server from a different location (non
cluster) and we are unable, database seems to go but sql manager states the
database is in a still loading state, is there anything I have to do, like
stop the cluster services.
Thanks,
Bob Smith
Hi
Make sure that you select "Leave database oprational..." from the options
tab in the Restore dialogue.
You can re-run the restore, overwriting the DB you tried earlier.
Regards
Mike
"Bob Smith" wrote:

> I have a SQL cluster I built about two weeks ago on Windows 2003, now we are
> trying to restore a database to the server from a different location (non
> cluster) and we are unable, database seems to go but sql manager states the
> database is in a still loading state, is there anything I have to do, like
> stop the cluster services.
> Thanks,
> Bob Smith
|||Mike,
Thanks, but this was checked, do I need to break the cluster to do a
restore, I even tried creating new database and doing an import, btw I am not
a very experienced sql admin, I know my way around and can troubleshoot etc,
but I done the restore on a straight sql server non cluster, then I go to the
clustered setup and do the restore and it sits there saying loading and it is
grayed out.
Thanks Again,
Bob Smith
"Bob Smith" wrote:

> I have a SQL cluster I built about two weeks ago on Windows 2003, now we are
> trying to restore a database to the server from a different location (non
> cluster) and we are unable, database seems to go but sql manager states the
> database is in a still loading state, is there anything I have to do, like
> stop the cluster services.
> Thanks,
> Bob Smith
|||See if this helps.
Copy the data and log files for your user database from the non-clustered server to the shared disk that your SQL Server virtual instance recognizes.
Lets say for example, you copied the mydbdata.mdf and mydbdata.ldf files to "F:\Program Files\Microsoft SQL Server\MSSQL\Data" folder
Now re-attach the database pointing to the files in the new location as follows: (Run the following commands from Query Analyzer)
use master
go
sp_attach_db 'mydb','C:\Program Files\Microsoft SQL Server\MSSQL\Data\mydbdata.mdf','C:\Program Files\Microsoft SQL Server\MSSQL\Data\mydblog.ldf'
go
Verify the change in file locations using sp_helpfile :
use mydb
go
sp_helpfile
go
If you are not comforable with Query Analyzer, you can use Enterprise Manager to attach the db. Here are the steps from SQL Server Books Online
To attach a database
Expand a server group, and then expand a server.
Right-click Databases, and select All Tasks/Attach Database.
Enter the name of the MDF (master data file) of the database to attach. If you are not sure where the file is located, click browse (...) to search. There can only be up to 16 file names specified. For more information,
see sp_attach_db.
To ensure that the specified MDF file is correct, click Verify. The Original File Name(s) column lists all the files in the database (data files and log files). The Current File(s) Location column lists the file names and
paths. If Microsoft SQL Server cannot find the files in the specified locations, the attach operation fails. The Current File(s) Location column can be edited, and the current location of the file must be in this column for
the attach operation to work. For example, if you have changed the default location of the file before you detached it, you must specify the current location for the attach operation to be successful.
In the Attach as box, enter the name of the database. The database name must not match any existing database names.
Specify the database owner.
Click OK. A database node for the newly attached database is created in the Database folder.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx