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 stuck on (Loading)

I recently installed MSDE on a laptop. I then restored several databases from backup files without problem. However, one database is constantly stuck at the grey colour displaying the (Loading) message.

Why won't this database restore ? It is of course the most important database and I only have the one backup file.

This only appears to fail on his server. Do I need to uninstall MSDE and start over ?It's probably the machine's "critical need detector", it might be a wee bit too sensitive or mis-adjusted.

On a slightly more serious note, what tools are you using to do the restore? The only tool that could do a restore that ships with MSDE is OSQL.EXE, and I'm pretty comfortable that you aren't using that! Enterprise Mangler would be my first guess, but that is still only a guess.

Could the dump (backup) have been made with a later service pack than you are using to restore it? That can make things get really crabby sometimes.

Is the database file one that this instance of MSDE can handle? Depending on the MSDE version, you can handle up to 2 Gb of data (although I've seen data and log dumps over 10 Gb).

If none of those ideas pan out, then my first suggestion would be to:

1 Disable everything you can that uses MSDE (keep notes!)
2 Stop the MSDE service
3 Restart the MSDE service
4 Try to load the offending database
5 If you can't load the database, stop the MSDE service
6 Examine the Errorlog file carefully
7 If you can't find anything in the Errorlog, consider posting it here.
8 if the database can be posted, consider posting the backup too for us to play with!

-PatP|||If it's in Loading state, could it be that you are restoring from a backup device that contained incomplete set of full db backup and some trx logs?|||But it restores without fail on any number of other servers. So I think the backup file is ok.

I have gone through the steps above and can't see anything wrong. I have pasted the errorlog below for perusal.

Yes I am using Enterprise manager. I also tried the restore database command from isqlw utility and that also produced the error :

Processed 8432 pages for database 'IPSHOW_NEW', file 'IPSHOW_Data' on file 1.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Processed 1 pages for database 'IPSHOW_NEW', file 'IPSHOW_Log' on file 1.

Connection Broken

Errorlog
2004-12-23 15:56:36.01 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2004-12-23 15:56:36.01 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-12-23 15:56:36.01 server All rights reserved.
2004-12-23 15:56:36.01 server Server Process ID is 1392.
2004-12-23 15:56:36.01 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2004-12-23 15:56:36.09 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-12-23 15:56:36.13 server SQL Server configured for thread mode processing.
2004-12-23 15:56:36.13 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2004-12-23 15:56:36.15 spid3 Starting up database 'master'.
2004-12-23 15:56:36.53 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-12-23 15:56:36.53 spid5 Starting up database 'model'.
2004-12-23 15:56:36.68 spid3 Server name is 'SBUTLER'.
2004-12-23 15:56:36.68 spid3 Skipping startup of clean database id 4
2004-12-23 15:56:36.68 spid3 Skipping startup of clean database id 5
2004-12-23 15:56:36.68 spid3 Skipping startup of clean database id 6
2004-12-23 15:56:36.68 spid3 Skipping startup of clean database id 9
2004-12-23 15:56:36.68 spid3 Skipping startup of clean database id 10
2004-12-23 15:56:36.68 spid3 Starting up database 'IPSHOW_NEW'.
2004-12-23 15:56:36.68 server SQL server listening on 192.168.1.110: 1433.
2004-12-23 15:56:36.69 server SQL server listening on 127.0.0.1: 1433.
2004-12-23 15:56:36.98 spid3 Bypassing recovery for database 'IPSHOW_NEW' because it is marked IN LOAD.
2004-12-23 15:56:37.09 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-12-23 15:56:37.09 server SQL Server is ready for client connections
2004-12-23 15:56:37.22 spid5 Clearing tempdb database.
2004-12-23 15:56:38.58 spid5 Starting up database 'tempdb'.
2004-12-23 15:56:38.78 spid3 Recovery complete.
2004-12-23 15:56:38.78 spid3 SQL global counter collection task is created.
2004-12-23 15:57:35.63 spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-12-23 15:57:35.78 spid51 Starting up database 'msdb'.|||Post the statement used in ISQL to restore the database.
And ensure the backup file is stored on the MSDE server and if you are trying to restore from a network location due to th network issues it may be failing with the above error.

Refer to this KBA http://support.microsoft.com/default.aspx?scid=kb;en-us;827452 for relevancy.|||I gave up and uninstalled MSDE. As it turns out he required Reporting Services and a Developer Edition of SQL Server so all for the best in the end. Thanks for the assistance.

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 overwriting databases without the Replace option (SQL Server 2000)

Hi everybody,
I'm trying to figure out why my installation of SQL Server 2000 is allowing
databases to be restored that overwrite an exisitng databases, when I do
*not* specify the replace option.
Is the server currupted? Did a setting get set somewhere that allows this?
What should I look for,
Thanks, JRAccording to the SQL Server 2000 Books Online
<tsqlref.chm::/ts_ra-rz_25rm.htm>, REPLACE is required only if both of
the following are true:
a. The database named in the RESTORE statement already exists on the
current server, and
b. The database name is different from the database name recorded in the
backup set.
Consequently, if the database name you backed up from is the same as the
target, the RESTORE will be allowed even without the REPLACE option.
Your server is not corrupted if this is the case in your situation.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"JR" <newsreader@.jmrcs.com> wrote in message
news:%23jjMpQ5TDHA.3188@.tk2msftngp13.phx.gbl...
> Hi everybody,
> I'm trying to figure out why my installation of SQL Server 2000 is
allowing
> databases to be restored that overwrite an exisitng databases, when I
do
> *not* specify the replace option.
> Is the server currupted? Did a setting get set somewhere that allows
this?
> What should I look for,
> Thanks, JR
>

Restore database need to recreate a user

Everytime I restored a user database, I need to delete a user of that
database and recreate the same one, otherwise my application cannot connect
to that database.
Any idea ?
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>
|||Hi,
No need to recreate the user. You could re-sync the Logins and users using
the below system stored procedure.
sp_change_users_login 'update_one','user_name','login_name'
See more details of this procedure in books online
Thanks
Hari
SQL Server MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>

Restore database need to recreate a user

Everytime I restored a user database, I need to delete a user of that
database and recreate the same one, otherwise my application cannot connect
to that database.
Any idea ?http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>|||Hi,
No need to recreate the user. You could re-sync the Logins and users using
the below system stored procedure.
sp_change_users_login 'update_one','user_name','login_name'
See more details of this procedure in books online
Thanks
Hari
SQL Server MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>

Restore database need to recreate a user

Everytime I restored a user database, I need to delete a user of that
database and recreate the same one, otherwise my application cannot connect
to that database.
Any idea ?http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>|||Hi,
No need to recreate the user. You could re-sync the Logins and users using
the below system stored procedure.
sp_change_users_login 'update_one','user_name','login_name'
See more details of this procedure in books online
Thanks
Hari
SQL Server MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>sql

Restore database error

I get ODBC SQLState error 42000 with message "internal
consistency error occured" while I restored database.
I tried MSSQL 7 and MSSQL 2000 with the same result.
This database was running without problem (MSSQL 7) and backup
was created "successfully".
I have another backups on the tape (for three years)
and all files act equally.
Any idea?
Perhaps the database was corrupt when you backuped it? Can you run a DBCC CHECKDB on the original
database (assuming you still have that online)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Horak" <richard_horak@.hotmail.com> wrote in message
news:eGWqC6k0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> I get ODBC SQLState error 42000 with message "internal
> consistency error occured" while I restored database.
> I tried MSSQL 7 and MSSQL 2000 with the same result.
> This database was running without problem (MSSQL 7) and backup
> was created "successfully".
> I have another backups on the tape (for three years)
> and all files act equally.
> Any idea?
|||Corrupt? I think, that database, running and serving all requests
wouldn't be corrupt.
Original server is running now new OS and new SQL, return back is not
possible.....
Tibor Karaszi wrote:
> Perhaps the database was corrupt when you backuped it? Can you run a DBCC CHECKDB on the original
> database (assuming you still have that online)?
>
|||You can still have a corruption in the database even if you work against it. This is why you want to
run regular DBCC CHECKDB commands to see if you have any type of corruption (most often introduced
by hardware problems). I suggest you open a case with MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Horak" <richard_horak@.hotmail.com> wrote in message
news:%23%23lMjD50EHA.3840@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Corrupt? I think, that database, running and serving all requests
> wouldn't be corrupt.
> Original server is running now new OS and new SQL, return back is not
> possible.....
> Tibor Karaszi wrote:
original[vbcol=seagreen]

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 and filegroups question

I have a situation that involves restoring a database to a sencond SQL
Server with 3 filegroups and files.
The problem is that when the db is restored to svr2, all of the objects that
were in other file groups wind up in the PRIMARY/default filegroup.
I'm trying to understand how this is happening, but I haven't actually
watched the people doing this, but that's how it ends up.
Any idea of what I am missing?
This should not happen.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"frankm" <frankm@.nospam.postalias> wrote in message
news:%23tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl...
>I have a situation that involves restoring a database to a sencond SQL
> Server with 3 filegroups and files.
> The problem is that when the db is restored to svr2, all of the objects
> that
> were in other file groups wind up in the PRIMARY/default filegroup.
> I'm trying to understand how this is happening, but I haven't actually
> watched the people doing this, but that's how it ends up.
> Any idea of what I am missing?
>
|||Hi,
Can you contact the people who perform the back/restore operations to
clarify how they backup and restore the database? In addition, what is the
result of running "RESTORE FILELISTONLY"?
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "frankm" <frankm@.nospam.postalias>
>Subject: Restore database and filegroups question
>Date: Thu, 10 Mar 2005 12:35:50 -0600
>Lines: 11
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
>Message-ID: <#tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: assocfw1.ms.acxiom.com 206.66.66.1
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.phx.gbl!TK2MSFTNGP1
4.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:381260
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I have a situation that involves restoring a database to a sencond SQL
>Server with 3 filegroups and files.
>The problem is that when the db is restored to svr2, all of the objects
that
>were in other file groups wind up in the PRIMARY/default filegroup.
>I'm trying to understand how this is happening, but I haven't actually
>watched the people doing this, but that's how it ends up.
>Any idea of what I am missing?
>
>
|||FILELISTONLY comes back correctly.
Can a WITH MOVE, move the tables and indexes of other filegroups into
default/primary?
I'm doing some testing to see if I can reproduce this.
I caught this while looking at Queue Lengths in perfmon vs trans/sec on the
db's. The pattern wasn't right.
I found that in the original db that the filegroups were correct.
When I looked at the restored db, all the objects were in PRIMARY/DEFAULT,
although all the files from the source db are there, all the objects have
been moved into PRIMARY/DEFAULT.
This is really messing me up...I didn't know this could happen...
I'm trying to get the script that was used to restore the db....
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:EmyuU0hJFHA.3548@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> Can you contact the people who perform the back/restore operations to
> clarify how they backup and restore the database? In addition, what is the
> result of running "RESTORE FILELISTONLY"?
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
>
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.phx.gbl!TK2MSFTNGP1
> 4.phx.gbl
> that
>
|||Hi Frank,
Would you please list the detailed steps on how you reproduce this issue as
I could not get the same result as yours? Regardless of whether WITH MOVE
is used, a RESTORE statement always produces a database that is identical
to the one that was backed up. The database produced by the RESTORE will
have the same number of files and filegroups, and the files will be of the
same size, and the tables are still placed in the filegroups where they
originally reside. Here are the steps I've performed for testing purpose:
1. Run the following script :
USE master
GO
-- Create the database with the default data
-- filegroup and the log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
( NAME='MyDB_Primary',
FILENAME=
'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_Prm.mdf',
SIZE=4,
MAXSIZE=10,
FILEGROWTH=1),
FILEGROUP MyDB_FG1
( NAME = 'MyDB_FG1_Dat1',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10,
FILEGROWTH=1),
( NAME = 'MyDB_FG1_Dat2',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10,
FILEGROWTH=1)
LOG ON
( NAME='MyDB_log',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB.ldf',
SIZE=1,
MAXSIZE=10,
FILEGROWTH=1)
GO
-- Create a table in the user-defined filegroup.
USE MyDB
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON MyDB_FG1
GO
2. Backup the newly created database and copy the backup file to another
SQL Server box.
3. Restore the database on the destination server.
4. View the properties of MyTable, it is still in the MyDB_FG1 filegroup.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "frankm" <frankm@.nospam.postalias>
>References: <#tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl>
<EmyuU0hJFHA.3548@.TK2MSFTNGXA02.phx.gbl>
>Subject: Re: Restore database and filegroups question
>Date: Mon, 14 Mar 2005 13:10:36 -0600
>Lines: 71
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
>Message-ID: <#rdqBmMKFHA.3340@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: assocfw1.ms.acxiom.com 206.66.66.1
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.phx.gbl!TK2MSFTNGP1
4.phx.gbl[vbcol=seagreen]
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:381637
>X-Tomcat-NG: microsoft.public.sqlserver.server
>FILELISTONLY comes back correctly.
>Can a WITH MOVE, move the tables and indexes of other filegroups into
>default/primary?
>I'm doing some testing to see if I can reproduce this.
>I caught this while looking at Queue Lengths in perfmon vs trans/sec on the
>db's. The pattern wasn't right.
>I found that in the original db that the filegroups were correct.
>When I looked at the restored db, all the objects were in PRIMARY/DEFAULT,
>although all the files from the source db are there, all the objects have
>been moved into PRIMARY/DEFAULT.
>This is really messing me up...I didn't know this could happen...
>I'm trying to get the script that was used to restore the db....
>
>
>"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
>news:EmyuU0hJFHA.3548@.TK2MSFTNGXA02.phx.gbl...
the
>rights.
>TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MS FTNGP08.phx.gbl!TK2MSFTNGP
1
>
>

Restore database and filegroups question

I have a situation that involves restoring a database to a sencond SQL
Server with 3 filegroups and files.
The problem is that when the db is restored to svr2, all of the objects that
were in other file groups wind up in the PRIMARY/default filegroup.
I'm trying to understand how this is happening, but I haven't actually
watched the people doing this, but that's how it ends up.
Any idea of what I am missing?This should not happen.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"frankm" <frankm@.nospam.postalias> wrote in message
news:%23tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl...
>I have a situation that involves restoring a database to a sencond SQL
> Server with 3 filegroups and files.
> The problem is that when the db is restored to svr2, all of the objects
> that
> were in other file groups wind up in the PRIMARY/default filegroup.
> I'm trying to understand how this is happening, but I haven't actually
> watched the people doing this, but that's how it ends up.
> Any idea of what I am missing?
>|||Hi,
Can you contact the people who perform the back/restore operations to
clarify how they backup and restore the database? In addition, what is the
result of running "RESTORE FILELISTONLY"?
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "frankm" <frankm@.nospam.postalias>
>Subject: Restore database and filegroups question
>Date: Thu, 10 Mar 2005 12:35:50 -0600
>Lines: 11
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
>Message-ID: <#tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: assocfw1.ms.acxiom.com 206.66.66.1
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP1
4.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:381260
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I have a situation that involves restoring a database to a sencond SQL
>Server with 3 filegroups and files.
>The problem is that when the db is restored to svr2, all of the objects
that
>were in other file groups wind up in the PRIMARY/default filegroup.
>I'm trying to understand how this is happening, but I haven't actually
>watched the people doing this, but that's how it ends up.
>Any idea of what I am missing?
>
>|||FILELISTONLY comes back correctly.
Can a WITH MOVE, move the tables and indexes of other filegroups into
default/primary?
I'm doing some testing to see if I can reproduce this.
I caught this while looking at Queue Lengths in perfmon vs trans/sec on the
db's. The pattern wasn't right.
I found that in the original db that the filegroups were correct.
When I looked at the restored db, all the objects were in PRIMARY/DEFAULT,
although all the files from the source db are there, all the objects have
been moved into PRIMARY/DEFAULT.
This is really messing me up...I didn't know this could happen...
I'm trying to get the script that was used to restore the db....
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:EmyuU0hJFHA.3548@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> Can you contact the people who perform the back/restore operations to
> clarify how they backup and restore the database? In addition, what is the
> result of running "RESTORE FILELISTONLY"?
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
>
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP1
> 4.phx.gbl
> that
>|||Hi Frank,
Would you please list the detailed steps on how you reproduce this issue as
I could not get the same result as yours? Regardless of whether WITH MOVE
is used, a RESTORE statement always produces a database that is identical
to the one that was backed up. The database produced by the RESTORE will
have the same number of files and filegroups, and the files will be of the
same size, and the tables are still placed in the filegroups where they
originally reside. Here are the steps I've performed for testing purpose:
1. Run the following script :
USE master
GO
-- Create the database with the default data
-- filegroup and the log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
( NAME='MyDB_Primary',
FILENAME=
'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_Prm.mdf',
SIZE=4,
MAXSIZE=10,
FILEGROWTH=1),
FILEGROUP MyDB_FG1
( NAME = 'MyDB_FG1_Dat1',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10,
FILEGROWTH=1),
( NAME = 'MyDB_FG1_Dat2',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10,
FILEGROWTH=1)
LOG ON
( NAME='MyDB_log',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB.ldf',
SIZE=1,
MAXSIZE=10,
FILEGROWTH=1)
GO
-- Create a table in the user-defined filegroup.
USE MyDB
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON MyDB_FG1
GO
2. Backup the newly created database and copy the backup file to another
SQL Server box.
3. Restore the database on the destination server.
4. View the properties of MyTable, it is still in the MyDB_FG1 filegroup.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "frankm" <frankm@.nospam.postalias>
>References: <#tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl>
<EmyuU0hJFHA.3548@.TK2MSFTNGXA02.phx.gbl>
>Subject: Re: Restore database and filegroups question
>Date: Mon, 14 Mar 2005 13:10:36 -0600
>Lines: 71
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
>Message-ID: <#rdqBmMKFHA.3340@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: assocfw1.ms.acxiom.com 206.66.66.1
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP1
4.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:381637
>X-Tomcat-NG: microsoft.public.sqlserver.server
>FILELISTONLY comes back correctly.
>Can a WITH MOVE, move the tables and indexes of other filegroups into
>default/primary?
>I'm doing some testing to see if I can reproduce this.
>I caught this while looking at Queue Lengths in perfmon vs trans/sec on the
>db's. The pattern wasn't right.
>I found that in the original db that the filegroups were correct.
>When I looked at the restored db, all the objects were in PRIMARY/DEFAULT,
>although all the files from the source db are there, all the objects have
>been moved into PRIMARY/DEFAULT.
>This is really messing me up...I didn't know this could happen...
>I'm trying to get the script that was used to restore the db....
>
>
>"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
>news:EmyuU0hJFHA.3548@.TK2MSFTNGXA02.phx.gbl...
the[vbcol=seagreen]
>rights.
>TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP
1
>
>

restore database (Loading) error

I am trying to restore a database from backup (Veritas)
The backup says it is restored successfully, but ....
in enterprise manager the database reads 'Data(loading)'
I tried two different distination names
two different sources.
With the same result.
I tried EXEC sp_delete_backuphistory '07/16/04'
based on something I found on the internet.
I am using SQL2000
Does anybody have any ideas?
Perhaps the Veritas software did not run recovery as the final step? This
will leave your db marked as loading, but you can run recovery manually.
RESTORE LOG mydb WITH RECOVERY
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arniecds" <arniecds@.discussions.microsoft.com> wrote in message
news:113E17E0-6F53-4D9A-809F-F303063CC2F0@.microsoft.com...
> I am trying to restore a database from backup (Veritas)
> The backup says it is restored successfully, but ....
> in enterprise manager the database reads 'Data(loading)'
> I tried two different distination names
> two different sources.
> With the same result.
> I tried EXEC sp_delete_backuphistory '07/16/04'
> based on something I found on the internet.
> I am using SQL2000
> Does anybody have any ideas?
>

Restore and update problem on Windows

Hi,
I have restored a database from a BAK file and tried to run a update query.
While executing the query , i got this error.

Server: Msg 208, Level 16, State 1, Procedure ctsv_992F1918D5FC11D4968C0090271F4533, Line 2
Invalid object name 'MSmerge_contents'.

What can be the possible reasons for the error?
Suggest the solutions if any.Which version of sql server are you using ? Are you using merge replication ?|||Hi,
I am using the MS SQL Server 2000.
About merge replication, i am not aware of this as i am a new user. More over we are restoring the backup that we have, from the restored database we can see all this views and procedures which are used for merge replication.
How can we check that we are using merge replication?

Regards,|||Look under enterprise manager - go to the replication folder - look at publications or subscriptions and you should see entries.|||Are you restoring onto the same sql server instance ? Are you only restoring a particular database ?|||Hi,
There are no entries in the Publications and Subscriptions folder.
We are restoring a particular backup that we got from our client.|||Are you saying then that this is not a backup of the current machine you are working with - it was a backup of another sql server machine ? Or are you restoring on the clients machine ?|||Hi,
Yes , you guessed that correctly.
It's a backup from some other machine (client's db backup) and we are restoring it on our machine for fixing few things.
This original database is not with us and we do not have details of his machine either. We only have the .BAK file.|||Without that information, you are running in the fog. My only recommendation is to try and run sp_mergecleanupmetadata - make sure you read the bol about this stored procedure. The only problem is that you have no replication so this stored procedure may be of no use. Let me know if you have any success with this.

When you say you are fixing a few things - will these fixes go back to the client ?

Restore a DB

Hi,
I've restored a DB with 6.5 GB.
Now the DB is loading over 5 hours!!!
What is the DB doing?
How long could it take?
br
CharlieThat seems a bit long. Have you checked the errorlog? How about using
perfmon.exe to see if CPU or disk is currently busy?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Charlie Schaubmair" <CharlieSchaubmair@.discussions.microsoft.com> wrote in
message news:117C3217-812F-4689-B963-52029F71B161@.microsoft.com...
> Hi,
> I've restored a DB with 6.5 GB.
> Now the DB is loading over 5 hours!!!
> What is the DB doing?
> How long could it take?
> br
> Charlie

Restore a DB

Hi,
I've restored a DB with 6.5 GB.
Now the DB is loading over 5 hours!!!
What is the DB doing?
How long could it take?
br
Charlie
That seems a bit long. Have you checked the errorlog? How about using
perfmon.exe to see if CPU or disk is currently busy?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Charlie Schaubmair" <CharlieSchaubmair@.discussions.microsoft.com> wrote in
message news:117C3217-812F-4689-B963-52029F71B161@.microsoft.com...
> Hi,
> I've restored a DB with 6.5 GB.
> Now the DB is loading over 5 hours!!!
> What is the DB doing?
> How long could it take?
> br
> Charlie

