restore db stuck loading

Hi

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

Restore database project problem

I have a development db, test db and live db.
I want to copy my development db to my test db.
I've backed both up and then restored the test db using the development db
backup
changing the file and log paths.
However when I go into the test db the data in the tables is not correct.
For example in the development db I have 10 rows in one table but in the
corresponding table in test db I only have the 2 original Test db rows.
Can you explain why?
Hi
Did youn follow theses steps
1) BACKUP DATABASE TO..... (Develop database)
2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
Darabase (Step1))
Probably during the RESTORE from the Develop database ,users do Insert to
the Develop DataBase, do not they?
As result you see different data.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?
|||Perhaps your backup file contains more than one database backup and the
first (oldest) is restored by default. You can list the contents with
RESTORE HEADERONLY and specify the desired one with the FILE parameter on
the RESTORE statement.
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Also, you can specify the INIT option on your BACKUP command to overwrite
instead of appending.
Hope this helps.
Dan Guzman
SQL Server MVP
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?
|||Hi,
No -one was working on either db while I did the backup and restore.
Linda
"Uri Dimant" wrote:

> Hi
> Did youn follow theses steps
> 1) BACKUP DATABASE TO..... (Develop database)
> 2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
> Darabase (Step1))
> Probably during the RESTORE from the Develop database ,users do Insert to
> the Develop DataBase, do not they?
> As result you see different data.
>
>
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>
|||I have retried the backup and restore - this time making sure I had selected
the overwrite option and the restore has worked properly.
Many thanks to both of you for your help.
"Dan Guzman" wrote:

> Perhaps your backup file contains more than one database backup and the
> first (oldest) is restored by default. You can list the contents with
> RESTORE HEADERONLY and specify the desired one with the FILE parameter on
> the RESTORE statement.
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatabase.bak'
> Also, you can specify the INIT option on your BACKUP command to overwrite
> instead of appending.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>

Restore database project problem

I have a development db, test db and live db.
I want to copy my development db to my test db.
I've backed both up and then restored the test db using the development db
backup
changing the file and log paths.
However when I go into the test db the data in the tables is not correct.
For example in the development db I have 10 rows in one table but in the
corresponding table in test db I only have the 2 original Test db rows.
Can you explain why?Hi
Did youn follow theses steps
1) BACKUP DATABASE TO..... (Develop database)
2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
Darabase (Step1))
Probably during the RESTORE from the Develop database ,users do Insert to
the Develop DataBase, do not they?
As result you see different data.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Perhaps your backup file contains more than one database backup and the
first (oldest) is restored by default. You can list the contents with
RESTORE HEADERONLY and specify the desired one with the FILE parameter on
the RESTORE statement.
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Also, you can specify the INIT option on your BACKUP command to overwrite
instead of appending.
Hope this helps.
Dan Guzman
SQL Server MVP
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Hi,
No -one was working on either db while I did the backup and restore.
Linda
"Uri Dimant" wrote:

> Hi
> Did youn follow theses steps
> 1) BACKUP DATABASE TO..... (Develop database)
> 2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
> Darabase (Step1))
> Probably during the RESTORE from the Develop database ,users do Insert to
> the Develop DataBase, do not they?
> As result you see different data.
>
>
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>|||I have retried the backup and restore - this time making sure I had selected
the overwrite option and the restore has worked properly.
Many thanks to both of you for your help.
"Dan Guzman" wrote:

> Perhaps your backup file contains more than one database backup and the
> first (oldest) is restored by default. You can list the contents with
> RESTORE HEADERONLY and specify the desired one with the FILE parameter on
> the RESTORE statement.
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatabase.bak'
> Also, you can specify the INIT option on your BACKUP command to overwrite
> instead of appending.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>

Restore database project problem

I have a development db, test db and live db.
I want to copy my development db to my test db.
I've backed both up and then restored the test db using the development db
backup
changing the file and log paths.
However when I go into the test db the data in the tables is not correct.
For example in the development db I have 10 rows in one table but in the
corresponding table in test db I only have the 2 original Test db rows.
Can you explain why?Hi
Did youn follow theses steps
1) BACKUP DATABASE TO..... (Develop database)
2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
Darabase (Step1))
Probably during the RESTORE from the Develop database ,users do Insert to
the Develop DataBase, do not they?
As result you see different data.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Perhaps your backup file contains more than one database backup and the
first (oldest) is restored by default. You can list the contents with
RESTORE HEADERONLY and specify the desired one with the FILE parameter on
the RESTORE statement.
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Also, you can specify the INIT option on your BACKUP command to overwrite
instead of appending.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Hi,
No -one was working on either db while I did the backup and restore.
Linda
"Uri Dimant" wrote:
> Hi
> Did youn follow theses steps
> 1) BACKUP DATABASE TO..... (Develop database)
> 2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
> Darabase (Step1))
> Probably during the RESTORE from the Develop database ,users do Insert to
> the Develop DataBase, do not they?
> As result you see different data.
>
>
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
> >I have a development db, test db and live db.
> > I want to copy my development db to my test db.
> > I've backed both up and then restored the test db using the development db
> > backup
> > changing the file and log paths.
> > However when I go into the test db the data in the tables is not correct.
> > For example in the development db I have 10 rows in one table but in the
> > corresponding table in test db I only have the 2 original Test db rows.
> > Can you explain why?
>
>|||I have retried the backup and restore - this time making sure I had selected
the overwrite option and the restore has worked properly.
Many thanks to both of you for your help.
"Dan Guzman" wrote:
> Perhaps your backup file contains more than one database backup and the
> first (oldest) is restored by default. You can list the contents with
> RESTORE HEADERONLY and specify the desired one with the FILE parameter on
> the RESTORE statement.
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatabase.bak'
> Also, you can specify the INIT option on your BACKUP command to overwrite
> instead of appending.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
> >I have a development db, test db and live db.
> > I want to copy my development db to my test db.
> > I've backed both up and then restored the test db using the development db
> > backup
> > changing the file and log paths.
> > However when I go into the test db the data in the tables is not correct.
> > For example in the development db I have 10 rows in one table but in the
> > corresponding table in test db I only have the 2 original Test db rows.
> > Can you explain why?
>
>

