Restore mdf file: March 2012

Friday, March 30, 2012

Restore deleted table from log?

Hi,
If a table was deleted, is it possible to roll back the deletion with the
transaction log?
ThanksCheck out www.lumigent.com.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"BDB" <bdb@.reply.to.group.com> wrote in message
news:%23iSygiyYGHA.508@.TK2MSFTNGP02.phx.gbl...
Hi,
If a table was deleted, is it possible to roll back the deletion with the
transaction log?
Thanks|||Okay this is what I have:
BACKUP LOG mydb
TO DISK= 'mydblog200604180415.bak'
WITH NO_TRUNCATE, INIT
go
use master
go
RESTORE LOG mydb
FROM DISK= 'mydblog200604180415.bak'
WITH STOPAT = 'Apr 18, 2006 3:30 PM'
I get this error:
Msg 3117, Level 16, State 1, Line 2
The log or differential backup cannot be restored because no files are ready
to rollforward.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
Can anyone help?|||"BDB" <bdb@.reply.to.group.com> wrote in message
news:%23iSygiyYGHA.508@.TK2MSFTNGP02.phx.gbl...
> Hi,
> If a table was deleted, is it possible to roll back the deletion with the
> transaction log?
You can restore the database and then all logs up to the point in time just
before it was deleted.
If you mean recover the table w/o doing that, http://www.lumigent.com/ may
help.
> Thanks
>|||When you restore log, you first need to restore database, then the log backups, in order. I suggest
you read some in Books Online about backup and restore to get a grip on how this work. Also, you
might want to check out: http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BDB" <bdb@.reply.to.group.com> wrote in message news:%23$zI%23BzYGHA.428@.TK2MSFTNGP02.phx.gbl...
> Okay this is what I have:
> BACKUP LOG mydb
> TO DISK= 'mydblog200604180415.bak'
> WITH NO_TRUNCATE, INIT
> go
> use master
> go
> RESTORE LOG mydb
> FROM DISK= 'mydblog200604180415.bak'
> WITH STOPAT = 'Apr 18, 2006 3:30 PM'
> I get this error:
> Msg 3117, Level 16, State 1, Line 2
> The log or differential backup cannot be restored because no files are ready to rollforward.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE LOG is terminating abnormally.
>
> Can anyone help?
>|||Just keep in mind the below point in the article to minimise impact on
production db.
* Restore into another database and copy the affected data into the
production database.
Thanks
Ajay Rengunthwarsql

Restore deleted table

Is it possible to restore the deleted table? No changes were made to the
database after the table was deleted.
SQL Server 2000.
Thanks.You can restore the database to a new location/name and get at teh table
that way.
"Vik" <viktorum@.==yahoo.com==> wrote in message
news:%23I6xAlbFIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Is it possible to restore the deleted table? No changes were made to the
> database after the table was deleted.
> SQL Server 2000.
> Thanks.
>|||I just want to make Jay's answer clearer.
First of all, I hope you have a backup of your database. If so, restore your
backup using another name and then export mentioned table to your current
database. (Right click on your restored database and then go to Tasks and
Export...)
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Vik" <viktorum@.==yahoo.com==> wrote in message
news:%23I6xAlbFIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Is it possible to restore the deleted table? No changes were made to the
> database after the table was deleted.
> SQL Server 2000.
> Thanks.
>|||Try ApexSQL Log.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Vik" <viktorum@.==yahoo.com==> wrote in message
news:%23I6xAlbFIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Is it possible to restore the deleted table? No changes were made to the
> database after the table was deleted.
> SQL Server 2000.
> Thanks.
>|||Thanks for the replies.
Is there a free tool to work with a transaction log (at least to view it)?
Vik
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13huna7gk3qcb3a@.corp.supernews.com...
> Try ApexSQL Log.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Vik" <viktorum@.==yahoo.com==> wrote in message
> news:%23I6xAlbFIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Is it possible to restore the deleted table? No changes were made to the
>> database after the table was deleted.
>> SQL Server 2000.
>> Thanks.
>>
>

restore deleted records

Hi i am currentlly using the SQL sever 2000, i am
wondering is there anyway to restore back the deleted
records which i had deleted at the SQL server table with
the default configuration?
Help it's urgent!!
Thanks...
Regards
sharonSHARON wrote:
> Hi i am currentlly using the SQL sever 2000, i am
> wondering is there anyway to restore back the deleted
> records which i had deleted at the SQL server table with
> the default configuration?
> Help it's urgent!!
> Thanks...
> Regards
> sharon
As long as you are not running simple recovery mode you can use
Lumigent Log explorer to do this http://www.lumigent.com/
Otherwise the only way would be to restore your latest backup.
p.s. if you arent using simple recovery mode and perform regular
transaction log backups you could also restore to a point in time.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============

Restore Deleted Instance of SQL 2000

We had a server with SQL 2000 on it. It had two instances. Worked
great.
We decided to upgrade to SQL 2005. We ran the upgrade on the default
instance. Everything was great.
Before we upgraded the second instance, someone went into 'add remove
programs' and removed the second instance of SQL 2000. Can I get it
back?
I created a second instance in SQL 2005 and attached the single
important DB from that second instance of SQL 2000 and the data is all
there, but we are having connection issues. I'm afraid it may be
because we deleted the second instance of SQL 2000 before we got a
chance to upgrade.
Any ideas?
-BDOn Nov 2, 3:16 am, i...@.clowwater.com wrote:
> We had a server with SQL 2000 on it. It had two instances. Worked
> great.
> We decided to upgrade to SQL 2005. We ran the upgrade on the default
> instance. Everything was great.
> Before we upgraded the second instance, someone went into 'add remove
> programs' and removed the second instance of SQL 2000. Can I get it
> back?
> I created a second instance in SQL 2005 and attached the single
> important DB from that second instance of SQL 2000 and the data is all
> there, but we are having connection issues. I'm afraid it may be
> because we deleted the second instance of SQL 2000 before we got a
> chance to upgrade.
> Any ideas?
> -BD
I don't think that this has something to do with the fact that the
second instance was deleted. Can you post more details? What is the
error that you get? Do you get it each time that you try to connect
to the server? Do you also get it if you try to connect to the SQL
Server from the server it self?
Adi

Restore Default Trigger Template

Hello,
I am locally running SS2K, but the database is sitting on a SS7 server. I
created a trigger and accidentally clicked the "Save As Template" button.
Is there any way to set the template back to the default?Did you overwrite one of your trigger templates? Which one? You may be able to get it from another machine that has the =client tools installed on your network. Perhaps you could find the =appropriate .tql file on the SQL Server CD.
-- Keith
"Steven K" <sckeels@.lucent.com> wrote in message =news:ucs4#yMbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> Hello,
> > I am locally running SS2K, but the database is sitting on a SS7 =server. I
> created a trigger and accidentally clicked the "Save As Template" =button.
> Is there any way to set the template back to the default?
> >|||Thanks Keith, I overwrote the "new" template. I will get it from another
machine.
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:OUfh9pNbDHA.2588@.TK2MSFTNGP09.phx.gbl...
Did you overwrite one of your trigger templates?
Which one? You may be able to get it from another machine that has the
client tools installed on your network. Perhaps you could find the
appropriate .tql file on the SQL Server CD.
--
Keith
"Steven K" <sckeels@.lucent.com> wrote in message
news:ucs4#yMbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am locally running SS2K, but the database is sitting on a SS7 server. I
> created a trigger and accidentally clicked the "Save As Template" button.
> Is there any way to set the template back to the default?
>

Restore default setting for MSDE

How can I restore the default setting for MSDE to have only WindowsAutentication and not mixed mode? And to have my sa account withoutpassword? Please help me! Thanks, Nibbles

Nibbles wrote:
How can I restore the default setting for MSDE to have only WindowsAutentication and not mixed mode?

This setting cannot be changed with T-SQL as far as I know. Youcan do it with Enterprise Manager, or with your own custom SQL-DMOprogram, or by changing the registry directly.

Nibbles wrote:

And to have my sa account withoutpassword?

Never ever leave the sa account without a pasword! You can change the password using thesp_password system stored procedure.
You will find this article helpful as it addresses both of your needs:How To Verify and Change the MSDE System Administrator Password
sql

Restore default security settings on MASTER database

