SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
Showing posts with label system. Show all posts
Showing posts with label system. Show all posts
Monday, March 26, 2012
restore to new server causes problems
SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might wan
t to check out
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & use
r db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text report
s no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its st
eps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaroun
d is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sys
servers? There are 3
> columns, srvname, datasource & srvnetname that all have the original serve
r name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select
enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue
to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.sql
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might wan
t to check out
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & use
r db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text report
s no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its st
eps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaroun
d is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sys
servers? There are 3
> columns, srvname, datasource & srvnetname that all have the original serve
r name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select
enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue
to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.sql
restore to new server causes problems
SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
--
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might want to check out
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text reports no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sysservers? There are 3
> columns, srvname, datasource & srvnetname that all have the original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
--
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might want to check out
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text reports no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sysservers? There are 3
> columns, srvname, datasource & srvnetname that all have the original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
Friday, March 23, 2012
restore the replication system
Database A and B are replicated to each other. Both db reside on the same box.
The box has been rebuild due to a system crash. All data base files have
been preserved.
NT SA restored all user databases by attaching the datafiles except
master and msdb. Now, the replication system is out of sync.
Can I restore the master and msdb using attach db to restore the
entire replication system?
Any other better or correct way to restore the entire system when there is a
system crash?
Appreciate any help.
Wen
Wen,
provided you also restore the distribution database this may be possible. It
partly depends on 2 factors:
firstly, is the server name the same as the original one? If not then the
recovery strategy won't work and you'll need to start again - recreate the
publications and initialize.
If the servername is identical, then it may be possible - have a look in BOL
for "replication, backup and restore operations".
The second question is what type of replication are you using? In most cases
it is ok but I'd mention that if the distribution database's backup has been
allowed to get ahead of the publisher's backup and you are using
transactional replication there are likely to be problems.
Anyway, the first port of call is the BOL reference above, and please post
back if you have any issues which arise from that.
Regards,
Paul Ibison
|||Paul.
1. Yes, the servername is the same as the original one. and the
Distribution db
has been restored.
2. We have transactional and merge replication.
I will attache back the master and msdb and see if it works.
Thanks for the help.
Wen
"Paul Ibison" wrote:
> Wen,
> provided you also restore the distribution database this may be possible. It
> partly depends on 2 factors:
> firstly, is the server name the same as the original one? If not then the
> recovery strategy won't work and you'll need to start again - recreate the
> publications and initialize.
> If the servername is identical, then it may be possible - have a look in BOL
> for "replication, backup and restore operations".
> The second question is what type of replication are you using? In most cases
> it is ok but I'd mention that if the distribution database's backup has been
> allowed to get ahead of the publisher's backup and you are using
> transactional replication there are likely to be problems.
> Anyway, the first port of call is the BOL reference above, and please post
> back if you have any issues which arise from that.
> Regards,
> Paul Ibison
>
>
|||Wen,
there should be no problem with the merge database restoration, and you
might want to synchronize with the most upto date subscriber after the
restore.
As for the transactional, there could be issues. Did the publisher have the
sync with backup option set to true? If not, the distribution backup could
be ahead of the publisher restore. After the restore you will get an error
from the Log Reader Agent because it will detect that the Distributor is
ahead of the Publisher. The recommendation is to run sp_replrestart in the
publication database with no parameters. and ensure that that the
distribution agent, which could now deliver duplicate rows to Subscribers,
can continue despite these failures. Choose the -SkipError Distribution
Agent profile, or you can manually add the -SkipError parameter to the
runtime parameters of the Distribution Agents and supply the errors you want
the Distribution Agents to ignore.
You'll need to use linked servers to fix the inconsistencies, or you could
of course resort to reinitializing.
HTH,
Paul Ibison
|||Paul, the sync with backup option is fairly useless unless you are shipping
the transaction logs to a standby server.
The sequence to get this to work is
1) use the sync with backup option on your publication database and possibly
your distribution database
2) restore the msdb, publication database, and distribution database backups
on the standby server with the keep_replication switch.
3) then ship the publication and distribution databases tlogs and without
the keep_replication switch.
4) when the publisher goes offline you rename the standby server with the
name of the original publisher, and then do a sp_replrestart,
and -skiperrors switch.
In the case of this user you can't really use any of these options as they
have not been shipping the database. With a new master database, the user
probably does not have any of the subscribers listed as remote servers and
restoring or attaching any databases will be highly problematic.
The user is best to drop all replication and create publications and
subscriptions from scratch as their data is likely out of sync.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uY4Vt9RfEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Wen,
> there should be no problem with the merge database restoration, and you
> might want to synchronize with the most upto date subscriber after the
> restore.
> As for the transactional, there could be issues. Did the publisher have
the
> sync with backup option set to true? If not, the distribution backup could
> be ahead of the publisher restore. After the restore you will get an error
> from the Log Reader Agent because it will detect that the Distributor is
> ahead of the Publisher. The recommendation is to run sp_replrestart in the
> publication database with no parameters. and ensure that that the
> distribution agent, which could now deliver duplicate rows to Subscribers,
> can continue despite these failures. Choose the -SkipError Distribution
> Agent profile, or you can manually add the -SkipError parameter to the
> runtime parameters of the Distribution Agents and supply the errors you
want
> the Distribution Agents to ignore.
> You'll need to use linked servers to fix the inconsistencies, or you could
> of course resort to reinitializing.
> HTH,
> Paul Ibison
>
|||Hilary,
from a practical point of view I accept your point - log-shipping, the 'sync
with backup' option and keep_replication go well together to minimize the
latency involved if you ship every few minutes.
However in BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
implementation:
- they advise 'keep_replication' for the last log.
- and sp_replrestart for semi-synchronous mode only - ie when the 'sync with
backup' option is false.
Are these just alternative methods?
Regards,
Paul Ibison
|||BOL lies.
Try to restore a tlog with the keep_replication switch.
I have reported this doc bug to Microsoft.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23PFoxqifEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> from a practical point of view I accept your point - log-shipping, the
'sync
> with backup' option and keep_replication go well together to minimize the
> latency involved if you ship every few minutes.
> However in BOL
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
> plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
> implementation:
> - they advise 'keep_replication' for the last log.
> - and sp_replrestart for semi-synchronous mode only - ie when the 'sync
with
> backup' option is false.
> Are these just alternative methods?
> Regards,
> Paul Ibison
>
|||Hilary,
thanks for pointing this out, and for posting up a bonafide working
sequence.
As far as I remember, when I tested this I followed a different methodology
where I used "WITH KEEP_REPLICATION, RECOVERY" on the last transaction log
and this seemed to work. Unfortunately I didn't make a proper note of it and
I really need 3 boxes to repeat the test. Later this week it might be
possible to set it up and I'll post back then.
Regards,
Paul Ibison
The box has been rebuild due to a system crash. All data base files have
been preserved.
NT SA restored all user databases by attaching the datafiles except
master and msdb. Now, the replication system is out of sync.
Can I restore the master and msdb using attach db to restore the
entire replication system?
Any other better or correct way to restore the entire system when there is a
system crash?
Appreciate any help.
Wen
Wen,
provided you also restore the distribution database this may be possible. It
partly depends on 2 factors:
firstly, is the server name the same as the original one? If not then the
recovery strategy won't work and you'll need to start again - recreate the
publications and initialize.
If the servername is identical, then it may be possible - have a look in BOL
for "replication, backup and restore operations".
The second question is what type of replication are you using? In most cases
it is ok but I'd mention that if the distribution database's backup has been
allowed to get ahead of the publisher's backup and you are using
transactional replication there are likely to be problems.
Anyway, the first port of call is the BOL reference above, and please post
back if you have any issues which arise from that.
Regards,
Paul Ibison
|||Paul.
1. Yes, the servername is the same as the original one. and the
Distribution db
has been restored.
2. We have transactional and merge replication.
I will attache back the master and msdb and see if it works.
Thanks for the help.
Wen
"Paul Ibison" wrote:
> Wen,
> provided you also restore the distribution database this may be possible. It
> partly depends on 2 factors:
> firstly, is the server name the same as the original one? If not then the
> recovery strategy won't work and you'll need to start again - recreate the
> publications and initialize.
> If the servername is identical, then it may be possible - have a look in BOL
> for "replication, backup and restore operations".
> The second question is what type of replication are you using? In most cases
> it is ok but I'd mention that if the distribution database's backup has been
> allowed to get ahead of the publisher's backup and you are using
> transactional replication there are likely to be problems.
> Anyway, the first port of call is the BOL reference above, and please post
> back if you have any issues which arise from that.
> Regards,
> Paul Ibison
>
>
|||Wen,
there should be no problem with the merge database restoration, and you
might want to synchronize with the most upto date subscriber after the
restore.
As for the transactional, there could be issues. Did the publisher have the
sync with backup option set to true? If not, the distribution backup could
be ahead of the publisher restore. After the restore you will get an error
from the Log Reader Agent because it will detect that the Distributor is
ahead of the Publisher. The recommendation is to run sp_replrestart in the
publication database with no parameters. and ensure that that the
distribution agent, which could now deliver duplicate rows to Subscribers,
can continue despite these failures. Choose the -SkipError Distribution
Agent profile, or you can manually add the -SkipError parameter to the
runtime parameters of the Distribution Agents and supply the errors you want
the Distribution Agents to ignore.
You'll need to use linked servers to fix the inconsistencies, or you could
of course resort to reinitializing.
HTH,
Paul Ibison
|||Paul, the sync with backup option is fairly useless unless you are shipping
the transaction logs to a standby server.
The sequence to get this to work is
1) use the sync with backup option on your publication database and possibly
your distribution database
2) restore the msdb, publication database, and distribution database backups
on the standby server with the keep_replication switch.
3) then ship the publication and distribution databases tlogs and without
the keep_replication switch.
4) when the publisher goes offline you rename the standby server with the
name of the original publisher, and then do a sp_replrestart,
and -skiperrors switch.
In the case of this user you can't really use any of these options as they
have not been shipping the database. With a new master database, the user
probably does not have any of the subscribers listed as remote servers and
restoring or attaching any databases will be highly problematic.
The user is best to drop all replication and create publications and
subscriptions from scratch as their data is likely out of sync.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uY4Vt9RfEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Wen,
> there should be no problem with the merge database restoration, and you
> might want to synchronize with the most upto date subscriber after the
> restore.
> As for the transactional, there could be issues. Did the publisher have
the
> sync with backup option set to true? If not, the distribution backup could
> be ahead of the publisher restore. After the restore you will get an error
> from the Log Reader Agent because it will detect that the Distributor is
> ahead of the Publisher. The recommendation is to run sp_replrestart in the
> publication database with no parameters. and ensure that that the
> distribution agent, which could now deliver duplicate rows to Subscribers,
> can continue despite these failures. Choose the -SkipError Distribution
> Agent profile, or you can manually add the -SkipError parameter to the
> runtime parameters of the Distribution Agents and supply the errors you
want
> the Distribution Agents to ignore.
> You'll need to use linked servers to fix the inconsistencies, or you could
> of course resort to reinitializing.
> HTH,
> Paul Ibison
>
|||Hilary,
from a practical point of view I accept your point - log-shipping, the 'sync
with backup' option and keep_replication go well together to minimize the
latency involved if you ship every few minutes.
However in BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
implementation:
- they advise 'keep_replication' for the last log.
- and sp_replrestart for semi-synchronous mode only - ie when the 'sync with
backup' option is false.
Are these just alternative methods?
Regards,
Paul Ibison
|||BOL lies.
Try to restore a tlog with the keep_replication switch.
I have reported this doc bug to Microsoft.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23PFoxqifEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> from a practical point of view I accept your point - log-shipping, the
'sync
> with backup' option and keep_replication go well together to minimize the
> latency involved if you ship every few minutes.
> However in BOL
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
> plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
> implementation:
> - they advise 'keep_replication' for the last log.
> - and sp_replrestart for semi-synchronous mode only - ie when the 'sync
with
> backup' option is false.
> Are these just alternative methods?
> Regards,
> Paul Ibison
>
|||Hilary,
thanks for pointing this out, and for posting up a bonafide working
sequence.
As far as I remember, when I tested this I followed a different methodology
where I used "WITH KEEP_REPLICATION, RECOVERY" on the last transaction log
and this seemed to work. Unfortunately I didn't make a proper note of it and
I really need 3 boxes to repeat the test. Later this week it might be
possible to set it up and I'll post back then.
Regards,
Paul Ibison
Restore system dbs from SP3 to SP3a?
Can system databases (master, model, msdb) backed up from SQL2k SP3 be restored to an instance of SP3a? Can they be restored from SP3a to SP3? TIA!Not going to happen buddy. try installing a fresh copy of sql server somewhere else upgrade to sp3, restore your system db's there, upgrade to sp3a then move the system db's to the live server. hope this helps.|||Thanks - that's what I thought. However, how can it be determined which SP (3 or 3a) a given instance is running? @.@.version returns 8.00.760 for both of them, & Properties in EM show SP3 for both. I remember there being a way to find the build number, but I don't know how to get it. TIA.|||Check with MS website at the SP3/SP3a download pages. They usually have a section that tells you how to determine which SP you're currently running under.|||Even better: http://www.sqlteam.com/item.asp?ItemID=8318
restore system databases to server with different/alternate data p
I finally figured out how to do this when the source server has its master,
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source who’s data
directory path is different from this computer, use the following procedure:
1.WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
oOpen enterprise manager
oRestore the model database from the source server
oRestore the msdb database from the source server
2.Stop the MSSQL service.
3.Go to Start menu and open a command prompt.
4.Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5.Start sql server with: sqlservr –m –c -f (this brings the system up in
Single User Mode; if you add -T3608 (don’t use a lowercase t) so SQL Server
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6.Go to Start menu and open a second command prompt.
7.Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8.Type (case sensitive): osql –S serverName -E
oThis logs in to the specified server and logs in with the Windows user
currently logged in.
oYou should see a command prompt that looks like: >1
9.Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_bk.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the ‘with move’ part is necessary because the location of the files is
changing)
go
oWhen that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10.to move the model database, start sql server with sqlservr -m -c -f -T3608
11.in the other window, run osql –S serverName –E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12.Stop the server with Ctrl+C
13.Restart the server with sqlservr -m -c –f
14.in the other window, run osql –S serverName –E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename =
'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename =
'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file after
restarting SQL Server.}
exit
oyou will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15.Stop the server with Ctrl+C
oYou should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16.start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
oNote: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location.
This can easily be done with Enterprise Manager (assuming it doesn’t hang up
or take forever for the restore database dialog box to come up, which usually
is the case, actually).
I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source who’s data
directory path is different from this computer, use the following procedure:
1.WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
oOpen enterprise manager
oRestore the model database from the source server
oRestore the msdb database from the source server
2.Stop the MSSQL service.
3.Go to Start menu and open a command prompt.
4.Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5.Start sql server with: sqlservr –m –c -f (this brings the system up in
Single User Mode; if you add -T3608 (don’t use a lowercase t) so SQL Server
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6.Go to Start menu and open a second command prompt.
7.Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8.Type (case sensitive): osql –S serverName -E
oThis logs in to the specified server and logs in with the Windows user
currently logged in.
oYou should see a command prompt that looks like: >1
9.Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_bk.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the ‘with move’ part is necessary because the location of the files is
changing)
go
oWhen that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10.to move the model database, start sql server with sqlservr -m -c -f -T3608
11.in the other window, run osql –S serverName –E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12.Stop the server with Ctrl+C
13.Restart the server with sqlservr -m -c –f
14.in the other window, run osql –S serverName –E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename =
'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename =
'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file after
restarting SQL Server.}
exit
oyou will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15.Stop the server with Ctrl+C
oYou should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16.start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
oNote: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location.
This can easily be done with Enterprise Manager (assuming it doesn’t hang up
or take forever for the restore database dialog box to come up, which usually
is the case, actually).
I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
restore system databases to server with different/alternate data p
I finally figured out how to do this when the source server has its master,
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source who’s data
directory path is different from this computer, use the following procedure:
1. WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
o Open enterprise manager
o Restore the model database from the source server
o Restore the msdb database from the source server
2. Stop the MSSQL service.
3. Go to Start menu and open a command prompt.
4. Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5. Start sql server with: sqlservr –m –c -f (this brings the system up i
n
Single User Mode; if you add -T3608 (don’t use a lowercase t) so SQL Serve
r
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6. Go to Start menu and open a second command prompt.
7. Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8. Type (case sensitive): osql –S serverName -E
o This logs in to the specified server and logs in with the Windows user
currently logged in.
o You should see a command prompt that looks like: >1
9. Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_b
k.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the ‘with move’ part is necessary because the location of the files is
changing)
go
o When that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10. to move the model database, start sql server with sqlservr -m -c -f -T36
08
11. in the other window, run osql –S serverName –E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12. Stop the server with Ctrl+C
13. Restart the server with sqlservr -m -c –f
14. in the other window, run osql –S serverName –E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename =
'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file
after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename =
'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file
after
restarting SQL Server.}
exit
o you will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15. Stop the server with Ctrl+C
o You should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16. start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
o Note: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location
.
This can easily be done with Enterprise Manager (assuming it doesn’t hang
up
or take forever for the restore database dialog box to come up, which usuall
y
is the case, actually).I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source who’s data
directory path is different from this computer, use the following procedure:
1. WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
o Open enterprise manager
o Restore the model database from the source server
o Restore the msdb database from the source server
2. Stop the MSSQL service.
3. Go to Start menu and open a command prompt.
4. Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5. Start sql server with: sqlservr –m –c -f (this brings the system up i
n
Single User Mode; if you add -T3608 (don’t use a lowercase t) so SQL Serve
r
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6. Go to Start menu and open a second command prompt.
7. Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8. Type (case sensitive): osql –S serverName -E
o This logs in to the specified server and logs in with the Windows user
currently logged in.
o You should see a command prompt that looks like: >1
9. Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_b
k.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the ‘with move’ part is necessary because the location of the files is
changing)
go
o When that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10. to move the model database, start sql server with sqlservr -m -c -f -T36
08
11. in the other window, run osql –S serverName –E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12. Stop the server with Ctrl+C
13. Restart the server with sqlservr -m -c –f
14. in the other window, run osql –S serverName –E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename =
'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file
after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename =
'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file
after
restarting SQL Server.}
exit
o you will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15. Stop the server with Ctrl+C
o You should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16. start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
o Note: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location
.
This can easily be done with Enterprise Manager (assuming it doesn’t hang
up
or take forever for the restore database dialog box to come up, which usuall
y
is the case, actually).I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
restore system databases to server with different/alternate data p
I finally figured out how to do this when the source server has its master,
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source whoâ's data
directory path is different from this computer, use the following procedure:
1. WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
o Open enterprise manager
o Restore the model database from the source server
o Restore the msdb database from the source server
2. Stop the MSSQL service.
3. Go to Start menu and open a command prompt.
4. Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5. Start sql server with: sqlservr â'm â'c -f (this brings the system up in
Single User Mode; if you add -T3608 (donâ't use a lowercase t) so SQL Server
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6. Go to Start menu and open a second command prompt.
7. Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8. Type (case sensitive): osql â'S serverName -E
o This logs in to the specified server and logs in with the Windows user
currently logged in.
o You should see a command prompt that looks like: >1
9. Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_bk.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the â'with moveâ' part is necessary because the location of the files is
changing)
go
o When that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10. to move the model database, start sql server with sqlservr -m -c -f -T3608
11. in the other window, run osql â'S serverName â'E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12. Stop the server with Ctrl+C
13. Restart the server with sqlservr -m -c â'f
14. in the other window, run osql â'S serverName â'E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename = 'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file after
restarting SQL Server.}
exit
o you will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15. Stop the server with Ctrl+C
o You should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16. start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
o Note: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location.
This can easily be done with Enterprise Manager (assuming it doesnâ't hang up
or take forever for the restore database dialog box to come up, which usually
is the case, actually).I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source whoâ's data
directory path is different from this computer, use the following procedure:
1. WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
o Open enterprise manager
o Restore the model database from the source server
o Restore the msdb database from the source server
2. Stop the MSSQL service.
3. Go to Start menu and open a command prompt.
4. Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5. Start sql server with: sqlservr â'm â'c -f (this brings the system up in
Single User Mode; if you add -T3608 (donâ't use a lowercase t) so SQL Server
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6. Go to Start menu and open a second command prompt.
7. Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8. Type (case sensitive): osql â'S serverName -E
o This logs in to the specified server and logs in with the Windows user
currently logged in.
o You should see a command prompt that looks like: >1
9. Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_bk.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the â'with moveâ' part is necessary because the location of the files is
changing)
go
o When that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10. to move the model database, start sql server with sqlservr -m -c -f -T3608
11. in the other window, run osql â'S serverName â'E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12. Stop the server with Ctrl+C
13. Restart the server with sqlservr -m -c â'f
14. in the other window, run osql â'S serverName â'E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename = 'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file after
restarting SQL Server.}
exit
o you will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15. Stop the server with Ctrl+C
o You should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16. start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
o Note: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location.
This can easily be done with Enterprise Manager (assuming it doesnâ't hang up
or take forever for the restore database dialog box to come up, which usually
is the case, actually).I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
Restore sys databases
Am i able to restore system databases to sql2005 from sql2k SP4 backup
dumps?
Thanks
No.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ud1tSqdUGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Am i able to restore system databases to sql2005 from sql2k SP4 backup
> dumps?
> Thanks
>
|||thanks, Geoff. you saved me a lot of time searching it
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ud1tSqdUGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Am i able to restore system databases to sql2005 from sql2k SP4 backup
> dumps?
> Thanks
>
sql
dumps?
Thanks
No.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ud1tSqdUGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Am i able to restore system databases to sql2005 from sql2k SP4 backup
> dumps?
> Thanks
>
|||thanks, Geoff. you saved me a lot of time searching it
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ud1tSqdUGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Am i able to restore system databases to sql2005 from sql2k SP4 backup
> dumps?
> Thanks
>
sql
Monday, March 12, 2012
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]
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 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!!
--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 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
> 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|||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
> 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
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!!
--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 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
> 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|||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
> 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
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]
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]
Friday, March 9, 2012
Restore of System databases... in earlier to SQL 2K versions....
Restoring of Master/MSDB databases used in SQL 2K is a new feature in SQL2K?
How did earlier versions of SQL Server 2K use to restore the system
databases?
http://support.microsoft.com/default...b;en-us;169039
As per the above link it seems to be there is no trace of restoring the
MASTER or MSDB databases as explained in SQL 2K documentation.
Thank you for your comment,
Allen
Restore of the system databases has worked the same way across all versions of SQL Server (well,
msdb was introduced in 6.0).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:uhvZCEFqFHA.3160@.TK2MSFTNGP14.phx.gbl...
>
> Restoring of Master/MSDB databases used in SQL 2K is a new feature in SQL2K?
> How did earlier versions of SQL Server 2K use to restore the system
> databases?
> http://support.microsoft.com/default...b;en-us;169039
> As per the above link it seems to be there is no trace of restoring the
> MASTER or MSDB databases as explained in SQL 2K documentation.
> Thank you for your comment,
> Allen
>
>
>
How did earlier versions of SQL Server 2K use to restore the system
databases?
http://support.microsoft.com/default...b;en-us;169039
As per the above link it seems to be there is no trace of restoring the
MASTER or MSDB databases as explained in SQL 2K documentation.
Thank you for your comment,
Allen
Restore of the system databases has worked the same way across all versions of SQL Server (well,
msdb was introduced in 6.0).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:uhvZCEFqFHA.3160@.TK2MSFTNGP14.phx.gbl...
>
> Restoring of Master/MSDB databases used in SQL 2K is a new feature in SQL2K?
> How did earlier versions of SQL Server 2K use to restore the system
> databases?
> http://support.microsoft.com/default...b;en-us;169039
> As per the above link it seems to be there is no trace of restoring the
> MASTER or MSDB databases as explained in SQL 2K documentation.
> Thank you for your comment,
> Allen
>
>
>
Restore of System databases... in earlier to SQL 2K versions....
Restoring of Master/MSDB databases used in SQL 2K is a new feature in SQL2K?
How did earlier versions of SQL Server 2K use to restore the system
databases?
http://support.microsoft.com/default.aspx?scid=kb;en-us;169039
As per the above link it seems to be there is no trace of restoring the
MASTER or MSDB databases as explained in SQL 2K documentation.
Thank you for your comment,
AllenRestore of the system databases has worked the same way across all versions of SQL Server (well,
msdb was introduced in 6.0).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:uhvZCEFqFHA.3160@.TK2MSFTNGP14.phx.gbl...
>
> Restoring of Master/MSDB databases used in SQL 2K is a new feature in SQL2K?
> How did earlier versions of SQL Server 2K use to restore the system
> databases?
> http://support.microsoft.com/default.aspx?scid=kb;en-us;169039
> As per the above link it seems to be there is no trace of restoring the
> MASTER or MSDB databases as explained in SQL 2K documentation.
> Thank you for your comment,
> Allen
>
>
>
How did earlier versions of SQL Server 2K use to restore the system
databases?
http://support.microsoft.com/default.aspx?scid=kb;en-us;169039
As per the above link it seems to be there is no trace of restoring the
MASTER or MSDB databases as explained in SQL 2K documentation.
Thank you for your comment,
AllenRestore of the system databases has worked the same way across all versions of SQL Server (well,
msdb was introduced in 6.0).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:uhvZCEFqFHA.3160@.TK2MSFTNGP14.phx.gbl...
>
> Restoring of Master/MSDB databases used in SQL 2K is a new feature in SQL2K?
> How did earlier versions of SQL Server 2K use to restore the system
> databases?
> http://support.microsoft.com/default.aspx?scid=kb;en-us;169039
> As per the above link it seems to be there is no trace of restoring the
> MASTER or MSDB databases as explained in SQL 2K documentation.
> Thank you for your comment,
> Allen
>
>
>
Restore of System databases... in earlier to SQL 2K versions....
Restoring of Master/MSDB databases used in SQL 2K is a new feature in SQL2K?
How did earlier versions of SQL Server 2K use to restore the system
databases?
http://support.microsoft.com/defaul...kb;en-us;169039
As per the above link it seems to be there is no trace of restoring the
MASTER or MSDB databases as explained in SQL 2K documentation.
Thank you for your comment,
AllenRestore of the system databases has worked the same way across all versions
of SQL Server (well,
msdb was introduced in 6.0).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:uhvZCEFqFHA.3160@.TK2MSFTNGP14.phx.gbl...
>
> Restoring of Master/MSDB databases used in SQL 2K is a new feature in SQL2
K?
> How did earlier versions of SQL Server 2K use to restore the system
> databases?
> http://support.microsoft.com/defaul...kb;en-us;169039
> As per the above link it seems to be there is no trace of restoring the
> MASTER or MSDB databases as explained in SQL 2K documentation.
> Thank you for your comment,
> Allen
>
>
>
How did earlier versions of SQL Server 2K use to restore the system
databases?
http://support.microsoft.com/defaul...kb;en-us;169039
As per the above link it seems to be there is no trace of restoring the
MASTER or MSDB databases as explained in SQL 2K documentation.
Thank you for your comment,
AllenRestore of the system databases has worked the same way across all versions
of SQL Server (well,
msdb was introduced in 6.0).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:uhvZCEFqFHA.3160@.TK2MSFTNGP14.phx.gbl...
>
> Restoring of Master/MSDB databases used in SQL 2K is a new feature in SQL2
K?
> How did earlier versions of SQL Server 2K use to restore the system
> databases?
> http://support.microsoft.com/defaul...kb;en-us;169039
> As per the above link it seems to be there is no trace of restoring the
> MASTER or MSDB databases as explained in SQL 2K documentation.
> Thank you for your comment,
> Allen
>
>
>
Saturday, February 25, 2012
Restore msdb file
I had a problem with operating system so copy copied all of my database
backup files to another. I reinstalled the operating system and restored al
l
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Star
t by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored
all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>
backup files to another. I reinstalled the operating system and restored al
l
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Star
t by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored
all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>
Restore msdb file
I had a problem with operating system so copy copied all of my database
backup files to another. I reinstalled the operating system and restored all
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Start by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>
backup files to another. I reinstalled the operating system and restored all
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Start by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>
Restore mdf after system crash in sqls2k
During a system crash I was able to manually copy my .mdf but not the log.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?
Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?
|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:
> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
>
>
|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?
|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?
Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?
|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:
> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
>
>
|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?
|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.
Restore mdf after system crash in sqls2k
During a system crash I was able to manually copy my .mdf but not the log.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
--
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:
> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> > During a system crash I was able to manually copy my .mdf but not the log.
> > After restoring the system sql runs an older version of the file, which i
> > want to replace with the copied mdf. Sql manager denies attaching the file
> > because it doesn't mach the log-file.
> > Is there a way of resetting the database or any other way for me to
> > restore
> > the .mdf without dataloss?
>
>|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
--
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:
> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> > During a system crash I was able to manually copy my .mdf but not the log.
> > After restoring the system sql runs an older version of the file, which i
> > want to replace with the copied mdf. Sql manager denies attaching the file
> > because it doesn't mach the log-file.
> > Is there a way of resetting the database or any other way for me to
> > restore
> > the .mdf without dataloss?
>
>|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.
Restore master on an another disk !
Hi,
I have 2 servers SQL7SP3 on W4SerSP6a. On my first server all the system db (master, model, etc) are on the unit C:.
I want restore master on the unit D: on the second server. The restore with the -m option successful, but the restart of sqlserver failed because it can't find master, model etc in C:\mssql7\data.
How can I do to force SQL to start on the D:\mssaql7\data ?
thxQ1 How can I do to force SQL to start on the D:\mssaql7\data ?
A1 For a temp fix, use service startup parameters (in Sql 2k, and probably also 7 this would be -lD:\mssaql7\data\Mastlog.ldf -dlD:\mssaql7\data\Master.mdf)
A2 You may edit the paths in your server's registry for a permanent fix.
I have 2 servers SQL7SP3 on W4SerSP6a. On my first server all the system db (master, model, etc) are on the unit C:.
I want restore master on the unit D: on the second server. The restore with the -m option successful, but the restart of sqlserver failed because it can't find master, model etc in C:\mssql7\data.
How can I do to force SQL to start on the D:\mssaql7\data ?
thxQ1 How can I do to force SQL to start on the D:\mssaql7\data ?
A1 For a temp fix, use service startup parameters (in Sql 2k, and probably also 7 this would be -lD:\mssaql7\data\Mastlog.ldf -dlD:\mssaql7\data\Master.mdf)
A2 You may edit the paths in your server's registry for a permanent fix.
Subscribe to:
Posts (Atom)