Asp.net / sql help: backup

Showing posts with label backup. Show all posts

Sunday, March 25, 2012

Attach Database

Is there a way to attach a database and leave it in a read-
only mode to restore additional transaction log files just
like restoring from a backup file '
Thanks.You are talking about two different things here. When you attach a database,
the db is going to be online, unlike 'restoring from a backup'.
Could you explain what it is that you are trying to do?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
Is there a way to attach a database and leave it in a read-
only mode to restore additional transaction log files just
like restoring from a backup file '
Thanks.|||Why it is so different. In both cases, the database can be
online. It is much faster to attach a database than
restoring a database from a backup. If the database is
large (More that 50 GB) copying the *.mdf and *.ldf files
to a backup server rather than backing up it may take
shorter time.
>--Original Message--
>You are talking about two different things here. When you
attach a database,
>the db is going to be online, unlike 'restoring from a
backup'.
>Could you explain what it is that you are trying to do?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
>Is there a way to attach a database and leave it in a
read-
>only mode to restore additional transaction log files just
>like restoring from a backup file '
>Thanks.
>
>.
>|||Like the other post said the restoration and attaching are two different
things. Now if you want to put a database into read-only mode after the
attach you can issue the following T-SQL command:
exec sp_dboption 'yourdb','read only','true'
To turn off read only issue the follwoing:
exec sp_dboption 'yourdb','read only','false'
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
> Is there a way to attach a database and leave it in a read-
> only mode to restore additional transaction log files just
> like restoring from a backup file '
> Thanks.|||What?
If you're attaching a database you detached, how are you confident that you
can accurately apply transaction logs to it to bring it up to date?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
> Why it is so different. In both cases, the database can be
> online. It is much faster to attach a database than
> restoring a database from a backup. If the database is
> large (More that 50 GB) copying the *.mdf and *.ldf files
> to a backup server rather than backing up it may take
> shorter time.
>
> >--Original Message--
> >You are talking about two different things here. When you
> attach a database,
> >the db is going to be online, unlike 'restoring from a
> backup'.
> >
> >Could you explain what it is that you are trying to do?
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >Is .NET important for a database professional?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"David" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
> >Is there a way to attach a database and leave it in a
> read-
> >only mode to restore additional transaction log files just
> >like restoring from a backup file '
> >
> >Thanks.
> >
> >
> >.
> >|||I am stopping the SQL Server services to copy the *.mdf
and *.ldf file. When I started the services transaction
log backups resumes according to the maintenance plan. SO,
what is so different. What does Stopping and restarting
services changes''/
>--Original Message--
>What?
>If you're attaching a database you detached, how are you
confident that you
>can accurately apply transaction logs to it to bring it
up to date?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
>> Why it is so different. In both cases, the database can
be
>> online. It is much faster to attach a database than
>> restoring a database from a backup. If the database is
>> large (More that 50 GB) copying the *.mdf and *.ldf
files
>> to a backup server rather than backing up it may take
>> shorter time.
>>
>> >--Original Message--
>> >You are talking about two different things here. When
you
>> attach a database,
>> >the db is going to be online, unlike 'restoring from a
>> backup'.
>> >
>> >Could you explain what it is that you are trying to do?
>> >--
>> >HTH,
>> >Vyas, MVP (SQL Server)
>> >http://vyaskn.tripod.com/
>> >Is .NET important for a database professional?
>> >http://vyaskn.tripod.com/poll.htm
>> >
>> >
>> >"David" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
>> >Is there a way to attach a database and leave it in a
>> read-
>> >only mode to restore additional transaction log files
just
>> >like restoring from a backup file '
>> >
>> >Thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||That's not a proper detach! So now you're assuming that your hack method
will allow you to attach such an MDF file without problems? Have you tested
this scenario? Why would you use this method as opposed to accepted and
proven methods?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fbfa01c43e7c$80b3cb70$a501280a@.phx.gbl...
> I am stopping the SQL Server services to copy the *.mdf
> and *.ldf file. When I started the services transaction
> log backups resumes according to the maintenance plan. SO,
> what is so different. What does Stopping and restarting
> services changes''/
>
> >--Original Message--
> >What?
> >
> >If you're attaching a database you detached, how are you
> confident that you
> >can accurately apply transaction logs to it to bring it
> up to date?
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> >"David" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
> >> Why it is so different. In both cases, the database can
> be
> >> online. It is much faster to attach a database than
> >> restoring a database from a backup. If the database is
> >> large (More that 50 GB) copying the *.mdf and *.ldf
> files
> >> to a backup server rather than backing up it may take
> >> shorter time.
> >>
> >>
> >>
> >> >--Original Message--
> >> >You are talking about two different things here. When
> you
> >> attach a database,
> >> >the db is going to be online, unlike 'restoring from a
> >> backup'.
> >> >
> >> >Could you explain what it is that you are trying to do?
> >> >--
> >> >HTH,
> >> >Vyas, MVP (SQL Server)
> >> >http://vyaskn.tripod.com/
> >> >Is .NET important for a database professional?
> >> >http://vyaskn.tripod.com/poll.htm
> >> >
> >> >
> >> >"David" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
> >> >Is there a way to attach a database and leave it in a
> >> read-
> >> >only mode to restore additional transaction log files
> just
> >> >like restoring from a backup file '
> >> >
> >> >Thanks.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||It is a HACK method to you and not to me. What difference
would it make if I detach it and copy it '? The
question is why the same options (READ_ONLY, SINGLE_USER
e.t.c) that can be applied to backup restore can not be
applied to attach database...........
>--Original Message--
>That's not a proper detach! So now you're assuming that
your hack method
>will allow you to attach such an MDF file without
problems? Have you tested
>this scenario? Why would you use this method as
opposed to accepted and
>proven methods?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fbfa01c43e7c$80b3cb70$a501280a@.phx.gbl...
>> I am stopping the SQL Server services to copy the *.mdf
>> and *.ldf file. When I started the services transaction
>> log backups resumes according to the maintenance plan.
SO,
>> what is so different. What does Stopping and restarting
>> services changes''/
>>
>> >--Original Message--
>> >What?
>> >
>> >If you're attaching a database you detached, how are
you
>> confident that you
>> >can accurately apply transaction logs to it to bring it
>> up to date?
>> >
>> >--
>> >Aaron Bertrand
>> >SQL Server MVP
>> >http://www.aspfaq.com/
>> >
>> >
>> >
>> >
>> >"David" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
>> >> Why it is so different. In both cases, the database
can
>> be
>> >> online. It is much faster to attach a database than
>> >> restoring a database from a backup. If the database
is
>> >> large (More that 50 GB) copying the *.mdf and *.ldf
>> files
>> >> to a backup server rather than backing up it may take
>> >> shorter time.
>> >>
>> >>
>> >>
>> >> >--Original Message--
>> >> >You are talking about two different things here.
When
>> you
>> >> attach a database,
>> >> >the db is going to be online, unlike 'restoring
from a
>> >> backup'.
>> >> >
>> >> >Could you explain what it is that you are trying to
do?
>> >> >--
>> >> >HTH,
>> >> >Vyas, MVP (SQL Server)
>> >> >http://vyaskn.tripod.com/
>> >> >Is .NET important for a database professional?
>> >> >http://vyaskn.tripod.com/poll.htm
>> >> >
>> >> >
>> >> >"David" <anonymous@.discussions.microsoft.com> wrote
in
>> >> message
>> >> >news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
>> >> >Is there a way to attach a database and leave it in
a
>> >> read-
>> >> >only mode to restore additional transaction log
files
>> just
>> >> >like restoring from a backup file '
>> >> >
>> >> >Thanks.
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||> What difference would it make if I detach it and copy it '?
Because the system does a proper detach and makes sure the db/log are in
sync and can be attached to other systems flawlessly. If you stop the SQL
Server service, this synchronization is not necessarily going to be done for
you (because this SQL Server knows it can resume any ongoing operations, or
roll them back, when the server comes back online...).
What is the BENEFIT you are deriving from stopping the service and copying
the files (which is, absolutely, a hack) as compared to using generally
accepted methods (restore/backup, detach/attach, etc)?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||To make a long story short,
You cannot do this. In order to restore the transaction logs you have to
first restore the database and then restore all the logs in the proper
order, starting with the first one after teh most recent backup.. You
cannot pick and choose which logs you want to restore. So attaching is a
moot point because you still have to restore the database backup in order
to restore any of the logs..
To answer your question more simply:
SQL Server was not designed to work in the manner in which are suggesting.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand voiced my opinion much more succinctly. ;-)|||I did not mean to be harsh on anyone. I am aware of all of
this. It was just an idea I was throwing it may be
possible one day..........
I was looking for a way out from database backup taking 4
hours every night and restoring on the backup server. When
you have a few database on production server that take too
long to backup and restore along with all the other
processes (Nightly loads, data extracts e.t.c) there isn't
enough time to do everything. Plus a cheap management who
don't want to pay for any third party tools.
Thanks everyone.........
>--Original Message--
>To make a long story short,
>You cannot do this. In order to restore the transaction
logs you have to
>first restore the database and then restore all the logs
in the proper
>order, starting with the first one after teh most recent
backup.. You
>cannot pick and choose which logs you want to restore. So
attaching is a
>moot point because you still have to restore the database
backup in order
>to restore any of the logs..
>To answer your question more simply:
>SQL Server was not designed to work in the manner in
which are suggesting.
>Rand
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Log shipping? Transactional replication? Clustering?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fd9401c43ea6$c3862c20$a601280a@.phx.gbl...
> I did not mean to be harsh on anyone. I am aware of all of
> this. It was just an idea I was throwing it may be
> possible one day..........
> I was looking for a way out from database backup taking 4
> hours every night and restoring on the backup server. When
> you have a few database on production server that take too
> long to backup and restore along with all the other
> processes (Nightly loads, data extracts e.t.c) there isn't
> enough time to do everything. Plus a cheap management who
> don't want to pay for any third party tools.
> Thanks everyone.........|||It is SQL Server 2000 Standard. Our manager thinks
Transactional replication is resource intense......
>--Original Message--
>log shipping is the best way and the cheapest way, if you
have sql server enterprise edition
> -- Aaron Bertrand - MVP wrote: --
> Log shipping? Transactional replication?
Clustering?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "David" <anonymous@.discussions.microsoft.com> wrote
in message
> news:fd9401c43ea6$c3862c20$a601280a@.phx.gbl...
> > I did not mean to be harsh on anyone. I am aware
of all of
> > this. It was just an idea I was throwing it may be
> > possible one day..........
> >> I was looking for a way out from database backup
taking 4
> > hours every night and restoring on the backup
server. When
> > you have a few database on production server that
take too
> > long to backup and restore along with all the other
> > processes (Nightly loads, data extracts e.t.c)
there isn't
> > enough time to do everything. Plus a cheap
management who
> > don't want to pay for any third party tools.
> >> Thanks everyone.........
>
>.
>|||> Our manager thinks
Well, that's scary on its own.
However, instead of letting the manager think something, why not test it and
prove it one way or the other? Rejecting an option because of someone's
perception is a huge mistake, and the manager should be the *first* to
recognize that, if he's worth his salt.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Log shipping can be performed with any edition, you just cannot just the built-in stuff in EM and Maint wiz
for it. Search and you will find scripts on the net that does the same.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"roy" <anonymous@.discussions.microsoft.com> wrote in message
news:7F7BB8B6-90B8-471F-91B9-6111E79BF8AE@.microsoft.com...
> log shipping is the best way and the cheapest way, if you have sql server enterprise edition
> -- Aaron Bertrand - MVP wrote: --
> Log shipping? Transactional replication? Clustering?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "David" <anonymous@.discussions.microsoft.com> wrote in message
> news:fd9401c43ea6$c3862c20$a601280a@.phx.gbl...
> > I did not mean to be harsh on anyone. I am aware of all of
> > this. It was just an idea I was throwing it may be
> > possible one day..........
> >> I was looking for a way out from database backup taking 4
> > hours every night and restoring on the backup server. When
> > you have a few database on production server that take too
> > long to backup and restore along with all the other
> > processes (Nightly loads, data extracts e.t.c) there isn't
> > enough time to do everything. Plus a cheap management who
> > don't want to pay for any third party tools.
> >> Thanks everyone.........
>
>

