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]
Monday, March 12, 2012
Restore production database into development database problem - Ur
Labels:
170gb,
backup,
commandrestore,
database,
filelistonly,
following,
microsoft,
mysql,
oracle,
production,
restore,
restored,
server,
sql,
system
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment