attach multiply databases

This summary is not available. Please click here to view the post.

attach multiply databases

This summary is not available. Please click here to view the post.

attach db file copied previously

I got a mdf file from my co-worker
who just stopped SQL server and delete ldf file
and give me a mdf file.
if it was detached, it would work fine
but it was just copied.

Is there any way to attach this mdf file?
I tried sp_attach_db and sp_attach_single_file_db
and both failed.

thanks,second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311241337.7338362d@.posting.google.com>...
> I got a mdf file from my co-worker
> who just stopped SQL server and delete ldf file
> and give me a mdf file.
> if it was detached, it would work fine
> but it was just copied.
> Is there any way to attach this mdf file?
> I tried sp_attach_db and sp_attach_single_file_db
> and both failed.
> thanks,

If sp_attach_single_file_db failed, then there is no other way to
attach the file. In future, it would be better to ask your colleague
to give you a backup of the database, not the database file(s). This
also means your colleague will not have to stop SQL Server.

Simon|||What error do you get back from the sp_attach_single_file_db command?

> I got a mdf file from my co-worker
> who just stopped SQL server and delete ldf file
> and give me a mdf file.
> if it was detached, it would work fine
> but it was just copied.
> Is there any way to attach this mdf file?
> I tried sp_attach_db and sp_attach_single_file_db
> and both failed.
> thanks,

Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or http://www.ntfaq.com/Articles/Index...epartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||I ran
sp_attach_single_file_db 'Spectrum', 'e:\spectrum.mdf'
and got

Could not open new database 'Spectrum'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'd:\Spectrum\Data\Spectrum_log.LDF' may be incorrect.

Neil Pike <neilpike@.compuserve.com> wrote in message news:<VA.00001010.008366e0@.compuserve.com>...
> What error do you get back from the sp_attach_single_file_db command?
> > I got a mdf file from my co-worker
> > who just stopped SQL server and delete ldf file
> > and give me a mdf file.
> > if it was detached, it would work fine
> > but it was just copied.
> > Is there any way to attach this mdf file?
> > I tried sp_attach_db and sp_attach_single_file_db
> > and both failed.
> > thanks,
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or http://www.ntfaq.com/Articles/Index...epartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq|||[posted and mailed, please reply in news]

neo (second714@.hotmail.com) writes:
>> > I got a mdf file from my co-worker
>> > who just stopped SQL server and delete ldf file
>> > and give me a mdf file.
>> > if it was detached, it would work fine
>> > but it was just copied.
> I ran
> sp_attach_single_file_db 'Spectrum', 'e:\spectrum.mdf'
> and got
> Could not open new database 'Spectrum'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'd:\Spectrum\Data\Spectrum_log.LDF' may be incorrect.

Tell your co-worker to never do that again! Deleting LDF files is a
very stupid thing to do!

It is not clear how you co-worker shut down SQL Server, but it does
not seem that this database was shut down cleanly. If this database
was in the midst of the transaction, this transaction needs to be
rolled back, to bring the database to a consistent state. But to
roll back the transaction, you need the transaction log.

Yes, there *are* ways to attach a single MDF file in such a situation,
but these ways are not documented with a very good reason. It follows
from the previous paragraph that the database you get might be grossly
corrupt. On the other hand, if there was no activity in the database
at the time of the shutdown,

If you have a backup of this database, I suggest that you restore it.
If the database contains data which is not on a backup, and you really
need to save as much as possible, I would suggest that you open a
case with Microsoft. That is not going to be cheap, but neither is it
cheap to lose the data.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

attach db

I'm moving a db from serverA to serverB. I detached the db on serverA and
copied the files to serverB. I want to change the file names when I attach
it on serverB. How can I do this? If I try to change the name and attach
the db, it doesn't like the renamed files; it will only let me attach as the
original name of the files.
If it's not available in attach, can I rename the file once I attach them
somehow?
Thanks, AndreI figured this out, thanks.
"Andre" <no@.spam.com> wrote in message
news:uvackOWzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> I'm moving a db from serverA to serverB. I detached the db on serverA and
> copied the files to serverB. I want to change the file names when I
attach
> it on serverB. How can I do this? If I try to change the name and attach
> the db, it doesn't like the renamed files; it will only let me attach as
the
> original name of the files.
> If it's not available in attach, can I rename the file once I attach them
> somehow?
>
> Thanks, Andre
>

Attach database

Hi folks
I have copied a .mdf file from machine1 (without detaching it)
Can I attach it to a SQL Server which is running on a different machine
(machine2)? If not, is there anyother way to restore the database with
the .mdf file (without using the backup)?
Thanks in advance.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I gather you stopped the SQL service first to do the copy without detaching
the database.
I would recommend using a backup but if you want to do it this way you can
use EM, right click on databases > attach database
OR TSQL
EXEC sp_attach_db 'MyDb', 'C:\MyDirectory\MyDb_Data.mdf',
'C:\MyDirectory\MyDb_Log.ldf'
Andy
"Venkatesan M" wrote:

> Hi folks
> I have copied a .mdf file from machine1 (without detaching it)
> Can I attach it to a SQL Server which is running on a different machine
> (machine2)? If not, is there anyother way to restore the database with
> the .mdf file (without using the backup)?
> Thanks in advance.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>|||The only way to gaurantee an attach is when the db files are properly
detached first (without open tran). In your case you could try with
e.g.
sp_attach_single_file_db 'db_name',N'mdf file path'
-oj
"Venkatesan M" <venkat_asp@.yahoo.co.uk> wrote in message
news:%23FJfMrDBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi folks
> I have copied a .mdf file from machine1 (without detaching it)
> Can I attach it to a SQL Server which is running on a different machine
> (machine2)? If not, is there anyother way to restore the database with
> the .mdf file (without using the backup)?
> Thanks in advance.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Attach a DB that was never Detached