Attach Database

Is there a way to attach a database and leave it in a read-
only mode to restore additional transaction log files just
like restoring from a backup file ?
Thanks.
You are talking about two different things here. When you attach a database,
the db is going to be online, unlike 'restoring from a backup'.
Could you explain what it is that you are trying to do?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
Is there a way to attach a database and leave it in a read-
only mode to restore additional transaction log files just
like restoring from a backup file ?
Thanks.
|||Why it is so different. In both cases, the database can be
online. It is much faster to attach a database than
restoring a database from a backup. If the database is
large (More that 50 GB) copying the *.mdf and *.ldf files
to a backup server rather than backing up it may take
shorter time.

>--Original Message--
>You are talking about two different things here. When you
attach a database,
>the db is going to be online, unlike 'restoring from a
backup'.
>Could you explain what it is that you are trying to do?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
>Is there a way to attach a database and leave it in a
read-
>only mode to restore additional transaction log files just
>like restoring from a backup file ?
>Thanks.
>
>.
>
|||Like the other post said the restoration and attaching are two different
things. Now if you want to put a database into read-only mode after the
attach you can issue the following T-SQL command:
exec sp_dboption 'yourdb','read only','true'
To turn off read only issue the follwoing:
exec sp_dboption 'yourdb','read only','false'
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
> Is there a way to attach a database and leave it in a read-
> only mode to restore additional transaction log files just
> like restoring from a backup file ?
> Thanks.
|||What?
If you're attaching a database you detached, how are you confident that you
can accurately apply transaction logs to it to bring it up to date?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fbb301c43e79$757bd070$a501280a@.phx.gbl...[vbcol=seagreen]
> Why it is so different. In both cases, the database can be
> online. It is much faster to attach a database than
> restoring a database from a backup. If the database is
> large (More that 50 GB) copying the *.mdf and *.ldf files
> to a backup server rather than backing up it may take
> shorter time.
>
> attach a database,
> backup'.
> message
> read-
|||I am stopping the SQL Server services to copy the *.mdf
and *.ldf file. When I started the services transaction
log backups resumes according to the maintenance plan. SO,
what is so different. What does Stopping and restarting
services changes??/