Restore database over network

Hello, everyone:
Every time to restore database, I copy the database backup file to local hard drive. Is is possible to restore database over network? Is other tools need to install? Thanks
YTZIt should work, but I wouldn't recommend it. if the network goes down in the middle of the restore, you would probably have to start over.

it's more reliable to copy the backup locally if you have enough space.

Restore database file with difference name

I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
database to a file and I'll like to copy that file to another SQL 2000
Server and restore it with a difference name. what is the procedures and
how to go about doing it. I did tried to restore but getting the error:
Exclusive access could not be obtained because the database is in use.
Thanks in advance.
Hi,
You have to use RESTORE DATABASE with MOVE option.
Eg:-
Restore database <new_dbname> from disk='c;\backup\dbname.bak'
with move 'logical_data_filename' to 'physical_data_filename',
move 'logical_log_filename' to 'physical_log_filename', stats=10
Thanks
Hari
SQL Server MVP
"toby one" <support@.re.com> wrote in message
news:%23ee8Dq6ZFHA.3488@.tk2msftngp13.phx.gbl...
>I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
> database to a file and I'll like to copy that file to another SQL 2000
> Server and restore it with a difference name. what is the procedures and
> how to go about doing it. I did tried to restore but getting the error:
> Exclusive access could not be obtained because the database is in use.
> Thanks in advance.
>
|||Seems like you want to overwrite an existing database with the overwrite
function in "Restore Database", try specifyin another name and perhpas
change the file locations the data will be restored in. If you make a
restore in the EM, the GUI will read the original destination of the data
and log files. If they already exists and error is thrown, if the directory
doesnt exists you have to create it first.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"toby one" <support@.re.com> schrieb im Newsbeitrag
news:%23ee8Dq6ZFHA.3488@.tk2msftngp13.phx.gbl...
>I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
> database to a file and I'll like to copy that file to another SQL 2000
> Server and restore it with a difference name. what is the procedures and
> how to go about doing it. I did tried to restore but getting the error:
> Exclusive access could not be obtained because the database is in use.
> Thanks in advance.
>
|||There's nothing special here. Do the backup, copy the backup file and then do the restore,
specifying the desired database name and possibly using the MOVE option to specify the desired
physical filenames. See the BACKUP DATABASE and RESTORE DATABASE commands in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"toby one" <support@.re.com> wrote in message news:%23ee8Dq6ZFHA.3488@.tk2msftngp13.phx.gbl...
>I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
> database to a file and I'll like to copy that file to another SQL 2000
> Server and restore it with a difference name. what is the procedures and
> how to go about doing it. I did tried to restore but getting the error:
> Exclusive access could not be obtained because the database is in use.
> Thanks in advance.
>
sql

Restore database file with difference name

I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
database to a file and I'll like to copy that file to another SQL 2000
Server and restore it with a difference name. what is the procedures and
how to go about doing it. I did tried to restore but getting the error:
Exclusive access could not be obtained because the database is in use.
Thanks in advance.Hi,
You have to use RESTORE DATABASE with MOVE option.
Eg:-
Restore database <new_dbname> from disk='c;\backup\dbname.bak'
with move 'logical_data_filename' to 'physical_data_filename',
move 'logical_log_filename' to 'physical_log_filename', stats=10
Thanks
Hari
SQL Server MVP
"toby one" <support@.re.com> wrote in message
news:%23ee8Dq6ZFHA.3488@.tk2msftngp13.phx.gbl...
>I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
> database to a file and I'll like to copy that file to another SQL 2000
> Server and restore it with a difference name. what is the procedures and
> how to go about doing it. I did tried to restore but getting the error:
> Exclusive access could not be obtained because the database is in use.
> Thanks in advance.
>|||Seems like you want to overwrite an existing database with the overwrite
function in "Restore Database", try specifyin another name and perhpas
change the file locations the data will be restored in. If you make a
restore in the EM, the GUI will read the original destination of the data
and log files. If they already exists and error is thrown, if the directory
doesn´t exists you have to create it first.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"toby one" <support@.re.com> schrieb im Newsbeitrag
news:%23ee8Dq6ZFHA.3488@.tk2msftngp13.phx.gbl...
>I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
> database to a file and I'll like to copy that file to another SQL 2000
> Server and restore it with a difference name. what is the procedures and
> how to go about doing it. I did tried to restore but getting the error:
> Exclusive access could not be obtained because the database is in use.
> Thanks in advance.
>|||There's nothing special here. Do the backup, copy the backup file and then do the restore,
specifying the desired database name and possibly using the MOVE option to specify the desired
physical filenames. See the BACKUP DATABASE and RESTORE DATABASE commands in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"toby one" <support@.re.com> wrote in message news:%23ee8Dq6ZFHA.3488@.tk2msftngp13.phx.gbl...
>I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
> database to a file and I'll like to copy that file to another SQL 2000
> Server and restore it with a difference name. what is the procedures and
> how to go about doing it. I did tried to restore but getting the error:
> Exclusive access could not be obtained because the database is in use.
> Thanks in advance.
>

Restore database file with difference name

