Reinstalling database devices

Hello,
I am experiencing the following problem: The SAs were
upgrading apps on the server and the server literally ate
itself. Thus requiring a reinstall of SQL Server.
However, the data and log files are still in tact on the
box. The backup tapes are empty and I am trying to
recreate the devices and databases using the existing
files. Is that possible? If yes, how would I proceed?
Thanks.You can hopefully attach the database file using sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Albert H. Offer" <ahoffer@.magellanhealth.com> wrote in message
news:0ebe01c38f34$df72f430$a101280a@.phx.gbl...
> Hello,
> I am experiencing the following problem: The SAs were
> upgrading apps on the server and the server literally ate
> itself. Thus requiring a reinstall of SQL Server.
> However, the data and log files are still in tact on the
> box. The backup tapes are empty and I am trying to
> recreate the devices and databases using the existing
> files. Is that possible? If yes, how would I proceed?
> Thanks.|||Hi Albert, if the old directories are still valid, replacing the new master
with old one will be enough: it will point to your data files.
Another option (if the old master is not available) would be using
sp_attach_db to fill the new master with the information about your old db
data files.
The first option is better because the old master will be on-line with all
of your configuration options, logins, etc.
Syntax about sp_attach_db is bellow (from books on line):
sp_attach_db
Attaches a database to a server.
Syntax
sp_attach_db [ @.dbname = ] 'dbname'
, [ @.filename1 = ] 'filename_n' [ ,...16 ]
Arguments
[@.dbname =] 'dbname'
Is the name of the database to be attached to the server. The name must be
unique. dbname is sysname, with a default of NULL.
[@.filename1 =] 'filename_n'
Is the physical name, including path, of a database file. filename_n is
nvarchar(260), with a default of NULL. There can be up to 16 file names
specified. The parameter names start at @.filename1 and increment to
@.filename16. The file name list must include at least the primary file,
which contains the system tables that point to other files in the database.
The list must also include any files that were moved after the database was
detached.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
sp_attach_db should only be executed on databases that were previously
detached from the database server using an explicit sp_detach_db operation.
If more than 16 files must be specified, use CREATE DATABASE with the FOR
ATTACH clause.
If you attach a database to a server other than the server from which the
database was detached, and the detached database was enabled for
replication, you should run sp_removedbreplication to remove replication
from the database.
Permissions
Only members of the sysadmin and dbcreator fixed server roles can execute
this procedure.
Examples
This example attaches two files from pubs to the current server.
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
hth,
Roberto de Souza Santos.
"Albert H. Offer" <ahoffer@.magellanhealth.com> wrote in message
news:0ebe01c38f34$df72f430$a101280a@.phx.gbl...
> Hello,
> I am experiencing the following problem: The SAs were
> upgrading apps on the server and the server literally ate
> itself. Thus requiring a reinstall of SQL Server.
> However, the data and log files are still in tact on the
> box. The backup tapes are empty and I am trying to
> recreate the devices and databases using the existing
> files. Is that possible? If yes, how would I proceed?
> Thanks.|||Thanks Tibor. I forgot to mention that the box is running SQL Server
6.5.. The backup tapes are no good, so I don't have a good backup of
Master.
Albert H. Offer
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Then you need to use DISK REINIT and DISK REFIT. Make sure you read all you can find about these
commands. It is not even closely as easy as in 7.0 or 2000 as you need to get the database fragments
right with DISK REINIT.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Albert Offer" <ahoffer@.magellanhealth.com> wrote in message
news:eFLcGN0jDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor. I forgot to mention that the box is running SQL Server
> 6.5.. The backup tapes are no good, so I don't have a good backup of
> Master.
> Albert H. Offer
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Reinitializing subscriptions not working

Hi,

I'm doing merge sync between SQL Compact on mobile devices and SQL Server 2005. I recently made a schema change on the server and noticed that it caused an error on the subscribers when syncing. I fixed the schema problem, and did a reinitialize all subscriptions with a new snapshot. On the mobile devices I forced a reinitialisation (upload changes first). I'm still getting the same error message, which relates to a schema change which is no longer relevant to the server db and snapshot. For some reason the subscriber dbs are trying to reapply the original problem schema change even though they're supposed to be reinitializing to a new snapshot. I've even tried dropping the affected tables from the publication, and reinitializing all subscriptions with a new snapshot, but still the same error on the subscribers. Does anyone know why the subscriber dbs are still trying to apply a now defunct schema change (on tables which no longer exist in the publication)? Is there any way to flush this from the subscribers so they correctly pick up the new snapshot?

Regards,

Greg McNamara

Some additional information on the above:

The "rogue" schema change appears to be coming from the publisher, and not cached on the subscriber. The basic problem is that I'm trying to reinitialize subscribers, but it's trying to make a schema change on the subscribers which was made before the current snapshot was created. My understanding of reinitialization was that it would upload subscriber changes and then basically rebuild the subscriber db from the snapshot. Instead it seems to be applying incremental, historical schema changes on the subscriber.

Hope someone can help me with this.

Regards,

Greg McNamara

|||

And more info:

The schema change causing the original problem was the addition of a foreign key constraint. Records in a table were deleted on the server db before adding the constraint but the subscriber dbs still contain records. The sync is failing because it's trying to apply a constraint against existing records and failing. Reinitializing the subscribers is not fixing the problem (as detailed above). I decided to try deleting the records on the subscriber db before re-syncing. I now get a different error message on sync:

"Either the cursor is not on a row or there are no rows left"

Apparently this is a SQL Compact engine internal error. The malfunctioning reinitialization function seems to have caused this. I tried a repair/compact on the db but still get the same error message on syncing.

Back to the reinitialize not using the current snapshot, is anyone aware of places I should look to see if an old snapshot is being cached and somehow used by the merge sync agent?

Regards,

Greg McNamara