>--Original Message--
>What?
>If you're attaching a database you detached, how are you
confident that you
>can accurately apply transaction logs to it to bring it
up to date?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
be[vbcol=seagreen]
files[vbcol=seagreen]
you[vbcol=seagreen]
just
>
>.
>
|||That's not a proper detach! So now you're assuming that your hack method
will allow you to attach such an MDF file without problems? Have you tested
this scenario? Why would you use this method as opposed to accepted and
proven methods?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fbfa01c43e7c$80b3cb70$a501280a@.phx.gbl...[vbcol=seagreen]
> I am stopping the SQL Server services to copy the *.mdf
> and *.ldf file. When I started the services transaction
> log backups resumes according to the maintenance plan. SO,
> what is so different. What does Stopping and restarting
> services changes??/
>
> confident that you
> up to date?
> message
> be
> files
> you
> just
|||It is a HACK method to you and not to me. What difference
would it make if I detach it and copy it ?? The
question is why the same options (READ_ONLY, SINGLE_USER
e.t.c) that can be applied to backup restore can not be
applied to attach database...........

>--Original Message--
>That's not a proper detach! So now you're assuming that
your hack method
>will allow you to attach such an MDF file without
problems? Have you tested
>this scenario? Why would you use this method as
opposed to accepted and
>proven methods?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:fbfa01c43e7c$80b3cb70$a501280a@.phx.gbl...
SO,[vbcol=seagreen]
you[vbcol=seagreen]
can[vbcol=seagreen]
is[vbcol=seagreen]
When[vbcol=seagreen]
from a[vbcol=seagreen]
do?[vbcol=seagreen]
in[vbcol=seagreen]
a[vbcol=seagreen]
files
>
>.
>
|||> What difference would it make if I detach it and copy it ??
Because the system does a proper detach and makes sure the db/log are in
sync and can be attached to other systems flawlessly. If you stop the SQL
Server service, this synchronization is not necessarily going to be done for
you (because this SQL Server knows it can resume any ongoing operations, or
roll them back, when the server comes back online...).
What is the BENEFIT you are deriving from stopping the service and copying
the files (which is, absolutely, a hack) as compared to using generally
accepted methods (restore/backup, detach/attach, etc)?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||To make a long story short,
You cannot do this. In order to restore the transaction logs you have to
first restore the database and then restore all the logs in the proper
order, starting with the first one after teh most recent backup.. You
cannot pick and choose which logs you want to restore. So attaching is a
moot point because you still have to restore the database backup in order
to restore any of the logs..
To answer your question more simply:
SQL Server was not designed to work in the manner in which are suggesting.
Rand
This posting is provided "as is" with no warranties and confers no rights.

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

Wednesday, March 7, 2012

Assertion Errors, temdb guest account, and other errors

Last night, we had a sql server (2000, sp3) whose transaction logs get
backed up to a share have the log backup jobs fail. At the same time, a
database that is a replication publisher became unavailable. We were getting
the following errors in the sql server error log but this error was written
only once to the W2003 Application event log:
Error: 3624, Severity: 20, State: 1.
SQL Server Assertion: File: <sysscan.cpp>, line=565
Failed Assertion = 'xdes->GetDbId () == sDbid'.
I'm still researching this error.
The replicated database showed up in EM but there were no tables (not even
system tables). We tried to detach it to see if we could perhaps re-attach
the files and return it to availability but since it was being replicated,
it would not allow us to do so. We tried various ways of dropping the
publication but since the database was unavailable, we were unable to drop
it.
Since regardless of our resolution to bringing this database back on-line
(restore, etc.) was going to require that we get rid of it as it is, we
restarted the sql server service. The database showed up again and seemed to
be fine. However, this morning, after numerous reports from people unable to
see the databases on this server and errors related to invalid login in
tempdb, I discovered that the guest account was disabled. I granted access
to tempdb to the guest account and that resolved this problem.
We have also had a number of problems with the linked servers that were
previously functional to and from this server.
We recreated the replication of the database that had the problem last night
and the conflict tables are named aonflict% instead of conflict%. We're not
sure if this is related or not but the other problems certainly seem to be
related.
Everything seems to be functional now but I am faced with the task of
putting all of these pieces together in order to determine the cause and to
prevent it from happening again. Any advice on any of these possibly related
issues would be appreciated.It appears as if guest was disabled in model which would explain why it was
disabled in tempdb after restarting the sql server service. I'm not sure why
it was disabled in model.
We're calling MS PSS regarding the assertion errors.
It appears as if the funny conflict table names are due to the failure to
clean up the old publication and/or subscription.
"michelle" <michelle@.nospam.com> wrote in message
news:uUP169kdEHA.1604@.TK2MSFTNGP11.phx.gbl...
> Last night, we had a sql server (2000, sp3) whose transaction logs get
> backed up to a share have the log backup jobs fail. At the same time, a
> database that is a replication publisher became unavailable. We were
getting
> the following errors in the sql server error log but this error was
written
> only once to the W2003 Application event log:
> Error: 3624, Severity: 20, State: 1.
> SQL Server Assertion: File: <sysscan.cpp>, line=565
> Failed Assertion = 'xdes->GetDbId () == sDbid'.
> I'm still researching this error.
> The replicated database showed up in EM but there were no tables (not even
> system tables). We tried to detach it to see if we could perhaps re-attach
> the files and return it to availability but since it was being replicated,
> it would not allow us to do so. We tried various ways of dropping the
> publication but since the database was unavailable, we were unable to drop
> it.
> Since regardless of our resolution to bringing this database back on-line
> (restore, etc.) was going to require that we get rid of it as it is, we
> restarted the sql server service. The database showed up again and seemed
to
> be fine. However, this morning, after numerous reports from people unable
to
> see the databases on this server and errors related to invalid login in
> tempdb, I discovered that the guest account was disabled. I granted access
> to tempdb to the guest account and that resolved this problem.
> We have also had a number of problems with the linked servers that were
> previously functional to and from this server.
> We recreated the replication of the database that had the problem last
night
> and the conflict tables are named aonflict% instead of conflict%. We're
not
> sure if this is related or not but the other problems certainly seem to be
> related.
> Everything seems to be functional now but I am faced with the task of
> putting all of these pieces together in order to determine the cause and
to
> prevent it from happening again. Any advice on any of these possibly
related
> issues would be appreciated.
>

