Because of a corrupt mainboard we tried to restore our SQL Server 7
databases to a new computer but get the same error for all backups we
try to restore:
---
ODBC: Nachr.-Nr. 0, Schweregrad 19, Status 1
SqlDumpExceptionHandler: Prozess 21 erzeugte eine schwere
Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION.Sql Server
beendet diesen Prozess.
---
The Windows event log shows an event ID 17052. What can be wrong?I check out www.eventid.net for this issue and found something about it and
wanted to share with you. Maybe you can find something useful.
From www.EventId.net
Adrian Grigorof (Last update 1/4/2005):
This appears to be a generic event that can be recorded by the MS SQL Server
service for a wide range of problems. See the information for various errors
(ordered by the error number).
Error: 0, Description: SqlDumpExceptionHandler: Process 10 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating
this process. - This type of error indicates a bug in one of the MS SQL
Server binaries. Installation of the latest service pack recommended. If
that does not fix it then call Microsoft Product support. Some newsgroup
postings suggest that this may occur due to lack of space on drive
containing the SQL logs
Event ID: 17052
Source MSSQLSERVER
Type Error
Description You do not have sufficient operating system permission to open
the SQLServerAgent status event.
Comments Adrian Grigorof (Last update 7/28/2004):
From a newsgroup post: "Installing SP3 or SP3a on MS SQL Server 2000
Enterprise Edition caused this problem in my case (MSSQLServer service is
running under different local user account than the SqlServerAgent service
and MSSQLServer is not given Administrator privileges). I see this buggy
behavior on various installations: W2K Advanced Server SP4 or W2K3. SP3 or
SP3a. Default instance or named instance. New freshly installed server or
old one. The only condition is that the MSSQLServer and SQLServerAgent
services run with different user accounts and the MSSQLServer
one does not have admin rights. Before SP3 installation everything runs OK.
What helps: Add MSSQLServer service account to Administrators group. OR Run
MSSQLServer and SQLServerAgent with the same account.
What doesn't help: Changing the services' user accounts using Enterprise
Manager. Add services' accounts to Power Users."
An answer from a MS engineer in a newsgroup post: "I suspect that the issue
is addressed in the cross-database ownership chain which uses in Microsoft
SQL Server Service Pack 3 (SP3). Based on my research, SP3 provides a new
security enhancement related option for configuring cross-database ownership
chaining; enable cross-database ownership chaining for all databases during
setup. With this new option, you can control whether or not you permit
cross-database ownership chaining. By default, this option is disabled.
Microsoft recommends that you use the default option, because it makes your
database server more secure.
However, database objects may have different owners. When an object such as
a view, a stored procedure, or a user-defined function references another
object, an ownership chain can be established. When the ownership chain is
unbroken, SQL Server checks permissions on the source object but not on the
target objects.
Therefore, it is recommended that you change the configuration of the
cross-database ownership chaining. You can reference the following
step-by-step article to perform such a change: M810474 (Cross-Database
Ownership Chaining Behavior Changes in SQL Server).
Additionally, I also suggest you running the MSSQLServer service with a
local account and
SQLServerAgent with Admin privilege to see if the jobs work fine. Otherwise,
it seems that you
should run these both services with the FULL privileges."
From a newsgroup post: "This is most likely a permissions issue. Make sure
the account SQLAgent starting under has permissions to:
- Replace A process level token
- Increase Quotas
- Logon as a service.
Books online states that the account needs to be a member of the local
admins group as well."
Anonymous (Last update 7/28/2004):
I got this error after I have created different accounts for sqlserver and
sqlagent service.
Ekrem Önsoy
"dentaku" <dentaku@.web.de> wrote in message
news:1189672135.231927.120740@.d55g2000hsg.googlegroups.com...
> Because of a corrupt mainboard we tried to restore our SQL Server 7
> databases to a new computer but get the same error for all backups we
> try to restore:
> ---
> ODBC: Nachr.-Nr. 0, Schweregrad 19, Status 1
> SqlDumpExceptionHandler: Prozess 21 erzeugte eine schwere
> Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION.Sql Server
> beendet diesen Prozess.
> ---
> The Windows event log shows an event ID 17052. What can be wrong?
>|||We have plenty of free disk and memory space (multiple GBs). We also
have the latest microsoft service packs and updates. And our DB user
is an admin (DBO rights didn't help either).
All restore verifyonly/filelistonly/headeronly requests returned no
error. But every time I try to restore the database (file) I get this
error and the sql error log files says
Bypassing recovery for database 'mine' because it is marked IN LOAD.
I can delete this database, and restore again, but with always the
same error.
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Friday, March 30, 2012
Restore won't open
I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
Ted
Possibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
sql
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
Ted
Possibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
sql
Restore won't open
I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPris
e
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the
backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time t
o run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPri
se
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER
to
> stop it. If I do this for any of the sample databases, the Restore dialogu
e
> opens. I even verified there were no open connections to the 2 databases i
n
> question, & it happens on both servers. I had to do a command line restore
.
> The backup, etc works fine, just the restore won't open. It did this on SQ
L
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec service
s,
> Trend Micro AV, etc.
> Ted
e
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the
backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time t
o run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPri
se
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER
to
> stop it. If I do this for any of the sample databases, the Restore dialogu
e
> opens. I even verified there were no open connections to the 2 databases i
n
> question, & it happens on both servers. I had to do a command line restore
.
> The backup, etc works fine, just the restore won't open. It did this on SQ
L
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec service
s,
> Trend Micro AV, etc.
> Ted
Restore won't open
I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
Wednesday, March 28, 2012
Restore with Percent free space
Usually you have huge databases in production with atleast 50-60% free space
....buth when you want to restore the db in dev...you need to have similar
storage size as in prod. Would it be nice if we have a option like
"FreeSpacePercent" parameter in the restore command where we can mention how
much free space is good enough.
For Example, if a DB is 100 GB size, with only 40% of data and 60% free
space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
all I need is 45GB instead of 100 GB in my dev environment to restore the db?
Makes sense ?
Thanks,
Ranga
Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
"Tibor Karaszi" wrote:
> The request makes sense, but the implementation would have huge ramifications on the restore
> process.
> Consider a database file in which you have pages. Any page can be in use, and any page in there can
> have the address for this page in the page header (for instance). The restore process cannot
> "compact" these pages to move them towards the beginning of the file, because of the linkage etc
> between pages. Sure, it might be physically possible, but that restore would be much much slower.
> And it might not even be possible at all, since recovery is part of the restore process and physical
> page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
> Perhaps is it possible to have two types of restore: what we have today and a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
>
....buth when you want to restore the db in dev...you need to have similar
storage size as in prod. Would it be nice if we have a option like
"FreeSpacePercent" parameter in the restore command where we can mention how
much free space is good enough.
For Example, if a DB is 100 GB size, with only 40% of data and 60% free
space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
all I need is 45GB instead of 100 GB in my dev environment to restore the db?
Makes sense ?
Thanks,
Ranga
Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
"Tibor Karaszi" wrote:
> The request makes sense, but the implementation would have huge ramifications on the restore
> process.
> Consider a database file in which you have pages. Any page can be in use, and any page in there can
> have the address for this page in the page header (for instance). The restore process cannot
> "compact" these pages to move them towards the beginning of the file, because of the linkage etc
> between pages. Sure, it might be physically possible, but that restore would be much much slower.
> And it might not even be possible at all, since recovery is part of the restore process and physical
> page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
> Perhaps is it possible to have two types of restore: what we have today and a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
>
Restore with Percent free space
Usually you have huge databases in production with atleast 50-60% free space
...buth when you want to restore the db in dev...you need to have similar
storage size as in prod. Would it be nice if we have a option like
"FreeSpacePercent" parameter in the restore command where we can mention how
much free space is good enough.
For Example, if a DB is 100 GB size, with only 40% of data and 60% free
space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
all I need is 45GB instead of 100 GB in my dev environment to restore the db
?
Makes sense '
Thanks,
Ranga> Makes sense '
The request makes sense, but the implementation would have huge ramification
s on the restore
process.
Consider a database file in which you have pages. Any page can be in use, an
d any page in there can
have the address for this page in the page header (for instance). The restor
e process cannot
"compact" these pages to move them towards the beginning of the file, becaus
e of the linkage etc
between pages. Sure, it might be physically possible, but that restore would
be much much slower.
And it might not even be possible at all, since recovery is part of the rest
ore process and physical
page addresses might be in the transaction log records (might be, I'm not 10
0% certain of this).
Perhaps is it possible to have two types of restore: what we have today and
a "compact-and-restore".
You might want to vent such a request at http://connect.microsoft.com/site/s...aspx?SiteID=68.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
> Usually you have huge databases in production with atleast 50-60% free spa
ce
> ...buth when you want to restore the db in dev...you need to have simila
r
> storage size as in prod. Would it be nice if we have a option like
> "FreeSpacePercent" parameter in the restore command where we can mention h
ow
> much free space is good enough.
> For Example, if a DB is 100 GB size, with only 40% of data and 60% free
> space. I should be able to restore the DB using "FreeSpacePercent" = 5%, S
o,
> all I need is 45GB instead of 100 GB in my dev environment to restore the
db?
> Makes sense '
> Thanks,
> Ranga|||Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLSe...=1252
87
"Tibor Karaszi" wrote:
> The request makes sense, but the implementation would have huge ramificati
ons on the restore
> process.
> Consider a database file in which you have pages. Any page can be in use,
and any page in there can
> have the address for this page in the page header (for instance). The rest
ore process cannot
> "compact" these pages to move them towards the beginning of the file, beca
use of the linkage etc
> between pages. Sure, it might be physically possible, but that restore wou
ld be much much slower.
> And it might not even be possible at all, since recovery is part of the re
store process and physical
> page addresses might be in the transaction log records (might be, I'm not
100% certain of this).
> Perhaps is it possible to have two types of restore: what we have today an
d a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/s...aspx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
>|||I almost figured that MS wouldn't be too keen on re-writing large portions o
f the restore code.
Thanks for the update, Ranga.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:6010C190-C1DB-4980-8FF8-E6CC37C75913@.microsoft.com...[vbcol=seagreen]
> Thanks...There was a similar request...but declined by MS
> https://connect.microsoft.com/SQLSe...=12
5287
> "Tibor Karaszi" wrote:
>
...buth when you want to restore the db in dev...you need to have similar
storage size as in prod. Would it be nice if we have a option like
"FreeSpacePercent" parameter in the restore command where we can mention how
much free space is good enough.
For Example, if a DB is 100 GB size, with only 40% of data and 60% free
space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
all I need is 45GB instead of 100 GB in my dev environment to restore the db
?
Makes sense '
Thanks,
Ranga> Makes sense '
The request makes sense, but the implementation would have huge ramification
s on the restore
process.
Consider a database file in which you have pages. Any page can be in use, an
d any page in there can
have the address for this page in the page header (for instance). The restor
e process cannot
"compact" these pages to move them towards the beginning of the file, becaus
e of the linkage etc
between pages. Sure, it might be physically possible, but that restore would
be much much slower.
And it might not even be possible at all, since recovery is part of the rest
ore process and physical
page addresses might be in the transaction log records (might be, I'm not 10
0% certain of this).
Perhaps is it possible to have two types of restore: what we have today and
a "compact-and-restore".
You might want to vent such a request at http://connect.microsoft.com/site/s...aspx?SiteID=68.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
> Usually you have huge databases in production with atleast 50-60% free spa
ce
> ...buth when you want to restore the db in dev...you need to have simila
r
> storage size as in prod. Would it be nice if we have a option like
> "FreeSpacePercent" parameter in the restore command where we can mention h
ow
> much free space is good enough.
> For Example, if a DB is 100 GB size, with only 40% of data and 60% free
> space. I should be able to restore the DB using "FreeSpacePercent" = 5%, S
o,
> all I need is 45GB instead of 100 GB in my dev environment to restore the
db?
> Makes sense '
> Thanks,
> Ranga|||Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLSe...=1252
87
"Tibor Karaszi" wrote:
> The request makes sense, but the implementation would have huge ramificati
ons on the restore
> process.
> Consider a database file in which you have pages. Any page can be in use,
and any page in there can
> have the address for this page in the page header (for instance). The rest
ore process cannot
> "compact" these pages to move them towards the beginning of the file, beca
use of the linkage etc
> between pages. Sure, it might be physically possible, but that restore wou
ld be much much slower.
> And it might not even be possible at all, since recovery is part of the re
store process and physical
> page addresses might be in the transaction log records (might be, I'm not
100% certain of this).
> Perhaps is it possible to have two types of restore: what we have today an
d a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/s...aspx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
>|||I almost figured that MS wouldn't be too keen on re-writing large portions o
f the restore code.
Thanks for the update, Ranga.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:6010C190-C1DB-4980-8FF8-E6CC37C75913@.microsoft.com...[vbcol=seagreen]
> Thanks...There was a similar request...but declined by MS
> https://connect.microsoft.com/SQLSe...=12
5287
> "Tibor Karaszi" wrote:
>
Restore with Percent free space
Usually you have huge databases in production with atleast 50-60% free space
...buth when you want to restore the db in dev...you need to have similar
storage size as in prod. Would it be nice if we have a option like
"FreeSpacePercent" parameter in the restore command where we can mention how
much free space is good enough.
For Example, if a DB is 100 GB size, with only 40% of data and 60% free
space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
all I need is 45GB instead of 100 GB in my dev environment to restore the db?
Makes sense '
Thanks,
Ranga> Makes sense '
The request makes sense, but the implementation would have huge ramifications on the restore
process.
Consider a database file in which you have pages. Any page can be in use, and any page in there can
have the address for this page in the page header (for instance). The restore process cannot
"compact" these pages to move them towards the beginning of the file, because of the linkage etc
between pages. Sure, it might be physically possible, but that restore would be much much slower.
And it might not even be possible at all, since recovery is part of the restore process and physical
page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
Perhaps is it possible to have two types of restore: what we have today and a "compact-and-restore".
You might want to vent such a request at http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
> Usually you have huge databases in production with atleast 50-60% free space
> ...buth when you want to restore the db in dev...you need to have similar
> storage size as in prod. Would it be nice if we have a option like
> "FreeSpacePercent" parameter in the restore command where we can mention how
> much free space is good enough.
> For Example, if a DB is 100 GB size, with only 40% of data and 60% free
> space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
> all I need is 45GB instead of 100 GB in my dev environment to restore the db?
> Makes sense '
> Thanks,
> Ranga|||Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
"Tibor Karaszi" wrote:
> > Makes sense '
> The request makes sense, but the implementation would have huge ramifications on the restore
> process.
> Consider a database file in which you have pages. Any page can be in use, and any page in there can
> have the address for this page in the page header (for instance). The restore process cannot
> "compact" these pages to move them towards the beginning of the file, because of the linkage etc
> between pages. Sure, it might be physically possible, but that restore would be much much slower.
> And it might not even be possible at all, since recovery is part of the restore process and physical
> page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
> Perhaps is it possible to have two types of restore: what we have today and a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
> > Usually you have huge databases in production with atleast 50-60% free space
> > ...buth when you want to restore the db in dev...you need to have similar
> > storage size as in prod. Would it be nice if we have a option like
> > "FreeSpacePercent" parameter in the restore command where we can mention how
> > much free space is good enough.
> >
> > For Example, if a DB is 100 GB size, with only 40% of data and 60% free
> > space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
> > all I need is 45GB instead of 100 GB in my dev environment to restore the db?
> >
> > Makes sense '
> >
> > Thanks,
> > Ranga
>|||I almost figured that MS wouldn't be too keen on re-writing large portions of the restore code.
Thanks for the update, Ranga.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:6010C190-C1DB-4980-8FF8-E6CC37C75913@.microsoft.com...
> Thanks...There was a similar request...but declined by MS
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
> "Tibor Karaszi" wrote:
>> > Makes sense '
>> The request makes sense, but the implementation would have huge ramifications on the restore
>> process.
>> Consider a database file in which you have pages. Any page can be in use, and any page in there
>> can
>> have the address for this page in the page header (for instance). The restore process cannot
>> "compact" these pages to move them towards the beginning of the file, because of the linkage etc
>> between pages. Sure, it might be physically possible, but that restore would be much much slower.
>> And it might not even be possible at all, since recovery is part of the restore process and
>> physical
>> page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
>> Perhaps is it possible to have two types of restore: what we have today and a
>> "compact-and-restore".
>> You might want to vent such a request at
>> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
>> > Usually you have huge databases in production with atleast 50-60% free space
>> > ...buth when you want to restore the db in dev...you need to have similar
>> > storage size as in prod. Would it be nice if we have a option like
>> > "FreeSpacePercent" parameter in the restore command where we can mention how
>> > much free space is good enough.
>> >
>> > For Example, if a DB is 100 GB size, with only 40% of data and 60% free
>> > space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
>> > all I need is 45GB instead of 100 GB in my dev environment to restore the db?
>> >
>> > Makes sense '
>> >
>> > Thanks,
>> > Ranga
>>
...buth when you want to restore the db in dev...you need to have similar
storage size as in prod. Would it be nice if we have a option like
"FreeSpacePercent" parameter in the restore command where we can mention how
much free space is good enough.
For Example, if a DB is 100 GB size, with only 40% of data and 60% free
space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
all I need is 45GB instead of 100 GB in my dev environment to restore the db?
Makes sense '
Thanks,
Ranga> Makes sense '
The request makes sense, but the implementation would have huge ramifications on the restore
process.
Consider a database file in which you have pages. Any page can be in use, and any page in there can
have the address for this page in the page header (for instance). The restore process cannot
"compact" these pages to move them towards the beginning of the file, because of the linkage etc
between pages. Sure, it might be physically possible, but that restore would be much much slower.
And it might not even be possible at all, since recovery is part of the restore process and physical
page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
Perhaps is it possible to have two types of restore: what we have today and a "compact-and-restore".
You might want to vent such a request at http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
> Usually you have huge databases in production with atleast 50-60% free space
> ...buth when you want to restore the db in dev...you need to have similar
> storage size as in prod. Would it be nice if we have a option like
> "FreeSpacePercent" parameter in the restore command where we can mention how
> much free space is good enough.
> For Example, if a DB is 100 GB size, with only 40% of data and 60% free
> space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
> all I need is 45GB instead of 100 GB in my dev environment to restore the db?
> Makes sense '
> Thanks,
> Ranga|||Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
"Tibor Karaszi" wrote:
> > Makes sense '
> The request makes sense, but the implementation would have huge ramifications on the restore
> process.
> Consider a database file in which you have pages. Any page can be in use, and any page in there can
> have the address for this page in the page header (for instance). The restore process cannot
> "compact" these pages to move them towards the beginning of the file, because of the linkage etc
> between pages. Sure, it might be physically possible, but that restore would be much much slower.
> And it might not even be possible at all, since recovery is part of the restore process and physical
> page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
> Perhaps is it possible to have two types of restore: what we have today and a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
> > Usually you have huge databases in production with atleast 50-60% free space
> > ...buth when you want to restore the db in dev...you need to have similar
> > storage size as in prod. Would it be nice if we have a option like
> > "FreeSpacePercent" parameter in the restore command where we can mention how
> > much free space is good enough.
> >
> > For Example, if a DB is 100 GB size, with only 40% of data and 60% free
> > space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
> > all I need is 45GB instead of 100 GB in my dev environment to restore the db?
> >
> > Makes sense '
> >
> > Thanks,
> > Ranga
>|||I almost figured that MS wouldn't be too keen on re-writing large portions of the restore code.
Thanks for the update, Ranga.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:6010C190-C1DB-4980-8FF8-E6CC37C75913@.microsoft.com...
> Thanks...There was a similar request...but declined by MS
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
> "Tibor Karaszi" wrote:
>> > Makes sense '
>> The request makes sense, but the implementation would have huge ramifications on the restore
>> process.
>> Consider a database file in which you have pages. Any page can be in use, and any page in there
>> can
>> have the address for this page in the page header (for instance). The restore process cannot
>> "compact" these pages to move them towards the beginning of the file, because of the linkage etc
>> between pages. Sure, it might be physically possible, but that restore would be much much slower.
>> And it might not even be possible at all, since recovery is part of the restore process and
>> physical
>> page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
>> Perhaps is it possible to have two types of restore: what we have today and a
>> "compact-and-restore".
>> You might want to vent such a request at
>> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
>> > Usually you have huge databases in production with atleast 50-60% free space
>> > ...buth when you want to restore the db in dev...you need to have similar
>> > storage size as in prod. Would it be nice if we have a option like
>> > "FreeSpacePercent" parameter in the restore command where we can mention how
>> > much free space is good enough.
>> >
>> > For Example, if a DB is 100 GB size, with only 40% of data and 60% free
>> > space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
>> > all I need is 45GB instead of 100 GB in my dev environment to restore the db?
>> >
>> > Makes sense '
>> >
>> > Thanks,
>> > Ranga
>>
restore two databases from one device
Is it possible to have two restores running at the same
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!
Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!
|||Darn NT!!!
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.[vbcol=seagreen]
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
from[vbcol=seagreen]
and
>
>.
>
|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.[vbcol=seagreen]
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
from[vbcol=seagreen]
and
>
>.
>
sql
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!
Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!
|||Darn NT!!!
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.[vbcol=seagreen]
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
from[vbcol=seagreen]
and
>
>.
>
|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.[vbcol=seagreen]
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
from[vbcol=seagreen]
and
>
>.
>
sql
restore two databases from one device
Is it possible to have two restores running at the same
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!|||Darn NT!!!
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
from[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
from[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!|||Darn NT!!!
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
from[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
from[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>
restore two databases from one device
Is it possible to have two restores running at the same
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!|||Darn NT!!!
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
>> Is it possible to have two restores running at the same
>> time restoring two databases to two different servers
from
>> the same backup device? For example, I have server A
and
>> server B. I want to restore pubs to both A and B at the
>> same time from last night's backup device. Thanks!
>
>.
>|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
>> Is it possible to have two restores running at the same
>> time restoring two databases to two different servers
from
>> the same backup device? For example, I have server A
and
>> server B. I want to restore pubs to both A and B at the
>> same time from last night's backup device. Thanks!
>
>.
>
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!|||Darn NT!!!
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
>> Is it possible to have two restores running at the same
>> time restoring two databases to two different servers
from
>> the same backup device? For example, I have server A
and
>> server B. I want to restore pubs to both A and B at the
>> same time from last night's backup device. Thanks!
>
>.
>|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
>> Is it possible to have two restores running at the same
>> time restoring two databases to two different servers
from
>> the same backup device? For example, I have server A
and
>> server B. I want to restore pubs to both A and B at the
>> same time from last night's backup device. Thanks!
>
>.
>
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.
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.
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.
Restore to Multiple Files?
I am in the process of configuring a new laptop. It has two hard drives and
I want to restore several of my large databases to files on both hard
drives, even though the original dbs on my old laptop are single-file.
Anyone know a secret that will allow you to restore a single-file database
to spread it's data over two files during the restore'
--
TheSQLGuru
President
Indicium Resources, Inc.Hi
I'm affraid you cannot do that. As SQL Server needs to restore/attach the
same number of files as you created db.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>I am in the process of configuring a new laptop. It has two hard drives
>and I want to restore several of my large databases to files on both hard
>drives, even though the original dbs on my old laptop are single-file.
>Anyone know a secret that will allow you to restore a single-file database
>to spread it's data over two files during the restore'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>|||I didn't think it could be done either, but I have found quite a number of
things that fall into that category on this forum so I thought I would ask
anyway!! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqQ5U8H9HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Hi
> I'm affraid you cannot do that. As SQL Server needs to restore/attach the
> same number of files as you created db.
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>>I am in the process of configuring a new laptop. It has two hard drives
>>and I want to restore several of my large databases to files on both hard
>>drives, even though the original dbs on my old laptop are single-file.
>>Anyone know a secret that will allow you to restore a single-file database
>>to spread it's data over two files during the restore'
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>>
>|||You are probably better off placing the log files on one drive and the data
on the other.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>I am in the process of configuring a new laptop. It has two hard drives
>and I want to restore several of my large databases to files on both hard
>drives, even though the original dbs on my old laptop are single-file.
>Anyone know a secret that will allow you to restore a single-file database
>to spread it's data over two files during the restore'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>|||That will be done for my 'standard' databases. I have a few very large ones
I use for analyzing trace files and also one large test db for a client and
for those the read-throughput is key to optimal perforance thus the desire
to spread the data across both drives.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uTTCReJ9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> You are probably better off placing the log files on one drive and the
> data on the other.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>>I am in the process of configuring a new laptop. It has two hard drives
>>and I want to restore several of my large databases to files on both hard
>>drives, even though the original dbs on my old laptop are single-file.
>>Anyone know a secret that will allow you to restore a single-file database
>>to spread it's data over two files during the restore'
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>>
>sql
I want to restore several of my large databases to files on both hard
drives, even though the original dbs on my old laptop are single-file.
Anyone know a secret that will allow you to restore a single-file database
to spread it's data over two files during the restore'
--
TheSQLGuru
President
Indicium Resources, Inc.Hi
I'm affraid you cannot do that. As SQL Server needs to restore/attach the
same number of files as you created db.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>I am in the process of configuring a new laptop. It has two hard drives
>and I want to restore several of my large databases to files on both hard
>drives, even though the original dbs on my old laptop are single-file.
>Anyone know a secret that will allow you to restore a single-file database
>to spread it's data over two files during the restore'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>|||I didn't think it could be done either, but I have found quite a number of
things that fall into that category on this forum so I thought I would ask
anyway!! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqQ5U8H9HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Hi
> I'm affraid you cannot do that. As SQL Server needs to restore/attach the
> same number of files as you created db.
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>>I am in the process of configuring a new laptop. It has two hard drives
>>and I want to restore several of my large databases to files on both hard
>>drives, even though the original dbs on my old laptop are single-file.
>>Anyone know a secret that will allow you to restore a single-file database
>>to spread it's data over two files during the restore'
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>>
>|||You are probably better off placing the log files on one drive and the data
on the other.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>I am in the process of configuring a new laptop. It has two hard drives
>and I want to restore several of my large databases to files on both hard
>drives, even though the original dbs on my old laptop are single-file.
>Anyone know a secret that will allow you to restore a single-file database
>to spread it's data over two files during the restore'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>|||That will be done for my 'standard' databases. I have a few very large ones
I use for analyzing trace files and also one large test db for a client and
for those the read-throughput is key to optimal perforance thus the desire
to spread the data across both drives.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uTTCReJ9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> You are probably better off placing the log files on one drive and the
> data on the other.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>>I am in the process of configuring a new laptop. It has two hard drives
>>and I want to restore several of my large databases to files on both hard
>>drives, even though the original dbs on my old laptop are single-file.
>>Anyone know a secret that will allow you to restore a single-file database
>>to spread it's data over two files during the restore'
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>>
>sql
Restore to different drive
I use Enterprise Manager to backup and restore various SQL Server databases
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
Restore Timing Transaction Logs versus Differentials
Currently we backup out SQL Server 2005 databases with a combination of full, differential and transaction log backups and plan on continuing this practice. Currently all backups are written to physical servers other than the servers where the DBs are running and all backups are also written to tape. Our tape backups are costly and I am considering no longer writing the differential backups to tape to reduce costs. Before making this decision I would like to understand how much longer it would take to recover several days of transaction log backups, versus a single differential and only a few hours of transation log backups. Does anyone have any any information about the time difference? Any ratios or rules of thumb?
Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?
|||
Thanks,
Julia
Stopping the tape copies of differentials seems like a reasonable plan IF you kept two or three of the DIFFERENTIAL BACKUP copies on disk. (I prefer to keep all differentials since the last FULL BACKUP, tossing them only after the next FULL BACKUP is verified.
Time to recover: Seems like the major time issue is the manual process of handling each individual restore. The fewer files to restore, the less 'manual' time. The greated the number of files involved, the greater the risk of a corrupt or damaged file.
|||Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?
|||
There are so many variables on that, I wouldn't hazard a guess.
You could run a comparision test on another server, comparing the timings.
|||Has anyone run these types of timed tests comparing the restore times from 2 types of backups?|||That is purely depends upon the number of transactions on your database to restore, see this blog http://sqlserver-qa.net/blogs/perftune/archive/2007/06/12/get-backup-and-restore-performance-stats-with-a-dmv.aspx on the performance stats for backup & restore tasks.
EgleK wrote:
Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?
Friday, March 23, 2012
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.
Subscribe to:
Posts (Atom)