Showing posts with label abackup. Show all posts
Showing posts with label abackup. Show all posts

Sunday, February 19, 2012

Backup/Restore from MSDE 1.0 to MSDE 2000

To anyone who can assist:
Everything I have read online today states that I should be able to take a
backup copy of my database in MSDE 1.0 and restore it to MSDE 2000. However,
I am experiencing problems with this. We are wanting to upgrade our customer
sites to MSDE 2000 (from MSDE 1.0), and we have as part of our product a
utility which they can backup and restore their databases.
The Backup utility calls this statement:
BACKUP DATABASE dbName TO DISK = fileName WITH BLOCKSIZE=2048
The Restore utility calls this statement:
RESTORE DATABASE dbName FROM DISK = fileName WITH REPLACE
I am able to uninstall MSDE 1.0 from our server, install MSDE 2000, copy our
DB (mdf & ldf files) from the original location to the Data directory for
MSDE 2000, and attach to the database. Everything works great until I
attempt to restore a copy of the database that was generated in MSDE 1.0.
Our Restore utility stops our Database server service, runs the RESTORE
command, then attempts to restart our DB service. This fails, and when I
attempt to look at the database in DbaMgr2K (a very nice tool, BTW), I
receive an error message stating that I need to upgrade this database.
Is there something I am overlooking? The only thing seems to be different
between the two installations is that we had to change our ODBC
configuration. Using MSDE 1.0, we could connect to the "SQL Server"
connection. Using MSDE 2000, we had to change this to our local server.
Thanks in advance for your help!
Becky
hi Becky,
BElders wrote:
> ...
> I am able to uninstall MSDE 1.0 from our server, install MSDE 2000,
> copy our DB (mdf & ldf files) from the original location to the Data
> directory for MSDE 2000, and attach to the database. Everything
> works great until I attempt to restore a copy of the database that
> was generated in MSDE 1.0. Our Restore utility stops our Database
> server service, runs the RESTORE command, then attempts to restart
> our DB service. This fails, and when I attempt to look at the
> database in DbaMgr2K (a very nice tool, BTW), I receive an error
> message stating that I need to upgrade this database.
it's not clear to me why are you stopping the service... if you stop it, how
do you perform the RESTORE operation?
the service of the restoring server must be running to do that , but
probably I misunderstood your words.. or this is can be the cause of the
restore operation failing...
restoring the MSDE 1.0 database on a MSDE 2000 instance will upgrade it to
2000 version, including the te database compatibility level, and DbaMgr2k
and DbaMgr are engine version specefic and not database version (thank you
for your interest in these prjs of mine), so you have to use DbaMgr2k for
MSDE 2000 instances, and DbaMgr for MSDE 1.0 instances..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Just to clear up the confusion...I am not stopping the SQL server service
when using our Restore utility. The service I mentioned is part of our
product, and this is really performed as a precaution. Also, the Restore
command appears to execute OK. The error occurs when our service is
attempting to restart.
That being said, I decided to play around with the Backup/Restore operations
within DbaMgr2k (I am pretty new at using the tool). I received an error
message when trying to run the Restore using the old copy because it could
not locate the mssql7\data directory (removed - by me - as part of the
uninstall of MSDE 1.0). I added this directory back, and the RESTORE
functioned correctly. One thing that I did not realize was that the MDF/LDF
files are relocated in this process. I attempted to mimic this using our
Restore utility, but the same error occurred.
My conclusion is that it looks as though the RESTORE statement in our
utility is not "robust" enough. So I am going to either have to modify our
utility, or just provide instructions to our clients of the steps needed to
perform the same operations using DbaMgr2K (I think I like the latter
solution .
Thanks for your help!!
Becky

Sunday, February 12, 2012

Backup transaction logs when the database is damaged.

Hi,
Due to a disk failure, I lost my .mdf file of a database. Of course I have a
backup, of the database and transactionlogs from last night, but I want to
restore untill the time of failure. So I tried to follow the procedure
explained in : how to backup transaction logs when the database is damaged.
But it wont work because I can't bring my database online. The only thing I
can do is restore my backup from last night but all the work that was
performed during the day is lost.
Can anyone help?
Thanks
Felix
First, you have to be able to bring the SQL server itself online. If you
can't do that, you can't get any further. If the server is online but the
user database is missing the .mdf file, you can use the BACKUP LOG command
with the NO_TRUNCATE option. This will extract the "tail" of the log and
allow you to recover the missing transactions. This command works even with
a suspect or damaged database, provided the log file is undamaged.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> Hi,
> Due to a disk failure, I lost my .mdf file of a database. Of course I have
> a
> backup, of the database and transactionlogs from last night, but I want to
> restore untill the time of failure. So I tried to follow the procedure
> explained in : how to backup transaction logs when the database is
> damaged.
> But it wont work because I can't bring my database online. The only thing
> I
> can do is restore my backup from last night but all the work that was
> performed during the day is lost.
> Can anyone help?
> Thanks
> Felix
|||Hi Geoff,
Thanks for the very fast reply. The situation is the following:
My SQL server is online, I only lost the .mdf file of one user database.
The user database is offline (of course .mdf file is lost).
I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE' to
try and save the last portion of the log file but:
Server: MSG 942, Level 14, State3, Line 1
Datebase 'user_database' cannot be opened because it is offline.
How to proceed now?
Thanks in advance
Felix
"Geoff N. Hiten" wrote:

> First, you have to be able to bring the SQL server itself online. If you
> can't do that, you can't get any further. If the server is online but the
> user database is missing the .mdf file, you can use the BACKUP LOG command
> with the NO_TRUNCATE option. This will extract the "tail" of the log and
> allow you to recover the missing transactions. This command works even with
> a suspect or damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>
>
|||Offline and Suspect are two very different things in SQL Server. Offline is
a SQL controlled database state. Suspect is a reaction to an external
event. Try ALTER DATABASE user_database SET ONLINE and then try the backup
log command.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:93749B04-F531-4C2E-9395-F7F2ED4E2EB4@.microsoft.com...[vbcol=seagreen]
> Hi Geoff,
> Thanks for the very fast reply. The situation is the following:
> My SQL server is online, I only lost the .mdf file of one user database.
> The user database is offline (of course .mdf file is lost).
> I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE'
> to
> try and save the last portion of the log file but:
> Server: MSG 942, Level 14, State3, Line 1
> Datebase 'user_database' cannot be opened because it is offline.
> How to proceed now?
> Thanks in advance
> Felix
>
> "Geoff N. Hiten" wrote:
|||> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further.
Some nit-picking, if you don't mind ;-)
You can get further. Copy the ldf file to a healthy SQL Server. Create a database with same file
names as the original database. Stop that SQL Server. Delete the newly created database files. Copy
the ldf file from the crashed machine to the place of the newly created database ldf file. Start
that SQL Server. That database is now suspect. Do the backup using NO_TRUNCATE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further. If the server is online but the user database is missing the .mdf file, you can
> use the BACKUP LOG command with the NO_TRUNCATE option. This will extract the "tail" of the log
> and allow you to recover the missing transactions. This command works even with a suspect or
> damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>
|||Good point.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>
|||Woooo...Tibor does that work? Kinda cool if it does ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>
|||> Woooo...Tibor does that work? Kinda cool if it does ;-)
Yep, sure is. I've done this several times, when customers had suspect databases, stopped SQL
Server, copied the ldf file "for safety", started SQL Server, dropped the database since "it is
suspect anyhow...". See http://support.microsoft.com/default...;en-us;253817. The KB
states rebuilding master on the same machine, but that is no different from doing the same operation
on some other machine. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Woooo...Tibor does that work? Kinda cool if it does ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>
|||Ok...cudos to you...you da man ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
> Yep, sure is. I've done this several times, when customers had suspect
> databases, stopped SQL Server, copied the ldf file "for safety", started
> SQL Server, dropped the database since "it is suspect anyhow...". See
> http://support.microsoft.com/default...;en-us;253817. The KB
> states rebuilding master on the same machine, but that is no different
> from doing the same operation on some other machine. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
>
|||Hi,
I have to thank you all, I succeeded in restoring the userdatabase to the
point of failure. My problem was that I always brought de database offline.
How it worked:
I stopped and started the SQL server, the database was left in status
suspect, not offline.
In this status, I was able to execute : backup log user_db to disk = 'file'
with INIT, NO_TRUNCATE.
Then I restored the backup from last night but with the NORECOVERY clause.
Then I executed restore log user_db from disk = 'file' with RECOVERY
And my database was back online containing everything until the point of
failure.
Thank you very much.
Hope this helps other people too.
"Jerry Spivey" wrote:

> Ok...cudos to you...you da man ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
>
>

Backup transaction logs when the database is damaged.

Hi,
Due to a disk failure, I lost my .mdf file of a database. Of course I have a
backup, of the database and transactionlogs from last night, but I want to
restore untill the time of failure. So I tried to follow the procedure
explained in : how to backup transaction logs when the database is damaged.
But it wont work because I can't bring my database online. The only thing I
can do is restore my backup from last night but all the work that was
performed during the day is lost.
Can anyone help?
Thanks
FelixFirst, you have to be able to bring the SQL server itself online. If you
can't do that, you can't get any further. If the server is online but the
user database is missing the .mdf file, you can use the BACKUP LOG command
with the NO_TRUNCATE option. This will extract the "tail" of the log and
allow you to recover the missing transactions. This command works even with
a suspect or damaged database, provided the log file is undamaged.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> Hi,
> Due to a disk failure, I lost my .mdf file of a database. Of course I have
> a
> backup, of the database and transactionlogs from last night, but I want to
> restore untill the time of failure. So I tried to follow the procedure
> explained in : how to backup transaction logs when the database is
> damaged.
> But it wont work because I can't bring my database online. The only thing
> I
> can do is restore my backup from last night but all the work that was
> performed during the day is lost.
> Can anyone help?
> Thanks
> Felix|||Hi Geoff,
Thanks for the very fast reply. The situation is the following:
My SQL server is online, I only lost the .mdf file of one user database.
The user database is offline (of course .mdf file is lost).
I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE' to
try and save the last portion of the log file but:
Server: MSG 942, Level 14, State3, Line 1
Datebase 'user_database' cannot be opened because it is offline.
How to proceed now?
Thanks in advance
Felix
"Geoff N. Hiten" wrote:

> First, you have to be able to bring the SQL server itself online. If you
> can't do that, you can't get any further. If the server is online but the
> user database is missing the .mdf file, you can use the BACKUP LOG command
> with the NO_TRUNCATE option. This will extract the "tail" of the log and
> allow you to recover the missing transactions. This command works even wi
th
> a suspect or damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>
>|||Offline and Suspect are two very different things in SQL Server. Offline is
a SQL controlled database state. Suspect is a reaction to an external
event. Try ALTER DATABASE user_database SET ONLINE and then try the backup
log command.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:93749B04-F531-4C2E-9395-F7F2ED4E2EB4@.microsoft.com...[vbcol=seagreen]
> Hi Geoff,
> Thanks for the very fast reply. The situation is the following:
> My SQL server is online, I only lost the .mdf file of one user database.
> The user database is offline (of course .mdf file is lost).
> I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE'
> to
> try and save the last portion of the log file but:
> Server: MSG 942, Level 14, State3, Line 1
> Datebase 'user_database' cannot be opened because it is offline.
> How to proceed now?
> Thanks in advance
> Felix
>
> "Geoff N. Hiten" wrote:
>|||> First, you have to be able to bring the SQL server itself online. If you can't do that, y
ou can't
> get any further.
Some nit-picking, if you don't mind ;-)
You can get further. Copy the ldf file to a healthy SQL Server. Create a dat
abase with same file
names as the original database. Stop that SQL Server. Delete the newly creat
ed database files. Copy
the ldf file from the crashed machine to the place of the newly created data
base ldf file. Start
that SQL Server. That database is now suspect. Do the backup using NO_TRUNCA
TE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> First, you have to be able to bring the SQL server itself online. If you
can't do that, you can't
> get any further. If the server is online but the user database is missing
the .mdf file, you can
> use the BACKUP LOG command with the NO_TRUNCATE option. This will extract
the "tail" of the log
> and allow you to recover the missing transactions. This command works eve
n with a suspect or
> damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>|||Good point.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>|||Woooo...Tibor does that work? Kinda cool if it does ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>|||> Woooo...Tibor does that work? Kinda cool if it does ;-)
Yep, sure is. I've done this several times, when customers had suspect datab
ases, stopped SQL
Server, copied the ldf file "for safety", started SQL Server, dropped the da
tabase since "it is
suspect anyhow...". See http://support.microsoft.com/defaul...25
3817. The KB
states rebuilding master on the same machine, but that is no different from
doing the same operation
on some other machine. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Woooo...Tibor does that work? Kinda cool if it does ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>|||Ok...cudos to you...you da man ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
> Yep, sure is. I've done this several times, when customers had suspect
> databases, stopped SQL Server, copied the ldf file "for safety", started
> SQL Server, dropped the database since "it is suspect anyhow...". See
> http://support.microsoft.com/defaul...b;en-us;253817. The KB
> states rebuilding master on the same machine, but that is no different
> from doing the same operation on some other machine. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
>|||Hi,
I have to thank you all, I succeeded in restoring the userdatabase to the
point of failure. My problem was that I always brought de database offline.
How it worked:
I stopped and started the SQL server, the database was left in status
suspect, not offline.
In this status, I was able to execute : backup log user_db to disk = 'file'
with INIT, NO_TRUNCATE.
Then I restored the backup from last night but with the NORECOVERY clause.
Then I executed restore log user_db from disk = 'file' with RECOVERY
And my database was back online containing everything until the point of
failure.
Thank you very much.
Hope this helps other people too.
"Jerry Spivey" wrote:

> Ok...cudos to you...you da man ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
>
>