I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
database to a file and I'll like to copy that file to another SQL 2000
Server and restore it with a difference name. what is the procedures and
how to go about doing it. I did tried to restore but getting the error:
Exclusive access could not be obtained because the database is in use.
Thanks in advance.Hi,
You have to use RESTORE DATABASE with MOVE option.
Eg:-
Restore database <new_dbname> from disk='c;\backup\dbname.bak'
with move 'logical_data_filename' to 'physical_data_filename',
move 'logical_log_filename' to 'physical_log_filename', stats=10
Thanks
Hari
SQL Server MVP
"toby one" <support@.re.com> wrote in message
news:%23ee8Dq6ZFHA.3488@.tk2msftngp13.phx.gbl...
>I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
> database to a file and I'll like to copy that file to another SQL 2000
> Server and restore it with a difference name. what is the procedures and
> how to go about doing it. I did tried to restore but getting the error:
> Exclusive access could not be obtained because the database is in use.
> Thanks in advance.
>|||Seems like you want to overwrite an existing database with the overwrite
function in "Restore Database", try specifyin another name and perhpas
change the file locations the data will be restored in. If you make a
restore in the EM, the GUI will read the original destination of the data
and log files. If they already exists and error is thrown, if the directory
doesnt exists you have to create it first.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"toby one" <support@.re.com> schrieb im Newsbeitrag
news:%23ee8Dq6ZFHA.3488@.tk2msftngp13.phx.gbl...
>I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
> database to a file and I'll like to copy that file to another SQL 2000
> Server and restore it with a difference name. what is the procedures and
> how to go about doing it. I did tried to restore but getting the error:
> Exclusive access could not be obtained because the database is in use.
> Thanks in advance.
>|||There's nothing special here. Do the backup, copy the backup file and then d
o the restore,
specifying the desired database name and possibly using the MOVE option to s
pecify the desired
physical filenames. See the BACKUP DATABASE and RESTORE DATABASE commands in
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"toby one" <support@.re.com> wrote in message news:%23ee8Dq6ZFHA.3488@.tk2msftngp13.phx.gbl...

>I have SQL 2000 SP3 running on Windows Server 2003, I've backup one of the
> database to a file and I'll like to copy that file to another SQL 2000
> Server and restore it with a difference name. what is the procedures and
> how to go about doing it. I did tried to restore but getting the error:
> Exclusive access could not be obtained because the database is in use.
> Thanks in advance.
>

Restore Database failed - Excusive Access could not be obtained.