Saturday, February 25, 2012

ASR backup of SQL server 2000

Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
Thanks,
Gordon
please disregard this post.
"GoHwang" wrote:

> Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
> Thanks,
> Gordon

ASR backup of SQL server 2000

Is there a process or procedure to use ASR as part of backup and recovery of
a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
Thanks,
Gordonplease disregard this post.
"GoHwang" wrote:

> Is there a process or procedure to use ASR as part of backup and recovery
of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
> Thanks,
> Gordon

ASR backup of SQL 2000 on win2k3

Is there a process or procedure to use ASR as part of backup and recovery of
a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
Thanks,
Gordonplease disregard this post
"GoHwang" wrote:

> Is there a process or procedure to use ASR as part of backup and recovery
of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
> Thanks,
> Gordon

ASR backup of SQL 2000 on win2k3

Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
Thanks,
Gordonplease disregard this post
"GoHwang" wrote:
> Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
> Thanks,
> Gordon

ASR backup of SQL 2000 on win2k3

Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
Thanks,
Gordon
please disregard this post
"GoHwang" wrote:

> Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
> Thanks,
> Gordon

Thursday, February 9, 2012

ASP.NET 2.0 and SQL Server Express Backup

I′m developing an application that uses SQL Server Express as the Back-End, i want to provide to the user an inteface where he can make backup of the database or restore a backup file, i really don′t know where i can begin with this task or what′s the best approach for doing stuff like this, i need some guidance here because we finish with the application and we are just adding some usefull features to the user.