I have a utility script that I wrote sometime ago to grant access to all
tables and stored procedures in a database to the PUBLIC user group.
Unfortunately, I used it on another server on which my default database was
not setup and as such it has been run against the MASTER database resulting
in completely screwed security on that database.
It is not a production server and as such I can remove and re-install SQL2k
if required, but before I do; is there any known way to re-instate the
default security settings?
Cheers.U can run rebuildm, this however will rebuild (master, msdb, model) so make
sure U have good backup of msdb and possibly model if you have any specifics
in there.|||Thanks. That was what I was looking for.
Martin.
"Olu Adedeji" <anonymous@.discussions.microsoft.com> wrote in message
news:C55C7D94-1F3F-4C79-955F-8B0565129D92@.microsoft.com...
> U can run rebuildm, this however will rebuild (master, msdb, model) so
make sure U have good backup of msdb and possibly model if you have any
specifics in there.

Restore dbase but leave 1 table the same

I have 2 servers and am migrating data occasionally from one server to the
other. There are several tables but one of the tables I do not want to be
updated by the restore. Just wondering if there is an easy way to do this?
Also the destination server has an additional stored procedure so would like
this procedure to be retained with the dbase restore.
thanks.
--
Paul G
Software engineer.There are many potential approaches, the choice of which depends on factors
such as the size of the database, the size of the table in question, etc.
If the table is smallish, you can preserve a copy in a different database
before the restore. After the database restore, repopulate the table back
from the saved copy. Same can be done to the stored procedure.
Linchi
"Paul" wrote:

> I have 2 servers and am migrating data occasionally from one server to the
> other. There are several tables but one of the tables I do not want to be
> updated by the restore. Just wondering if there is an easy way to do this
?
> Also the destination server has an additional stored procedure so would li
ke
> this procedure to be retained with the dbase restore.
> thanks.
> --
> Paul G
> Software engineer.|||thanks for the response. I forgot to mention that the schema is a bit
different between the two tables, destination table has 2 additional columns
.
I have used the create script file for stored procedures and tables out of
the server explorer in .net. Guess there is no way to script to file a tabl
e
and the data that is in it.
--
Paul G
Software engineer.
"Linchi Shea" wrote:
[vbcol=seagreen]
> There are many potential approaches, the choice of which depends on factor
s
> such as the size of the database, the size of the table in question, etc.
> If the table is smallish, you can preserve a copy in a different database
> before the restore. After the database restore, repopulate the table back
> from the saved copy. Same can be done to the stored procedure.
> Linchi
> "Paul" wrote:
>|||> Guess there is no way to script to file a table
> and the data that is in it.
You could always use DMO/SMO to script the table and bcp to export the data.
Linchi
"Paul" wrote:
[vbcol=seagreen]
> thanks for the response. I forgot to mention that the schema is a bit
> different between the two tables, destination table has 2 additional colum
ns.
> I have used the create script file for stored procedures and tables out o
f
> the server explorer in .net. Guess there is no way to script to file a ta
ble
> and the data that is in it.
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
>|||use DTS...
Jay
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8A97BB5B-3F3E-419E-917F-19B16A050400@.microsoft.com...
>I have 2 servers and am migrating data occasionally from one server to the
> other. There are several tables but one of the tables I do not want to be
> updated by the restore. Just wondering if there is an easy way to do
> this?
> Also the destination server has an additional stored procedure so would
> like
> this procedure to be retained with the dbase restore.
> thanks.
> --
> Paul G
> Software engineer.

Restore dbase but leave 1 table the same

I have 2 servers and am migrating data occasionally from one server to the
other. There are several tables but one of the tables I do not want to be
updated by the restore. Just wondering if there is an easy way to do this?
Also the destination server has an additional stored procedure so would like
this procedure to be retained with the dbase restore.
thanks.
--
Paul G
Software engineer.There are many potential approaches, the choice of which depends on factors
such as the size of the database, the size of the table in question, etc.
If the table is smallish, you can preserve a copy in a different database
before the restore. After the database restore, repopulate the table back
from the saved copy. Same can be done to the stored procedure.
Linchi
"Paul" wrote:
> I have 2 servers and am migrating data occasionally from one server to the
> other. There are several tables but one of the tables I do not want to be
> updated by the restore. Just wondering if there is an easy way to do this?
> Also the destination server has an additional stored procedure so would like
> this procedure to be retained with the dbase restore.
> thanks.
> --
> Paul G
> Software engineer.|||thanks for the response. I forgot to mention that the schema is a bit
different between the two tables, destination table has 2 additional columns.
I have used the create script file for stored procedures and tables out of
the server explorer in .net. Guess there is no way to script to file a table
and the data that is in it.
--
Paul G
Software engineer.
"Linchi Shea" wrote:
> There are many potential approaches, the choice of which depends on factors
> such as the size of the database, the size of the table in question, etc.
> If the table is smallish, you can preserve a copy in a different database
> before the restore. After the database restore, repopulate the table back
> from the saved copy. Same can be done to the stored procedure.
> Linchi
> "Paul" wrote:
> > I have 2 servers and am migrating data occasionally from one server to the
> > other. There are several tables but one of the tables I do not want to be
> > updated by the restore. Just wondering if there is an easy way to do this?
> > Also the destination server has an additional stored procedure so would like
> > this procedure to be retained with the dbase restore.
> > thanks.
> > --
> > Paul G
> > Software engineer.|||> Guess there is no way to script to file a table
> and the data that is in it.
You could always use DMO/SMO to script the table and bcp to export the data.
Linchi
"Paul" wrote:
> thanks for the response. I forgot to mention that the schema is a bit
> different between the two tables, destination table has 2 additional columns.
> I have used the create script file for stored procedures and tables out of
> the server explorer in .net. Guess there is no way to script to file a table
> and the data that is in it.
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> > There are many potential approaches, the choice of which depends on factors
> > such as the size of the database, the size of the table in question, etc.
> >
> > If the table is smallish, you can preserve a copy in a different database
> > before the restore. After the database restore, repopulate the table back
> > from the saved copy. Same can be done to the stored procedure.
> >
> > Linchi
> >
> > "Paul" wrote:
> >
> > > I have 2 servers and am migrating data occasionally from one server to the
> > > other. There are several tables but one of the tables I do not want to be
> > > updated by the restore. Just wondering if there is an easy way to do this?
> > > Also the destination server has an additional stored procedure so would like
> > > this procedure to be retained with the dbase restore.
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.|||use DTS...
Jay
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8A97BB5B-3F3E-419E-917F-19B16A050400@.microsoft.com...
>I have 2 servers and am migrating data occasionally from one server to the
> other. There are several tables but one of the tables I do not want to be
> updated by the restore. Just wondering if there is an easy way to do
> this?
> Also the destination server has an additional stored procedure so would
> like
> this procedure to be retained with the dbase restore.
> thanks.
> --
> Paul G
> Software engineer.

Restore DB**

Hi
Thanks for your reply,
now I tried to restore master DB to create related
logins, but following error appeared:
--
restore database must be used in single user mode
when tring to restore the master database .
restore database is terminated abnormally.
--
what's wrong?
any help would be greatly appreciated.
On Thu, 8 Jan 2004 07:36:12 -0500, Ray Higdon <sqlhigdon@.nospam.yahoo.com>
wrote:

quote:

> This link should help you
> http://support.microsoft.com/defaul...kb;en-us;246133
> HTH
>

Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/Thanks for your reply
I used "sp_change_users_login" and I've got
successfull result ,but there's a question
with me,
(note : my DB =dbtest, Login=L1 , User=U1)
now when I select login properties of my L1 which
added manually I see there's user U1 related to it,
nut when I refer to database dbtest and I expand the
user section of it in enterprise manager I just
see the U1 in "name" colunm and there's nothing in
"loginname" colunm,and permit in "database access" colunm.
why?
but when I connect as L1 and try to select or create or ...
table,there's no problem.
thanks,
On Thu, 8 Jan 2004 17:59:16 +0530, Hari <hari_prasad_k@.hotmail.com> wrote:

quote:

> Hi,
> After restoration you can use the system stored procedure to fix the
> login
> issue,
> sp_change_users_login
> Please refere BOL for more infomation. The only issue is you may need to
> create the logins manually based on the first server. After that you can
> run
> the above procedure with required parameters.
> Thanks
> Hari
> MCDBA
>
>
> "RM" <m_r1824@.yahoo.co.uk> wrote in message
> news:opr1gd7geghqligo@.msnews.microsoft.com...
>

Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/sql

Restore DB**