I have a job that runs at 4:00 AM, which refreshes(RESTORE
DATABASE) the read only copy of the database. Last night
the job has failed and the message I got is, Exculsive
access could not be obtained because the database is in
use and the acutal message is as following -
Executed as user: UserNameHere. ...Executing... DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
(80040E14) Error string: RESTORE DATABASE is
terminating abnormally. Error source: Microsoft OLE
DB Provider for SQL Server Help file: Help
context: 0 Error Detail Records: Error: -
2147217900 (80040E14); Provider Error: 3013 (BC5)
Error string: RESTORE DATABASE is terminating
abnormally. Error source: Microsoft OLE DB Provider
for SQL Server Help file: Help context:
0 Error: -2147217900 (80040E14); Provider Error:
3101 (C1D) Error string: Exclusive access could not
be obtained because the database is in use. Error
source: Microsoft OLE DB Provider for SQL Serv. The step
failed.
Is there any option in RESTORE DATABASE that kills all the
logged in users and restores a fresh copy of the database.
Otherwise do I have to kill all the logged in users by
using sysprocesses. Also the error message doesn't show
the user(s) name, is there any way I can find it out now.
Going forward I will start running profiler but is there a
way figure out who logged in last night.
Thanks,
Narendra.Hi,
There is no option in restore command to kill all the users connected to
database. Instead you can use the below
statements along with your Restore database command and schedule this as a
task.
use master
go
declare @.x varchar(255)
select @.x = @.x + " kill " + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('dbname')
exec (@.x)
go
restore database command
Thanks
Hari
MCDBA
"Narendra" <nnarendra@.yahoo.com> wrote in message
news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
> I have a job that runs at 4:00 AM, which refreshes(RESTORE
> DATABASE) the read only copy of the database. Last night
> the job has failed and the message I got is, Exculsive
> access could not be obtained because the database is in
> use and the acutal message is as following -
> Executed as user: UserNameHere. ...Executing... DTSRun
> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
> (80040E14) Error string: RESTORE DATABASE is
> terminating abnormally. Error source: Microsoft OLE
> DB Provider for SQL Server Help file: Help
> context: 0 Error Detail Records: Error: -
> 2147217900 (80040E14); Provider Error: 3013 (BC5)
> Error string: RESTORE DATABASE is terminating
> abnormally. Error source: Microsoft OLE DB Provider
> for SQL Server Help file: Help context:
> 0 Error: -2147217900 (80040E14); Provider Error:
> 3101 (C1D) Error string: Exclusive access could not
> be obtained because the database is in use. Error
> source: Microsoft OLE DB Provider for SQL Serv. The step
> failed.
>
> Is there any option in RESTORE DATABASE that kills all the
> logged in users and restores a fresh copy of the database.
> Otherwise do I have to kill all the logged in users by
> using sysprocesses. Also the error message doesn't show
> the user(s) name, is there any way I can find it out now.
> Going forward I will start running profiler but is there a
> way figure out who logged in last night.
> Thanks,
> Narendra.|||Instead of this, you could just set the database to
SINGLE_USER MODE for the duration of the restore and then
set it back to MULTI_USER when done. Using the WITH
ROLLBACK IMMEDIATE when setting to SINGLE_USER MODE
alter database AHP set SINGLE_USER with rollback immediate
restore database command
alter database AHP set MULTI_USER
>--Original Message--
>Hi,
>There is no option in restore command to kill all the
users connected to
>database. Instead you can use the below
>statements along with your Restore database command and
schedule this as a
>task.
>use master
>go
>declare @.x varchar(255)
>select @.x = @.x + " kill " + convert(varchar(5), spid)
>from master.dbo.sysprocesses
>where dbid = db_id ('dbname')
>exec (@.x)
>go
>restore database command
>Thanks
>Hari
>MCDBA
>
>"Narendra" <nnarendra@.yahoo.com> wrote in message
>news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
>> I have a job that runs at 4:00 AM, which refreshes
(RESTORE
>> DATABASE) the read only copy of the database. Last night
>> the job has failed and the message I got is, Exculsive
>> access could not be obtained because the database is in
>> use and the acutal message is as following -
>> Executed as user: UserNameHere. ...Executing... DTSRun
>> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
>> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
>> (80040E14) Error string: RESTORE DATABASE is
>> terminating abnormally. Error source: Microsoft
OLE
>> DB Provider for SQL Server Help file: Help
>> context: 0 Error Detail Records: Error: -
>> 2147217900 (80040E14); Provider Error: 3013 (BC5)
>> Error string: RESTORE DATABASE is terminating
>> abnormally. Error source: Microsoft OLE DB
Provider
>> for SQL Server Help file: Help context:
>> 0 Error: -2147217900 (80040E14); Provider
Error:
>> 3101 (C1D) Error string: Exclusive access could
not
>> be obtained because the database is in use. Error
>> source: Microsoft OLE DB Provider for SQL Serv. The
step
>> failed.
>>
>> Is there any option in RESTORE DATABASE that kills all
the
>> logged in users and restores a fresh copy of the
database.
>> Otherwise do I have to kill all the logged in users by
>> using sysprocesses. Also the error message doesn't show
>> the user(s) name, is there any way I can find it out
now.
>> Going forward I will start running profiler but is
there a
>> way figure out who logged in last night.
>> Thanks,
>> Narendra.
>
>.
>|||Van/Hari,
Thanks for the information. I will follow the suggestions.
Narendra.
>--Original Message--
>Instead of this, you could just set the database to
>SINGLE_USER MODE for the duration of the restore and then
>set it back to MULTI_USER when done. Using the WITH
>ROLLBACK IMMEDIATE when setting to SINGLE_USER MODE
>alter database AHP set SINGLE_USER with rollback immediate
>restore database command
>alter database AHP set MULTI_USER
>>--Original Message--
>>Hi,
>>There is no option in restore command to kill all the
>users connected to
>>database. Instead you can use the below
>>statements along with your Restore database command and
>schedule this as a
>>task.
>>use master
>>go
>>declare @.x varchar(255)
>>select @.x = @.x + " kill " + convert(varchar(5), spid)
>>from master.dbo.sysprocesses
>>where dbid = db_id ('dbname')
>>exec (@.x)
>>go
>>restore database command
>>Thanks
>>Hari
>>MCDBA
>>
>>"Narendra" <nnarendra@.yahoo.com> wrote in message
>>news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
>> I have a job that runs at 4:00 AM, which refreshes
>(RESTORE
>> DATABASE) the read only copy of the database. Last
night
>> the job has failed and the message I got is, Exculsive
>> access could not be obtained because the database is in
>> use and the acutal message is as following -
>> Executed as user: UserNameHere. ...Executing...
DTSRun
>> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish:
>> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
>> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
>> (80040E14) Error string: RESTORE DATABASE is
>> terminating abnormally. Error source: Microsoft
>OLE
>> DB Provider for SQL Server Help file: Help
>> context: 0 Error Detail Records: Error: -
>> 2147217900 (80040E14); Provider Error: 3013 (BC5)
>> Error string: RESTORE DATABASE is terminating
>> abnormally. Error source: Microsoft OLE DB
>Provider
>> for SQL Server Help file: Help context:
>> 0 Error: -2147217900 (80040E14); Provider
>Error:
>> 3101 (C1D) Error string: Exclusive access could
>not
>> be obtained because the database is in use. Error
>> source: Microsoft OLE DB Provider for SQL Serv. The
>step
>> failed.
>>
>> Is there any option in RESTORE DATABASE that kills all
>the
>> logged in users and restores a fresh copy of the
>database.
>> Otherwise do I have to kill all the logged in users by
>> using sysprocesses. Also the error message doesn't show
>> the user(s) name, is there any way I can find it out
>now.
>> Going forward I will start running profiler but is
>there a
>> way figure out who logged in last night.
>> Thanks,
>> Narendra.
>>
>>.
>.
>|||FYI...the WITH ROLLBACK IMMEDIATE will rollback anything
that any users connected may have been in the middle of
doing. But that should be a little cleaner than a KILL
command.
Van Jones
MCDBA, MCSE, MCSA, MCAD
>--Original Message--
>Instead of this, you could just set the database to
>SINGLE_USER MODE for the duration of the restore and then
>set it back to MULTI_USER when done. Using the WITH
>ROLLBACK IMMEDIATE when setting to SINGLE_USER MODE
>alter database AHP set SINGLE_USER with rollback immediate
>restore database command
>alter database AHP set MULTI_USER
>>--Original Message--
>>Hi,
>>There is no option in restore command to kill all the
>users connected to
>>database. Instead you can use the below
>>statements along with your Restore database command and
>schedule this as a
>>task.
>>use master
>>go
>>declare @.x varchar(255)
>>select @.x = @.x + " kill " + convert(varchar(5), spid)
>>from master.dbo.sysprocesses
>>where dbid = db_id ('dbname')
>>exec (@.x)
>>go
>>restore database command
>>Thanks
>>Hari
>>MCDBA
>>
>>"Narendra" <nnarendra@.yahoo.com> wrote in message
>>news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
>> I have a job that runs at 4:00 AM, which refreshes
>(RESTORE
>> DATABASE) the read only copy of the database. Last
night
>> the job has failed and the message I got is, Exculsive
>> access could not be obtained because the database is in
>> use and the acutal message is as following -
>> Executed as user: UserNameHere. ...Executing...
DTSRun
>> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish:
>> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
>> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
>> (80040E14) Error string: RESTORE DATABASE is
>> terminating abnormally. Error source: Microsoft
>OLE
>> DB Provider for SQL Server Help file: Help
>> context: 0 Error Detail Records: Error: -
>> 2147217900 (80040E14); Provider Error: 3013 (BC5)
>> Error string: RESTORE DATABASE is terminating
>> abnormally. Error source: Microsoft OLE DB
>Provider
>> for SQL Server Help file: Help context:
>> 0 Error: -2147217900 (80040E14); Provider
>Error:
>> 3101 (C1D) Error string: Exclusive access could
>not
>> be obtained because the database is in use. Error
>> source: Microsoft OLE DB Provider for SQL Serv. The
>step
>> failed.
>>
>> Is there any option in RESTORE DATABASE that kills all
>the
>> logged in users and restores a fresh copy of the
>database.
>> Otherwise do I have to kill all the logged in users by
>> using sysprocesses. Also the error message doesn't show
>> the user(s) name, is there any way I can find it out
>now.
>> Going forward I will start running profiler but is
>there a
>> way figure out who logged in last night.
>> Thanks,
>> Narendra.
>>
>>.
>.
>