Restore a DB

Hi,
I've restored a DB with 6.5 GB.
Now the DB is loading over 5 hours!!!
What is the DB doing?
How long could it take?
br
CharlieThat seems a bit long. Have you checked the errorlog? How about using
perfmon.exe to see if CPU or disk is currently busy?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Charlie Schaubmair" <CharlieSchaubmair@.discussions.microsoft.com> wrote in
message news:117C3217-812F-4689-B963-52029F71B161@.microsoft.com...
> Hi,
> I've restored a DB with 6.5 GB.
> Now the DB is loading over 5 hours!!!
> What is the DB doing?
> How long could it take?
> br
> Charlie

Restore 6.5 backup file to 7 or 2000

I have a Sql Server database backup file from version 6.5
that I need to restore to either 7 or 2000. Is there any
way to get this DAT file restored to a later version of
SQL?No, you must restore it to a 6.5 db first and then you can upgrade it from
there.
--
Andrew J. Kelly
SQL Server MVP
"T Craig" <tcraig@.cfl.rr.com> wrote in message
news:328e01c373f8$a7076830$a601280a@.phx.gbl...
> I have a Sql Server database backup file from version 6.5
> that I need to restore to either 7 or 2000. Is there any
> way to get this DAT file restored to a later version of
> SQL?

Restore

I've backed-up a database with empty tables and then restored it to a new
database. Everything restored OK, apart from the tables, which are not
created. I'd expect it to create the tables, even though they are empty.
Am I doing something wrong?
Possibly ownership problems and orphaned users. Check out sp_change_users_login in Book sOnline.
Also check if the database owner is orphaned.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"HB" <HB@.discussions.microsoft.com> wrote in message
news:C98EAFC0-13F8-41F8-825D-1F25CA2D7E56@.microsoft.com...
> I've backed-up a database with empty tables and then restored it to a new
> database. Everything restored OK, apart from the tables, which are not
> created. I'd expect it to create the tables, even though they are empty.
> Am I doing something wrong?