Hi
I've backed up my DB called DB1 in SQLServer 2000,
which include some users that owns some objects in
the database such as tables,views,...and these users
related to some logins.
now I want to restore DB1 in another Server,
but my problem is how can I define my logins and make a
relation between them and DB1'users?
(note: if I want to restore my DB1 in first server
there's no problem cause the defination of related logins exists)
or is there anyway to restore these information
without any problem?
any help would be greatly apprreciated.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/Hi,
After restoration you can use the system stored procedure to fix the login
issue,
sp_change_users_login
Please refere BOL for more infomation. The only issue is you may need to
create the logins manually based on the first server. After that you can run
the above procedure with required parameters.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr1gd7geghqligo@.msnews.microsoft.com...
> Hi
> I've backed up my DB called DB1 in SQLServer 2000,
> which include some users that owns some objects in
> the database such as tables,views,...and these users
> related to some logins.
> now I want to restore DB1 in another Server,
> but my problem is how can I define my logins and make a
> relation between them and DB1'users?
> (note: if I want to restore my DB1 in first server
> there's no problem cause the defination of related logins exists)
> or is there anyway to restore these information
> without any problem?
> any help would be greatly apprreciated.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||This link should help you
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr1gd7geghqligo@.msnews.microsoft.com...
> Hi
> I've backed up my DB called DB1 in SQLServer 2000,
> which include some users that owns some objects in
> the database such as tables,views,...and these users
> related to some logins.
> now I want to restore DB1 in another Server,
> but my problem is how can I define my logins and make a
> relation between them and DB1'users?
> (note: if I want to restore my DB1 in first server
> there's no problem cause the defination of related logins exists)
> or is there anyway to restore these information
> without any problem?
> any help would be greatly apprreciated.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Hi
Thanks for your reply,
now I tried to restore master DB to create related
logins, but following error appeared:
--
restore database must be used in single user mode
when tring to restore the master database .
restore database is terminated abnormally.
--
what's wrong?
any help would be greatly appreciated.
On Thu, 8 Jan 2004 07:36:12 -0500, Ray Higdon <sqlhigdon@.nospam.yahoo.com>
wrote:
> This link should help you
> http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
> HTH
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Thanks for your reply
I used "sp_change_users_login" and I've got
successfull result ,but there's a question
with me,
(note : my DB =dbtest, Login=L1 , User=U1)
now when I select login properties of my L1 which
added manually I see there's user U1 related to it,
nut when I refer to database dbtest and I expand the
user section of it in enterprise manager I just
see the U1 in "name" colunm and there's nothing in
"loginname" colunm,and permit in "database access" colunm.
why?
but when I connect as L1 and try to select or create or ...
table,there's no problem.
thanks,
On Thu, 8 Jan 2004 17:59:16 +0530, Hari <hari_prasad_k@.hotmail.com> wrote:
> Hi,
> After restoration you can use the system stored procedure to fix the
> login
> issue,
> sp_change_users_login
> Please refere BOL for more infomation. The only issue is you may need to
> create the logins manually based on the first server. After that you can
> run
> the above procedure with required parameters.
> Thanks
> Hari
> MCDBA
>
>
> "RM" <m_r1824@.yahoo.co.uk> wrote in message
> news:opr1gd7geghqligo@.msnews.microsoft.com...
>> Hi
>> I've backed up my DB called DB1 in SQLServer 2000,
>> which include some users that owns some objects in
>> the database such as tables,views,...and these users
>> related to some logins.
>> now I want to restore DB1 in another Server,
>> but my problem is how can I define my logins and make a
>> relation between them and DB1'users?
>> (note: if I want to restore my DB1 in first server
>> there's no problem cause the defination of related logins exists)
>> or is there anyway to restore these information
>> without any problem?
>> any help would be greatly apprreciated.
>> --
>> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

Restore DB without Transaction Log

Hi,

How can I restore a DB without TRN log. As database transaction logs grows too much and I only want to restore the database.

Thanks

Shafiq

If you only restore the transaction log then the database will be transactionally inconsistent - the transaction log is an integral part of the backup.

What's the scenario context here?

BTW, what size DB and transaction log are we talking about here?

Thanks

|||

1. You must change your recovery model to simple and the backups wont record the transaction logs, this is the easiest way, but You shouldnt restore a backup without transaction logs, its supposed for you to have the info upto date until the failover.

|||

You should be wary about just going to simple recovery model. This is not suitable for production systems where loss of recent changes (in the event of a problem) is not acceptable.

This is because after a data backup in the simple recovery model, the transaction log is truncated. This means that its only possible to restore back to the time the last backup was completed - its not possible to do a point-in-time restore up to just before the problem occured so all work between those two points is lost.

See the SQL Server 2005 BOL entry for 'Overview of Simple Recovery' for more details.

Thanks

restore db without the log?

Is it possible in sql2k to restore a db without restoring the transaction
log? If so, how?
TIA, ChrisR
Hi,
If you have the FULL Database backup you could restore the full database.
See RESTORE DATABASE command in books online.
Log restore will help you to recover the database till POINT IN TIME.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Is it possible in sql2k to restore a db without restoring the transaction
> log? If so, how?
> TIA, ChrisR
>
|||Hari, I have a FULL backup and want to restore the full db, minus the 20 gig
transaction log.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If you have the FULL Database backup you could restore the full database.
> See RESTORE DATABASE command in books online.
> Log restore will help you to recover the database till POINT IN TIME.
> Thanks
> Hari
> SQL Server MVP
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>
|||If you mean without creating the transaction log file then no. The restore
process from a FULL backup will always rebuild the db and log files exactly
as they were when you made the backup. You can detach the db and attach it
with sing_file_db and it will create a new log file. I am not sure how
large it makes the new file though. It's probably the size you originally
created it at or explicitly did an alter file on it.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23dKNBNMiFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hari, I have a FULL backup and want to restore the full db, minus the 20
> gig transaction log.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
>

restore db without the log?

Is it possible in sql2k to restore a db without restoring the transaction
log? If so, how?
TIA, ChrisRHi,
If you have the FULL Database backup you could restore the full database.
See RESTORE DATABASE command in books online.
Log restore will help you to recover the database till POINT IN TIME.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Is it possible in sql2k to restore a db without restoring the transaction
> log? If so, how?
> TIA, ChrisR
>|||Hari, I have a FULL backup and want to restore the full db, minus the 20 gig
transaction log.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If you have the FULL Database backup you could restore the full database.
> See RESTORE DATABASE command in books online.
> Log restore will help you to recover the database till POINT IN TIME.
> Thanks
> Hari
> SQL Server MVP
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Is it possible in sql2k to restore a db without restoring the transaction
>> log? If so, how?
>> TIA, ChrisR
>|||If you mean without creating the transaction log file then no. The restore
process from a FULL backup will always rebuild the db and log files exactly
as they were when you made the backup. You can detach the db and attach it
with sing_file_db and it will create a new log file. I am not sure how
large it makes the new file though. It's probably the size you originally
created it at or explicitly did an alter file on it.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23dKNBNMiFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hari, I have a FULL backup and want to restore the full db, minus the 20
> gig transaction log.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> If you have the FULL Database backup you could restore the full database.
>> See RESTORE DATABASE command in books online.
>> Log restore will help you to recover the database till POINT IN TIME.
>> Thanks
>> Hari
>> SQL Server MVP
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Is it possible in sql2k to restore a db without restoring the
>> transaction log? If so, how?
>> TIA, ChrisR
>>
>

restore db without the log?

Is it possible in sql2k to restore a db without restoring the transaction
log? If so, how?
TIA, ChrisRHi,
If you have the FULL Database backup you could restore the full database.
See RESTORE DATABASE command in books online.
Log restore will help you to recover the database till POINT IN TIME.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Is it possible in sql2k to restore a db without restoring the transaction
> log? If so, how?
> TIA, ChrisR
>|||Hari, I have a FULL backup and want to restore the full db, minus the 20 gig
transaction log.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If you have the FULL Database backup you could restore the full database.
> See RESTORE DATABASE command in books online.
> Log restore will help you to recover the database till POINT IN TIME.
> Thanks
> Hari
> SQL Server MVP
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>|||If you mean without creating the transaction log file then no. The restore
process from a FULL backup will always rebuild the db and log files exactly
as they were when you made the backup. You can detach the db and attach it
with sing_file_db and it will create a new log file. I am not sure how
large it makes the new file though. It's probably the size you originally
created it at or explicitly did an alter file on it.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23dKNBNMiFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hari, I have a FULL backup and want to restore the full db, minus the 20
> gig transaction log.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
>sql

restore DB with SQLSever DMO

Hi,
SQL 2000 had an example for using DMO programing with VB to restore and
backup a database, which named BackRestEvents.
I want to extend this project to build a tool, which can restore from a
backup file to a new database (new name). But I don't know, how I can set
the database filenames to another name (such as test1_data.mdf and
test1_log..ldf), because the database test (with test_data.mdf and
test_log..ldf) still exists on the system.
How can I do?
The backup file serves only as a template to create a new database. Is there
another way?
I use MSDE 2000, I don't have any tool such as Enterprise manager or SQL
Analyzer.
Thanks for help
Martin
hi Martin,
Martin wrote:
> Hi,
> SQL 2000 had an example for using DMO programing with VB to restore
> and backup a database, which named BackRestEvents.
> I want to extend this project to build a tool, which can restore from
> a backup file to a new database (new name). But I don't know, how I
> can set the database filenames to another name (such as
> test1_data.mdf and test1_log..ldf), because the database test (with
> test_data.mdf and test_log..ldf) still exists on the system.
> How can I do?
assuming your relative T-SQL syntax is
RESTORE DATABASE [Pubs2] FROM DISK = N'C:\Pubs.bak' WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
RECOVERY ,
MOVE N'pubs' TO N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\pubs2.mdf',
MOVE N'pubs_log' TO N'C:\Programmi\Microsoft SQL
Server\MSSQL\Data\pubs_log2.ldf'
where you restore to Pubs2 database and move file to other location, you
have to specify the
oRestore.RelocateFiles = "[pubs],[C:\Programmi\Microsoft SQL
Server\MSSQL\Data\pubs2.mdf],[pubs_log],[C:\Programmi\Microsoft SQL
Server\MSSQL\Data\pubs_log2.ldf]"
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

restore db with script?

quote:

> Hi all.
> I have a sql server backup file for SQL Server 2000.
> Is there a way to bring this file to another computer with SQL and run a
> script that tells the server to restore the backup file automatically
> without using the GUI?
>
> Thanks in advance.
>

restore db with script?

Hi all.
I have a sql server backup file for SQL Server 2000.
Is there a way to bring this file to another computer with SQL and run a
script that tells the server to restore the backup file automatically
without using the GUI?
Thanks in advance.Yes, on the target machine, schedule a T-sql job to run a restore command,
see restore syntax in BOL.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Daniel" <dhw377@.nothing.no.spam.com> wrote in message
news:pHYMb.1223$ZJ1.790@.lakeread01...
> Hi all.
> I have a sql server backup file for SQL Server 2000.
> Is there a way to bring this file to another computer with SQL and run a
> script that tells the server to restore the backup file automatically
> without using the GUI?
>
> Thanks in advance.
>

Restore DB to sql 2k

After restoring a db from SQL 7.0 running on a Win98
system (English version) to a SQL 2000 on an XP system
(Spanish version) I get the following message on a stored
procedure:
Server: Msg 446, Level 16, State 9, Procedure
sp_RptPartida, Line 33
Cannot resolve collation conflict for equal to operation.
I did not get such message in the old system. Is there a
way to avoid it. I need to be able to maintain the db on
my client (they have sql 7.0) and I wish to do the
development on sql 2000.There is a conflict in collations between your Charcter table attributes
that you are comparing
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7ory.asp
http://tinyurl.com/3dr2j
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mauricio" <macs01@.telesal.net> wrote in message
news:036f01c3cb24$72727230$a001280a@.phx.gbl...
> After restoring a db from SQL 7.0 running on a Win98
> system (English version) to a SQL 2000 on an XP system
> (Spanish version) I get the following message on a stored
> procedure:
> Server: Msg 446, Level 16, State 9, Procedure
> sp_RptPartida, Line 33
> Cannot resolve collation conflict for equal to operation.
> I did not get such message in the old system. Is there a
> way to avoid it. I need to be able to maintain the db on
> my client (they have sql 7.0) and I wish to do the
> development on sql 2000.

Restore DB to Point in Time

I have a group of folks that are Peoplesoft Developers, of which the
back-end is a 65 GB SQL Server database. They run test procedures and
processes against this database, but then it is a pretty common practice for
them to ask the DBAs to restore to an old backup so they can run more tests
after modifying their code.
Is there an easier way to do this? Restoring a 65 GB database is rather
daunting and time consuming. Wouldn't there be a way to not commit a
timeframe of activity (transaction log, for example), ultimately returning
the database back to a previous point in time?
I hope this makes sense, and that someone has come across this scenario in
the past.
Thanks for any advice,
AllenIf you are running SQL Server 2005 this is a perfect situation for
database snapshots. This effectively creates a read-only copy of the
database, and you can revert the database back to the state at the
time of the snapshot.
Roy Harvey
Beacon Falls, CT
On Wed, 19 Jul 2006 08:58:37 -0500, "A McGuire"
<allen.mcguire@.gmail.com.invalid> wrote:
>I have a group of folks that are Peoplesoft Developers, of which the
>back-end is a 65 GB SQL Server database. They run test procedures and
>processes against this database, but then it is a pretty common practice for
>them to ask the DBAs to restore to an old backup so they can run more tests
>after modifying their code.
>Is there an easier way to do this? Restoring a 65 GB database is rather
>daunting and time consuming. Wouldn't there be a way to not commit a
>timeframe of activity (transaction log, for example), ultimately returning
>the database back to a previous point in time?
>I hope this makes sense, and that someone has come across this scenario in
>the past.
>Thanks for any advice,
>Allen
>|||Unfortunately in this instance I'm using SQL 2000. Any ideas for SQL 2000?
What about getting copies of the databases at the point in time they
continually want to restore to, and then attach to them when I need to
revert back to the older version (after deleting the modified ones of
course)?
Still thinking about this, but there has to be a more efficient way to
revert back to a previous state without having to restore a 65 GB
database...
Would it be
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:9qgsb2td3nc7csfk6rogastm6vohds7iq4@.4ax.com...
> If you are running SQL Server 2005 this is a perfect situation for
> database snapshots. This effectively creates a read-only copy of the
> database, and you can revert the database back to the state at the
> time of the snapshot.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 19 Jul 2006 08:58:37 -0500, "A McGuire"
> <allen.mcguire@.gmail.com.invalid> wrote:
>>I have a group of folks that are Peoplesoft Developers, of which the
>>back-end is a 65 GB SQL Server database. They run test procedures and
>>processes against this database, but then it is a pretty common practice
>>for
>>them to ask the DBAs to restore to an old backup so they can run more
>>tests
>>after modifying their code.
>>Is there an easier way to do this? Restoring a 65 GB database is rather
>>daunting and time consuming. Wouldn't there be a way to not commit a
>>timeframe of activity (transaction log, for example), ultimately returning
>>the database back to a previous point in time?
>>I hope this makes sense, and that someone has come across this scenario in
>>the past.
>>Thanks for any advice,
>>Allen|||Yes you can use sp_detach copy a file to another location or rename it in
the same folder and then sp_attach one file for them to play with.
If they need to revert just sp_attach the renamed file.
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:eDh9NH0qGHA.5008@.TK2MSFTNGP05.phx.gbl...
> Unfortunately in this instance I'm using SQL 2000. Any ideas for SQL
> 2000?
> What about getting copies of the databases at the point in time they
> continually want to restore to, and then attach to them when I need to
> revert back to the older version (after deleting the modified ones of
> course)?
> Still thinking about this, but there has to be a more efficient way to
> revert back to a previous state without having to restore a 65 GB
> database...
> Would it be
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:9qgsb2td3nc7csfk6rogastm6vohds7iq4@.4ax.com...
>> If you are running SQL Server 2005 this is a perfect situation for
>> database snapshots. This effectively creates a read-only copy of the
>> database, and you can revert the database back to the state at the
>> time of the snapshot.
>> Roy Harvey
>> Beacon Falls, CT
>> On Wed, 19 Jul 2006 08:58:37 -0500, "A McGuire"
>> <allen.mcguire@.gmail.com.invalid> wrote:
>>I have a group of folks that are Peoplesoft Developers, of which the
>>back-end is a 65 GB SQL Server database. They run test procedures and
>>processes against this database, but then it is a pretty common practice
>>for
>>them to ask the DBAs to restore to an old backup so they can run more
>>tests
>>after modifying their code.
>>Is there an easier way to do this? Restoring a 65 GB database is rather
>>daunting and time consuming. Wouldn't there be a way to not commit a
>>timeframe of activity (transaction log, for example), ultimately
>>returning
>>the database back to a previous point in time?
>>I hope this makes sense, and that someone has come across this scenario
>>in
>>the past.
>>Thanks for any advice,
>>Allen
>|||Awesome - thanks for confirming my suspicions ;-)
That will work great considering this continual restore process takes about
4-6 hours.
"alex sadykov" <alexsadykov@.gmail.com> wrote in message
news:%23JJKkE1qGHA.4684@.TK2MSFTNGP05.phx.gbl...
> Yes you can use sp_detach copy a file to another location or rename it in
> the same folder and then sp_attach one file for them to play with.
> If they need to revert just sp_attach the renamed file.
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:eDh9NH0qGHA.5008@.TK2MSFTNGP05.phx.gbl...
>> Unfortunately in this instance I'm using SQL 2000. Any ideas for SQL
>> 2000?
>> What about getting copies of the databases at the point in time they
>> continually want to restore to, and then attach to them when I need to
>> revert back to the older version (after deleting the modified ones of
>> course)?
>> Still thinking about this, but there has to be a more efficient way to
>> revert back to a previous state without having to restore a 65 GB
>> database...
>> Would it be
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:9qgsb2td3nc7csfk6rogastm6vohds7iq4@.4ax.com...
>> If you are running SQL Server 2005 this is a perfect situation for
>> database snapshots. This effectively creates a read-only copy of the
>> database, and you can revert the database back to the state at the
>> time of the snapshot.
>> Roy Harvey
>> Beacon Falls, CT
>> On Wed, 19 Jul 2006 08:58:37 -0500, "A McGuire"
>> <allen.mcguire@.gmail.com.invalid> wrote:
>>I have a group of folks that are Peoplesoft Developers, of which the
>>back-end is a 65 GB SQL Server database. They run test procedures and
>>processes against this database, but then it is a pretty common practice
>>for
>>them to ask the DBAs to restore to an old backup so they can run more
>>tests
>>after modifying their code.
>>Is there an easier way to do this? Restoring a 65 GB database is rather
>>daunting and time consuming. Wouldn't there be a way to not commit a
>>timeframe of activity (transaction log, for example), ultimately
>>returning
>>the database back to a previous point in time?
>>I hope this makes sense, and that someone has come across this scenario
>>in
>>the past.
>>Thanks for any advice,
>>Allen
>>
>|||A McGuire wrote:
> Awesome - thanks for confirming my suspicions ;-)
> That will work great considering this continual restore process takes about
> 4-6 hours.
> "alex sadykov" <alexsadykov@.gmail.com> wrote in message
> news:%23JJKkE1qGHA.4684@.TK2MSFTNGP05.phx.gbl...
>
Just keep in mind that it also takes a while to copy a 65GB database
file and a log file in order to use sp_attach_db. I'm also wondering why
it takes 4-6 hours to restore a 65 GB database. Our biggest database
which is a 110 GB database file and a 65 GB log file can be restored in
less than 1½ hour. If it's because you have a slow server/disk system,
the copy/sp_attach_db solution will take quite a while as well.
Often I find that it's not that much faster to copy the files and attach
them compared to doing a RESTORE. The part that takes time is for the OS
to create the files on disk and that's more or less the same with both
solutions.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||On my way home last night that notion came to me - copying the 65 GB file
took a few hours. I'm not saving a whole lot of time... unfortunately.
I don't deal at all with the hardware - in fact I've never seen the
servers - so I'm not quite sure what they are.
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:efL%23In8qGHA.4508@.TK2MSFTNGP04.phx.gbl...
>A McGuire wrote:
>> Awesome - thanks for confirming my suspicions ;-)
>> That will work great considering this continual restore process takes
>> about 4-6 hours.
>> "alex sadykov" <alexsadykov@.gmail.com> wrote in message
>> news:%23JJKkE1qGHA.4684@.TK2MSFTNGP05.phx.gbl...
> Just keep in mind that it also takes a while to copy a 65GB database file
> and a log file in order to use sp_attach_db. I'm also wondering why it
> takes 4-6 hours to restore a 65 GB database. Our biggest database which is
> a 110 GB database file and a 65 GB log file can be restored in less than
> 1½ hour. If it's because you have a slow server/disk system, the
> copy/sp_attach_db solution will take quite a while as well.
> Often I find that it's not that much faster to copy the files and attach
> them compared to doing a RESTORE. The part that takes time is for the OS
> to create the files on disk and that's more or less the same with both
> solutions.
>
> --
> Regards
> Steen Schlüter Persson
> Databaseadministrator / Systemadministrator|||On Thu, 20 Jul 2006 11:41:41 -0500, "A McGuire"
<allen.mcguire@.gmail.com.invalid> wrote:
>On my way home last night that notion came to me - copying the 65 GB file
>took a few hours. I'm not saving a whole lot of time... unfortunately.
To use file copy you need a master "saved" copy on hand, plus the copy
currently attached and in use. If you have space for a third copy you
might be able to save time. Don't wait for them to ask for the
database to be restored. Copy the master version using different file
names, so that when they do ask you just have to detach the active
copy and attach to the one already in place. Then delete the files
you just detached and replace them with fresh copies from the master
set right away.
Which, as a read it, is probably confusing.
Roy|||I did get a copy of the restored .MDF (detatch, copy, attach), so I'm good
there. Before they only had the .BAK files from early May, for example, so
my only option this time around was a restore. Only problem is that the
.MDF is in a neighboring folder, not the same one, which means a multi-hour
copy. I suppose I could rename it as you suggest and put it in the same
folder. That will save me the copy time.
I assume you suggest the third copy so when I go to 'restore' next time
(drop current, attach to .MDF) that I have an additional free .MDF of the
snapshot handy that won't be in use. The current would get dropped, one of
the copies would be put to use (attached to), and I would just make another
copy of the 3rd - this would be the procedure each time.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:pngvb2psrs8hg44hh15vlsjl3hqfsn2ilr@.4ax.com...
> On Thu, 20 Jul 2006 11:41:41 -0500, "A McGuire"
> <allen.mcguire@.gmail.com.invalid> wrote:
>>On my way home last night that notion came to me - copying the 65 GB file
>>took a few hours. I'm not saving a whole lot of time... unfortunately.
> To use file copy you need a master "saved" copy on hand, plus the copy
> currently attached and in use. If you have space for a third copy you
> might be able to save time. Don't wait for them to ask for the
> database to be restored. Copy the master version using different file
> names, so that when they do ask you just have to detach the active
> copy and attach to the one already in place. Then delete the files
> you just detached and replace them with fresh copies from the master
> set right away.
> Which, as a read it, is probably confusing.
> Roy