RESTORE DATABASE consistency error

I have SQL Server 2000 on two different servers I have
daily copy the database backup file to another server and
restored the database backup file.
Yesterday, I received the following errors listed below
after copying the database backup file to another and
restoring the database file.
An internal consistency error occurred. RESTORE DATABASE
is terminating abnormally.
Event Viewer
18272 :
I/O error on backup or restore restart-checkpoint
file 'n:\backup\NewOrleans_Back.ckp'. Operating system
error 3(The system cannot find the path specified.). The
statement is proceeding but is non-restartable.
Please help me with this restore database issue.
Thanks,
Jake
Hi,
Check if the folder n:\backup\ exist.
If not, add the folder and try the restore again
Thanks
Hari
"Jake" <anonymous@.discussions.microsoft.com> wrote in message
news:0e3901c4a653$b0d7de10$a401280a@.phx.gbl...
> I have SQL Server 2000 on two different servers I have
> daily copy the database backup file to another server and
> restored the database backup file.
> Yesterday, I received the following errors listed below
> after copying the database backup file to another and
> restoring the database file.
> An internal consistency error occurred. RESTORE DATABASE
> is terminating abnormally.
> Event Viewer
> 18272 :
> I/O error on backup or restore restart-checkpoint
> file 'n:\backup\NewOrleans_Back.ckp'. Operating system
> error 3(The system cannot find the path specified.). The
> statement is proceeding but is non-restartable.
> Please help me with this restore database issue.
> Thanks,
> Jake

RESTORE DATABASE consistency error

I have SQL Server 2000 on two different servers I have
daily copy the database backup file to another server and
restored the database backup file.
Yesterday, I received the following errors listed below
after copying the database backup file to another and
restoring the database file.
An internal consistency error occurred. RESTORE DATABASE
is terminating abnormally.
Event Viewer
18272 :
I/O error on backup or restore restart-checkpoint
file 'n:\backup\NewOrleans_Back.ckp'. Operating system
error 3(The system cannot find the path specified.). The
statement is proceeding but is non-restartable.
Please help me with this restore database issue.
Thanks,
JakeHi,
Check if the folder n:\backup\ exist.
If not, add the folder and try the restore again
Thanks
Hari
"Jake" <anonymous@.discussions.microsoft.com> wrote in message
news:0e3901c4a653$b0d7de10$a401280a@.phx.gbl...
> I have SQL Server 2000 on two different servers I have
> daily copy the database backup file to another server and
> restored the database backup file.
> Yesterday, I received the following errors listed below
> after copying the database backup file to another and
> restoring the database file.
> An internal consistency error occurred. RESTORE DATABASE
> is terminating abnormally.
> Event Viewer
> 18272 :
> I/O error on backup or restore restart-checkpoint
> file 'n:\backup\NewOrleans_Back.ckp'. Operating system
> error 3(The system cannot find the path specified.). The
> statement is proceeding but is non-restartable.
> Please help me with this restore database issue.
> Thanks,
> Jake

restore database

I have a copy of a backup from a user site. a .sbk file..
I have a development server at my work site and an old copy of the
database that I use for testing. I would like to overlay my old copy of
the database with the backup from the user site. What is the best way
to do this?
Thanks
See the RESTORE DATABASE topic in Books Online. To overwrite the old copy,
just specify the same name and the REPLACE options, along with all the other
requried items...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"terry" <cooleyt@.woh.rr.com> wrote in message
news:phaaf.179542$lI5.158808@.tornado.ohiordc.rr.co m...
>I have a copy of a backup from a user site. a .sbk file..
> I have a development server at my work site and an old copy of the
> database that I use for testing. I would like to overlay my old copy of
> the database with the backup from the user site. What is the best way to
> do this?
> Thanks
>
|||here are the steps I am taking to do the restore
step 1
Restore filelistonly
from disk = c:\projects\\lams.sdk
this produces a table with
logical filename = hrattend_data physical name =
f:\sqldata\\hrattend.mdf
logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
step 2
restore database hrattend
from disk = c:\projects\..\lams.sdk
with
move hrattend_data to c:\program files\\hrattend.mdf,
move hrattend_log to c:\program files\..\hrattend.ldf,
replace,
stats = 10
the data is still the old data.
my question is following the restore filelistonly, why does the physical
name fields reference f:\ ? shouldn't this be in c:\ where the backup
is located?
|||> the data is still the old data.
Do RESTORE HEADERONLY. You might have several backups on that file and you are restorring the oldest
one. Use the FILE option to specify anything else than the first (1).

> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ?
Where do you see this physical name field?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"terry" <cooleyt@.woh.rr.com> wrote in message news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com ...
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name = f:\sqldata\\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ? shouldn't this be in c:\ where the backup is located?
>
>
|||Hi,
Physical name field is the actual place your MDF and LDF were stored when
you backup the source database. Based on that
you can decide whether you need a MOVE command or not. Incase if you have
same folder in destination server then you do not want to
give a MOVE command; else you have mention move command to redirect the MDF
and LDF to a new path.
The other question:- As Tibor mentioned use RESTORE HEADERONLY command to
find the latest backup file and use FILE = 'latest number'
in your RESTORE DATABASE command.
Thanks
Hari
SQL server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23PXQcE4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Do RESTORE HEADERONLY. You might have several backups on that file and you
> are restorring the oldest one. Use the FILE option to specify anything
> else than the first (1).
>
> Where do you see this physical name field?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "terry" <cooleyt@.woh.rr.com> wrote in message
> news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com ...
>
|||terry wrote:
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name =
> f:\sqldata\\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical
> name fields reference f:\ ? shouldn't this be in c:\ where the backup
> is located?
>
>
The physical name in the RESTORE FILELISTONLY, refers to the original
location of the files. This is info that is stored in the backup file
and isn't related to anything on your server.
If you want to overwrite your existing database, you don't have to use
the MOVE option. Basically you just run -
RESTORE DATABASE hrattend FROM DISK = 'c:\projects\...\lams.sdk'
WITH RECOVERY, REPLACE, STATS =10
HTH
Regards
Steen
|||this did not work as expected. The backup file that I am using was
created on 11/02. I previously restored the database in Jun. The new
back up file is larger than the previous one, so I am assuming that it
contains more records. Following the restore, my tables still only
contain records ending in June.
I tried to create a new database from the 11/02 backup using the Restore
function in the enterprise manager. I successfully created a new
database, but it still only contains records up to Jun. What am I missing?
|||I finally figured it out. Thanks for the help...
The restore headeronly set me off in the right direction. There were
three backup sets in the backup file. They neglected to tell me that.

restore database

I have a copy of a backup from a user site. a .sbk file..
I have a development server at my work site and an old copy of the
database that I use for testing. I would like to overlay my old copy of
the database with the backup from the user site. What is the best way
to do this?
ThanksSee the RESTORE DATABASE topic in Books Online. To overwrite the old copy,
just specify the same name and the REPLACE options, along with all the other
requried items...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"terry" <cooleyt@.woh.rr.com> wrote in message
news:phaaf.179542$lI5.158808@.tornado.ohiordc.rr.com...
>I have a copy of a backup from a user site. a .sbk file..
> I have a development server at my work site and an old copy of the
> database that I use for testing. I would like to overlay my old copy of
> the database with the backup from the user site. What is the best way to
> do this?
> Thanks
>|||here are the steps I am taking to do the restore
step 1
Restore filelistonly
from disk = c:\projects\?\lams.sdk
this produces a table with
logical filename = hrattend_data physical name =f:\sqldata\'\hrattend.mdf
logical filename = hrattend_log physical name = f:\sqldata\'\hrattend.ldf
step 2
restore database hrattend
from disk = c:\projects\?..\lams.sdk
with
move ?hrattend_data to c:\program files\'\hrattend.mdf?,
move ?hrattend_log to c:\program files\?..\hrattend.ldf,
replace,
stats = 10
the data is still the old data.
my question is following the restore filelistonly, why does the physical
name fields reference f:\ ? shouldn't this be in c:\ where the backup
is located?|||> the data is still the old data.
Do RESTORE HEADERONLY. You might have several backups on that file and you are restorring the oldest
one. Use the FILE option to specify anything else than the first (1).
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ?
Where do you see this physical name field?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"terry" <cooleyt@.woh.rr.com> wrote in message news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com...
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\?\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name = f:\sqldata\'\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\'\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\?..\lams.sdk
> with
> move ?hrattend_data to c:\program files\'\hrattend.mdf?,
> move ?hrattend_log to c:\program files\?..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ? shouldn't this be in c:\ where the backup is located?
>
>|||Hi,
Physical name field is the actual place your MDF and LDF were stored when
you backup the source database. Based on that
you can decide whether you need a MOVE command or not. Incase if you have
same folder in destination server then you do not want to
give a MOVE command; else you have mention move command to redirect the MDF
and LDF to a new path.
The other question:- As Tibor mentioned use RESTORE HEADERONLY command to
find the latest backup file and use FILE = 'latest number'
in your RESTORE DATABASE command.
Thanks
Hari
SQL server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23PXQcE4FHA.252@.TK2MSFTNGP15.phx.gbl...
>> the data is still the old data.
> Do RESTORE HEADERONLY. You might have several backups on that file and you
> are restorring the oldest one. Use the FILE option to specify anything
> else than the first (1).
>
>> my question is following the restore filelistonly, why does the physical
>> name fields reference f:\ ?
> Where do you see this physical name field?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "terry" <cooleyt@.woh.rr.com> wrote in message
> news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com...
>> here are the steps I am taking to do the restore
>> step 1
>> Restore filelistonly
>> from disk = c:\projects\.\lams.sdk
>> this produces a table with
>> logical filename = hrattend_data physical name =>> f:\sqldata\..\hrattend.mdf
>> logical filename = hrattend_log physical name =>> f:\sqldata\..\hrattend.ldf
>> step 2
>> restore database hrattend
>> from disk = c:\projects\...\lams.sdk
>> with
>> move 'hrattend_data to c:\program files\..\hrattend.mdf',
>> move 'hrattend_log to c:\program files\...\hrattend.ldf,
>> replace,
>> stats = 10
>> the data is still the old data.
>> my question is following the restore filelistonly, why does the physical
>> name fields reference f:\ ? shouldn't this be in c:\ where the backup is
>> located?
>>
>|||terry wrote:
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\?\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name => f:\sqldata\'\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\'\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\?..\lams.sdk
> with
> move ?hrattend_data to c:\program files\'\hrattend.mdf?,
> move ?hrattend_log to c:\program files\?..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical
> name fields reference f:\ ? shouldn't this be in c:\ where the backup
> is located?
>
>
The physical name in the RESTORE FILELISTONLY, refers to the original
location of the files. This is info that is stored in the backup file
and isn't related to anything on your server.
If you want to overwrite your existing database, you don't have to use
the MOVE option. Basically you just run -
RESTORE DATABASE hrattend FROM DISK = 'c:\projects\...\lams.sdk'
WITH RECOVERY, REPLACE, STATS =10
HTH
Regards
Steen|||this did not work as expected. The backup file that I am using was
created on 11/02. I previously restored the database in Jun. The new
back up file is larger than the previous one, so I am assuming that it
contains more records. Following the restore, my tables still only
contain records ending in June.
I tried to create a new database from the 11/02 backup using the Restore
function in the enterprise manager. I successfully created a new
database, but it still only contains records up to Jun. What am I missing?|||I finally figured it out. Thanks for the help...
The restore headeronly set me off in the right direction. There were
three backup sets in the backup file. They neglected to tell me that.