If somebody has any idea, i am willing to learn and listen carefully. thnaks to everybody.

Thanks to everybody, Cesar Sad If you are running under Full Trust, you can make use of the SMO library. See, for example,Using SMO for Backup,Restore and Security Purposes orGetting Started with SMO in SQL 2005 - Backups|||I try with the"Getting Started with SMO in SQL 2005 - Backups" sample and it doesn't show me the SQL Express instances, i got installed a SQL Server Standard and a SQL Express instance and the sample app only shows me the Standard instance.

I dont know if this got something to do with the "full trust"(i'm sorry but i dont know what it is).Could you be more specific ?.

Cesar

Sunday, March 25, 2012

Attach Database

Attach Database

Attach Database

Attach a DB that was never Detached

Attach a DB that was never Detached

Attach a DB that was never Detached

Wednesday, March 7, 2012

Assertion Errors, temdb guest account, and other errors

Saturday, February 25, 2012

ASR backup of SQL server 2000

ASR backup of SQL server 2000

ASR backup of SQL 2000 on win2k3

ASR backup of SQL 2000 on win2k3

ASR backup of SQL 2000 on win2k3

Thursday, February 9, 2012

ASP.NET 2.0 and SQL Server Express Backup

Asp.net / sql help

Blog Archive

About Me