Restore DB to new Database Name Cannot see new Database SQL2000

Hi
I wanted to create a test database based on formally live data in SQL2000.
I created the new Database and then forced a restore from a backup.
Unfortunately the client workstations cannot see the new database.
Any ideas?
Many thanks
Mike
When you say client workstations... do mean via the QUery Analyzer?
Perhaps the user information is messed up in the restored database and
the users/roles need to be dropped and re-added? I'm guessing you
restored via EM.
"Mike" <mikevl@.paradise.net.nz> wrote in message
news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi
> I wanted to create a test database based on formally live data in SQL2000.
> I created the new Database and then forced a restore from a backup.
> Unfortunately the client workstations cannot see the new database.
> Any ideas?
> Many thanks
> Mike
>
|||Thanks for your help
It appears that the financial application based on SQL would only find
databases with a certain prefix name. Real cleaver. Took a while to find.
Many thanks
Mike
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:OhWVVsdzEHA.1404@.TK2MSFTNGP11.phx.gbl...
> When you say client workstations... do mean via the QUery Analyzer?
> Perhaps the user information is messed up in the restored database and
> the users/roles need to be dropped and re-added? I'm guessing you
> restored via EM.
> "Mike" <mikevl@.paradise.net.nz> wrote in message
> news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
>

Restore DB to new Database Name Cannot see new Database SQL2000

