Restore/Create new db using existing mdf and ldf

Hi All,

I'm sure there's a simple answer for this but I haven't found anything so far that gives me the info I'm needing, so if anyone could help, I'd be grateful!

I've been given mdf and ldf files from an existing off-site database to be "restored" locally (sqlserver 7). Usually I restore from a backup file, so I haven't had any experience with this scenario.

I've found info regarding sp_attach...if I create a new database locally and run sp_attach to point to the mdf and ldf files, will this work?

Many thanks in advance for any suggestions!

Cheers,
Megan:rolleyes:

Have answered my own question and for anyone who might have the same issue, yes it can be done by sp_attach_db very easily.
This will create a new database, and you just need to configure your users (sp_change_users_login), etc, again when it's attached.

Easy as!

Restore SQL2K on different machine

Hello,
Would anyone know or recommend/advise the easiest approach in bringing down
and retiring an existing SQL2K running on W2K Server and brining it up with
all of the data, jobs, etc. onto a newer server machine? Wondering if there
was a way maybe do a full server restore from one of our server backup tapes
instead of having to reinstall/reconfigure everything from scratch.
Thanks in advance.Imaging is one approach if you have an imaging software like StorageCraft or
Acronis. But a main consideration here is hardware and configuration. The
source and destination may have different disk configurations. Another
considertation maybe to use Win2K3 instead of Win2K. If what you're
intending to restore is simply SQL Server, just a backup of all system and
user database would be enough and a very good restore process
On the old SQL Server instance,
1) stop all applications accessing your SQL Server
2) backup all your system and user databases
3) identify the latest hotfix you have. This is necessary for you to
restore on the new one
On the new SQL Server instance,
1) Install SQL Server together with the service packs and hotfixes up to the
one you have on the old instance
2) start SQL Server in single-user mode using the sqlservr.exe -c -m
3) restore the master database and restart the SQL Server service
4) restore the msdb database - be sure the SQLAgent service is not runnig
before you do so
5) restore model database if necessary
6) reconfigure your tempdb if necessary
7) restore all user databases and reconfigure if necessary - file locations
in particular
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:OmuenB62HHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Would anyone know or recommend/advise the easiest approach in bringing
> down and retiring an existing SQL2K running on W2K Server and brining it
> up with all of the data, jobs, etc. onto a newer server machine?
> Wondering if there was a way maybe do a full server restore from one of
> our server backup tapes instead of having to reinstall/reconfigure
> everything from scratch.
> Thanks in advance.
>|||Actually I personally am leaning more towards putting it on a w2k3 OS
instead of w2k but am concerned if there would be anything in w2k3 that
wouldn't support w2k as in the system files, ad/domain, etc? Like would you
happen to know if there were anything that was left out in w2k3 that was in
w2k?
Thanks a bunch for your quick and helpful reply. Much appreciated
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:uVKtk%2372HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Imaging is one approach if you have an imaging software like StorageCraft
> or Acronis. But a main consideration here is hardware and configuration.
> The source and destination may have different disk configurations. Another
> considertation maybe to use Win2K3 instead of Win2K. If what you're
> intending to restore is simply SQL Server, just a backup of all system and
> user database would be enough and a very good restore process
> On the old SQL Server instance,
> 1) stop all applications accessing your SQL Server
> 2) backup all your system and user databases
> 3) identify the latest hotfix you have. This is necessary for you to
> restore on the new one
> On the new SQL Server instance,
> 1) Install SQL Server together with the service packs and hotfixes up to
> the one you have on the old instance
> 2) start SQL Server in single-user mode using the sqlservr.exe -c -m
> 3) restore the master database and restart the SQL Server service
> 4) restore the msdb database - be sure the SQLAgent service is not runnig
> before you do so
> 5) restore model database if necessary
> 6) reconfigure your tempdb if necessary
> 7) restore all user databases and reconfigure if necessary - file
> locations in particular
>
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:OmuenB62HHA.4712@.TK2MSFTNGP04.phx.gbl...
>|||Moving SQL Server 2000 from Win2K to Win2K3 will be just fine. Just remember
to ignore the warning when you install SQL Server 2000 on a Win2K3 telling
you that you do not have SP3. Install the latest service pack after
installing SQL Server 2000
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:u%238963c3HHA.4584@.TK2MSFTNGP03.phx.gbl...
> Actually I personally am leaning more towards putting it on a w2k3 OS
> instead of w2k but am concerned if there would be anything in w2k3 that
> wouldn't support w2k as in the system files, ad/domain, etc? Like would
> you happen to know if there were anything that was left out in w2k3 that
> was in w2k?
> Thanks a bunch for your quick and helpful reply. Much appreciated
>
> "bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
> news:uVKtk%2372HHA.5316@.TK2MSFTNGP04.phx.gbl...
>|||I guess 2 main things that come to mind on that w2k server is that I've
installed a certificate from an ms ca service (in use for network ssl 'Force
protocol encryption' SQL Server Network Utility) in which I can just do
another certificate request/install and some .dll's that a VB app uses but I
would think that these shouldn't be an issue in that w2k3 would definitely
continue to support these. Will look into it further. Thanks so much for
your speedy informative reply. Will greatly help out in clearing things up
and expediting my research
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:u$QuvTm3HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Moving SQL Server 2000 from Win2K to Win2K3 will be just fine. Just
> remember to ignore the warning when you install SQL Server 2000 on a
> Win2K3 telling you that you do not have SP3. Install the latest service
> pack after installing SQL Server 2000
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:u%238963c3HHA.4584@.TK2MSFTNGP03.phx.gbl...
>|||Sorry if I'm over analyzing this perhaps but I can't seem to visualize some
of the "X" factors like how to keep the same server name, domain and IP
address the same after rebuilding/restoring the master db onto the new
machine since this holds the domain logins/jobs. Are there any websites
that can layout it out step by step in retiring an old w2k-sql2k and restore
it onto a newer machine with w2k3-sql2k?
Thanks for your helpful replies bass_player.
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:u$QuvTm3HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Moving SQL Server 2000 from Win2K to Win2K3 will be just fine. Just
> remember to ignore the warning when you install SQL Server 2000 on a
> Win2K3 telling you that you do not have SP3. Install the latest service
> pack after installing SQL Server 2000
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:u%238963c3HHA.4584@.TK2MSFTNGP03.phx.gbl...
>

Restore SQL2K on different machine

Hello,
Would anyone know or recommend/advise the easiest approach in bringing down
and retiring an existing SQL2K running on W2K Server and brining it up with
all of the data, jobs, etc. onto a newer server machine? Wondering if there
was a way maybe do a full server restore from one of our server backup tapes
instead of having to reinstall/reconfigure everything from scratch.
Thanks in advance.
Imaging is one approach if you have an imaging software like StorageCraft or
Acronis. But a main consideration here is hardware and configuration. The
source and destination may have different disk configurations. Another
considertation maybe to use Win2K3 instead of Win2K. If what you're
intending to restore is simply SQL Server, just a backup of all system and
user database would be enough and a very good restore process
On the old SQL Server instance,
1) stop all applications accessing your SQL Server
2) backup all your system and user databases
3) identify the latest hotfix you have. This is necessary for you to
restore on the new one
On the new SQL Server instance,
1) Install SQL Server together with the service packs and hotfixes up to the
one you have on the old instance
2) start SQL Server in single-user mode using the sqlservr.exe -c -m
3) restore the master database and restart the SQL Server service
4) restore the msdb database - be sure the SQLAgent service is not runnig
before you do so
5) restore model database if necessary
6) reconfigure your tempdb if necessary
7) restore all user databases and reconfigure if necessary - file locations
in particular
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:OmuenB62HHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Would anyone know or recommend/advise the easiest approach in bringing
> down and retiring an existing SQL2K running on W2K Server and brining it
> up with all of the data, jobs, etc. onto a newer server machine?
> Wondering if there was a way maybe do a full server restore from one of
> our server backup tapes instead of having to reinstall/reconfigure
> everything from scratch.
> Thanks in advance.
>
|||Actually I personally am leaning more towards putting it on a w2k3 OS
instead of w2k but am concerned if there would be anything in w2k3 that
wouldn't support w2k as in the system files, ad/domain, etc? Like would you
happen to know if there were anything that was left out in w2k3 that was in
w2k?
Thanks a bunch for your quick and helpful reply. Much appreciated
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:uVKtk%2372HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Imaging is one approach if you have an imaging software like StorageCraft
> or Acronis. But a main consideration here is hardware and configuration.
> The source and destination may have different disk configurations. Another
> considertation maybe to use Win2K3 instead of Win2K. If what you're
> intending to restore is simply SQL Server, just a backup of all system and
> user database would be enough and a very good restore process
> On the old SQL Server instance,
> 1) stop all applications accessing your SQL Server
> 2) backup all your system and user databases
> 3) identify the latest hotfix you have. This is necessary for you to
> restore on the new one
> On the new SQL Server instance,
> 1) Install SQL Server together with the service packs and hotfixes up to
> the one you have on the old instance
> 2) start SQL Server in single-user mode using the sqlservr.exe -c -m
> 3) restore the master database and restart the SQL Server service
> 4) restore the msdb database - be sure the SQLAgent service is not runnig
> before you do so
> 5) restore model database if necessary
> 6) reconfigure your tempdb if necessary
> 7) restore all user databases and reconfigure if necessary - file
> locations in particular
>
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:OmuenB62HHA.4712@.TK2MSFTNGP04.phx.gbl...
>
|||Moving SQL Server 2000 from Win2K to Win2K3 will be just fine. Just remember
to ignore the warning when you install SQL Server 2000 on a Win2K3 telling
you that you do not have SP3. Install the latest service pack after
installing SQL Server 2000
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:u%238963c3HHA.4584@.TK2MSFTNGP03.phx.gbl...
> Actually I personally am leaning more towards putting it on a w2k3 OS
> instead of w2k but am concerned if there would be anything in w2k3 that
> wouldn't support w2k as in the system files, ad/domain, etc? Like would
> you happen to know if there were anything that was left out in w2k3 that
> was in w2k?
> Thanks a bunch for your quick and helpful reply. Much appreciated
>
> "bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
> news:uVKtk%2372HHA.5316@.TK2MSFTNGP04.phx.gbl...
>
|||I guess 2 main things that come to mind on that w2k server is that I've
installed a certificate from an ms ca service (in use for network ssl 'Force
protocol encryption' SQL Server Network Utility) in which I can just do
another certificate request/install and some .dll's that a VB app uses but I
would think that these shouldn't be an issue in that w2k3 would definitely
continue to support these. Will look into it further. Thanks so much for
your speedy informative reply. Will greatly help out in clearing things up
and expediting my research
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:u$QuvTm3HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Moving SQL Server 2000 from Win2K to Win2K3 will be just fine. Just
> remember to ignore the warning when you install SQL Server 2000 on a
> Win2K3 telling you that you do not have SP3. Install the latest service
> pack after installing SQL Server 2000
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:u%238963c3HHA.4584@.TK2MSFTNGP03.phx.gbl...
>
|||Sorry if I'm over analyzing this perhaps but I can't seem to visualize some
of the "X" factors like how to keep the same server name, domain and IP
address the same after rebuilding/restoring the master db onto the new
machine since this holds the domain logins/jobs. Are there any websites
that can layout it out step by step in retiring an old w2k-sql2k and restore
it onto a newer machine with w2k3-sql2k?
Thanks for your helpful replies bass_player.
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:u$QuvTm3HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Moving SQL Server 2000 from Win2K to Win2K3 will be just fine. Just
> remember to ignore the warning when you install SQL Server 2000 on a
> Win2K3 telling you that you do not have SP3. Install the latest service
> pack after installing SQL Server 2000
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:u%238963c3HHA.4584@.TK2MSFTNGP03.phx.gbl...
>

Restore SQL2K on different machine

Hello,
Would anyone know or recommend/advise the easiest approach in bringing down
and retiring an existing SQL2K running on W2K Server and brining it up with
all of the data, jobs, etc. onto a newer server machine? Wondering if there
was a way maybe do a full server restore from one of our server backup tapes
instead of having to reinstall/reconfigure everything from scratch.
Thanks in advance.Imaging is one approach if you have an imaging software like StorageCraft or
Acronis. But a main consideration here is hardware and configuration. The
source and destination may have different disk configurations. Another
considertation maybe to use Win2K3 instead of Win2K. If what you're
intending to restore is simply SQL Server, just a backup of all system and
user database would be enough and a very good restore process
On the old SQL Server instance,
1) stop all applications accessing your SQL Server
2) backup all your system and user databases
3) identify the latest hotfix you have. This is necessary for you to
restore on the new one
On the new SQL Server instance,
1) Install SQL Server together with the service packs and hotfixes up to the
one you have on the old instance
2) start SQL Server in single-user mode using the sqlservr.exe -c -m
3) restore the master database and restart the SQL Server service
4) restore the msdb database - be sure the SQLAgent service is not runnig
before you do so
5) restore model database if necessary
6) reconfigure your tempdb if necessary
7) restore all user databases and reconfigure if necessary - file locations
in particular
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:OmuenB62HHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Would anyone know or recommend/advise the easiest approach in bringing
> down and retiring an existing SQL2K running on W2K Server and brining it
> up with all of the data, jobs, etc. onto a newer server machine?
> Wondering if there was a way maybe do a full server restore from one of
> our server backup tapes instead of having to reinstall/reconfigure
> everything from scratch.
> Thanks in advance.
>|||Actually I personally am leaning more towards putting it on a w2k3 OS
instead of w2k but am concerned if there would be anything in w2k3 that
wouldn't support w2k as in the system files, ad/domain, etc? Like would you
happen to know if there were anything that was left out in w2k3 that was in
w2k?
Thanks a bunch for your quick and helpful reply. Much appreciated :)
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:uVKtk%2372HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Imaging is one approach if you have an imaging software like StorageCraft
> or Acronis. But a main consideration here is hardware and configuration.
> The source and destination may have different disk configurations. Another
> considertation maybe to use Win2K3 instead of Win2K. If what you're
> intending to restore is simply SQL Server, just a backup of all system and
> user database would be enough and a very good restore process
> On the old SQL Server instance,
> 1) stop all applications accessing your SQL Server
> 2) backup all your system and user databases
> 3) identify the latest hotfix you have. This is necessary for you to
> restore on the new one
> On the new SQL Server instance,
> 1) Install SQL Server together with the service packs and hotfixes up to
> the one you have on the old instance
> 2) start SQL Server in single-user mode using the sqlservr.exe -c -m
> 3) restore the master database and restart the SQL Server service
> 4) restore the msdb database - be sure the SQLAgent service is not runnig
> before you do so
> 5) restore model database if necessary
> 6) reconfigure your tempdb if necessary
> 7) restore all user databases and reconfigure if necessary - file
> locations in particular
>
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:OmuenB62HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Would anyone know or recommend/advise the easiest approach in bringing
>> down and retiring an existing SQL2K running on W2K Server and brining it
>> up with all of the data, jobs, etc. onto a newer server machine?
>> Wondering if there was a way maybe do a full server restore from one of
>> our server backup tapes instead of having to reinstall/reconfigure
>> everything from scratch.
>> Thanks in advance.
>|||Moving SQL Server 2000 from Win2K to Win2K3 will be just fine. Just remember
to ignore the warning when you install SQL Server 2000 on a Win2K3 telling
you that you do not have SP3. Install the latest service pack after
installing SQL Server 2000
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:u%238963c3HHA.4584@.TK2MSFTNGP03.phx.gbl...
> Actually I personally am leaning more towards putting it on a w2k3 OS
> instead of w2k but am concerned if there would be anything in w2k3 that
> wouldn't support w2k as in the system files, ad/domain, etc? Like would
> you happen to know if there were anything that was left out in w2k3 that
> was in w2k?
> Thanks a bunch for your quick and helpful reply. Much appreciated :)
>
> "bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
> news:uVKtk%2372HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Imaging is one approach if you have an imaging software like StorageCraft
>> or Acronis. But a main consideration here is hardware and configuration.
>> The source and destination may have different disk configurations.
>> Another considertation maybe to use Win2K3 instead of Win2K. If what
>> you're intending to restore is simply SQL Server, just a backup of all
>> system and user database would be enough and a very good restore process
>> On the old SQL Server instance,
>> 1) stop all applications accessing your SQL Server
>> 2) backup all your system and user databases
>> 3) identify the latest hotfix you have. This is necessary for you to
>> restore on the new one
>> On the new SQL Server instance,
>> 1) Install SQL Server together with the service packs and hotfixes up to
>> the one you have on the old instance
>> 2) start SQL Server in single-user mode using the sqlservr.exe -c -m
>> 3) restore the master database and restart the SQL Server service
>> 4) restore the msdb database - be sure the SQLAgent service is not runnig
>> before you do so
>> 5) restore model database if necessary
>> 6) reconfigure your tempdb if necessary
>> 7) restore all user databases and reconfigure if necessary - file
>> locations in particular
>>
>> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
>> news:OmuenB62HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Would anyone know or recommend/advise the easiest approach in bringing
>> down and retiring an existing SQL2K running on W2K Server and brining it
>> up with all of the data, jobs, etc. onto a newer server machine?
>> Wondering if there was a way maybe do a full server restore from one of
>> our server backup tapes instead of having to reinstall/reconfigure
>> everything from scratch.
>> Thanks in advance.
>>
>|||I guess 2 main things that come to mind on that w2k server is that I've
installed a certificate from an ms ca service (in use for network ssl 'Force
protocol encryption' SQL Server Network Utility) in which I can just do
another certificate request/install and some .dll's that a VB app uses but I
would think that these shouldn't be an issue in that w2k3 would definitely
continue to support these. Will look into it further. Thanks so much for
your speedy informative reply. Will greatly help out in clearing things up
and expediting my research :)
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:u$QuvTm3HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Moving SQL Server 2000 from Win2K to Win2K3 will be just fine. Just
> remember to ignore the warning when you install SQL Server 2000 on a
> Win2K3 telling you that you do not have SP3. Install the latest service
> pack after installing SQL Server 2000
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:u%238963c3HHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Actually I personally am leaning more towards putting it on a w2k3 OS
>> instead of w2k but am concerned if there would be anything in w2k3 that
>> wouldn't support w2k as in the system files, ad/domain, etc? Like would
>> you happen to know if there were anything that was left out in w2k3 that
>> was in w2k?
>> Thanks a bunch for your quick and helpful reply. Much appreciated :)
>>
>> "bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
>> news:uVKtk%2372HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Imaging is one approach if you have an imaging software like
>> StorageCraft or Acronis. But a main consideration here is hardware and
>> configuration. The source and destination may have different disk
>> configurations. Another considertation maybe to use Win2K3 instead of
>> Win2K. If what you're intending to restore is simply SQL Server, just a
>> backup of all system and user database would be enough and a very good
>> restore process
>> On the old SQL Server instance,
>> 1) stop all applications accessing your SQL Server
>> 2) backup all your system and user databases
>> 3) identify the latest hotfix you have. This is necessary for you to
>> restore on the new one
>> On the new SQL Server instance,
>> 1) Install SQL Server together with the service packs and hotfixes up to
>> the one you have on the old instance
>> 2) start SQL Server in single-user mode using the sqlservr.exe -c -m
>> 3) restore the master database and restart the SQL Server service
>> 4) restore the msdb database - be sure the SQLAgent service is not
>> runnig before you do so
>> 5) restore model database if necessary
>> 6) reconfigure your tempdb if necessary
>> 7) restore all user databases and reconfigure if necessary - file
>> locations in particular
>>
>> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
>> news:OmuenB62HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Would anyone know or recommend/advise the easiest approach in bringing
>> down and retiring an existing SQL2K running on W2K Server and brining
>> it up with all of the data, jobs, etc. onto a newer server machine?
>> Wondering if there was a way maybe do a full server restore from one of
>> our server backup tapes instead of having to reinstall/reconfigure
>> everything from scratch.
>> Thanks in advance.
>>
>>
>|||Sorry if I'm over analyzing this perhaps but I can't seem to visualize some
of the "X" factors like how to keep the same server name, domain and IP
address the same after rebuilding/restoring the master db onto the new
machine since this holds the domain logins/jobs. Are there any websites
that can layout it out step by step in retiring an old w2k-sql2k and restore
it onto a newer machine with w2k3-sql2k?
Thanks for your helpful replies bass_player.
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:u$QuvTm3HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Moving SQL Server 2000 from Win2K to Win2K3 will be just fine. Just
> remember to ignore the warning when you install SQL Server 2000 on a
> Win2K3 telling you that you do not have SP3. Install the latest service
> pack after installing SQL Server 2000
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:u%238963c3HHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Actually I personally am leaning more towards putting it on a w2k3 OS
>> instead of w2k but am concerned if there would be anything in w2k3 that
>> wouldn't support w2k as in the system files, ad/domain, etc? Like would
>> you happen to know if there were anything that was left out in w2k3 that
>> was in w2k?
>> Thanks a bunch for your quick and helpful reply. Much appreciated :)
>>
>> "bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
>> news:uVKtk%2372HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Imaging is one approach if you have an imaging software like
>> StorageCraft or Acronis. But a main consideration here is hardware and
>> configuration. The source and destination may have different disk
>> configurations. Another considertation maybe to use Win2K3 instead of
>> Win2K. If what you're intending to restore is simply SQL Server, just a
>> backup of all system and user database would be enough and a very good
>> restore process
>> On the old SQL Server instance,
>> 1) stop all applications accessing your SQL Server
>> 2) backup all your system and user databases
>> 3) identify the latest hotfix you have. This is necessary for you to
>> restore on the new one
>> On the new SQL Server instance,
>> 1) Install SQL Server together with the service packs and hotfixes up to
>> the one you have on the old instance
>> 2) start SQL Server in single-user mode using the sqlservr.exe -c -m
>> 3) restore the master database and restart the SQL Server service
>> 4) restore the msdb database - be sure the SQLAgent service is not
>> runnig before you do so
>> 5) restore model database if necessary
>> 6) reconfigure your tempdb if necessary
>> 7) restore all user databases and reconfigure if necessary - file
>> locations in particular
>>
>> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
>> news:OmuenB62HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Would anyone know or recommend/advise the easiest approach in bringing
>> down and retiring an existing SQL2K running on W2K Server and brining
>> it up with all of the data, jobs, etc. onto a newer server machine?
>> Wondering if there was a way maybe do a full server restore from one of
>> our server backup tapes instead of having to reinstall/reconfigure
>> everything from scratch.
>> Thanks in advance.
>>
>>
>|||Just a quick question in that did you mean w2k3 SP2 instead of SP3? My
coworker the network admin had w2k3 SP1 currently installed on our new
server and was wondering if he should go ahead and install SP2 which then
I'll install the sql2k or should I install sql2k first and then let him
install w2k3 SP2 and the rest of the windows updates?
Thanks again bass_player.
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:u$QuvTm3HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Moving SQL Server 2000 from Win2K to Win2K3 will be just fine. Just
> remember to ignore the warning when you install SQL Server 2000 on a
> Win2K3 telling you that you do not have SP3. Install the latest service
> pack after installing SQL Server 2000
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:u%238963c3HHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Actually I personally am leaning more towards putting it on a w2k3 OS
>> instead of w2k but am concerned if there would be anything in w2k3 that
>> wouldn't support w2k as in the system files, ad/domain, etc? Like would
>> you happen to know if there were anything that was left out in w2k3 that
>> was in w2k?
>> Thanks a bunch for your quick and helpful reply. Much appreciated :)
>>
>> "bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
>> news:uVKtk%2372HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Imaging is one approach if you have an imaging software like
>> StorageCraft or Acronis. But a main consideration here is hardware and
>> configuration. The source and destination may have different disk
>> configurations. Another considertation maybe to use Win2K3 instead of
>> Win2K. If what you're intending to restore is simply SQL Server, just a
>> backup of all system and user database would be enough and a very good
>> restore process
>> On the old SQL Server instance,
>> 1) stop all applications accessing your SQL Server
>> 2) backup all your system and user databases
>> 3) identify the latest hotfix you have. This is necessary for you to
>> restore on the new one
>> On the new SQL Server instance,
>> 1) Install SQL Server together with the service packs and hotfixes up to
>> the one you have on the old instance
>> 2) start SQL Server in single-user mode using the sqlservr.exe -c -m
>> 3) restore the master database and restart the SQL Server service
>> 4) restore the msdb database - be sure the SQLAgent service is not
>> runnig before you do so
>> 5) restore model database if necessary
>> 6) reconfigure your tempdb if necessary
>> 7) restore all user databases and reconfigure if necessary - file
>> locations in particular
>>
>> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
>> news:OmuenB62HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Would anyone know or recommend/advise the easiest approach in bringing
>> down and retiring an existing SQL2K running on W2K Server and brining
>> it up with all of the data, jobs, etc. onto a newer server machine?
>> Wondering if there was a way maybe do a full server restore from one of
>> our server backup tapes instead of having to reinstall/reconfigure
>> everything from scratch.
>> Thanks in advance.
>>
>>
>sql