Sorry if this is the wrong place to ask this. I have a file "restored.mdf",
that was simply copied from the disk by backup software, i need the data
inside that file. It is 12GB. the file was backed up from
E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
E:\SQLData\MSSQL\Data\restore\restored.mdf.
Is there any way to attach this database to any SQL Server 2000 Server ? If
so, i can then just copy the data from one table to another. I tried
EXEC sp_attach_single_file_db @.dbname = N'restored',
@.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
But when i run this i get:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
It looks like it is looking for the original logfile, i guess because this
database was never actually detached.
Attaching databases that were not detached properly is unreliable, and does
not always work.
If there were open transactions, you will need to use sp_attach_db instead
(as these transactions will need to be rolled forward when the database is
attached). You will therefore need access to the log.
John.
hogwart wrote:
> Sorry if this is the wrong place to ask this. I have a file
> "restored.mdf", that was simply copied from the disk by backup
> software, i need the data inside that file. It is 12GB. the file
> was backed up from E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000
> Server ? If so, i can then just copy the data from one table to
> another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because
> this database was never actually detached.
|||Give the suggestion here a try (no guarantees). Make a backup of your files
first though.
http://tinyurl.com/3s4b6
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"hogwart" <hogwart@.dannyland.com> wrote in message
news:%230tcOwzlEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Sorry if this is the wrong place to ask this. I have a file
"restored.mdf",
> that was simply copied from the disk by backup software, i need the data
> inside that file. It is 12GB. the file was backed up from
> E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000 Server ?
If
> so, i can then just copy the data from one table to another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because this
> database was never actually detached.
>

Attach a DB that was never Detached

Sorry if this is the wrong place to ask this. I have a file "restored.mdf",
that was simply copied from the disk by backup software, i need the data
inside that file. It is 12GB. the file was backed up from
E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
E:\SQLData\MSSQL\Data\restore\restored.mdf.
Is there any way to attach this database to any SQL Server 2000 Server ? If
so, i can then just copy the data from one table to another. I tried
EXEC sp_attach_single_file_db @.dbname = N'restored',
@.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
But when i run this i get:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
It looks like it is looking for the original logfile, i guess because this
database was never actually detached.Attaching databases that were not detached properly is unreliable, and does
not always work.
If there were open transactions, you will need to use sp_attach_db instead
(as these transactions will need to be rolled forward when the database is
attached). You will therefore need access to the log.
John.
hogwart wrote:
> Sorry if this is the wrong place to ask this. I have a file
> "restored.mdf", that was simply copied from the disk by backup
> software, i need the data inside that file. It is 12GB. the file
> was backed up from E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000
> Server ? If so, i can then just copy the data from one table to
> another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because
> this database was never actually detached.|||Give the suggestion here a try (no guarantees). Make a backup of your files
first though.
http://tinyurl.com/3s4b6
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"hogwart" <hogwart@.dannyland.com> wrote in message
news:%230tcOwzlEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Sorry if this is the wrong place to ask this. I have a file
"restored.mdf",
> that was simply copied from the disk by backup software, i need the data
> inside that file. It is 12GB. the file was backed up from
> E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000 Server ?
If
> so, i can then just copy the data from one table to another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because this
> database was never actually detached.
>

Attach a DB that was never Detached

Sorry if this is the wrong place to ask this. I have a file "restored.mdf",
that was simply copied from the disk by backup software, i need the data
inside that file. It is 12GB. the file was backed up from
E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
E:\SQLData\MSSQL\Data\restore\restored.mdf.
Is there any way to attach this database to any SQL Server 2000 Server ? If
so, i can then just copy the data from one table to another. I tried
EXEC sp_attach_single_file_db @.dbname = N'restored',
@.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
But when i run this i get:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
It looks like it is looking for the original logfile, i guess because this
database was never actually detached.
Attaching databases that were not detached properly is unreliable, and does
not always work.
If there were open transactions, you will need to use sp_attach_db instead
(as these transactions will need to be rolled forward when the database is
attached). You will therefore need access to the log.
John.
hogwart wrote:
> Sorry if this is the wrong place to ask this. I have a file
> "restored.mdf", that was simply copied from the disk by backup
> software, i need the data inside that file. It is 12GB. the file
> was backed up from E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000
> Server ? If so, i can then just copy the data from one table to
> another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because
> this database was never actually detached.
|||Give the suggestion here a try (no guarantees). Make a backup of your files
first though.
http://tinyurl.com/3s4b6
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"hogwart" <hogwart@.dannyland.com> wrote in message
news:%230tcOwzlEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Sorry if this is the wrong place to ask this. I have a file
"restored.mdf",
> that was simply copied from the disk by backup software, i need the data
> inside that file. It is 12GB. the file was backed up from
> E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000 Server ?
If
> so, i can then just copy the data from one table to another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because this
> database was never actually detached.
>
sql

Asymmetric data syncronization: possible?

I have two sql server instances on two different machines with the same db;
on the first machine some tables need to be copied on the second machine,
and on the second machine some other tables need to be copied on the first;
all the tables are related with the others in some way.
It is possible to make that replication?
Thanks
Mik
I'm wondering if the same data can be changed on each node? If so, then it
sounds like you need either merge or updating transactional subscribers
(queued or immediate). I'd control this from one server designated as the
publisher and synchronize this way. If this is not the case, then both nodes
could be set up as transactional publishers.
Rgds,
Paul Ibison