restore database

I have a copy of a backup from a user site. a .sbk file..
I have a development server at my work site and an old copy of the
database that I use for testing. I would like to overlay my old copy of
the database with the backup from the user site. What is the best way
to do this?
ThanksSee the RESTORE DATABASE topic in Books Online. To overwrite the old copy,
just specify the same name and the REPLACE options, along with all the other
requried items...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"terry" <cooleyt@.woh.rr.com> wrote in message
news:phaaf.179542$lI5.158808@.tornado.ohiordc.rr.com...
>I have a copy of a backup from a user site. a .sbk file..
> I have a development server at my work site and an old copy of the
> database that I use for testing. I would like to overlay my old copy of
> the database with the backup from the user site. What is the best way to
> do this?
> Thanks
>|||here are the steps I am taking to do the restore
step 1
Restore filelistonly
from disk = c:\projects\\lams.sdk
this produces a table with
logical filename = hrattend_data physical name =
f:\sqldata\\hrattend.mdf
logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
step 2
restore database hrattend
from disk = c:\projects\..\lams.sdk
with
move hrattend_data to c:\program files\\hrattend.mdf,
move hrattend_log to c:\program files\..\hrattend.ldf,
replace,
stats = 10
the data is still the old data.
my question is following the restore filelistonly, why does the physical
name fields reference f:\ ? shouldn't this be in c:\ where the backup
is located?|||> the data is still the old data.
Do RESTORE HEADERONLY. You might have several backups on that file and you a
re restorring the oldest
one. Use the FILE option to specify anything else than the first (1).

> my question is following the restore filelistonly, why does the physical n
ame fields reference f:\
> ?
Where do you see this physical name field?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"terry" <cooleyt@.woh.rr.com> wrote in message news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr
.com...
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name = f:\sqldata\\hrattend
.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ld
f
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical n
ame fields reference f:\
> ? shouldn't this be in c:\ where the backup is located?
>
>|||Hi,
Physical name field is the actual place your MDF and LDF were stored when
you backup the source database. Based on that
you can decide whether you need a MOVE command or not. Incase if you have
same folder in destination server then you do not want to
give a MOVE command; else you have mention move command to redirect the MDF
and LDF to a new path.
The other question:- As Tibor mentioned use RESTORE HEADERONLY command to
find the latest backup file and use FILE = 'latest number'
in your RESTORE DATABASE command.
Thanks
Hari
SQL server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23PXQcE4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Do RESTORE HEADERONLY. You might have several backups on that file and you
> are restorring the oldest one. Use the FILE option to specify anything
> else than the first (1).
>
> Where do you see this physical name field?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "terry" <cooleyt@.woh.rr.com> wrote in message
> news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com...
>|||terry wrote:
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name =
> f:\sqldata\\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ld
f
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical
> name fields reference f:\ ? shouldn't this be in c:\ where the backup
> is located?
>
>
The physical name in the RESTORE FILELISTONLY, refers to the original
location of the files. This is info that is stored in the backup file
and isn't related to anything on your server.
If you want to overwrite your existing database, you don't have to use
the MOVE option. Basically you just run -
RESTORE DATABASE hrattend FROM DISK = 'c:\projects\...\lams.sdk'
WITH RECOVERY, REPLACE, STATS =10
HTH
Regards
Steen|||this did not work as expected. The backup file that I am using was
created on 11/02. I previously restored the database in Jun. The new
back up file is larger than the previous one, so I am assuming that it
contains more records. Following the restore, my tables still only
contain records ending in June.
I tried to create a new database from the 11/02 backup using the Restore
function in the enterprise manager. I successfully created a new
database, but it still only contains records up to Jun. What am I missing?|||I finally figured it out. Thanks for the help...
The restore headeronly set me off in the right direction. There were
three backup sets in the backup file. They neglected to tell me that.

Restore Dabatase MSSQL

Dear all I need your help in order to restore a database.

I did a backup of one SQL database hosted on my web service, and after I copy the file on my C:\Wutemp disk.

I'd like to import this database on my MSSQL installed on my local pc in order to have the same web site in local.

I try to restore it with enterprise manager (..using Restore) but I receive a lot of error message:
(I translate from Italian so..be patient..)

The filename 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mioDB_DATA.mdf' could be not correct.
Impossible to restore the file 'miodb_Data' in 'C:\Program Files\Micorosft SQL Server\MSSQL\Data\mioDB_DATA.mdf'. Use WITH MOVE in oreder to identify a valid location for the file
The filename 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mioDB_Log.LDF'could be not correct
Impossible to restore the file 'miodb_Log' in 'C:\Program Files\Micorosft SQL Server\MSSQL\Data\mioDB_Log.ldf'.