Restore Messed Up Table Names

Using SQL Server 2000 and moving to a new computer. We did a full backup of
the existing database to tape, brought up the new computer with a clean
install using the same server name and IP address, and did a full restore.
Not only were some permissions messed up, but Crystal Reports 10 and some
Access Data Projects refused to run. I finally discovered while running an
SP_WHO that the individual database names that we'd created (meaning not
'master' and the other standard tables) had several dozen blanks appended
onto the end of them. Looking at dbnames in the SP_WHO made it clear that
this had happened, and once I knew what I was looking for it was apparent in
Enterprise Manager as well when I'd select a database name in the left pane.
Interestingly, VB6 applications have no trouble connecting to these tables
without modification of the connection string. Every single CR10 report so
far has had to have it's tables relinked, and this has broken some other
code that looks at dbnames.

1: How could something like this happen?

2: How is it best fixed?

Thanks!
DavidDavid C. Barber (david@.NOSPAMdbarber.com) writes:

Quote:

Originally Posted by

Using SQL Server 2000 and moving to a new computer. We did a full
backup of the existing database to tape, brought up the new computer
with a clean install using the same server name and IP address, and did
a full restore. Not only were some permissions messed up, but Crystal
Reports 10 and some Access Data Projects refused to run. I finally
discovered while running an SP_WHO that the individual database names
that we'd created (meaning not 'master' and the other standard tables)
had several dozen blanks appended onto the end of them. Looking at
dbnames in the SP_WHO made it clear that this had happened, and once I
knew what I was looking for it was apparent in Enterprise Manager as
well when I'd select a database name in the left pane. Interestingly,
VB6 applications have no trouble connecting to these tables without
modification of the connection string. Every single CR10 report so far
has had to have it's tables relinked, and this has broken some other
code that looks at dbnames.
>
1: How could something like this happen?
>
2: How is it best fixed?

I'm a little confused. You first say "existing database" in singular,
and then you say "individual database names" in plural. Your subject
talks about messed up table names, but table names do not display in
sp_who. Then again, you call master a table.

I'm sorry if I'm picky, but if I don't understand what you mean, it's
difficult to answer. But I try to address it as good as I can:

1) If the database are messed up, this is probably because you added
the spaces when you restored the databases on the new server. The
database names are not carried over from the old server. Or did you
copy master too?

2) If the table names have been altered this would be very strange.
I would even say that it is impossible.

3) What typically does gets messed up when you move databases like this
is the mapping between server logins and database users. This can easily
be examined with sp_helpuser. If you have a random mapping, then you
have this problem. The stored procedure sp_changes_users_login can
be used to address this.

--
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|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9832738788829Yazorman@.127.0.0.1...

Quote:

Originally Posted by

David C. Barber (david@.NOSPAMdbarber.com) writes:

Quote:

Originally Posted by

Using SQL Server 2000 and moving to a new computer. We did a full
backup of the existing database to tape, brought up the new computer
with a clean install using the same server name and IP address, and did
a full restore. Not only were some permissions messed up, but Crystal
Reports 10 and some Access Data Projects refused to run. I finally
discovered while running an SP_WHO that the individual database names
that we'd created (meaning not 'master' and the other standard tables)
had several dozen blanks appended onto the end of them. Looking at
dbnames in the SP_WHO made it clear that this had happened, and once I
knew what I was looking for it was apparent in Enterprise Manager as
well when I'd select a database name in the left pane. Interestingly,
VB6 applications have no trouble connecting to these tables without
modification of the connection string. Every single CR10 report so far
has had to have it's tables relinked, and this has broken some other
code that looks at dbnames.

