Restore production database into development database problem - Ur

I restored the production database backup(170GB) onto a development system.
I first tried to do the following command:
restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
Then stopped it after 30 minutes realized that I need to use the MOVE
command because the log and data file have different location on the
development system.
I then initiated the following:
restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
with move 'EEMSuite_model_Data' to
'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
move 'EEMSuite_model_Log' to
'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
replace
It ran for two days and finally came back with the following After two days
of restore process:
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LDF'
does not match the primary file. It may be from a different database or the
log may have been rebuilt previously.
Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' on
file 1.
Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on file
1.
RESTORE DATABASE successfully processed 22290138 pages in 176133.332 seconds
(1.036 MB/sec).
I am concerned about the frist error message but was wondering if that came
from first failed attempt. By looking at the following two messages it looks
like the restore was a successful one?
My user can not access the database and the newly restored user database
does not show up in the Object Browser. What am missing? Do I need a
"recovery" command somewhere? Please help!!
--Ling
After the restore, I also can not see the Permissions under properties for
MYDB. And, I while I tried to open the properties for other database such as
master, it flashes and goes away. Is this a permission problem after
restore? Thanks in advance.
Ling
"Ling" wrote:

> I restored the production database backup(170GB) onto a development system.
> I first tried to do the following command:
> restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
> Then stopped it after 30 minutes realized that I need to use the MOVE
> command because the log and data file have different location on the
> development system.
> I then initiated the following:
> restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
> with move 'EEMSuite_model_Data' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
> move 'EEMSuite_model_Log' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
> replace
> It ran for two days and finally came back with the following After two days
> of restore process:
> Server: Msg 5173, Level 16, State 1, Line 1
> Cannot associate files with different databases.
> Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LDF'
> does not match the primary file. It may be from a different database or the
> log may have been rebuilt previously.
> Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' on
> file 1.
> Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on file
> 1.
> RESTORE DATABASE successfully processed 22290138 pages in 176133.332 seconds
> (1.036 MB/sec).
> I am concerned about the frist error message but was wondering if that came
> from first failed attempt. By looking at the following two messages it looks
> like the restore was a successful one?
> My user can not access the database and the newly restored user database
> does not show up in the Object Browser. What am missing? Do I need a
> "recovery" command somewhere? Please help!!
> --Ling
|||Log in as a system administrator and try to access the database.. It is
common to have user/login mismatches when restoring a database to a
different server..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:5176807C-4D79-4DD2-B074-C9D010E51FB0@.microsoft.com...
> After the restore, I also can not see the Permissions under properties for
> MYDB. And, I while I tried to open the properties for other database such
as[vbcol=seagreen]
> master, it flashes and goes away. Is this a permission problem after
> restore? Thanks in advance.
> Ling
> "Ling" wrote:
system.[vbcol=seagreen]
days[vbcol=seagreen]
Server\MSSQL\data\EEMSuite_log.LDF'[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
file[vbcol=seagreen]
seconds[vbcol=seagreen]
came[vbcol=seagreen]
looks[vbcol=seagreen]
|||I rebooted the server and now I can query the tables within the database.
But, while my user trying to connect, they can not! Please advise how to fix
this problem. Thank you!!
Ling
"Ling" wrote:

> I restored the production database backup(170GB) onto a development system.
> I first tried to do the following command:
> restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
> Then stopped it after 30 minutes realized that I need to use the MOVE
> command because the log and data file have different location on the
> development system.
> I then initiated the following:
> restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
> with move 'EEMSuite_model_Data' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
> move 'EEMSuite_model_Log' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
> replace
> It ran for two days and finally came back with the following After two days
> of restore process:
> Server: Msg 5173, Level 16, State 1, Line 1
> Cannot associate files with different databases.
> Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LDF'
> does not match the primary file. It may be from a different database or the
> log may have been rebuilt previously.
> Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' on
> file 1.
> Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on file
> 1.
> RESTORE DATABASE successfully processed 22290138 pages in 176133.332 seconds
> (1.036 MB/sec).
> I am concerned about the frist error message but was wondering if that came
> from first failed attempt. By looking at the following two messages it looks
> like the restore was a successful one?
> My user can not access the database and the newly restored user database
> does not show up in the Object Browser. What am missing? Do I need a
> "recovery" command somewhere? Please help!!
> --Ling
|||First verify if the logins exist in the new server, then run
sp_change_users_login. See BOL for more details.
Gary
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:5FD532FE-7547-4018-9177-6B2A5D9A693F@.microsoft.com...
> I rebooted the server and now I can query the tables within the database.
> But, while my user trying to connect, they can not! Please advise how to
fix[vbcol=seagreen]
> this problem. Thank you!!
>
> Ling
> "Ling" wrote:
system.[vbcol=seagreen]
days[vbcol=seagreen]
Server\MSSQL\data\EEMSuite_log.LDF'[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
file[vbcol=seagreen]
seconds[vbcol=seagreen]
came[vbcol=seagreen]
looks[vbcol=seagreen]

Restore production database into development database problem - Ur

I restored the production database backup(170GB) onto a development system.
I first tried to do the following command:
restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
Then stopped it after 30 minutes realized that I need to use the MOVE
command because the log and data file have different location on the
development system.
I then initiated the following:
restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
with move 'EEMSuite_model_Data' to
'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
move 'EEMSuite_model_Log' to
'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
replace
It ran for two days and finally came back with the following After two days
of restore process:
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LDF'
does not match the primary file. It may be from a different database or the
log may have been rebuilt previously.
Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' on
file 1.
Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on file
1.
RESTORE DATABASE successfully processed 22290138 pages in 176133.332 seconds
(1.036 MB/sec).
I am concerned about the frist error message but was wondering if that came
from first failed attempt. By looking at the following two messages it look
s
like the restore was a successful one?
My user can not access the database and the newly restored user database
does not show up in the Object Browser. What am missing? Do I need a
"recovery" command somewhere? Please help!!
--LingAfter the restore, I also can not see the Permissions under properties for
MYDB. And, I while I tried to open the properties for other database such a
s
master, it flashes and goes away. Is this a permission problem after
restore? Thanks in advance.
Ling
"Ling" wrote:

> I restored the production database backup(170GB) onto a development system
.
> I first tried to do the following command:
> restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
> Then stopped it after 30 minutes realized that I need to use the MOVE
> command because the log and data file have different location on the
> development system.
> I then initiated the following:
> restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
> with move 'EEMSuite_model_Data' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
> move 'EEMSuite_model_Log' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
> replace
> It ran for two days and finally came back with the following After two day
s
> of restore process:
> Server: Msg 5173, Level 16, State 1, Line 1
> Cannot associate files with different databases.
> Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LD
F'
> does not match the primary file. It may be from a different database or t
he
> log may have been rebuilt previously.
> Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' o
n
> file 1.
> Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on fi
le
> 1.
> RESTORE DATABASE successfully processed 22290138 pages in 176133.332 secon
ds
> (1.036 MB/sec).
> I am concerned about the frist error message but was wondering if that cam
e
> from first failed attempt. By looking at the following two messages it lo
oks
> like the restore was a successful one?
> My user can not access the database and the newly restored user database
> does not show up in the Object Browser. What am missing? Do I need a
> "recovery" command somewhere? Please help!!
> --Ling|||Log in as a system administrator and try to access the database.. It is
common to have user/login mismatches when restoring a database to a
different server..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:5176807C-4D79-4DD2-B074-C9D010E51FB0@.microsoft.com...
> After the restore, I also can not see the Permissions under properties for
> MYDB. And, I while I tried to open the properties for other database such
as[vbcol=seagreen]
> master, it flashes and goes away. Is this a permission problem after
> restore? Thanks in advance.
> Ling
> "Ling" wrote:
>
system.[vbcol=seagreen]
days[vbcol=seagreen]
Server\MSSQL\data\EEMSuite_log.LDF'[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
file[vbcol=seagreen]
seconds[vbcol=seagreen]
came[vbcol=seagreen]
looks[vbcol=seagreen]|||I rebooted the server and now I can query the tables within the database.
But, while my user trying to connect, they can not! Please advise how to fi
x
this problem. Thank you!!
Ling
"Ling" wrote:

> I restored the production database backup(170GB) onto a development system
.
> I first tried to do the following command:
> restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
> Then stopped it after 30 minutes realized that I need to use the MOVE
> command because the log and data file have different location on the
> development system.
> I then initiated the following:
> restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
> with move 'EEMSuite_model_Data' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
> move 'EEMSuite_model_Log' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
> replace
> It ran for two days and finally came back with the following After two day
s
> of restore process:
> Server: Msg 5173, Level 16, State 1, Line 1
> Cannot associate files with different databases.
> Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LD
F'
> does not match the primary file. It may be from a different database or t
he
> log may have been rebuilt previously.
> Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' o
n
> file 1.
> Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on fi
le
> 1.
> RESTORE DATABASE successfully processed 22290138 pages in 176133.332 secon
ds
> (1.036 MB/sec).
> I am concerned about the frist error message but was wondering if that cam
e
> from first failed attempt. By looking at the following two messages it lo
oks
> like the restore was a successful one?
> My user can not access the database and the newly restored user database
> does not show up in the Object Browser. What am missing? Do I need a
> "recovery" command somewhere? Please help!!
> --Ling|||First verify if the logins exist in the new server, then run
sp_change_users_login. See BOL for more details.
Gary
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:5FD532FE-7547-4018-9177-6B2A5D9A693F@.microsoft.com...
> I rebooted the server and now I can query the tables within the database.
> But, while my user trying to connect, they can not! Please advise how to
fix[vbcol=seagreen]
> this problem. Thank you!!
>
> Ling
> "Ling" wrote:
>
system.[vbcol=seagreen]
days[vbcol=seagreen]
Server\MSSQL\data\EEMSuite_log.LDF'[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
file[vbcol=seagreen]
seconds[vbcol=seagreen]
came[vbcol=seagreen]
looks[vbcol=seagreen]

restore MASTER database ( SQL 7.0 sp4)

when I start SQL 7.0 with single user model and restore Master database

sqlservr -c -m

in QA use command:

restore database master from disk='c:\sqldbbackup\master.bak'
with move 'master' to 'e:\mssql7\data\master.mdf',
move 'mastlog' to 'f:\mssql7\log\mastlog.ldf',
replace
go

Get error message:

Server: Msg 3708, Level 16, State 4, Line 1
Cannot drop the database 'master' because it is a system database.
Server: Msg 3166, Level 16, State 1, Line 1
RESTORE DATABASE could not drop database 'master'. Drop the database and then reissue the RESTORE DATABASE statement.

How can I do with this error, try to drop the master db, but fail?

Thanks,

GuyangWhy do you want to restore the MASTER database?
Try REBUILDM utility to do so which is recommended method.|||restore master will keep the login information from previous database.
actually, we are doing a test for moving database from one location to another ( sql 7 database, Windows 2000 server).

when I do the restore, without the "with move" option, it is success.
but if add " with move " option, give me the error messsage above.

RebuildM is alternative option when master db crash, but we want to try restore master way here.

Any suggestion.

Thanks|||Do not attempt to move master database, just restore using RESTORE statement.

Any database users previously associated with logins that need to be re-created are orphaned because the login is lost. To associate an existing database user to a new SQL Server login, see sp_addlogin. To associate an existing database user with a Windows NT user, see sp_grantlogin.