Database restore help

Use Query Analyzer and run RESTORE FILELISTONLY for the backup file. Post
those results here. We'll follow up when we get those.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:523266A9-C34C-47F9-9C22-FC91A9E798E7@.microsoft.com...
I need to restore a database over an existing DB (I have made a backup and
it's SQL 2000).
When I do try and restore it via SQL Enterprose Manager 2000 (the only way I
know) it it says "logical file 'database' is not part of a database
'database2'. Use RESTORE FILELISTONLY to list the logocal file names.
RESTORE DATAVASE is terminating adbnormally.
Type:
use master
go
... before running the RESTORE. Also, be sure that no one is connected to
the DB when you restore it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:863AE2DE-3B3B-4647-8AC6-6BFAE4FFDDEC@.microsoft.com...
Hi I get:
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
> From QA, run:
> RESTORE DATABASE RM_test1
> FROM DISK = 'f:\MSSQL80\BACKUP\RM_test1.bak'
> WITH REPLACE
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:9F74A63C-2524-4AE0-AEAC-9E9853D66B30@.microsoft.com...
> Many thanks:
> RESTORE FILElISTONLY from Disk = 'f:\MSSQL80\BACKUP\RM_test1.bak'
> I get:
> Btest_Data C:\Program Files\Microsoft SQL Server\MSSQL\Data\BRITLIVE.mdf
> D PRIMARY
> Btest_Log C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\BRITLIVE_log.ldf L NULL
>
> On the server we have these DB's:
> Btest
> RM_test1
>
> We sent a company the Btest DB to make some changes that they have done
> and
> sent the bak file back. I need to restore this over the RM_test1 DB, but
> it
> seems that it still references the original Btest DB everywhere.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OUAXLKMtHHA.4572@.TK2MSFTNGP02.phx.gbl...
>
|||Hi
That means that somebody is using the database while you are trying to
restore it. Make sure that nobody is accessing the database - including
the session you are restoring from...:-).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Gonzo wrote:
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||There is still a connection to the db somewhere. SOmetimes EM doesn't show
it. Do a sp_helpdb, get the dbid of the database that you're dealing with, do
a select * from master..sysprocess where dbid = <whatever the dbid is>, get
the coresponding spid and kill <spid>, then try to restore.
MG
"Gonzo" wrote:

> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||You have to keep the logical names for the restore. You can change those
later.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:A4969E2E-1F25-484A-BDE3-91247145CD79@.microsoft.com...
It seems I can restore it using EM (tried on a test server) but only if i
keep the logical names. in E:\Program Files\Microsoft SQL Server\MSSQL\Data
the databse is RM_test1 but I right click on the database and go to
properties and then the tabs Data files and transaction log then the file
name is Btest_data and Btest_logs. Now a database on the live server is
already called 'Btest' will this cause a problem?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
> Type:
> use master
> go
> ... before running the RESTORE. Also, be sure that no one is connected
> to
> the DB when you restore it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:863AE2DE-3B3B-4647-8AC6-6BFAE4FFDDEC@.microsoft.com...
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||Use
Restore Database <db_name>
from Disk = 'f:\MSSQL80\BACKUP\RM_test1.bak'
WITH Move 'Btest_Data' To 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\BRITLIVE_2.mdf',
Move 'Btest_Log' To 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\BRITLIVE_2_log.ldf',
just change the name of the .mdf & .ldf to something that doesn't already
exist.
MG
"Gonzo" wrote:

> It seems I can restore it using EM (tried on a test server) but only if i
> keep the logical names. in E:\Program Files\Microsoft SQL Server\MSSQL\Data
> the databse is RM_test1 but I right click on the database and go to
> properties and then the tabs Data files and transaction log then the file
> name is Btest_data and Btest_logs. Now a database on the live server is
> already called 'Btest' will this cause a problem?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
>
|||Check out ALTER DATABASE in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:%23JzdGUNtHHA.768@.TK2MSFTNGP04.phx.gbl...
I have restored it now, how can I now change the logical names to something
else?
many thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O4i5xSNtHHA.3556@.TK2MSFTNGP05.phx.gbl...
> You have to keep the logical names for the restore. You can change those
> later.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:A4969E2E-1F25-484A-BDE3-91247145CD79@.microsoft.com...
> It seems I can restore it using EM (tried on a test server) but only if i
> keep the logical names. in E:\Program Files\Microsoft SQL
> Server\MSSQL\Data
> the databse is RM_test1 but I right click on the database and go to
> properties and then the tabs Data files and transaction log then the file
> name is Btest_data and Btest_logs. Now a database on the live server is
> already called 'Btest' will this cause a problem?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
>
|||No. Logical names are local to the DB.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:28BF2F6F-4256-4299-BE76-1F1DAAB4D61F@.microsoft.com...
I now get:
Processed 9336 pages for database 'RM_test1', file 'Btest_Data' on file 1.
Processed 1 pages for database 'RM_test1', file 'Btest_Log' on file 1.
RESTORE DATABASE successfully processed 9337 pages in 13.278 seconds (5.760
MB/sec).
There is a database caleld Btest already uses Btest for it's database name
and logical name, will this create a problem with both having the same name?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
> Type:
> use master
> go
> ... before running the RESTORE. Also, be sure that no one is connected
> to
> the DB when you restore it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:863AE2DE-3B3B-4647-8AC6-6BFAE4FFDDEC@.microsoft.com...
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||Yep.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:46D17BBE-E208-4A83-942E-B7446A202C5A@.microsoft.com...
Woudl this be it? I would have to do this for both files I guess (this is
all new to me)
ALTER DATABASE
MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23PPC$WNtHHA.4688@.TK2MSFTNGP05.phx.gbl...
> Check out ALTER DATABASE in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:%23JzdGUNtHHA.768@.TK2MSFTNGP04.phx.gbl...
> I have restored it now, how can I now change the logical names to
> something
> else?
> many thanks
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O4i5xSNtHHA.3556@.TK2MSFTNGP05.phx.gbl...
>
|||Run:
sp_helpfile
...and post the results.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:OKu6WmNtHHA.768@.TK2MSFTNGP04.phx.gbl...
Sorry about this, I'm now getting:
ALTER DATABASE RM_TEST1
MODIFY FILE (Btest_Data = logical_file_name, RM_test1_DATA =
new_logical_name...).
Server: Msg 155, Level 15, State 1, Line 2
'Btest_Data' is not a recognized CREATE/ALTER DATABASE option.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O$$Y4jNtHHA.4824@.TK2MSFTNGP06.phx.gbl...
> Yep.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:46D17BBE-E208-4A83-942E-B7446A202C5A@.microsoft.com...
> Woudl this be it? I would have to do this for both files I guess (this is
> all new to me)
> ALTER DATABASE
> MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23PPC$WNtHHA.4688@.TK2MSFTNGP05.phx.gbl...
>