1: How could something like this happen?

2: How is it best fixed?

>
I'm a little confused. You first say "existing database" in singular,
and then you say "individual database names" in plural. Your subject
talks about messed up table names, but table names do not display in
sp_who. Then again, you call master a table.
>
I'm sorry if I'm picky, but if I don't understand what you mean, it's
difficult to answer. But I try to address it as good as I can:
>
1) If the database are messed up, this is probably because you added
the spaces when you restored the databases on the new server. The
database names are not carried over from the old server. Or did you
copy master too?
>
2) If the table names have been altered this would be very strange.
I would even say that it is impossible.
>
3) What typically does gets messed up when you move databases like this
is the mapping between server logins and database users. This can easily
be examined with sp_helpuser. If you have a random mapping, then you
have this problem. The stored procedure sp_changes_users_login can
be used to address this.

Sorry that I wasn't more clear before. Comes of being in too much of a
hurry.

We were upgrading the server hardware for our SQL Server 2000.

We backed up the entire existing server as a full backup.

We installed the new hardware and software, named the new server to be
identical to the old server which was taken off-line entirely.

We used the same IP address with the new server box.

We did a full restore of the backed up server databases.

The resulting individual databases, except for the standard ones like master
that are created by SQL Server itself all restored with several dozen blanks
appended to the end of the existing database name, which appears to break
Crystal Reports 10, Access Data Projects, and some VB6 code, although not
the basic connection to the database using VB6.

We don't know why this happened, nor the best way(s) to fix it.

David|||David C. Barber (david@.NOSPAMdbarber.com) writes:

Quote:

Originally Posted by

We were upgrading the server hardware for our SQL Server 2000.
>
We backed up the entire existing server as a full backup.

So you backed all files on the server with Windows backup, and you did not
backup the individual databases through SQL Servers own BACKUP command?

Did you stop SQL Server prior to starting this backup?

Quote:

Originally Posted by

The resulting individual databases, except for the standard ones like
master that are created by SQL Server itself all restored with several
dozen blanks appended to the end of the existing database name, which
appears to break Crystal Reports 10, Access Data Projects, and some VB6
code, although not the basic connection to the database using VB6.

Could you post the output of this query:

SELECT len(name), datalength(name)/2, name
FROM master..sysdatabases
ORDER BY name

Is the old hardware available, so you can start it, and run the same
query there?

--
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