Any suggestions?you need to set the path it will restore to in the options section of the restore dialog. make sure it points to a valid local directory. if in doubt, look this up in Books Online - it's very clearly explained.

Restore backup on different machine

We are moving backups from being written on a local box, to writing tham on a
NAS and making a daily copy to a report server. So, to start, I made a manual
copy of the backup files to the NAS and then wen't to restore tham.
What I get is an error message that includes the path name from the machine
where the backup was made as well as the path name for the log file.
Duh!
I can see that I need to add the 'WITH MOVE' parameters to the restore
command, but I can't get them to work. This is probably because I flat-out
don't understand what is happening. Once I say where the full backup is, why
does it care about the path is was backed up to? Why does it care about
restoring a log file I didn't ask for?
Once I change the backup path on the production machine to the NAS (so that
the paths are the same, will this issue just go away?> I can see that I need to add the 'WITH MOVE' parameters to the restore
> command, but I can't get them to work. This is probably because I flat-out
> don't understand what is happening. Once I say where the full backup is,
> why
> does it care about the path is was backed up to?
Because when it restores the database, it needs to create an MDF and LDF
file of the same size for the database to be restored correctly and usable.
> Why does it care about
> restoring a log file I didn't ask for?
When you restore a database, you get the data file(s) and the log file(s).
This is not negotiable.
> Once I change the backup path on the production machine to the NAS (so
> that
> the paths are the same, will this issue just go away?
That depends, if you mean that you intend to restore to the exact same
location as an existing data/log file location, then no, that won't work.

Restore backup on different machine

We are moving backups from being written on a local box, to writing tham on a
NAS and making a daily copy to a report server. So, to start, I made a manual
copy of the backup files to the NAS and then wen't to restore tham.
What I get is an error message that includes the path name from the machine
where the backup was made as well as the path name for the log file.
Duh!
I can see that I need to add the 'WITH MOVE' parameters to the restore
command, but I can't get them to work. This is probably because I flat-out
don't understand what is happening. Once I say where the full backup is, why
does it care about the path is was backed up to? Why does it care about
restoring a log file I didn't ask for?
Once I change the backup path on the production machine to the NAS (so that
the paths are the same, will this issue just go away?
> I can see that I need to add the 'WITH MOVE' parameters to the restore
> command, but I can't get them to work. This is probably because I flat-out
> don't understand what is happening. Once I say where the full backup is,
> why
> does it care about the path is was backed up to?
Because when it restores the database, it needs to create an MDF and LDF
file of the same size for the database to be restored correctly and usable.

> Why does it care about
> restoring a log file I didn't ask for?
When you restore a database, you get the data file(s) and the log file(s).
This is not negotiable.

> Once I change the backup path on the production machine to the NAS (so
> that
> the paths are the same, will this issue just go away?
That depends, if you mean that you intend to restore to the exact same
location as an existing data/log file location, then no, that won't work.

Restore backup on different machine

We are moving backups from being written on a local box, to writing tham on
a
NAS and making a daily copy to a report server. So, to start, I made a manua
l
copy of the backup files to the NAS and then wen't to restore tham.
What I get is an error message that includes the path name from the machine
where the backup was made as well as the path name for the log file.
Duh!
I can see that I need to add the 'WITH MOVE' parameters to the restore
command, but I can't get them to work. This is probably because I flat-out
don't understand what is happening. Once I say where the full backup is, why
does it care about the path is was backed up to? Why does it care about
restoring a log file I didn't ask for?
Once I change the backup path on the production machine to the NAS (so that
the paths are the same, will this issue just go away?> I can see that I need to add the 'WITH MOVE' parameters to the restore
> command, but I can't get them to work. This is probably because I flat-out
> don't understand what is happening. Once I say where the full backup is,
> why
> does it care about the path is was backed up to?
Because when it restores the database, it needs to create an MDF and LDF
file of the same size for the database to be restored correctly and usable.

> Why does it care about
> restoring a log file I didn't ask for?
When you restore a database, you get the data file(s) and the log file(s).
This is not negotiable.

> Once I change the backup path on the production machine to the NAS (so
> that
> the paths are the same, will this issue just go away?
That depends, if you mean that you intend to restore to the exact same
location as an existing data/log file location, then no, that won't work.

restore backup

I have a copy of a database to install and work with locally (work from my
own pc at home instead of in the office). I have a copy of the bak file, how
can I install that on my SQL Server and use the database?
NuB,
Use either Enterprise Manager - Restore or the T-SQL RESTORE statement.
See:
'How to restore a backup from a backup device (Enterprise Manager)' in the
SQL Books Online
or
'RESTORE' in the SQL Books Online. (examples included)
HTH
Jerry
"NuB" <me@.me.com> wrote in message
news:eKQGG5L2FHA.2932@.TK2MSFTNGP10.phx.gbl...
>I have a copy of a database to install and work with locally (work from my
>own pc at home instead of in the office). I have a copy of the bak file,
>how can I install that on my SQL Server and use the database?
>
>

restore backup

I have a copy of a database to install and work with locally (work from my
own pc at home instead of in the office). I have a copy of the bak file, how
can I install that on my SQL Server and use the database?NuB,
Use either Enterprise Manager - Restore or the T-SQL RESTORE statement.
See:
'How to restore a backup from a backup device (Enterprise Manager)' in the
SQL Books Online
or
'RESTORE' in the SQL Books Online. (examples included)
HTH
Jerry
"NuB" <me@.me.com> wrote in message
news:eKQGG5L2FHA.2932@.TK2MSFTNGP10.phx.gbl...
>I have a copy of a database to install and work with locally (work from my
>own pc at home instead of in the office). I have a copy of the bak file,
>how can I install that on my SQL Server and use the database?
>
>