Hi
I wanted to create a test database based on formally live data in SQL2000.
I created the new Database and then forced a restore from a backup.
Unfortunately the client workstations cannot see the new database.
Any ideas?
Many thanks
MikeWhen you say client workstations... do mean via the QUery Analyzer?
Perhaps the user information is messed up in the restored database and
the users/roles need to be dropped and re-added? I'm guessing you
restored via EM.
"Mike" <mikevl@.paradise.net.nz> wrote in message
news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi
> I wanted to create a test database based on formally live data in SQL2000.
> I created the new Database and then forced a restore from a backup.
> Unfortunately the client workstations cannot see the new database.
> Any ideas?
> Many thanks
> Mike
>|||Thanks for your help
It appears that the financial application based on SQL would only find
databases with a certain prefix name. Real cleaver. Took a while to find.
Many thanks
Mike
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:OhWVVsdzEHA.1404@.TK2MSFTNGP11.phx.gbl...
> When you say client workstations... do mean via the QUery Analyzer?
> Perhaps the user information is messed up in the restored database and
> the users/roles need to be dropped and re-added? I'm guessing you
> restored via EM.
> "Mike" <mikevl@.paradise.net.nz> wrote in message
> news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> I wanted to create a test database based on formally live data in
>> SQL2000.
>> I created the new Database and then forced a restore from a backup.
>> Unfortunately the client workstations cannot see the new database.
>> Any ideas?
>> Many thanks
>> Mike
>>
>

