A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
MaurySee the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury|||"Kevin3NF" wrote:
> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:
> "Kevin3NF" wrote:
> > See the Restore Database command, and simply use MyDatabase_New instead of
> > MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> > there...just give it a new name
> Ok, thanks. Here goes nothing...
> Maurysql
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Wednesday, March 28, 2012
Restoring to get a single table
A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
Maury
See the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury
|||"Kevin3NF" wrote:
> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury
|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:
> "Kevin3NF" wrote:
>
> Ok, thanks. Here goes nothing...
> Maury
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
Maury
See the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury
|||"Kevin3NF" wrote:
> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury
|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:
> "Kevin3NF" wrote:
>
> Ok, thanks. Here goes nothing...
> Maury
Restoring to get a single table
A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
MaurySee the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury|||"Kevin3NF" wrote:
> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:
> "Kevin3NF" wrote:
>
> Ok, thanks. Here goes nothing...
> Maury
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
MaurySee the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury|||"Kevin3NF" wrote:
> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:
> "Kevin3NF" wrote:
>
> Ok, thanks. Here goes nothing...
> Maury
Monday, March 12, 2012
Restoring Master on another machine...
We set up a test box and having been trying to move an entire instance
from our production server to this one. I have moved all my created
dbs using the RESTORE WITH MOVE. Now I am trying to move the Master,
Model, and msdb. This is where I am having trouble. On the production
box the dbs are stored on D:\mssql$instancename\data. On the test
server the data is stored in C:\Program Files\Microsoft SQL
Server\mssql$instancename\data. Also the server names are different.
So I finally got the Master from production to restore over the test's
copy. Now the instance will not start. I was trying to use the
following code in cmd line to connect to the instace so I could then
copy model and msdb:
from our production server to this one. I have moved all my created
dbs using the RESTORE WITH MOVE. Now I am trying to move the Master,
Model, and msdb. This is where I am having trouble. On the production
box the dbs are stored on D:\mssql$instancename\data. On the test
server the data is stored in C:\Program Files\Microsoft SQL
Server\mssql$instancename\data. Also the server names are different.
So I finally got the Master from production to restore over the test's
copy. Now the instance will not start. I was trying to use the
following code in cmd line to connect to the instace so I could then
copy model and msdb:
sqlservr -c -f -T3608 -T4022 -sINSTANCENAME
When this is trying to connect I read it is failing and saying that the
MDF and LDF may be corrupt or not there. The problem is it is now
trying to look in D:\mssql$instancename\data instead of C:\Program
Files\Microsoft SQL Server\mssql$instancename\data. Any ideas on how I
can change this, or if I have to reinstall the entire instace, not run
into this again?(murrayb3024@.gmail.com) writes:
> We set up a test box and having been trying to move an entire instance
> from our production server to this one. I have moved all my created
> dbs using the RESTORE WITH MOVE. Now I am trying to move the Master,
> Model, and msdb. This is where I am having trouble. On the production
> box the dbs are stored on D:\mssql$instancename\data. On the test
> server the data is stored in C:\Program Files\Microsoft SQL
> Server\mssql$instancename\data. Also the server names are different.
> So I finally got the Master from production to restore over the test's
> copy. Now the instance will not start. I was trying to use the
> following code in cmd line to connect to the instace so I could then
> copy model and msdb:
> sqlservr -c -f -T3608 -T4022 -sINSTANCENAME
> When this is trying to connect I read it is failing and saying that the
> MDF and LDF may be corrupt or not there. The problem is it is now
> trying to look in D:\mssql$instancename\data instead of C:\Program
> Files\Microsoft SQL Server\mssql$instancename\data. Any ideas on how I
> can change this, or if I have to reinstall the entire instace, not run
> into this again?
Before you set off, did you look at
http://support.microsoft.com/defaul...b;EN-US;224071?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The article is really useful
http://support.microsoft.com/defaul...kb;en-us;224071
Also attach & detach should save your lot of time.
Thanks
Ajay Rengunthwar
MCDBA
Restoring Master database
Hi,
Production Server: Windows 2000 server, SQL Server 2000 SP2
Test Server: Windows 2003 server, SQL Server 2000 SP3
I have to restore the master database from Production Server to Test Server.
Would it cause any issues? Thanks.
Sharman,
Yes. Since logins, databases, linked servers, etc. are all defined in
master, those definitions on the Test Server will be replaced with the
definitions from the Production Server. Any unique logins will be
eliminated, databases may become unattached, and so forth.
So, you can do it, but you have to be very careful not to lose anything that
you need. You might restore master to your test server as
'master_from_prod' and determine what you have to do to merge any
differences.
(I do not fathom why Production master is needed on Test unless a pretty
good slice of other databases is also being brought over to Test as well.)
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
> Hi,
> Production Server: Windows 2000 server, SQL Server 2000 SP2
> Test Server: Windows 2003 server, SQL Server 2000 SP3
> I have to restore the master database from Production Server to Test
> Server.
> Would it cause any issues? Thanks.
|||I am asking this question because of the different Service Packs on
Production and Test servers with Production having a lower Service Pack. I
have read that Service Packs apply changes to system tables and I am
restoring a master created on SP2 on a SQL Server running SP3.
Once the restore of master is successful I will be restoring the other
system and user databases from Production Server as well.
"Russell Fields" wrote:
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything that
> you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>
>
|||sharman,
It was a while back, but I believe that SQL 2000 SP4 was the only one that
changed the format of the system tables in the SQL 2000 timeline.
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:4BB1EF1C-D7B9-43F6-B4AB-A64CA0A20834@.microsoft.com...[vbcol=seagreen]
>I am asking this question because of the different Service Packs on
> Production and Test servers with Production having a lower Service Pack. I
> have read that Service Packs apply changes to system tables and I am
> restoring a master created on SP2 on a SQL Server running SP3.
> Once the restore of master is successful I will be restoring the other
> system and user databases from Production Server as well.
>
> "Russell Fields" wrote:
|||There is more to it than logins I think. Isn't the location of all
databases stored there? Other stuff too . . .
TheSQLGuru
President
Indicium Resources, Inc.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything
> that you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>
|||I'd rather apply the SQL patches than restore master from PROD to TEST
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:utqOUsZuHHA.484@.TK2MSFTNGP06.phx.gbl...
> There is more to it than logins I think. Isn't the location of all
> databases stored there? Other stuff too . . .
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
>
Production Server: Windows 2000 server, SQL Server 2000 SP2
Test Server: Windows 2003 server, SQL Server 2000 SP3
I have to restore the master database from Production Server to Test Server.
Would it cause any issues? Thanks.
Sharman,
Yes. Since logins, databases, linked servers, etc. are all defined in
master, those definitions on the Test Server will be replaced with the
definitions from the Production Server. Any unique logins will be
eliminated, databases may become unattached, and so forth.
So, you can do it, but you have to be very careful not to lose anything that
you need. You might restore master to your test server as
'master_from_prod' and determine what you have to do to merge any
differences.
(I do not fathom why Production master is needed on Test unless a pretty
good slice of other databases is also being brought over to Test as well.)
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
> Hi,
> Production Server: Windows 2000 server, SQL Server 2000 SP2
> Test Server: Windows 2003 server, SQL Server 2000 SP3
> I have to restore the master database from Production Server to Test
> Server.
> Would it cause any issues? Thanks.
|||I am asking this question because of the different Service Packs on
Production and Test servers with Production having a lower Service Pack. I
have read that Service Packs apply changes to system tables and I am
restoring a master created on SP2 on a SQL Server running SP3.
Once the restore of master is successful I will be restoring the other
system and user databases from Production Server as well.
"Russell Fields" wrote:
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything that
> you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>
>
|||sharman,
It was a while back, but I believe that SQL 2000 SP4 was the only one that
changed the format of the system tables in the SQL 2000 timeline.
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:4BB1EF1C-D7B9-43F6-B4AB-A64CA0A20834@.microsoft.com...[vbcol=seagreen]
>I am asking this question because of the different Service Packs on
> Production and Test servers with Production having a lower Service Pack. I
> have read that Service Packs apply changes to system tables and I am
> restoring a master created on SP2 on a SQL Server running SP3.
> Once the restore of master is successful I will be restoring the other
> system and user databases from Production Server as well.
>
> "Russell Fields" wrote:
|||There is more to it than logins I think. Isn't the location of all
databases stored there? Other stuff too . . .
TheSQLGuru
President
Indicium Resources, Inc.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything
> that you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>
|||I'd rather apply the SQL patches than restore master from PROD to TEST
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:utqOUsZuHHA.484@.TK2MSFTNGP06.phx.gbl...
> There is more to it than logins I think. Isn't the location of all
> databases stored there? Other stuff too . . .
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
>
Restoring master database
Hi All
Im testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
Ive started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start.
On checking the error log there are some VDN errors, I assume that this is
because the server Im restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
Message posted via http://www.droptable.com
Hi
Does this occur on subsequent restarts?
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:5410926593904@.droptable.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.droptable.com
|||Hi
I get the VDN error reported after everytime I try and restart the SQL Server,
regardless of how I try to restart it.
Tony
|||Hi Tony
Even with the -f flag?
Have you followed
http://support.microsoft.com/default...22120121120120
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:54196F15F1D57@.droptable.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony
|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
Im not sure how Attach/Detach can help me as Im trying to restore from
backup?
The common error Im getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesnt exist
producing VDN errors
Tony
John Bell wrote:[vbcol=seagreen]
>Hi Tony
>Even with the -f flag?
>Have you followed
>http://support.microsoft.com/default...22120121120120
>John
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
http://support.microsoft.com/default...22120121120120
To move tempdb use the alter database command as described.
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:544195E6FFA0B@.droptable.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>
> --
> Message posted via http://www.droptable.com
Im testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
Ive started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start.
On checking the error log there are some VDN errors, I assume that this is
because the server Im restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
Message posted via http://www.droptable.com
Hi
Does this occur on subsequent restarts?
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:5410926593904@.droptable.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.droptable.com
|||Hi
I get the VDN error reported after everytime I try and restart the SQL Server,
regardless of how I try to restart it.
Tony
|||Hi Tony
Even with the -f flag?
Have you followed
http://support.microsoft.com/default...22120121120120
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:54196F15F1D57@.droptable.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony
|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
Im not sure how Attach/Detach can help me as Im trying to restore from
backup?
The common error Im getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesnt exist
producing VDN errors
Tony
John Bell wrote:[vbcol=seagreen]
>Hi Tony
>Even with the -f flag?
>Have you followed
>http://support.microsoft.com/default...22120121120120
>John
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
http://support.microsoft.com/default...22120121120120
To move tempdb use the alter database command as described.
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:544195E6FFA0B@.droptable.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>
> --
> Message posted via http://www.droptable.com
Restoring Master database
Hi,
Production Server: Windows 2000 server, SQL Server 2000 SP2
Test Server: Windows 2003 server, SQL Server 2000 SP3
I have to restore the master database from Production Server to Test Server.
Would it cause any issues? Thanks.Sharman,
Yes. Since logins, databases, linked servers, etc. are all defined in
master, those definitions on the Test Server will be replaced with the
definitions from the Production Server. Any unique logins will be
eliminated, databases may become unattached, and so forth.
So, you can do it, but you have to be very careful not to lose anything that
you need. You might restore master to your test server as
'master_from_prod' and determine what you have to do to merge any
differences.
(I do not fathom why Production master is needed on Test unless a pretty
good slice of other databases is also being brought over to Test as well.)
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
> Hi,
> Production Server: Windows 2000 server, SQL Server 2000 SP2
> Test Server: Windows 2003 server, SQL Server 2000 SP3
> I have to restore the master database from Production Server to Test
> Server.
> Would it cause any issues? Thanks.|||I am asking this question because of the different Service Packs on
Production and Test servers with Production having a lower Service Pack. I
have read that Service Packs apply changes to system tables and I am
restoring a master created on SP2 on a SQL Server running SP3.
Once the restore of master is successful I will be restoring the other
system and user databases from Production Server as well.
"Russell Fields" wrote:
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything that
> you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
> > Hi,
> >
> > Production Server: Windows 2000 server, SQL Server 2000 SP2
> > Test Server: Windows 2003 server, SQL Server 2000 SP3
> >
> > I have to restore the master database from Production Server to Test
> > Server.
> > Would it cause any issues? Thanks.
>
>|||sharman,
It was a while back, but I believe that SQL 2000 SP4 was the only one that
changed the format of the system tables in the SQL 2000 timeline.
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:4BB1EF1C-D7B9-43F6-B4AB-A64CA0A20834@.microsoft.com...
>I am asking this question because of the different Service Packs on
> Production and Test servers with Production having a lower Service Pack. I
> have read that Service Packs apply changes to system tables and I am
> restoring a master created on SP2 on a SQL Server running SP3.
> Once the restore of master is successful I will be restoring the other
> system and user databases from Production Server as well.
>
> "Russell Fields" wrote:
>> Sharman,
>> Yes. Since logins, databases, linked servers, etc. are all defined in
>> master, those definitions on the Test Server will be replaced with the
>> definitions from the Production Server. Any unique logins will be
>> eliminated, databases may become unattached, and so forth.
>> So, you can do it, but you have to be very careful not to lose anything
>> that
>> you need. You might restore master to your test server as
>> 'master_from_prod' and determine what you have to do to merge any
>> differences.
>> (I do not fathom why Production master is needed on Test unless a pretty
>> good slice of other databases is also being brought over to Test as
>> well.)
>> RLF
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>> > Hi,
>> >
>> > Production Server: Windows 2000 server, SQL Server 2000 SP2
>> > Test Server: Windows 2003 server, SQL Server 2000 SP3
>> >
>> > I have to restore the master database from Production Server to Test
>> > Server.
>> > Would it cause any issues? Thanks.
>>|||There is more to it than logins I think. Isn't the location of all
databases stored there? Other stuff too . . .
--
TheSQLGuru
President
Indicium Resources, Inc.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything
> that you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>> Hi,
>> Production Server: Windows 2000 server, SQL Server 2000 SP2
>> Test Server: Windows 2003 server, SQL Server 2000 SP3
>> I have to restore the master database from Production Server to Test
>> Server.
>> Would it cause any issues? Thanks.
>|||I'd rather apply the SQL patches than restore master from PROD to TEST
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:utqOUsZuHHA.484@.TK2MSFTNGP06.phx.gbl...
> There is more to it than logins I think. Isn't the location of all
> databases stored there? Other stuff too . . .
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
>> Sharman,
>> Yes. Since logins, databases, linked servers, etc. are all defined in
>> master, those definitions on the Test Server will be replaced with the
>> definitions from the Production Server. Any unique logins will be
>> eliminated, databases may become unattached, and so forth.
>> So, you can do it, but you have to be very careful not to lose anything
>> that you need. You might restore master to your test server as
>> 'master_from_prod' and determine what you have to do to merge any
>> differences.
>> (I do not fathom why Production master is needed on Test unless a pretty
>> good slice of other databases is also being brought over to Test as
>> well.)
>> RLF
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>> Hi,
>> Production Server: Windows 2000 server, SQL Server 2000 SP2
>> Test Server: Windows 2003 server, SQL Server 2000 SP3
>> I have to restore the master database from Production Server to Test
>> Server.
>> Would it cause any issues? Thanks.
>>
>
Production Server: Windows 2000 server, SQL Server 2000 SP2
Test Server: Windows 2003 server, SQL Server 2000 SP3
I have to restore the master database from Production Server to Test Server.
Would it cause any issues? Thanks.Sharman,
Yes. Since logins, databases, linked servers, etc. are all defined in
master, those definitions on the Test Server will be replaced with the
definitions from the Production Server. Any unique logins will be
eliminated, databases may become unattached, and so forth.
So, you can do it, but you have to be very careful not to lose anything that
you need. You might restore master to your test server as
'master_from_prod' and determine what you have to do to merge any
differences.
(I do not fathom why Production master is needed on Test unless a pretty
good slice of other databases is also being brought over to Test as well.)
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
> Hi,
> Production Server: Windows 2000 server, SQL Server 2000 SP2
> Test Server: Windows 2003 server, SQL Server 2000 SP3
> I have to restore the master database from Production Server to Test
> Server.
> Would it cause any issues? Thanks.|||I am asking this question because of the different Service Packs on
Production and Test servers with Production having a lower Service Pack. I
have read that Service Packs apply changes to system tables and I am
restoring a master created on SP2 on a SQL Server running SP3.
Once the restore of master is successful I will be restoring the other
system and user databases from Production Server as well.
"Russell Fields" wrote:
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything that
> you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
> > Hi,
> >
> > Production Server: Windows 2000 server, SQL Server 2000 SP2
> > Test Server: Windows 2003 server, SQL Server 2000 SP3
> >
> > I have to restore the master database from Production Server to Test
> > Server.
> > Would it cause any issues? Thanks.
>
>|||sharman,
It was a while back, but I believe that SQL 2000 SP4 was the only one that
changed the format of the system tables in the SQL 2000 timeline.
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:4BB1EF1C-D7B9-43F6-B4AB-A64CA0A20834@.microsoft.com...
>I am asking this question because of the different Service Packs on
> Production and Test servers with Production having a lower Service Pack. I
> have read that Service Packs apply changes to system tables and I am
> restoring a master created on SP2 on a SQL Server running SP3.
> Once the restore of master is successful I will be restoring the other
> system and user databases from Production Server as well.
>
> "Russell Fields" wrote:
>> Sharman,
>> Yes. Since logins, databases, linked servers, etc. are all defined in
>> master, those definitions on the Test Server will be replaced with the
>> definitions from the Production Server. Any unique logins will be
>> eliminated, databases may become unattached, and so forth.
>> So, you can do it, but you have to be very careful not to lose anything
>> that
>> you need. You might restore master to your test server as
>> 'master_from_prod' and determine what you have to do to merge any
>> differences.
>> (I do not fathom why Production master is needed on Test unless a pretty
>> good slice of other databases is also being brought over to Test as
>> well.)
>> RLF
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>> > Hi,
>> >
>> > Production Server: Windows 2000 server, SQL Server 2000 SP2
>> > Test Server: Windows 2003 server, SQL Server 2000 SP3
>> >
>> > I have to restore the master database from Production Server to Test
>> > Server.
>> > Would it cause any issues? Thanks.
>>|||There is more to it than logins I think. Isn't the location of all
databases stored there? Other stuff too . . .
--
TheSQLGuru
President
Indicium Resources, Inc.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything
> that you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>> Hi,
>> Production Server: Windows 2000 server, SQL Server 2000 SP2
>> Test Server: Windows 2003 server, SQL Server 2000 SP3
>> I have to restore the master database from Production Server to Test
>> Server.
>> Would it cause any issues? Thanks.
>|||I'd rather apply the SQL patches than restore master from PROD to TEST
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:utqOUsZuHHA.484@.TK2MSFTNGP06.phx.gbl...
> There is more to it than logins I think. Isn't the location of all
> databases stored there? Other stuff too . . .
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
>> Sharman,
>> Yes. Since logins, databases, linked servers, etc. are all defined in
>> master, those definitions on the Test Server will be replaced with the
>> definitions from the Production Server. Any unique logins will be
>> eliminated, databases may become unattached, and so forth.
>> So, you can do it, but you have to be very careful not to lose anything
>> that you need. You might restore master to your test server as
>> 'master_from_prod' and determine what you have to do to merge any
>> differences.
>> (I do not fathom why Production master is needed on Test unless a pretty
>> good slice of other databases is also being brought over to Test as
>> well.)
>> RLF
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>> Hi,
>> Production Server: Windows 2000 server, SQL Server 2000 SP2
>> Test Server: Windows 2003 server, SQL Server 2000 SP3
>> I have to restore the master database from Production Server to Test
>> Server.
>> Would it cause any issues? Thanks.
>>
>
Restoring master database
Hi All
I?m testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
I?ve started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start.
On checking the error log there are some VDN errors, I assume that this is
because the server I?m restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
--
Message posted via http://www.sqlmonster.comHi
Does this occur on subsequent restarts?
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5410926593904@.SQLMonster.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.sqlmonster.com|||Hi
I get the VDN error reported after everytime I try and restart the SQL Server,
regardless of how I try to restart it.
Tony|||Hi Tony
Even with the -f flag?
Have you followed
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54196F15F1D57@.SQLMonster.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
I?m not sure how Attach/Detach can help me as I?m trying to restore from
backup?
The common error I?m getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesn?t exist
producing VDN errors
Tony
John Bell wrote:
>Hi Tony
>Even with the -f flag?
>Have you followed
>http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
>John
>> Hi
>[quoted text clipped - 3 lines]
>> Tony
Message posted via http://www.sqlmonster.com|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
To move tempdb use the alter database command as described.
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:544195E6FFA0B@.SQLMonster.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>>Hi Tony
>>Even with the -f flag?
>>Have you followed
>>http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
>>John
>> Hi
>>[quoted text clipped - 3 lines]
>> Tony
>
> --
> Message posted via http://www.sqlmonster.com
I?m testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
I?ve started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start.
On checking the error log there are some VDN errors, I assume that this is
because the server I?m restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
--
Message posted via http://www.sqlmonster.comHi
Does this occur on subsequent restarts?
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5410926593904@.SQLMonster.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.sqlmonster.com|||Hi
I get the VDN error reported after everytime I try and restart the SQL Server,
regardless of how I try to restart it.
Tony|||Hi Tony
Even with the -f flag?
Have you followed
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54196F15F1D57@.SQLMonster.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
I?m not sure how Attach/Detach can help me as I?m trying to restore from
backup?
The common error I?m getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesn?t exist
producing VDN errors
Tony
John Bell wrote:
>Hi Tony
>Even with the -f flag?
>Have you followed
>http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
>John
>> Hi
>[quoted text clipped - 3 lines]
>> Tony
Message posted via http://www.sqlmonster.com|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
To move tempdb use the alter database command as described.
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:544195E6FFA0B@.SQLMonster.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>>Hi Tony
>>Even with the -f flag?
>>Have you followed
>>http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
>>John
>> Hi
>>[quoted text clipped - 3 lines]
>> Tony
>
> --
> Message posted via http://www.sqlmonster.com
Restoring master database
Hi All
Im testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
Ive started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start
.
On checking the error log there are some VDN errors, I assume that this is
because the server Im restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
Message posted via http://www.droptable.comHi
Does this occur on subsequent restarts?
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:5410926593904@.droptable.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.droptable.com|||Hi
I get the VDN error reported after everytime I try and restart the SQL Serve
r,
regardless of how I try to restart it.
Tony|||Hi Tony
Even with the -f flag?
Have you followed
120121120120" target="_blank">http://support.microsoft.com/defaul...>
120121120120
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:54196F15F1D57@.droptable.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
Im not sure how Attach/Detach can help me as Im trying to restore from
backup?
The common error Im getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesnt exist
producing VDN errors
Tony
John Bell wrote:[vbcol=seagreen]
>Hi Tony
>Even with the -f flag?
>Have you followed
>2120121120120" target="_blank">http://support.microsoft.com/defaul...
2120121120120
>John
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
120121120120" target="_blank">http://support.microsoft.com/defaul...>
120121120120
To move tempdb use the alter database command as described.
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:544195E6FFA0B@.droptable.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>
> --
> Message posted via http://www.droptable.com
Im testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
Ive started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start
.
On checking the error log there are some VDN errors, I assume that this is
because the server Im restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
Message posted via http://www.droptable.comHi
Does this occur on subsequent restarts?
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:5410926593904@.droptable.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.droptable.com|||Hi
I get the VDN error reported after everytime I try and restart the SQL Serve
r,
regardless of how I try to restart it.
Tony|||Hi Tony
Even with the -f flag?
Have you followed
120121120120" target="_blank">http://support.microsoft.com/defaul...>
120121120120
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:54196F15F1D57@.droptable.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
Im not sure how Attach/Detach can help me as Im trying to restore from
backup?
The common error Im getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesnt exist
producing VDN errors
Tony
John Bell wrote:[vbcol=seagreen]
>Hi Tony
>Even with the -f flag?
>Have you followed
>2120121120120" target="_blank">http://support.microsoft.com/defaul...
2120121120120
>John
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
120121120120" target="_blank">http://support.microsoft.com/defaul...>
120121120120
To move tempdb use the alter database command as described.
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:544195E6FFA0B@.droptable.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>
> --
> Message posted via http://www.droptable.com
Restoring Master database
Hi,
Production Server: Windows 2000 server, SQL Server 2000 SP2
Test Server: Windows 2003 server, SQL Server 2000 SP3
I have to restore the master database from Production Server to Test Server.
Would it cause any issues? Thanks.Sharman,
Yes. Since logins, databases, linked servers, etc. are all defined in
master, those definitions on the Test Server will be replaced with the
definitions from the Production Server. Any unique logins will be
eliminated, databases may become unattached, and so forth.
So, you can do it, but you have to be very careful not to lose anything that
you need. You might restore master to your test server as
'master_from_prod' and determine what you have to do to merge any
differences.
(I do not fathom why Production master is needed on Test unless a pretty
good slice of other databases is also being brought over to Test as well.)
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
> Hi,
> Production Server: Windows 2000 server, SQL Server 2000 SP2
> Test Server: Windows 2003 server, SQL Server 2000 SP3
> I have to restore the master database from Production Server to Test
> Server.
> Would it cause any issues? Thanks.|||I am asking this question because of the different Service Packs on
Production and Test servers with Production having a lower Service Pack. I
have read that Service Packs apply changes to system tables and I am
restoring a master created on SP2 on a SQL Server running SP3.
Once the restore of master is successful I will be restoring the other
system and user databases from Production Server as well.
"Russell Fields" wrote:
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything th
at
> you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>
>|||sharman,
It was a while back, but I believe that SQL 2000 SP4 was the only one that
changed the format of the system tables in the SQL 2000 timeline.
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:4BB1EF1C-D7B9-43F6-B4AB-A64CA0A20834@.microsoft.com...[vbcol=seagreen]
>I am asking this question because of the different Service Packs on
> Production and Test servers with Production having a lower Service Pack. I
> have read that Service Packs apply changes to system tables and I am
> restoring a master created on SP2 on a SQL Server running SP3.
> Once the restore of master is successful I will be restoring the other
> system and user databases from Production Server as well.
>
> "Russell Fields" wrote:
>|||There is more to it than logins I think. Isn't the location of all
databases stored there? Other stuff too . . .
TheSQLGuru
President
Indicium Resources, Inc.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything
> that you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>|||I'd rather apply the SQL patches than restore master from PROD to TEST
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:utqOUsZuHHA.484@.TK2MSFTNGP06.phx.gbl...
> There is more to it than logins I think. Isn't the location of all
> databases stored there? Other stuff too . . .
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
>
Production Server: Windows 2000 server, SQL Server 2000 SP2
Test Server: Windows 2003 server, SQL Server 2000 SP3
I have to restore the master database from Production Server to Test Server.
Would it cause any issues? Thanks.Sharman,
Yes. Since logins, databases, linked servers, etc. are all defined in
master, those definitions on the Test Server will be replaced with the
definitions from the Production Server. Any unique logins will be
eliminated, databases may become unattached, and so forth.
So, you can do it, but you have to be very careful not to lose anything that
you need. You might restore master to your test server as
'master_from_prod' and determine what you have to do to merge any
differences.
(I do not fathom why Production master is needed on Test unless a pretty
good slice of other databases is also being brought over to Test as well.)
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
> Hi,
> Production Server: Windows 2000 server, SQL Server 2000 SP2
> Test Server: Windows 2003 server, SQL Server 2000 SP3
> I have to restore the master database from Production Server to Test
> Server.
> Would it cause any issues? Thanks.|||I am asking this question because of the different Service Packs on
Production and Test servers with Production having a lower Service Pack. I
have read that Service Packs apply changes to system tables and I am
restoring a master created on SP2 on a SQL Server running SP3.
Once the restore of master is successful I will be restoring the other
system and user databases from Production Server as well.
"Russell Fields" wrote:
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything th
at
> you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>
>|||sharman,
It was a while back, but I believe that SQL 2000 SP4 was the only one that
changed the format of the system tables in the SQL 2000 timeline.
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:4BB1EF1C-D7B9-43F6-B4AB-A64CA0A20834@.microsoft.com...[vbcol=seagreen]
>I am asking this question because of the different Service Packs on
> Production and Test servers with Production having a lower Service Pack. I
> have read that Service Packs apply changes to system tables and I am
> restoring a master created on SP2 on a SQL Server running SP3.
> Once the restore of master is successful I will be restoring the other
> system and user databases from Production Server as well.
>
> "Russell Fields" wrote:
>|||There is more to it than logins I think. Isn't the location of all
databases stored there? Other stuff too . . .
TheSQLGuru
President
Indicium Resources, Inc.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Sharman,
> Yes. Since logins, databases, linked servers, etc. are all defined in
> master, those definitions on the Test Server will be replaced with the
> definitions from the Production Server. Any unique logins will be
> eliminated, databases may become unattached, and so forth.
> So, you can do it, but you have to be very careful not to lose anything
> that you need. You might restore master to your test server as
> 'master_from_prod' and determine what you have to do to merge any
> differences.
> (I do not fathom why Production master is needed on Test unless a pretty
> good slice of other databases is also being brought over to Test as well.)
> RLF
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:985B0C8F-6D5E-481C-8FD4-A60B7F043FB0@.microsoft.com...
>|||I'd rather apply the SQL patches than restore master from PROD to TEST
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:utqOUsZuHHA.484@.TK2MSFTNGP06.phx.gbl...
> There is more to it than logins I think. Isn't the location of all
> databases stored there? Other stuff too . . .
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:e$$AvFNuHHA.3404@.TK2MSFTNGP03.phx.gbl...
>
Friday, March 9, 2012
Restoring log & backup
Hi,
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
AntThe log files will bring you closer to the most recent state of the original
DB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
Hi,
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
Ant|||Hello,
To bring the database up to latest you may need to restore the LOG backups
as well.
Perform below:-
1. Restore the lastest full database backup with NORECOVERY
2. Restore the subsequent log backups one by one with NORECOVER until last
log backup
3. Restore the last log backup with RECOVERY to make the database
operational.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
> Hi,
> Our SQL server has a database with scheduled backups which backup the
> database & the log file of our production site.
> I would like to restore this backup to our test DB with the production
> backup in order for it to resemble the production one. Should I uncheck
> the
> log file backup when restoring or is it ok to restore it as well? Both
> files
> come up in the restore as ticked. What are the implications of this?
> Thanks very much for any ideas on this
> Ant
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
AntThe log files will bring you closer to the most recent state of the original
DB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
Hi,
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
Ant|||Hello,
To bring the database up to latest you may need to restore the LOG backups
as well.
Perform below:-
1. Restore the lastest full database backup with NORECOVERY
2. Restore the subsequent log backups one by one with NORECOVER until last
log backup
3. Restore the last log backup with RECOVERY to make the database
operational.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
> Hi,
> Our SQL server has a database with scheduled backups which backup the
> database & the log file of our production site.
> I would like to restore this backup to our test DB with the production
> backup in order for it to resemble the production one. Should I uncheck
> the
> log file backup when restoring or is it ok to restore it as well? Both
> files
> come up in the restore as ticked. What are the implications of this?
> Thanks very much for any ideas on this
> Ant
Restoring log & backup
Hi,
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
Ant
The log files will bring you closer to the most recent state of the original
DB.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
Hi,
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
Ant
|||Hello,
To bring the database up to latest you may need to restore the LOG backups
as well.
Perform below:-
1. Restore the lastest full database backup with NORECOVERY
2. Restore the subsequent log backups one by one with NORECOVER until last
log backup
3. Restore the last log backup with RECOVERY to make the database
operational.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
> Hi,
> Our SQL server has a database with scheduled backups which backup the
> database & the log file of our production site.
> I would like to restore this backup to our test DB with the production
> backup in order for it to resemble the production one. Should I uncheck
> the
> log file backup when restoring or is it ok to restore it as well? Both
> files
> come up in the restore as ticked. What are the implications of this?
> Thanks very much for any ideas on this
> Ant
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
Ant
The log files will bring you closer to the most recent state of the original
DB.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
Hi,
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
Ant
|||Hello,
To bring the database up to latest you may need to restore the LOG backups
as well.
Perform below:-
1. Restore the lastest full database backup with NORECOVERY
2. Restore the subsequent log backups one by one with NORECOVER until last
log backup
3. Restore the last log backup with RECOVERY to make the database
operational.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
> Hi,
> Our SQL server has a database with scheduled backups which backup the
> database & the log file of our production site.
> I would like to restore this backup to our test DB with the production
> backup in order for it to resemble the production one. Should I uncheck
> the
> log file backup when restoring or is it ok to restore it as well? Both
> files
> come up in the restore as ticked. What are the implications of this?
> Thanks very much for any ideas on this
> Ant
Restoring log & backup
Hi,
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
AntThe log files will bring you closer to the most recent state of the original
DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
Hi,
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
Ant|||Hello,
To bring the database up to latest you may need to restore the LOG backups
as well.
Perform below:-
1. Restore the lastest full database backup with NORECOVERY
2. Restore the subsequent log backups one by one with NORECOVER until last
log backup
3. Restore the last log backup with RECOVERY to make the database
operational.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
> Hi,
> Our SQL server has a database with scheduled backups which backup the
> database & the log file of our production site.
> I would like to restore this backup to our test DB with the production
> backup in order for it to resemble the production one. Should I uncheck
> the
> log file backup when restoring or is it ok to restore it as well? Both
> files
> come up in the restore as ticked. What are the implications of this?
> Thanks very much for any ideas on this
> Ant
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
AntThe log files will bring you closer to the most recent state of the original
DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
Hi,
Our SQL server has a database with scheduled backups which backup the
database & the log file of our production site.
I would like to restore this backup to our test DB with the production
backup in order for it to resemble the production one. Should I uncheck the
log file backup when restoring or is it ok to restore it as well? Both files
come up in the restore as ticked. What are the implications of this?
Thanks very much for any ideas on this
Ant|||Hello,
To bring the database up to latest you may need to restore the LOG backups
as well.
Perform below:-
1. Restore the lastest full database backup with NORECOVERY
2. Restore the subsequent log backups one by one with NORECOVER until last
log backup
3. Restore the last log backup with RECOVERY to make the database
operational.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DA827430-11D9-459D-A005-234C3F3035CF@.microsoft.com...
> Hi,
> Our SQL server has a database with scheduled backups which backup the
> database & the log file of our production site.
> I would like to restore this backup to our test DB with the production
> backup in order for it to resemble the production one. Should I uncheck
> the
> log file backup when restoring or is it ok to restore it as well? Both
> files
> come up in the restore as ticked. What are the implications of this?
> Thanks very much for any ideas on this
> Ant
restoring from tape to different server
hello all,
I preform regular sql server 2000 backups on our production server (Windows
2000 Server). I now need to restore, the Production DB to the backup server
(Windows 2000 Server) from tape (hp dds-4 data cartridge). The restore db
wizard (database radio button selected) should shows a list of all available
backups, but my backups to tape are not visible. Can someone explain this? I
know tape backups actually exist because I had verify backup selected and I
can see them on the production server.
When I select the "From Device" radio but on the wizard and then select my
tape drive everything is accepted, however everything locks up when I attemp
t
to "View Contents..." Any Ideas about that?
Please advise and remember I am a rookie?
much thanks
AndreAndre
Did you perform your backups direct to tape or did you do them to disk first
then copy to tape? If you did them to disk (the best way for speed and ease
of use), I suggest you copy them back to disk, then do you restore.
I'm afraid I don't know what your problem is if you performed your backups
direct to tape, as I have not done that since the days of version 6.5. Rathe
r
than using EM, have you tried using query analyser, with the restore command
?
HTH
John
"Andre Gibson" wrote:
> hello all,
> I preform regular sql server 2000 backups on our production server (Window
s
> 2000 Server). I now need to restore, the Production DB to the backup serve
r
> (Windows 2000 Server) from tape (hp dds-4 data cartridge). The restore db
> wizard (database radio button selected) should shows a list of all availab
le
> backups, but my backups to tape are not visible. Can someone explain this?
I
> know tape backups actually exist because I had verify backup selected and
I
> can see them on the production server.
> When I select the "From Device" radio but on the wizard and then select my
> tape drive everything is accepted, however everything locks up when I atte
mpt
> to "View Contents..." Any Ideas about that?
> Please advise and remember I am a rookie?
> much thanks
> Andre
>
I preform regular sql server 2000 backups on our production server (Windows
2000 Server). I now need to restore, the Production DB to the backup server
(Windows 2000 Server) from tape (hp dds-4 data cartridge). The restore db
wizard (database radio button selected) should shows a list of all available
backups, but my backups to tape are not visible. Can someone explain this? I
know tape backups actually exist because I had verify backup selected and I
can see them on the production server.
When I select the "From Device" radio but on the wizard and then select my
tape drive everything is accepted, however everything locks up when I attemp
t
to "View Contents..." Any Ideas about that?
Please advise and remember I am a rookie?
much thanks
AndreAndre
Did you perform your backups direct to tape or did you do them to disk first
then copy to tape? If you did them to disk (the best way for speed and ease
of use), I suggest you copy them back to disk, then do you restore.
I'm afraid I don't know what your problem is if you performed your backups
direct to tape, as I have not done that since the days of version 6.5. Rathe
r
than using EM, have you tried using query analyser, with the restore command
?
HTH
John
"Andre Gibson" wrote:
> hello all,
> I preform regular sql server 2000 backups on our production server (Window
s
> 2000 Server). I now need to restore, the Production DB to the backup serve
r
> (Windows 2000 Server) from tape (hp dds-4 data cartridge). The restore db
> wizard (database radio button selected) should shows a list of all availab
le
> backups, but my backups to tape are not visible. Can someone explain this?
I
> know tape backups actually exist because I had verify backup selected and
I
> can see them on the production server.
> When I select the "From Device" radio but on the wizard and then select my
> tape drive everything is accepted, however everything locks up when I atte
mpt
> to "View Contents..." Any Ideas about that?
> Please advise and remember I am a rookie?
> much thanks
> Andre
>
restoring from tape to different server
hello all,
I preform regular sql server 2000 backups on our production server (Windows
2000 Server). I now need to restore, the Production DB to the backup server
(Windows 2000 Server) from tape (hp dds-4 data cartridge). The restore db
wizard (database radio button selected) should shows a list of all available
backups, but my backups to tape are not visible. Can someone explain this? I
know tape backups actually exist because I had verify backup selected and I
can see them on the production server.
When I select the "From Device" radio but on the wizard and then select my
tape drive everything is accepted, however everything locks up when I attempt
to "View Contents..." Any Ideas about that?
Please advise and remember I am a rookie?
much thanks
AndreAndre
Did you perform your backups direct to tape or did you do them to disk first
then copy to tape? If you did them to disk (the best way for speed and ease
of use), I suggest you copy them back to disk, then do you restore.
I'm afraid I don't know what your problem is if you performed your backups
direct to tape, as I have not done that since the days of version 6.5. Rather
than using EM, have you tried using query analyser, with the restore command?
HTH
John
"Andre Gibson" wrote:
> hello all,
> I preform regular sql server 2000 backups on our production server (Windows
> 2000 Server). I now need to restore, the Production DB to the backup server
> (Windows 2000 Server) from tape (hp dds-4 data cartridge). The restore db
> wizard (database radio button selected) should shows a list of all available
> backups, but my backups to tape are not visible. Can someone explain this? I
> know tape backups actually exist because I had verify backup selected and I
> can see them on the production server.
> When I select the "From Device" radio but on the wizard and then select my
> tape drive everything is accepted, however everything locks up when I attempt
> to "View Contents..." Any Ideas about that?
> Please advise and remember I am a rookie?
> much thanks
> Andre
>
I preform regular sql server 2000 backups on our production server (Windows
2000 Server). I now need to restore, the Production DB to the backup server
(Windows 2000 Server) from tape (hp dds-4 data cartridge). The restore db
wizard (database radio button selected) should shows a list of all available
backups, but my backups to tape are not visible. Can someone explain this? I
know tape backups actually exist because I had verify backup selected and I
can see them on the production server.
When I select the "From Device" radio but on the wizard and then select my
tape drive everything is accepted, however everything locks up when I attempt
to "View Contents..." Any Ideas about that?
Please advise and remember I am a rookie?
much thanks
AndreAndre
Did you perform your backups direct to tape or did you do them to disk first
then copy to tape? If you did them to disk (the best way for speed and ease
of use), I suggest you copy them back to disk, then do you restore.
I'm afraid I don't know what your problem is if you performed your backups
direct to tape, as I have not done that since the days of version 6.5. Rather
than using EM, have you tried using query analyser, with the restore command?
HTH
John
"Andre Gibson" wrote:
> hello all,
> I preform regular sql server 2000 backups on our production server (Windows
> 2000 Server). I now need to restore, the Production DB to the backup server
> (Windows 2000 Server) from tape (hp dds-4 data cartridge). The restore db
> wizard (database radio button selected) should shows a list of all available
> backups, but my backups to tape are not visible. Can someone explain this? I
> know tape backups actually exist because I had verify backup selected and I
> can see them on the production server.
> When I select the "From Device" radio but on the wizard and then select my
> tape drive everything is accepted, however everything locks up when I attempt
> to "View Contents..." Any Ideas about that?
> Please advise and remember I am a rookie?
> much thanks
> Andre
>
Wednesday, March 7, 2012
restoring from .bak
i have sql server 2005 dev installed on a laptop. i have 2 .bak's from production sql server 2005 database. i went through the gui restore procedure and both backup files completed successfully. now both databases are in the (Restoring...) status and have been for quite some time. i have sp2 applied. when will these become available? each .bak was around 400 mb. it has been a few hours.Did you refresh your GUI afterte restore? Can you connect to your database?|||it's now the morning and been at least a good 12 hours. refresh in the GUI shows them still in restoring status. i closed and reopened the management studio and they are still both restoring. i am unable to choose either database from the drop down when i open a new query window. i told it during restore to do them in norecovery mode under the expectation that this would be the fastest.|||i did notice that the data files created were substantially bigger... one backup created a 7.2gb mdf, 92mb ldf, the other created a 543mb mdf, 1.3gb ldf, and 371mb ndf... actually it appears when I specified the new filepath for that one from the backup, that I forgot the suffix. will that matter? I suppose I could stop the db service and rename it to an .ndf real quick. doesn't seem to be the cause of my slow restore issue though..|||FYI i restored the db in norecovery mode, under the impression that db access was denied only for the duration of the restore. i tried it with the first option later and the db's loaded fine. there must be some step to flip the db into available once in the restoring status, however it was not obvious/intuitive to me based on the gui or error messages i was receiving. but i seemed to be missing that step.|||I've experienced the same symptom, but with both norecovery and recovery options. Each time this produces a process that cannot be terminated until the machine is turned off. No errors are logged in sql server or windows. I've also verified the database backup through the verifyrestoreonly option and by restoring to another machine/instance.
Any thoughts?
|||i did not appear to have an active ongoing process; my cpu appeared idle. i didn't have a problem installing sp2 hoping it would fix the problem while the databases were in the restoring state. my assumption is there is some toggle (that should be made more apparent in gui error messages) that one could flip in there or some hidden sql to flip the db to go. i'm just naively jumping into this and figuring it out, my assumption is some book (a book online maybe) covers this situation (?) or perhaps it is some flakey bug.|||
That's your key.
Restoring a database in norecovery mode tells SQL Server that you intend to apply transaction log backups to roll the database forward before making it available. Thus the restore process is not complete. That's why your database is staying in 'restoring' state.
To bring your database online, simply use the command RESTORE DATABASE <dbname> WITH RECOVERY
You don't need a backup file for this - it just invokes the recovery logic to bring the DB online.
restoring from .bak
i have sql server 2005 dev installed on a laptop. i have 2 .bak's from production sql server 2005 database. i went through the gui restore procedure and both backup files completed successfully. now both databases are in the (Restoring...) status and have been for quite some time. i have sp2 applied. when will these become available? each .bak was around 400 mb. it has been a few hours.Did you refresh your GUI afterte restore? Can you connect to your database?|||it's now the morning and been at least a good 12 hours. refresh in the GUI shows them still in restoring status. i closed and reopened the management studio and they are still both restoring. i am unable to choose either database from the drop down when i open a new query window. i told it during restore to do them in norecovery mode under the expectation that this would be the fastest.|||i did notice that the data files created were substantially bigger... one backup created a 7.2gb mdf, 92mb ldf, the other created a 543mb mdf, 1.3gb ldf, and 371mb ndf... actually it appears when I specified the new filepath for that one from the backup, that I forgot the suffix. will that matter? I suppose I could stop the db service and rename it to an .ndf real quick. doesn't seem to be the cause of my slow restore issue though..|||FYI i restored the db in norecovery mode, under the impression that db access was denied only for the duration of the restore. i tried it with the first option later and the db's loaded fine. there must be some step to flip the db into available once in the restoring status, however it was not obvious/intuitive to me based on the gui or error messages i was receiving. but i seemed to be missing that step.|||I've experienced the same symptom, but with both norecovery and recovery options. Each time this produces a process that cannot be terminated until the machine is turned off. No errors are logged in sql server or windows. I've also verified the database backup through the verifyrestoreonly option and by restoring to another machine/instance.
Any thoughts?
|||i did not appear to have an active ongoing process; my cpu appeared idle. i didn't have a problem installing sp2 hoping it would fix the problem while the databases were in the restoring state. my assumption is there is some toggle (that should be made more apparent in gui error messages) that one could flip in there or some hidden sql to flip the db to go. i'm just naively jumping into this and figuring it out, my assumption is some book (a book online maybe) covers this situation (?) or perhaps it is some flakey bug.|||
That's your key.
Restoring a database in norecovery mode tells SQL Server that you intend to apply transaction log backups to roll the database forward before making it available. Thus the restore process is not complete. That's why your database is staying in 'restoring' state.
To bring your database online, simply use the command RESTORE DATABASE <dbname> WITH RECOVERY
You don't need a backup file for this - it just invokes the recovery logic to bring the DB online.
Saturday, February 25, 2012
Restoring DBs as different names for testing
Good morning,
There is a job that runs daily to restore the production databases to a
different server for testing purposes so that the test site can have live
data on a daily basis. There is a script that runs after the database
restore to ensure views/procedures/triggers are not referencing the
production REPORTING server... however it errors out everyday. As I did not
build this job and the old DBA left months ago... I'm stumped as to how I
can resolve this.
The script is below... cphprod is the restored database (the prod db is
cph...) The error I receive everyday is: Error string: The column prefix
'cphprod' does not match with a table name or alias name used in the query.
Error source: Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0 Error Detail Records: Error: -2147217900
(80040E14); Provider Error: 107 (6B) ...
Any idea how I can work around and/or resolve this?
Does anyone have a better way to change all references to a prod reporting
environment?
Thanks in advance for your help...
----
--
use cphprod
go
declare @.name varchar(255), @.type char(2), @.id int
,@.contline int
,@.text varchar(8000),@.text2 varchar(8000)
,@.text3 varchar(8000)
,@.text4 varchar(8000)
,@.text5 varchar(8000)
,@.text6 varchar(8000)
,@.sql varchar(8000)
,@.char10 char(1)
set @.char10 = @.char10
declare mycursor insensitive cursor for
select distinct a.name,type, a.id
from sysobjects a
join syscomments b on a.id = b.id
where type in ('p','v','fn')
and (text like '%cph.%' or text like '%ss002repl.Billing.%' )
--and name = 'ar_oa_Invoice_Master'
--order by a.id,colid
order by type desc
open mycursor
--select max(colid) from syscomments
fetch next from mycursor into @.name, @.type, @.id
while @.@.fetch_status = 0
begin
set @.text = @.char10
set @.text2 = @.char10
set @.text3 = @.char10
set @.text4 = @.char10
set @.text5 = @.char10
set @.text6 = @.char10
set @.contline = 0
select top 1 @.text = text, @.contline = colid
from syscomments where id = @.id
while (select top 1 colid
from syscomments
where id = @.id and colid > @.contline) is not null
begin
if @.contline = 1
select top 1 @.text2 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 2
select top 1 @.text3 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 3
select top 1 @.text4 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 4
select top 1 @.text5 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 5
select top 1 @.text6 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
end
set @.text = replace(@.text,'cph.','cphprod.')
set @.text2 = replace(@.text2,'cph.','cphprod.')
set @.text3 = replace(@.text3,'cph.','cphprod.')
set @.text4 = replace(@.text4,'cph.','cphprod.')
set @.text5 = replace(@.text5,'cph.','cphprod.')
set @.text6 = replace(@.text6,'cph.','cphprod.')
--ss002repl.Billing.
set @.text = replace(@.text,'ss002repl.Billing.','Billingprod.')
set @.text2 = replace(@.text2,'ss002repl.Billing.','Billingprod.')
set @.text3 = replace(@.text3,'ss002repl.Billing.','Billingprod.')
set @.text4 = replace(@.text4,'ss002repl.Billing.','Billingprod.')
set @.text5 = replace(@.text5,'ss002repl.Billing.','Billingprod.')
set @.text6 = replace(@.text6,'ss002repl.Billing.','Billingprod.')
if @.type = 'p'
begin
set @.sql = 'drop proc ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
else
if @.type = 'v'
begin
set @.sql = 'drop view ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
else
if @.type = 'fn'
begin
set @.sql = 'drop function ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
fetch next from mycursor into @.name, @.type, @.id
end
close mycursor
deallocate mycursor
go
set xact_abort on
----
-->> There is a script that runs after the database restore to ensure
I could be wrong here, but are you updating the system tables here? Can you
explain what logic is being employed here?
On a cursory glance, I see some meaningless statements like "set @.char10 =
@.char10" and excessive usage of TOP clauses in your script.
The error message suggests that you are trying to use a table name/alias
which is already replaced in the FROM clause, but still exists in some other
section of a stored procedure, view or function. Without a detailed
inspection of the code and test it out, it is hard to spot out which
procedure, view or function is the real culprint here.
Anith
There is a job that runs daily to restore the production databases to a
different server for testing purposes so that the test site can have live
data on a daily basis. There is a script that runs after the database
restore to ensure views/procedures/triggers are not referencing the
production REPORTING server... however it errors out everyday. As I did not
build this job and the old DBA left months ago... I'm stumped as to how I
can resolve this.
The script is below... cphprod is the restored database (the prod db is
cph...) The error I receive everyday is: Error string: The column prefix
'cphprod' does not match with a table name or alias name used in the query.
Error source: Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0 Error Detail Records: Error: -2147217900
(80040E14); Provider Error: 107 (6B) ...
Any idea how I can work around and/or resolve this?
Does anyone have a better way to change all references to a prod reporting
environment?
Thanks in advance for your help...
----
--
use cphprod
go
declare @.name varchar(255), @.type char(2), @.id int
,@.contline int
,@.text varchar(8000),@.text2 varchar(8000)
,@.text3 varchar(8000)
,@.text4 varchar(8000)
,@.text5 varchar(8000)
,@.text6 varchar(8000)
,@.sql varchar(8000)
,@.char10 char(1)
set @.char10 = @.char10
declare mycursor insensitive cursor for
select distinct a.name,type, a.id
from sysobjects a
join syscomments b on a.id = b.id
where type in ('p','v','fn')
and (text like '%cph.%' or text like '%ss002repl.Billing.%' )
--and name = 'ar_oa_Invoice_Master'
--order by a.id,colid
order by type desc
open mycursor
--select max(colid) from syscomments
fetch next from mycursor into @.name, @.type, @.id
while @.@.fetch_status = 0
begin
set @.text = @.char10
set @.text2 = @.char10
set @.text3 = @.char10
set @.text4 = @.char10
set @.text5 = @.char10
set @.text6 = @.char10
set @.contline = 0
select top 1 @.text = text, @.contline = colid
from syscomments where id = @.id
while (select top 1 colid
from syscomments
where id = @.id and colid > @.contline) is not null
begin
if @.contline = 1
select top 1 @.text2 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 2
select top 1 @.text3 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 3
select top 1 @.text4 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 4
select top 1 @.text5 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 5
select top 1 @.text6 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
end
set @.text = replace(@.text,'cph.','cphprod.')
set @.text2 = replace(@.text2,'cph.','cphprod.')
set @.text3 = replace(@.text3,'cph.','cphprod.')
set @.text4 = replace(@.text4,'cph.','cphprod.')
set @.text5 = replace(@.text5,'cph.','cphprod.')
set @.text6 = replace(@.text6,'cph.','cphprod.')
--ss002repl.Billing.
set @.text = replace(@.text,'ss002repl.Billing.','Billingprod.')
set @.text2 = replace(@.text2,'ss002repl.Billing.','Billingprod.')
set @.text3 = replace(@.text3,'ss002repl.Billing.','Billingprod.')
set @.text4 = replace(@.text4,'ss002repl.Billing.','Billingprod.')
set @.text5 = replace(@.text5,'ss002repl.Billing.','Billingprod.')
set @.text6 = replace(@.text6,'ss002repl.Billing.','Billingprod.')
if @.type = 'p'
begin
set @.sql = 'drop proc ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
else
if @.type = 'v'
begin
set @.sql = 'drop view ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
else
if @.type = 'fn'
begin
set @.sql = 'drop function ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
fetch next from mycursor into @.name, @.type, @.id
end
close mycursor
deallocate mycursor
go
set xact_abort on
----
-->> There is a script that runs after the database restore to ensure
I could be wrong here, but are you updating the system tables here? Can you
explain what logic is being employed here?
On a cursory glance, I see some meaningless statements like "set @.char10 =
@.char10" and excessive usage of TOP clauses in your script.
The error message suggests that you are trying to use a table name/alias
which is already replaced in the FROM clause, but still exists in some other
section of a stored procedure, view or function. Without a detailed
inspection of the code and test it out, it is hard to spot out which
procedure, view or function is the real culprint here.
Anith
Tuesday, February 21, 2012
Restoring database on my localhost
Hi there,
I am using VWD and have my production website hosted elsewhere. The hosting company provides a backup service that allows me to download a something.bak file as the backup of my website. My query is: how to export or copy the data in the something.bak file into the database on my own computer? Can anyone helps please?
Thanks in advance.
Why you want to use .bak file. Just connect you sql server to remote database using enterprise manager and import the database from remote server to your server.
Please let me know if you have any doubt.
Thanks for your response.
I am not familiar with SQL server. I am using .bak because the backup service provided by my hosting company uses this method. BTW, what I have is just a VWD. Where can I get the enterprise manager?
Regards.
|||you can download SQL Management studio expresshere. You need external database access from your hosting company, I suggest contacting your hosting company for more information.
Regards Frederik
Subscribe to:
Posts (Atom)