Restore DB to new Database Name Cannot see new Database SQL2000

Hi
I wanted to create a test database based on formally live data in SQL2000.
I created the new Database and then forced a restore from a backup.
Unfortunately the client workstations cannot see the new database.
Any ideas?
Many thanks
MikeWhen you say client workstations... do mean via the QUery Analyzer?
Perhaps the user information is messed up in the restored database and
the users/roles need to be dropped and re-added? I'm guessing you
restored via EM.
"Mike" <mikevl@.paradise.net.nz> wrote in message
news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi
> I wanted to create a test database based on formally live data in SQL2000.
> I created the new Database and then forced a restore from a backup.
> Unfortunately the client workstations cannot see the new database.
> Any ideas?
> Many thanks
> Mike
>|||Thanks for your help
It appears that the financial application based on SQL would only find
databases with a certain prefix name. Real cleaver. Took a while to find.
Many thanks
Mike
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:OhWVVsdzEHA.1404@.TK2MSFTNGP11.phx.gbl...
> When you say client workstations... do mean via the QUery Analyzer?
> Perhaps the user information is messed up in the restored database and
> the users/roles need to be dropped and re-added? I'm guessing you
> restored via EM.
> "Mike" <mikevl@.paradise.net.nz> wrote in message
> news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
>

Restore DB to diferent data file struct

Hi,
I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
with 90GB each.
The thing is that the server is no more… died…. Kaput…
I have backups of the database and I have a spare server with 6 drive
letters with 80GB of free space eatch (aprox).
Considering that I can’t touch the operating system to change the drive
letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
the sql database to more data files than the original database to spread the
database over the avaible drive letters.
The problem is that when I try to restore, the sql restore utility does’t
allow to add new devices different from the original db file struct.
Can anyone help me on this one?
oh yes ...
i'm using sql 2000 sp3a
same version as on the previous version and same collation.
|||Since it sounds like a bit of an emergency, how about if you use a disk
utility and create compressed drives in the available space so the 90 GB
files will fit. Then restore the original file structure in the compressed
drives, partition them into smaller files, move them around, uncompress the
drives and go from there.
Sounds way out, but it might work. Somebody else here might have a far
better way of doing it.
Bob Castleman
DBA Poseur
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:EE908619-C160-4E11-AF90-6264D85416EE@.microsoft.com...
> oh yes ...
> i'm using sql 2000 sp3a
> same version as on the previous version and same collation.
>
|||SQL Server need the same amount of database files, each having the same logical file and at least
the same size, as the database had then you performed the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
> Hi,
> I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
> with 90GB each.
> The thing is that the server is no more. died.. Kaput.
> I have backups of the database and I have a spare server with 6 drive
> letters with 80GB of free space eatch (aprox).
> Considering that I can't touch the operating system to change the drive
> letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
> the sql database to more data files than the original database to spread the
> database over the avaible drive letters.
> The problem is that when I try to restore, the sql restore utility does't
> allow to add new devices different from the original db file struct.
> Can anyone help me on this one?
>
|||tks Tibor, that's true, and i new that, i was just hopping that someone
already managed to go around that.
But still, i realy don't see wy the hell one can't restore a backup to a
diferent data file struct, since what we nead is the data...
"Tibor Karaszi" wrote:

> SQL Server need the same amount of database files, each having the same logical file and at least
> the same size, as the database had then you performed the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
> news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
>
>
|||> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
Backup just copies all the used pages to the backup media. As they are, including pointers to other
pages, allocation pages etc.. This is why restore need to put everything in the right place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:1C8895ED-0205-42FE-99B5-C2A4AB4E5137@.microsoft.com...[vbcol=seagreen]
> tks Tibor, that's true, and i new that, i was just hopping that someone
> already managed to go around that.
> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
>
> "Tibor Karaszi" wrote:
sql

Restore DB to diferent data file struct

Hi,
I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
with 90GB each.
The thing is that the server is no more… died…. Kaput…
I have backups of the database and I have a spare server with 6 drive
letters with 80GB of free space eatch (aprox).
Considering that I can’t touch the operating system to change the drive
letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
the sql database to more data files than the original database to spread the
database over the avaible drive letters.
The problem is that when I try to restore, the sql restore utility does’t
allow to add new devices different from the original db file struct.
Can anyone help me on this one?oh yes ...
i'm using sql 2000 sp3a
same version as on the previous version and same collation.|||Since it sounds like a bit of an emergency, how about if you use a disk
utility and create compressed drives in the available space so the 90 GB
files will fit. Then restore the original file structure in the compressed
drives, partition them into smaller files, move them around, uncompress the
drives and go from there.
Sounds way out, but it might work. Somebody else here might have a far
better way of doing it.
Bob Castleman
DBA Poseur
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:EE908619-C160-4E11-AF90-6264D85416EE@.microsoft.com...
> oh yes ...
> i'm using sql 2000 sp3a
> same version as on the previous version and same collation.
>|||SQL Server need the same amount of database files, each having the same logi
cal file and at least
the same size, as the database had then you performed the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
> Hi,
> I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
> with 90GB each.
> The thing is that the server is no more. died.. Kaput.
> I have backups of the database and I have a spare server with 6 drive
> letters with 80GB of free space eatch (aprox).
> Considering that I can't touch the operating system to change the drive
> letters (using stripe sets, or a raid5 vol), I nead to restore the backup
of
> the sql database to more data files than the original database to spread t
he
> database over the avaible drive letters.
> The problem is that when I try to restore, the sql restore utility does't
> allow to add new devices different from the original db file struct.
> Can anyone help me on this one?
>|||tks Tibor, that's true, and i new that, i was just hopping that someone
already managed to go around that.
But still, i realy don't see wy the hell one can't restore a backup to a
diferent data file struct, since what we nead is the data...
"Tibor Karaszi" wrote:

> SQL Server need the same amount of database files, each having the same lo
gical file and at least
> the same size, as the database had then you performed the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
> news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
>
>|||> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
Backup just copies all the used pages to the backup media. As they are, incl
uding pointers to other
pages, allocation pages etc.. This is why restore need to put everything in
the right place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:1C8895ED-0205-42FE-99B5-C2A4AB4E5137@.microsoft.com...[vbcol=seagreen]
> tks Tibor, that's true, and i new that, i was just hopping that someone
> already managed to go around that.
> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
>
> "Tibor Karaszi" wrote:
>

Restore DB to diferent data file struct

Hi,
I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
with 90GB each.
The thing is that the server is no moreâ?¦ diedâ?¦. Kaputâ?¦
I have backups of the database and I have a spare server with 6 drive
letters with 80GB of free space eatch (aprox).
Considering that I canâ't touch the operating system to change the drive
letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
the sql database to more data files than the original database to spread the
database over the avaible drive letters.
The problem is that when I try to restore, the sql restore utility doesâ't
allow to add new devices different from the original db file struct.
Can anyone help me on this one?oh yes ...
i'm using sql 2000 sp3a
same version as on the previous version and same collation.|||Since it sounds like a bit of an emergency, how about if you use a disk
utility and create compressed drives in the available space so the 90 GB
files will fit. Then restore the original file structure in the compressed
drives, partition them into smaller files, move them around, uncompress the
drives and go from there.
Sounds way out, but it might work. Somebody else here might have a far
better way of doing it.
Bob Castleman
DBA Poseur
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:EE908619-C160-4E11-AF90-6264D85416EE@.microsoft.com...
> oh yes ...
> i'm using sql 2000 sp3a
> same version as on the previous version and same collation.
>|||SQL Server need the same amount of database files, each having the same logical file and at least
the same size, as the database had then you performed the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
> Hi,
> I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
> with 90GB each.
> The thing is that the server is no more. died.. Kaput.
> I have backups of the database and I have a spare server with 6 drive
> letters with 80GB of free space eatch (aprox).
> Considering that I can't touch the operating system to change the drive
> letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
> the sql database to more data files than the original database to spread the
> database over the avaible drive letters.
> The problem is that when I try to restore, the sql restore utility does't
> allow to add new devices different from the original db file struct.
> Can anyone help me on this one?
>|||tks Tibor, that's true, and i new that, i was just hopping that someone
already managed to go around that.
But still, i realy don't see wy the hell one can't restore a backup to a
diferent data file struct, since what we nead is the data...
"Tibor Karaszi" wrote:
> SQL Server need the same amount of database files, each having the same logical file and at least
> the same size, as the database had then you performed the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
> news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
> > Hi,
> > I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
> > with 90GB each.
> > The thing is that the server is no more. died.. Kaput.
> > I have backups of the database and I have a spare server with 6 drive
> > letters with 80GB of free space eatch (aprox).
> > Considering that I can't touch the operating system to change the drive
> > letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
> > the sql database to more data files than the original database to spread the
> > database over the avaible drive letters.
> > The problem is that when I try to restore, the sql restore utility does't
> > allow to add new devices different from the original db file struct.
> > Can anyone help me on this one?
> >
>
>|||> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
Backup just copies all the used pages to the backup media. As they are, including pointers to other
pages, allocation pages etc.. This is why restore need to put everything in the right place.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:1C8895ED-0205-42FE-99B5-C2A4AB4E5137@.microsoft.com...
> tks Tibor, that's true, and i new that, i was just hopping that someone
> already managed to go around that.
> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
>
> "Tibor Karaszi" wrote:
>> SQL Server need the same amount of database files, each having the same logical file and at least
>> the same size, as the database had then you performed the backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
>> news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
>> > Hi,
>> > I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
>> > with 90GB each.
>> > The thing is that the server is no more. died.. Kaput.
>> > I have backups of the database and I have a spare server with 6 drive
>> > letters with 80GB of free space eatch (aprox).
>> > Considering that I can't touch the operating system to change the drive
>> > letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
>> > the sql database to more data files than the original database to spread the
>> > database over the avaible drive letters.
>> > The problem is that when I try to restore, the sql restore utility does't
>> > allow to add new devices different from the original db file struct.
>> > Can anyone help me on this one?
>> >
>>

Restore db to another server

I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
(SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
it was cancelled after 10% write; error 34113 on BackupExec server, and
repeated error 57859 on destination SQL server.
When rerun job, no data is written and job cancels with same error in
BackupExec server, and new error in destination SQL server: 57859 --
"Exclusive access couldn not be obtained because the database is in use." (It
seems to me it's not in use, nobody uses this server yet.)
Strange thing is, I can restore another (test) database from same tape
without any problems.
Happy to get any hints or guidelines, I'm kinda stuck.
I deleted the restored database on new SQL server an reran the restore job,
and everything worked OK -- and so I will never know what went wrong the
first time, but will nevertheless be able to spend the weekend with family
instead of SQL.
Thanks for taking time, if you took any.
"JSL" wrote:

> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
> I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
> it was cancelled after 10% write; error 34113 on BackupExec server, and
> repeated error 57859 on destination SQL server.
> When rerun job, no data is written and job cancels with same error in
> BackupExec server, and new error in destination SQL server: 57859 --
> "Exclusive access couldn not be obtained because the database is in use." (It
> seems to me it's not in use, nobody uses this server yet.)
> Strange thing is, I can restore another (test) database from same tape
> without any problems.
> Happy to get any hints or guidelines, I'm kinda stuck.
|||Next time, use sp_who to see who is using the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:90762CE8-604D-4BD5-A1DE-9645952022C9@.microsoft.com...[vbcol=seagreen]
>I deleted the restored database on new SQL server an reran the restore job,
> and everything worked OK -- and so I will never know what went wrong the
> first time, but will nevertheless be able to spend the weekend with family
> instead of SQL.
> Thanks for taking time, if you took any.
> "JSL" wrote:

Restore db to another server

I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
(SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
it was cancelled after 10% write; error 34113 on BackupExec server, and
repeated error 57859 on destination SQL server.
When rerun job, no data is written and job cancels with same error in
BackupExec server, and new error in destination SQL server: 57859 --
"Exclusive access couldn not be obtained because the database is in use." (It
seems to me it's not in use, nobody uses this server yet.)
Strange thing is, I can restore another (test) database from same tape
without any problems.
Happy to get any hints or guidelines, I'm kinda stuck.I deleted the restored database on new SQL server an reran the restore job,
and everything worked OK -- and so I will never know what went wrong the
first time, but will nevertheless be able to spend the weekend with family
instead of SQL.
Thanks for taking time, if you took any.
"JSL" wrote:
> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
> I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
> it was cancelled after 10% write; error 34113 on BackupExec server, and
> repeated error 57859 on destination SQL server.
> When rerun job, no data is written and job cancels with same error in
> BackupExec server, and new error in destination SQL server: 57859 --
> "Exclusive access couldn not be obtained because the database is in use." (It
> seems to me it's not in use, nobody uses this server yet.)
> Strange thing is, I can restore another (test) database from same tape
> without any problems.
> Happy to get any hints or guidelines, I'm kinda stuck.|||Next time, use sp_who to see who is using the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:90762CE8-604D-4BD5-A1DE-9645952022C9@.microsoft.com...
>I deleted the restored database on new SQL server an reran the restore job,
> and everything worked OK -- and so I will never know what went wrong the
> first time, but will nevertheless be able to spend the weekend with family
> instead of SQL.
> Thanks for taking time, if you took any.
> "JSL" wrote:
>> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
>> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
>> I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
>> it was cancelled after 10% write; error 34113 on BackupExec server, and
>> repeated error 57859 on destination SQL server.
>> When rerun job, no data is written and job cancels with same error in
>> BackupExec server, and new error in destination SQL server: 57859 --
>> "Exclusive access couldn not be obtained because the database is in use." (It
>> seems to me it's not in use, nobody uses this server yet.)
>> Strange thing is, I can restore another (test) database from same tape
>> without any problems.
>> Happy to get any hints or guidelines, I'm kinda stuck.

Restore db to another server

I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
(SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
it was cancelled after 10% write; error 34113 on BackupExec server, and
repeated error 57859 on destination SQL server.
When rerun job, no data is written and job cancels with same error in
BackupExec server, and new error in destination SQL server: 57859 --
"Exclusive access couldn not be obtained because the database is in use." (I
t
seems to me it's not in use, nobody uses this server yet.)
Strange thing is, I can restore another (test) database from same tape
without any problems.
Happy to get any hints or guidelines, I'm kinda stuck.I deleted the restored database on new SQL server an reran the restore job,
and everything worked OK -- and so I will never know what went wrong the
first time, but will nevertheless be able to spend the weekend with family
instead of SQL.
Thanks for taking time, if you took any.
"JSL" wrote:

> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
> I'm restoring db from tape w. BackupExec 9.0. The first time I did a resto
re
> it was cancelled after 10% write; error 34113 on BackupExec server, and
> repeated error 57859 on destination SQL server.
> When rerun job, no data is written and job cancels with same error in
> BackupExec server, and new error in destination SQL server: 57859 --
> "Exclusive access couldn not be obtained because the database is in use."
(It
> seems to me it's not in use, nobody uses this server yet.)
> Strange thing is, I can restore another (test) database from same tape
> without any problems.
> Happy to get any hints or guidelines, I'm kinda stuck.|||Next time, use sp_who to see who is using the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:90762CE8-604D-4BD5-A1DE-9645952022C9@.microsoft.com...[vbcol=seagreen]
>I deleted the restored database on new SQL server an reran the restore job,
> and everything worked OK -- and so I will never know what went wrong the
> first time, but will nevertheless be able to spend the weekend with family
> instead of SQL.
> Thanks for taking time, if you took any.
> "JSL" wrote:
>sql

Restore DB table - SQL Server 2000

Hi,
I made a release on system at 12pm 22/02 and overwrote a table by
mistake.
I have a back from from the night before (12 at night) but from the
time I did a backup and and the time I did the release some records
would be lost.
I want to recover the data lost.
Can I examine the transaction logs to get back the data?
Is there a tool in SQL server to do this or is there a free one?
TIA,
Darren
This was replied to in another group. Please don't multipost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<darren.daniel@.iclployalty.com> wrote in message
news:1141131966.097622.3760@.p10g2000cwp.googlegrou ps.com...
> Hi,
> I made a release on system at 12pm 22/02 and overwrote a table by
> mistake.
>
> I have a back from from the night before (12 at night) but from the
> time I did a backup and and the time I did the release some records
> would be lost.
> I want to recover the data lost.
> Can I examine the transaction logs to get back the data?
> Is there a tool in SQL server to do this or is there a free one?
> TIA,
> Darren
>

restore db stuck loading

I am trying to copy a 20 GB database from server 1 to 2. I took a full
backup, copied to server2 and restored ( ready for use) using EM.

After the message restore completed, I see the database's status as LOADING.
We are on Sql Server 2000 (8.00.818) on Windows 2000 Adv Servers.

I ran dbcc on the source DB and it returned no errors. I tried the command
'restore db with recovery ' which didnot help. I get an message saying that
the data file is only partially restored.. It seems the last resort is to
change the status in system tables.

Any ideas?

Thanks
Ragu(ragudba@.sbcglobal.net) writes:
> I am trying to copy a 20 GB database from server 1 to 2. I took a full
> backup, copied to server2 and restored ( ready for use) using EM.
> After the message restore completed, I see the database's status as
> LOADING. We are on Sql Server 2000 (8.00.818) on Windows 2000 Adv
> Servers.
> I ran dbcc on the source DB and it returned no errors. I tried the
> command 'restore db with recovery ' which didnot help. I get an message
> saying that the data file is only partially restored.. It seems the last
> resort is to change the status in system tables.

What does sp_helpdb say about the database?

If you run the RESTORE command from Query Analyzer, do you get any
interesting messages?

I think that what may have happened is that the database was restored
in such a way, that SQL Server now anticiapates one or more transaction
logs to be applied.

An example command to restore a database from QA:

RESTORE DATABASE db FROM DISK = 'C:\temp\whatever.dmp' WITH
MOVE 'datafile' TO 'C:\datadir\db.mdf',
MOVE 'logilfe' TO 'D:\logdir\db.ldf',
REPLACE, STATS

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Thanks for the reply. sp_helpdb returns 0 rows. The same sql restore
using QA returns this error

"ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message 258)
General network error. Check your network documentation. [SQLSTATE
08S01] (Error 11) 10 percent restored. [SQLSTATE 01000] (Error 3211).
The step failed. "

But it restored fine once but when I tried again I got the above error.
The reasons could be 1. some timing limit ( Since I restore on the
server this should not be an issue.) 2. I see antivirus software trying
to scan the newly created data file.

Thanks for your time

Ragu|||Ragu (ragudba@.gmail.com) writes:
> Hi Thanks for the reply. sp_helpdb returns 0 rows.

Then the database is not there.

> The same sql restore using QA returns this error
> "ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message 258)
> General network error. Check your network documentation. [SQLSTATE
> 08S01] (Error 11) 10 percent restored. [SQLSTATE 01000] (Error 3211).
> The step failed. "

QA does not say "The step failed", does it? But, OK, I assume that it does
say "General network error" and all that jazz. That indicates that a tragic
accident occurred on the SQL Server side, and SQL Server had to terminate
the process. If you look in SQL Server's error log you are likely to find
a stack dump related to the accident. No, don't expect this stack dump
to make you that much wiser. But look there anyway.

> But it restored fine once but when I tried again I got the above error.
> The reasons could be 1. some timing limit ( Since I restore on the
> server this should not be an issue.) 2. I see antivirus software trying
> to scan the newly created data file.

I would certainly recommend preventing the antivirus stuff from sneaking
in.

What is funny is that you say that the backup loaded OK once. Had it never
loaded well I would suspect an accident in transport over the network. But,
hm, if I re-read your original message, not even then did the load complete
entirely successfully?

I would turn off the antivirus thing and try again. If it fails again,
I would try copying again. If I could find away to avoid the network,
I would try that. 20 GB is too much for a DVD, but a removable disk
that you connect over USB2 could be an alternative.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||HI Erland :

1. I will try to turn off the virus and try again.
2. I ran the query in QA on the server console using VPN and Remote
Connection and one thing or other always gets disconnected. So I put
the restore sql in a job and executed the job instead. Hence the words
"step failed"
3. But the error is the same in QA also.
4. Sometimes even thoug we get the same error in QA, DB after 3 hrs and
several "Bypassing recovery for database 'DB' because it is marked IN
LOAD." and one " Recovery is checkpointing database " messages, the DB
gets restored fine. The only message in the error log is Database "DB'
restore completed.
5. And in other environements with fatser servers ,we donot get this
error at all with the same backup file.

Thanks

Ragu|||Ragu (ragudba@.gmail.com) writes:
> 1. I will try to turn off the virus and try again.

If you have a virus on the machine, you should definitely turn if off! :-)

> 2. I ran the query in QA on the server console using VPN and Remote
> Connection and one thing or other always gets disconnected. So I put
> the restore sql in a job and executed the job instead. Hence the words
> "step failed"

I see.

If you run QA on the same machine as the server, and you get disconnected
that is likely to be a crash and not a network issue.

> 4. Sometimes even thoug we get the same error in QA, DB after 3 hrs and
> several "Bypassing recovery for database 'DB' because it is marked IN
> LOAD." and one " Recovery is checkpointing database " messages, the DB
> gets restored fine. The only message in the error log is Database "DB'
> restore completed.

Three hours to restore a 20 GB database? That appears to be a tad long
to me.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||1. I meant virus scan not virus per se.
2. Normally it takes 15 -20 minues on production servers.
3. Reg getting disconnedt while on the server, there was no crash. I
think that the error message is misleading.

Thanks for your responses.

ragu|||Another update. In the follwoing scenerio the restore was succesful.

1. created the db say DB1 on the server.

2, Using QA, run the sql

RESTORE DATABASE DB1 FROM DISK = 'c:\db_backup\DB.bak' with
move 'DB_Data' to 'c:\SQL_DATA\DB_RC4_Data.MDF',
move 'DB_Log' to 'c:\SQL_DATA\DB_RC4_Log.LDF',
REPLACE, STATS

3. After 15 min , got the error [Microsoft][ODBC SQL Server
Driver][Named Pipes]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent restored.
Connection Broken

4. The db looks fine. The tables,row count match. The db is healthy.

5. The error logs show s the same messages like starting up
datasbase,Bypassing recovery for database Db1because it is marked IN
LOAD ( 2 times ), Recovery is checkpointing database ,Database
restored: over a period of 37 minutes. ( We got error in QA after 15
minutes )

Ragu
Though the QA gave the error, the db restored fine.|||Ragu (ragudba@.gmail.com) writes:
> 1. I meant virus scan not virus per se.

I understand that. But it looked funny! :-)

I will have to look into your other post tonight. It looks very strange
to me.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ragu (ragudba@.gmail.com) writes:
> 3. After 15 min , got the error [Microsoft][ODBC SQL Server
> Driver][Named Pipes]ConnectionRead (WrapperRead()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> 10 percent restored.
> Connection Broken

One thing is funny here. Did you really run the BACKUP command on a QA
which is on the same box as SQL Server? In such case I would not
expect Named Pipes in the error message.

The conclusion I am prepared to make, is that the network is flaky,
but SQL Server does not notice that client is gone and jogs along
with the restore. The recovery messages you get are funny, though.

If you really run QA on the server box, open Client Network Utility,
and check that you have share memory enabled (this is a check box
in the lower left corner).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||HI I think you nailed the issue. Let us say if the database
Instance2\Database2 is running on DB server D02, and the network client
utility, there was entry for the Instance2\DB2 using namedpipes instead
of TCP. The share memory is enabled.

So The QA even though running on the same server connects as a client
using named pipes and that may be the issue.

I have removed the entry now and let me test it

Ragu|||Ragu (ragudba@.gmail.com) writes:
> HI I think you nailed the issue. Let us say if the database
> Instance2\Database2 is running on DB server D02, and the network client
> utility, there was entry for the Instance2\DB2 using namedpipes instead
> of TCP. The share memory is enabled.

So why does it not use shared memory? I have seen issues where shared
memory goes sour, but I seem to recall that in these cases you cannot
connect, or connection takes long time. If possible, may you should
retry rebooting the machine. (Not just only SQL Server. That's actually
how you can get shared memory to misbehave. Connect with QA on the local
server, restart the server without disconnecting the QA window.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||No errors after this. tested few times and db got restored and no
errors were reported on QA. Thx for the help

ragu|||Ragu (ragudba@.gmail.com) writes:
> No errors after this. tested few times and db got restored and no
> errors were reported on QA. Thx for the help

Great to hear that it worked out!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Friday, March 30, 2012

Restore deleted table from log?

Restore deleted table

restore deleted records

Restore Deleted Instance of SQL 2000

Restore Default Trigger Template

Restore default setting for MSDE

Restore default security settings on MASTER database

Restore dbase but leave 1 table the same

Restore dbase but leave 1 table the same

Restore DB**

Restore DB**

Restore DB without Transaction Log

restore db without the log?

restore db without the log?

restore db without the log?

restore DB with SQLSever DMO

restore db with script?

restore db with script?

Restore DB to sql 2k

Restore DB to Point in Time

Restore DB to Point in Time

Restore DB to new Database Name Cannot see new Database SQL2000

Restore DB to new Database Name Cannot see new Database SQL2000

Restore DB to new Database Name Cannot see new Database SQL2000

Restore DB to diferent data file struct

Restore DB to diferent data file struct

Restore DB to diferent data file struct

Restore db to another server

Restore db to another server

Restore db to another server

Restore DB table - SQL Server 2000

restore db stuck loading

Restore mdf file

Blog Archive

About Me