When you guys perform a regular reboot of your production SQL Servers,
do you just use the Start button and Shutdown/Restart? Or do you stop
SQL Server first, then reboot the server?
Stopping SQL Server first, then rebooting the server, has always
seemed obvious to me. My boss wants to just use the Start button and
reboot the boxes, because this has never caused us any problems, and
will save the time of taking the extra steps to stop SQL Server first.
Now that I'm looking for it, I can't find one clear bit of Best
Practice documentation on this!
Thanks much!We have always just used the system shut down from the
start or ctl alt del option... and no problems..
>--Original Message--
>When you guys perform a regular reboot of your production
SQL Servers,
>do you just use the Start button and Shutdown/Restart?
Or do you stop
>SQL Server first, then reboot the server?
>Stopping SQL Server first, then rebooting the server, has
always
>seemed obvious to me. My boss wants to just use the
Start button and
>reboot the boxes, because this has never caused us any
problems, and
>will save the time of taking the extra steps to stop SQL
Server first.
>Now that I'm looking for it, I can't find one clear bit
of Best
>Practice documentation on this!
>Thanks much!
>.
>|||I tend to just reboot the servers , this has always worked fine for me on
hundreds of servers big and small.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||James,
I have never needed to do a regular reboot of a SQL Server since Windows
2000, unless I have changed some hardware or installed software that
required a reboot. NT4 was a little different...
If the server is a dedicated server and there are no other applications on
there, then Shutdown should work fine without having to stop the service.
Remember to stop application services on your application servers before
rebooting SQL Server, it will be cleaner.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||You can just reboot the server. When Windows does a normal shutdown, it
first stops all running services, including SQL server.
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||I know everyone does it this way (just reboots), and in hundreds of
reboots, I've never had anything happen, as far as I know. My thought
is, you can just shutdown the O/S, and let the Service Control Manager
send a shutdown request to the SQL server service (as well as all the
other services), which attempts an orderly shutdown (logins disabled,
sql statements allowed to finish, and database checkpoints). But as I
understand, if this doesn't happen in 20 seconds (with SQL Server
competing with all other services shutting down during this time), the
SCM kills the service, so on restart SQL Server not only has to
rollback uncompleted transactions, but may have torn pages.
If anybody can shoot this concern out of the water, I'll be glad to
hear it, as just rebooting the server without doing anything else is a
bit easier!
Thanks
"Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...
> You can just reboot the server. When Windows does a normal shutdown, it
> first stops all running services, including SQL server.
> "James" <throwawaythrowaway@.hotmail.com> wrote in message
> news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> > When you guys perform a regular reboot of your production SQL Servers,
> > do you just use the Start button and Shutdown/Restart? Or do you stop
> > SQL Server first, then reboot the server?
> >
> > Stopping SQL Server first, then rebooting the server, has always
> > seemed obvious to me. My boss wants to just use the Start button and
> > reboot the boxes, because this has never caused us any problems, and
> > will save the time of taking the extra steps to stop SQL Server first.
> >
> > Now that I'm looking for it, I can't find one clear bit of Best
> > Practice documentation on this!
> >
> > Thanks much!|||You are mostly correct, but your concern about torn pages is largely
unfounded. Torn pages occur when Windows "thinks" data has been written to
disk, but in fact it has only been written to a disk cache when the power
fails. If your disk cache doesn't have a battery backup there would likely
be some pages that are partially written to disk and thus inconsistent.
I normally shut down SQL Server by itself to avoid unnecessarily long
recovery times and forcing a rollback on transactions that should be
committed etc...
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405180616.2eb650db@.posting.google.com...
> I know everyone does it this way (just reboots), and in hundreds of
> reboots, I've never had anything happen, as far as I know. My thought
> is, you can just shutdown the O/S, and let the Service Control Manager
> send a shutdown request to the SQL server service (as well as all the
> other services), which attempts an orderly shutdown (logins disabled,
> sql statements allowed to finish, and database checkpoints). But as I
> understand, if this doesn't happen in 20 seconds (with SQL Server
> competing with all other services shutting down during this time), the
> SCM kills the service, so on restart SQL Server not only has to
> rollback uncompleted transactions, but may have torn pages.
> If anybody can shoot this concern out of the water, I'll be glad to
> hear it, as just rebooting the server without doing anything else is a
> bit easier!
> Thanks
> "Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message
news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...
> > You can just reboot the server. When Windows does a normal shutdown, it
> > first stops all running services, including SQL server.
> >
> > "James" <throwawaythrowaway@.hotmail.com> wrote in message
> > news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> > > When you guys perform a regular reboot of your production SQL Servers,
> > > do you just use the Start button and Shutdown/Restart? Or do you stop
> > > SQL Server first, then reboot the server?
> > >
> > > Stopping SQL Server first, then rebooting the server, has always
> > > seemed obvious to me. My boss wants to just use the Start button and
> > > reboot the boxes, because this has never caused us any problems, and
> > > will save the time of taking the extra steps to stop SQL Server first.
> > >
> > > Now that I'm looking for it, I can't find one clear bit of Best
> > > Practice documentation on this!
> > >
> > > Thanks much!sql
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Thursday, March 29, 2012
Correct Way to Reboot a SQL Server
When you guys perform a regular reboot of your production SQL Servers,
do you just use the Start button and Shutdown/Restart? Or do you stop
SQL Server first, then reboot the server?
Stopping SQL Server first, then rebooting the server, has always
seemed obvious to me. My boss wants to just use the Start button and
reboot the boxes, because this has never caused us any problems, and
will save the time of taking the extra steps to stop SQL Server first.
Now that I'm looking for it, I can't find one clear bit of Best
Practice documentation on this!
Thanks much!I tend to just reboot the servers , this has always worked fine for me on
hundreds of servers big and small.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||James,
I have never needed to do a regular reboot of a SQL Server since Windows
2000, unless I have changed some hardware or installed software that
required a reboot. NT4 was a little different...
If the server is a dedicated server and there are no other applications on
there, then Shutdown should work fine without having to stop the service.
Remember to stop application services on your application servers before
rebooting SQL Server, it will be cleaner.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||You can just reboot the server. When Windows does a normal shutdown, it
first stops all running services, including SQL server.
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||I know everyone does it this way (just reboots), and in hundreds of
reboots, I've never had anything happen, as far as I know. My thought
is, you can just shutdown the O/S, and let the Service Control Manager
send a shutdown request to the SQL server service (as well as all the
other services), which attempts an orderly shutdown (logins disabled,
sql statements allowed to finish, and database checkpoints). But as I
understand, if this doesn't happen in 20 seconds (with SQL Server
competing with all other services shutting down during this time), the
SCM kills the service, so on restart SQL Server not only has to
rollback uncompleted transactions, but may have torn pages.
If anybody can shoot this concern out of the water, I'll be glad to
hear it, as just rebooting the server without doing anything else is a
bit easier!
Thanks
"Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message news:<u#YPFoJPEHA.2
976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> You can just reboot the server. When Windows does a normal shutdown, it
> first stops all running services, including SQL server.
> "James" <throwawaythrowaway@.hotmail.com> wrote in message
> news:27d8ba57.0405171047.4d8b8067@.posting.google.com...|||You are mostly correct, but your concern about torn pages is largely
unfounded. Torn pages occur when Windows "thinks" data has been written to
disk, but in fact it has only been written to a disk cache when the power
fails. If your disk cache doesn't have a battery backup there would likely
be some pages that are partially written to disk and thus inconsistent.
I normally shut down SQL Server by itself to avoid unnecessarily long
recovery times and forcing a rollback on transactions that should be
committed etc...
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405180616.2eb650db@.posting.google.com...
> I know everyone does it this way (just reboots), and in hundreds of
> reboots, I've never had anything happen, as far as I know. My thought
> is, you can just shutdown the O/S, and let the Service Control Manager
> send a shutdown request to the SQL server service (as well as all the
> other services), which attempts an orderly shutdown (logins disabled,
> sql statements allowed to finish, and database checkpoints). But as I
> understand, if this doesn't happen in 20 seconds (with SQL Server
> competing with all other services shutting down during this time), the
> SCM kills the service, so on restart SQL Server not only has to
> rollback uncompleted transactions, but may have torn pages.
> If anybody can shoot this concern out of the water, I'll be glad to
> hear it, as just rebooting the server without doing anything else is a
> bit easier!
> Thanks
> "Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message
news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
do you just use the Start button and Shutdown/Restart? Or do you stop
SQL Server first, then reboot the server?
Stopping SQL Server first, then rebooting the server, has always
seemed obvious to me. My boss wants to just use the Start button and
reboot the boxes, because this has never caused us any problems, and
will save the time of taking the extra steps to stop SQL Server first.
Now that I'm looking for it, I can't find one clear bit of Best
Practice documentation on this!
Thanks much!I tend to just reboot the servers , this has always worked fine for me on
hundreds of servers big and small.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||James,
I have never needed to do a regular reboot of a SQL Server since Windows
2000, unless I have changed some hardware or installed software that
required a reboot. NT4 was a little different...
If the server is a dedicated server and there are no other applications on
there, then Shutdown should work fine without having to stop the service.
Remember to stop application services on your application servers before
rebooting SQL Server, it will be cleaner.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||You can just reboot the server. When Windows does a normal shutdown, it
first stops all running services, including SQL server.
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||I know everyone does it this way (just reboots), and in hundreds of
reboots, I've never had anything happen, as far as I know. My thought
is, you can just shutdown the O/S, and let the Service Control Manager
send a shutdown request to the SQL server service (as well as all the
other services), which attempts an orderly shutdown (logins disabled,
sql statements allowed to finish, and database checkpoints). But as I
understand, if this doesn't happen in 20 seconds (with SQL Server
competing with all other services shutting down during this time), the
SCM kills the service, so on restart SQL Server not only has to
rollback uncompleted transactions, but may have torn pages.
If anybody can shoot this concern out of the water, I'll be glad to
hear it, as just rebooting the server without doing anything else is a
bit easier!
Thanks
"Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message news:<u#YPFoJPEHA.2
976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> You can just reboot the server. When Windows does a normal shutdown, it
> first stops all running services, including SQL server.
> "James" <throwawaythrowaway@.hotmail.com> wrote in message
> news:27d8ba57.0405171047.4d8b8067@.posting.google.com...|||You are mostly correct, but your concern about torn pages is largely
unfounded. Torn pages occur when Windows "thinks" data has been written to
disk, but in fact it has only been written to a disk cache when the power
fails. If your disk cache doesn't have a battery backup there would likely
be some pages that are partially written to disk and thus inconsistent.
I normally shut down SQL Server by itself to avoid unnecessarily long
recovery times and forcing a rollback on transactions that should be
committed etc...
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405180616.2eb650db@.posting.google.com...
> I know everyone does it this way (just reboots), and in hundreds of
> reboots, I've never had anything happen, as far as I know. My thought
> is, you can just shutdown the O/S, and let the Service Control Manager
> send a shutdown request to the SQL server service (as well as all the
> other services), which attempts an orderly shutdown (logins disabled,
> sql statements allowed to finish, and database checkpoints). But as I
> understand, if this doesn't happen in 20 seconds (with SQL Server
> competing with all other services shutting down during this time), the
> SCM kills the service, so on restart SQL Server not only has to
> rollback uncompleted transactions, but may have torn pages.
> If anybody can shoot this concern out of the water, I'll be glad to
> hear it, as just rebooting the server without doing anything else is a
> bit easier!
> Thanks
> "Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message
news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
Correct Way to Reboot a SQL Server
When you guys perform a regular reboot of your production SQL Servers,
do you just use the Start button and Shutdown/Restart? Or do you stop
SQL Server first, then reboot the server?
Stopping SQL Server first, then rebooting the server, has always
seemed obvious to me. My boss wants to just use the Start button and
reboot the boxes, because this has never caused us any problems, and
will save the time of taking the extra steps to stop SQL Server first.
Now that I'm looking for it, I can't find one clear bit of Best
Practice documentation on this!
Thanks much!
I tend to just reboot the servers , this has always worked fine for me on
hundreds of servers big and small.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!
|||James,
I have never needed to do a regular reboot of a SQL Server since Windows
2000, unless I have changed some hardware or installed software that
required a reboot. NT4 was a little different...
If the server is a dedicated server and there are no other applications on
there, then Shutdown should work fine without having to stop the service.
Remember to stop application services on your application servers before
rebooting SQL Server, it will be cleaner.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!
|||You can just reboot the server. When Windows does a normal shutdown, it
first stops all running services, including SQL server.
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!
|||I know everyone does it this way (just reboots), and in hundreds of
reboots, I've never had anything happen, as far as I know. My thought
is, you can just shutdown the O/S, and let the Service Control Manager
send a shutdown request to the SQL server service (as well as all the
other services), which attempts an orderly shutdown (logins disabled,
sql statements allowed to finish, and database checkpoints). But as I
understand, if this doesn't happen in 20 seconds (with SQL Server
competing with all other services shutting down during this time), the
SCM kills the service, so on restart SQL Server not only has to
rollback uncompleted transactions, but may have torn pages.
If anybody can shoot this concern out of the water, I'll be glad to
hear it, as just rebooting the server without doing anything else is a
bit easier!
Thanks
"Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> You can just reboot the server. When Windows does a normal shutdown, it
> first stops all running services, including SQL server.
> "James" <throwawaythrowaway@.hotmail.com> wrote in message
> news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
|||You are mostly correct, but your concern about torn pages is largely
unfounded. Torn pages occur when Windows "thinks" data has been written to
disk, but in fact it has only been written to a disk cache when the power
fails. If your disk cache doesn't have a battery backup there would likely
be some pages that are partially written to disk and thus inconsistent.
I normally shut down SQL Server by itself to avoid unnecessarily long
recovery times and forcing a rollback on transactions that should be
committed etc...
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405180616.2eb650db@.posting.google.c om...
> I know everyone does it this way (just reboots), and in hundreds of
> reboots, I've never had anything happen, as far as I know. My thought
> is, you can just shutdown the O/S, and let the Service Control Manager
> send a shutdown request to the SQL server service (as well as all the
> other services), which attempts an orderly shutdown (logins disabled,
> sql statements allowed to finish, and database checkpoints). But as I
> understand, if this doesn't happen in 20 seconds (with SQL Server
> competing with all other services shutting down during this time), the
> SCM kills the service, so on restart SQL Server not only has to
> rollback uncompleted transactions, but may have torn pages.
> If anybody can shoot this concern out of the water, I'll be glad to
> hear it, as just rebooting the server without doing anything else is a
> bit easier!
> Thanks
> "Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message
news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
do you just use the Start button and Shutdown/Restart? Or do you stop
SQL Server first, then reboot the server?
Stopping SQL Server first, then rebooting the server, has always
seemed obvious to me. My boss wants to just use the Start button and
reboot the boxes, because this has never caused us any problems, and
will save the time of taking the extra steps to stop SQL Server first.
Now that I'm looking for it, I can't find one clear bit of Best
Practice documentation on this!
Thanks much!
I tend to just reboot the servers , this has always worked fine for me on
hundreds of servers big and small.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!
|||James,
I have never needed to do a regular reboot of a SQL Server since Windows
2000, unless I have changed some hardware or installed software that
required a reboot. NT4 was a little different...
If the server is a dedicated server and there are no other applications on
there, then Shutdown should work fine without having to stop the service.
Remember to stop application services on your application servers before
rebooting SQL Server, it will be cleaner.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!
|||You can just reboot the server. When Windows does a normal shutdown, it
first stops all running services, including SQL server.
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!
|||I know everyone does it this way (just reboots), and in hundreds of
reboots, I've never had anything happen, as far as I know. My thought
is, you can just shutdown the O/S, and let the Service Control Manager
send a shutdown request to the SQL server service (as well as all the
other services), which attempts an orderly shutdown (logins disabled,
sql statements allowed to finish, and database checkpoints). But as I
understand, if this doesn't happen in 20 seconds (with SQL Server
competing with all other services shutting down during this time), the
SCM kills the service, so on restart SQL Server not only has to
rollback uncompleted transactions, but may have torn pages.
If anybody can shoot this concern out of the water, I'll be glad to
hear it, as just rebooting the server without doing anything else is a
bit easier!
Thanks
"Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> You can just reboot the server. When Windows does a normal shutdown, it
> first stops all running services, including SQL server.
> "James" <throwawaythrowaway@.hotmail.com> wrote in message
> news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
|||You are mostly correct, but your concern about torn pages is largely
unfounded. Torn pages occur when Windows "thinks" data has been written to
disk, but in fact it has only been written to a disk cache when the power
fails. If your disk cache doesn't have a battery backup there would likely
be some pages that are partially written to disk and thus inconsistent.
I normally shut down SQL Server by itself to avoid unnecessarily long
recovery times and forcing a rollback on transactions that should be
committed etc...
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405180616.2eb650db@.posting.google.c om...
> I know everyone does it this way (just reboots), and in hundreds of
> reboots, I've never had anything happen, as far as I know. My thought
> is, you can just shutdown the O/S, and let the Service Control Manager
> send a shutdown request to the SQL server service (as well as all the
> other services), which attempts an orderly shutdown (logins disabled,
> sql statements allowed to finish, and database checkpoints). But as I
> understand, if this doesn't happen in 20 seconds (with SQL Server
> competing with all other services shutting down during this time), the
> SCM kills the service, so on restart SQL Server not only has to
> rollback uncompleted transactions, but may have torn pages.
> If anybody can shoot this concern out of the water, I'll be glad to
> hear it, as just rebooting the server without doing anything else is a
> bit easier!
> Thanks
> "Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message
news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
Sunday, March 25, 2012
copying tables in SQL Server
Hi,
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.
did you hear "=?Utf-8?B?c2hhcm1hbg==?="
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:
> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.
|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY if
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easiest
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:
> Hi,
> I am a newbie. I want to copy a table from the production database and paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.
|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:
> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
>
>
|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
[vbcol=seagreen]
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.
did you hear "=?Utf-8?B?c2hhcm1hbg==?="
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:
> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.
|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY if
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easiest
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:
> Hi,
> I am a newbie. I want to copy a table from the production database and paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.
|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:
> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
>
>
|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
[vbcol=seagreen]
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:
copying tables in SQL Server
Hi,
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.did you hear "examnotes"
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:
> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY i
f
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easies
t
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:
> Hi,
> I am a newbie. I want to copy a table from the production database and pas
te
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:
> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See t
he
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
>
>|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
[vbcol=seagreen]
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:
>sql
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.did you hear "examnotes"
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:
> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY i
f
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easies
t
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:
> Hi,
> I am a newbie. I want to copy a table from the production database and pas
te
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:
> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See t
he
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
>
>|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
[vbcol=seagreen]
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:
>sql
copying tables in SQL Server
Hi,
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.did you hear "=?Utf-8?B?c2hhcm1hbg==?="
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:
> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY if
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easiest
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:
> Hi,
> I am a newbie. I want to copy a table from the production database and paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:
> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> > Hi,
> >
> > I am a newbie. I want to copy a table from the production database and
> > paste
> > it in the test database thus replacing the old copy in the test database.
> > What's the best way to do it? Thanks in advance.
>
>|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:
> > Sharman,
> >
> > Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> > SQL Books Online for more information.
> >
> > HTH
> >
> > Jerry
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> > > Hi,
> > >
> > > I am a newbie. I want to copy a table from the production database and
> > > paste
> > > it in the test database thus replacing the old copy in the test database.
> > > What's the best way to do it? Thanks in advance.
> >
> >
> >
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.did you hear "=?Utf-8?B?c2hhcm1hbg==?="
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:
> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY if
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easiest
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:
> Hi,
> I am a newbie. I want to copy a table from the production database and paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:
> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> > Hi,
> >
> > I am a newbie. I want to copy a table from the production database and
> > paste
> > it in the test database thus replacing the old copy in the test database.
> > What's the best way to do it? Thanks in advance.
>
>|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:
> > Sharman,
> >
> > Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> > SQL Books Online for more information.
> >
> > HTH
> >
> > Jerry
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> > > Hi,
> > >
> > > I am a newbie. I want to copy a table from the production database and
> > > paste
> > > it in the test database thus replacing the old copy in the test database.
> > > What's the best way to do it? Thanks in advance.
> >
> >
> >
Thursday, March 22, 2012
Copying table data from a different database
I am attempting to copy table data from our live production database to our development database. To do this, I need to specify the name of the database in addition to the table name. I'm running into some problems that I'm hoping are just syntax related. Here is a portion of the code that runs fine when I don't specify a database...
>> SELECT name,description FROM cec_cecil_permissions
And here is a portion of the code I'm trying to run that results in an error...
>> SELECT name,description FROM devsql01.cec_cecil_permissions
When I run this, I get the following error...
>> Invalid object name 'devsql01.cec_cecil_permissions'.
My first thought was that I need to specify the complete path to the table within the database, so I ran the following code...
>> SELECT name,description FROM devsql01.Databases.Intranet.Tables.cec_cecil_permi ssions
This resulted in the following error...
>> The object name 'devsql01.Databases.Intranet.Tables.cec_cecil_perm issions' contains more than the maximum number of prefixes. The maximum is 3.
I can't reduce the number of prefixes because I'd be leaving out part of the path to the table. I tried changing every "." to a "\" but that resulted in a syntax error...
>> Incorrect syntax near '\'.
What do I need to do to make this work? Thanks in advance for your help!Never mind. I realized I was putting the server name in instead of the database name.
Copying stored procedures to another database
Hi,
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex co
de for the assembly
instead of the file reference. Then also script your objects created from th
at assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl.
.
> Hi,
> We have recently deployed our application into a parallel production envir
onment which will
> eventually become the main production environment. We have had to make som
e changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production.
Now we are ready to
> deploy these stored procedures to our new production environment. Unfortun
ately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to
keep the production
> data.
> Is there a way that we can copy the stored procedures only from our develo
pment server to our
> production server? They're CLR stored procedures, so we can't script them
out either.
> Thanks
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex co
de for the assembly
instead of the file reference. Then also script your objects created from th
at assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl.
.
> Hi,
> We have recently deployed our application into a parallel production envir
onment which will
> eventually become the main production environment. We have had to make som
e changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production.
Now we are ready to
> deploy these stored procedures to our new production environment. Unfortun
ately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to
keep the production
> data.
> Is there a way that we can copy the stored procedures only from our develo
pment server to our
> production server? They're CLR stored procedures, so we can't script them
out either.
> Thanks
Copying stored procedures to another database
Hi,
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex code for the assembly
instead of the file reference. Then also script your objects created from that assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have recently deployed our application into a parallel production environment which will
> eventually become the main production environment. We have had to make some changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production. Now we are ready to
> deploy these stored procedures to our new production environment. Unfortunately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to keep the production
> data.
> Is there a way that we can copy the stored procedures only from our development server to our
> production server? They're CLR stored procedures, so we can't script them out either.
> Thanks
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex code for the assembly
instead of the file reference. Then also script your objects created from that assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have recently deployed our application into a parallel production environment which will
> eventually become the main production environment. We have had to make some changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production. Now we are ready to
> deploy these stored procedures to our new production environment. Unfortunately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to keep the production
> data.
> Is there a way that we can copy the stored procedures only from our development server to our
> production server? They're CLR stored procedures, so we can't script them out either.
> Thanks
Tuesday, March 20, 2012
Copying records from 7.0 2000 with problems
Hi.
I need to copy data from one database to another, on separate servers. I've got an old production database on "server1" and its SQL Server 7.0 on NT4. This data needs to move from there over to a database on "server2" which is SQL2000/Windows2000server.
Im trying to just run test queries from "server2" to read tables on "server1" and all I can get is 'invalid object name.'
Server1 is a linked server under Server2 and is being logged into as sa.
The computername for server1 happens to be "DATABASE"
Is this a problem? From what I understand I should be able to select from <server name>.<dbname>.dbo.<tablename>
I have tried typing <server name> as [DATABASE] and "DATABASE" and any other delimiting under the sun but all I get is 'invalid object name'
I have also tried just trying to select max(helptext) from "DATABASE".master.dbo.helpsql and I still get 'invalid object'
All of my queries will work if run from query analyzer on "server1" but not from query analyzer on "server2"
thanks in advance,
-jakedo you intend to move all the data in the database??
if you are maybe you can use the dts tools or if possible restore the full backup to the server2.
I can't really remember if sql 7's backup file can be restore to sql 2k version as haven't been using for quite a long time but you can try.|||I am only moving the data from select tables. The database names are different too.
I've pretty much ruled out the possibility of a backup/restore senario.|||have u tried import/export ??|||It was a communication issue.
I Turned Mixed Protocol OFF on the old 7.0 box and turned it ON the 2K box. Go Figure. The most confusing part of this ordeal was that the login for linked server was working from both servers and you could see the master tables/views under linked server.|||sorry in that case can't help you much
hopefully somebody else can help you on this|||Wow. That was like phone tag in a message board. Don't think I've ever experienced that.
I need to copy data from one database to another, on separate servers. I've got an old production database on "server1" and its SQL Server 7.0 on NT4. This data needs to move from there over to a database on "server2" which is SQL2000/Windows2000server.
Im trying to just run test queries from "server2" to read tables on "server1" and all I can get is 'invalid object name.'
Server1 is a linked server under Server2 and is being logged into as sa.
The computername for server1 happens to be "DATABASE"
Is this a problem? From what I understand I should be able to select from <server name>.<dbname>.dbo.<tablename>
I have tried typing <server name> as [DATABASE] and "DATABASE" and any other delimiting under the sun but all I get is 'invalid object name'
I have also tried just trying to select max(helptext) from "DATABASE".master.dbo.helpsql and I still get 'invalid object'
All of my queries will work if run from query analyzer on "server1" but not from query analyzer on "server2"
thanks in advance,
-jakedo you intend to move all the data in the database??
if you are maybe you can use the dts tools or if possible restore the full backup to the server2.
I can't really remember if sql 7's backup file can be restore to sql 2k version as haven't been using for quite a long time but you can try.|||I am only moving the data from select tables. The database names are different too.
I've pretty much ruled out the possibility of a backup/restore senario.|||have u tried import/export ??|||It was a communication issue.
I Turned Mixed Protocol OFF on the old 7.0 box and turned it ON the 2K box. Go Figure. The most confusing part of this ordeal was that the login for linked server was working from both servers and you could see the master tables/views under linked server.|||sorry in that case can't help you much
hopefully somebody else can help you on this|||Wow. That was like phone tag in a message board. Don't think I've ever experienced that.
Monday, March 19, 2012
Copying dBs
I am running SQL 2000 (using replication) & Win
2000.
We are getting a new production sever and would like to
know the best practice for moving all the SQL data from
the Old Server to the New Server. There are a few
questions the concern me.
- Will I have to setup replication on the new server?
- Will all the dB defaults be preserved?
Larry,
if the server names are the same, and you have backups of all databases, then you should be able torestore them on the new production server. If you're doing transactional replication, then make sure the distribution database is synchronized with the prod
uction database. If the server names are different, then I'd script out the publications and recreate on the new server. Provided no subscribers are allowed to do any data changes during this time, you should be able to add the subscriptions without apply
ing the data snapshot (@.sync_type ='none').
HTH,
Paul Ibison
|||In the long run you would be best to drop the subscriptions and publications
on the current production server. Back it up and restore it to the new
publisher.
Recreate the publications from scripts and then do create a nosync
subscription to your existing subscriber.
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:238c01c427b5$7a2e6040$a501280a@.phx.gbl...
> I am running SQL 2000 (using replication) & Win
> 2000.
> We are getting a new production sever and would like to
> know the best practice for moving all the SQL data from
> the Old Server to the New Server. There are a few
> questions the concern me.
> - Will I have to setup replication on the new server?
> - Will all the dB defaults be preserved?
>
2000.
We are getting a new production sever and would like to
know the best practice for moving all the SQL data from
the Old Server to the New Server. There are a few
questions the concern me.
- Will I have to setup replication on the new server?
- Will all the dB defaults be preserved?
Larry,
if the server names are the same, and you have backups of all databases, then you should be able torestore them on the new production server. If you're doing transactional replication, then make sure the distribution database is synchronized with the prod
uction database. If the server names are different, then I'd script out the publications and recreate on the new server. Provided no subscribers are allowed to do any data changes during this time, you should be able to add the subscriptions without apply
ing the data snapshot (@.sync_type ='none').
HTH,
Paul Ibison
|||In the long run you would be best to drop the subscriptions and publications
on the current production server. Back it up and restore it to the new
publisher.
Recreate the publications from scripts and then do create a nosync
subscription to your existing subscriber.
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:238c01c427b5$7a2e6040$a501280a@.phx.gbl...
> I am running SQL 2000 (using replication) & Win
> 2000.
> We are getting a new production sever and would like to
> know the best practice for moving all the SQL data from
> the Old Server to the New Server. There are a few
> questions the concern me.
> - Will I have to setup replication on the new server?
> - Will all the dB defaults be preserved?
>
Copying db from prod to dev without disturbing permissions
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db level
or at the instance level? Thanks.
Do a backup and restore. Then, make sure to run sp_change_users_login to
ensure you align your users and logins.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db
level
or at the instance level? Thanks.
|||Or using two stored procedures provided by MS to transfer logins withb their
original SID.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OGY7V2HQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Do a backup and restore. Then, make sure to run sp_change_users_login to
> ensure you align your users and logins.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "sfhank" <sfhank@.discussions.microsoft.com> wrote in message
> news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
> What's the best way to refresh a dev environment from production without
> disturbing the permissions on the dev box either at the table level, db
> level
> or at the instance level? Thanks.
>
disturbing the permissions on the dev box either at the table level, db level
or at the instance level? Thanks.
Do a backup and restore. Then, make sure to run sp_change_users_login to
ensure you align your users and logins.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db
level
or at the instance level? Thanks.
|||Or using two stored procedures provided by MS to transfer logins withb their
original SID.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OGY7V2HQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Do a backup and restore. Then, make sure to run sp_change_users_login to
> ensure you align your users and logins.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "sfhank" <sfhank@.discussions.microsoft.com> wrote in message
> news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
> What's the best way to refresh a dev environment from production without
> disturbing the permissions on the dev box either at the table level, db
> level
> or at the instance level? Thanks.
>
Copying db from prod to dev without disturbing permissions
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db leve
l
or at the instance level? Thanks.Do a backup and restore. Then, make sure to run sp_change_users_login to
ensure you align your users and logins.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db
level
or at the instance level? Thanks.|||Or using two stored procedures provided by MS to transfer logins withb their
original SID.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OGY7V2HQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Do a backup and restore. Then, make sure to run sp_change_users_login to
> ensure you align your users and logins.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "sfhank" <sfhank@.discussions.microsoft.com> wrote in message
> news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
> What's the best way to refresh a dev environment from production without
> disturbing the permissions on the dev box either at the table level, db
> level
> or at the instance level? Thanks.
>
disturbing the permissions on the dev box either at the table level, db leve
l
or at the instance level? Thanks.Do a backup and restore. Then, make sure to run sp_change_users_login to
ensure you align your users and logins.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db
level
or at the instance level? Thanks.|||Or using two stored procedures provided by MS to transfer logins withb their
original SID.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OGY7V2HQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Do a backup and restore. Then, make sure to run sp_change_users_login to
> ensure you align your users and logins.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "sfhank" <sfhank@.discussions.microsoft.com> wrote in message
> news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
> What's the best way to refresh a dev environment from production without
> disturbing the permissions on the dev box either at the table level, db
> level
> or at the instance level? Thanks.
>
Sunday, March 11, 2012
Copying database file to another drive fails
I have an active/passive cluster involving 2 machines. I have 3 instances
running on the production server. One of the drives allocated to instance 2
was running out of space so I decided to move one of the larger databases to
another drive that can handle its size. The drive that I was going to move
the data file to is part of the same instance group in the cluster. After
detaching the database I attempted to move the data file to this drive but at
the end of the copy I receive a message that there was an error copying file
because of insufficient resources exist to complete the requested server. I
double checked the drive and there is plenty of space for this file and like
I said the drive is already part of the cluster group.
Anyone have any other suggestions on what the problem might be? Thanks
You could be out of physical memory or file handles. I have seen some
monitoring programs that "leaked" handles badly. Sometimes stopping and
restarting the SNMP service fixed it, but sometimes I had to wait and reboot
the host node during a scheduled maintenance window.
You could also have a problem due to Anti-virus programs. AV and clusters
have never gotten along well.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ronc" <ronc@.discussions.microsoft.com> wrote in message
news:E699545D-9DD4-47EB-A6F1-F7A29EFD7C39@.microsoft.com...
>I have an active/passive cluster involving 2 machines. I have 3 instances
> running on the production server. One of the drives allocated to instance
> 2
> was running out of space so I decided to move one of the larger databases
> to
> another drive that can handle its size. The drive that I was going to
> move
> the data file to is part of the same instance group in the cluster. After
> detaching the database I attempted to move the data file to this drive but
> at
> the end of the copy I receive a message that there was an error copying
> file
> because of insufficient resources exist to complete the requested server.
> I
> double checked the drive and there is plenty of space for this file and
> like
> I said the drive is already part of the cluster group.
> Anyone have any other suggestions on what the problem might be? Thanks
|||One other thing that I forgot to mention was that I was able to successfully
move a 20 GB backup file to the drive I was trying to copy to and it copied
successfully. Memory does not seem to be the problem. I still think it
has something to do specifically with the .dat file being moved but what that
is I'm not sure. I know that I can always do a backup and restore and
speicify the drive but I know that I should be able to do it this way by
coping and then reattching. Thanks again.
"Geoff N. Hiten" wrote:
> You could be out of physical memory or file handles. I have seen some
> monitoring programs that "leaked" handles badly. Sometimes stopping and
> restarting the SNMP service fixed it, but sometimes I had to wait and reboot
> the host node during a scheduled maintenance window.
> You could also have a problem due to Anti-virus programs. AV and clusters
> have never gotten along well.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "ronc" <ronc@.discussions.microsoft.com> wrote in message
> news:E699545D-9DD4-47EB-A6F1-F7A29EFD7C39@.microsoft.com...
>
>
|||Do you use any anti virus software or things like that ?
"ronc" wrote:
[vbcol=seagreen]
> One other thing that I forgot to mention was that I was able to successfully
> move a 20 GB backup file to the drive I was trying to copy to and it copied
> successfully. Memory does not seem to be the problem. I still think it
> has something to do specifically with the .dat file being moved but what that
> is I'm not sure. I know that I can always do a backup and restore and
> speicify the drive but I know that I should be able to do it this way by
> coping and then reattching. Thanks again.
>
> "Geoff N. Hiten" wrote:
running on the production server. One of the drives allocated to instance 2
was running out of space so I decided to move one of the larger databases to
another drive that can handle its size. The drive that I was going to move
the data file to is part of the same instance group in the cluster. After
detaching the database I attempted to move the data file to this drive but at
the end of the copy I receive a message that there was an error copying file
because of insufficient resources exist to complete the requested server. I
double checked the drive and there is plenty of space for this file and like
I said the drive is already part of the cluster group.
Anyone have any other suggestions on what the problem might be? Thanks
You could be out of physical memory or file handles. I have seen some
monitoring programs that "leaked" handles badly. Sometimes stopping and
restarting the SNMP service fixed it, but sometimes I had to wait and reboot
the host node during a scheduled maintenance window.
You could also have a problem due to Anti-virus programs. AV and clusters
have never gotten along well.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ronc" <ronc@.discussions.microsoft.com> wrote in message
news:E699545D-9DD4-47EB-A6F1-F7A29EFD7C39@.microsoft.com...
>I have an active/passive cluster involving 2 machines. I have 3 instances
> running on the production server. One of the drives allocated to instance
> 2
> was running out of space so I decided to move one of the larger databases
> to
> another drive that can handle its size. The drive that I was going to
> move
> the data file to is part of the same instance group in the cluster. After
> detaching the database I attempted to move the data file to this drive but
> at
> the end of the copy I receive a message that there was an error copying
> file
> because of insufficient resources exist to complete the requested server.
> I
> double checked the drive and there is plenty of space for this file and
> like
> I said the drive is already part of the cluster group.
> Anyone have any other suggestions on what the problem might be? Thanks
|||One other thing that I forgot to mention was that I was able to successfully
move a 20 GB backup file to the drive I was trying to copy to and it copied
successfully. Memory does not seem to be the problem. I still think it
has something to do specifically with the .dat file being moved but what that
is I'm not sure. I know that I can always do a backup and restore and
speicify the drive but I know that I should be able to do it this way by
coping and then reattching. Thanks again.
"Geoff N. Hiten" wrote:
> You could be out of physical memory or file handles. I have seen some
> monitoring programs that "leaked" handles badly. Sometimes stopping and
> restarting the SNMP service fixed it, but sometimes I had to wait and reboot
> the host node during a scheduled maintenance window.
> You could also have a problem due to Anti-virus programs. AV and clusters
> have never gotten along well.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "ronc" <ronc@.discussions.microsoft.com> wrote in message
> news:E699545D-9DD4-47EB-A6F1-F7A29EFD7C39@.microsoft.com...
>
>
|||Do you use any anti virus software or things like that ?
"ronc" wrote:
[vbcol=seagreen]
> One other thing that I forgot to mention was that I was able to successfully
> move a 20 GB backup file to the drive I was trying to copy to and it copied
> successfully. Memory does not seem to be the problem. I still think it
> has something to do specifically with the .dat file being moved but what that
> is I'm not sure. I know that I can always do a backup and restore and
> speicify the drive but I know that I should be able to do it this way by
> coping and then reattching. Thanks again.
>
> "Geoff N. Hiten" wrote:
Thursday, March 8, 2012
copying Data Base from one sever to another
Hi,
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. On
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy a
newer one over from the Prod. server. The tape drive on the production server
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod server
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
GregHi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server. On
> the test server I have a duplicate sql 2000 with a copy of the database from
> the production server. Which all are working fine. Both systems have a tape
> drive. It's a very old D.B. copy on the test server so I would like to copy a
> newer one over from the Prod. server. The tape drive on the production server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. On
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy a
newer one over from the Prod. server. The tape drive on the production server
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod server
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
GregHi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server. On
> the test server I have a duplicate sql 2000 with a copy of the database from
> the production server. Which all are working fine. Both systems have a tape
> drive. It's a very old D.B. copy on the test server so I would like to copy a
> newer one over from the Prod. server. The tape drive on the production server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg
copying Data Base from one sever to another
Hi,
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. O
n
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy
a
newer one over from the Prod. server. The tape drive on the production serve
r
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod serve
r
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
GregHi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
On
> the test server I have a duplicate sql 2000 with a copy of the database fr
om
> the production server. Which all are working fine. Both systems have a tap
e
> drive. It's a very old D.B. copy on the test server so I would like to cop
y a
> newer one over from the Prod. server. The tape drive on the production ser
ver
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod ser
ver
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. O
n
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy
a
newer one over from the Prod. server. The tape drive on the production serve
r
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod serve
r
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
GregHi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
On
> the test server I have a duplicate sql 2000 with a copy of the database fr
om
> the production server. Which all are working fine. Both systems have a tap
e
> drive. It's a very old D.B. copy on the test server so I would like to cop
y a
> newer one over from the Prod. server. The tape drive on the production ser
ver
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod ser
ver
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg
copying Data Base from one sever to another
Hi,
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. On
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy a
newer one over from the Prod. server. The tape drive on the production server
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod server
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
Greg
Hi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg
|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server. On
> the test server I have a duplicate sql 2000 with a copy of the database from
> the production server. Which all are working fine. Both systems have a tape
> drive. It's a very old D.B. copy on the test server so I would like to copy a
> newer one over from the Prod. server. The tape drive on the production server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. On
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy a
newer one over from the Prod. server. The tape drive on the production server
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod server
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
Greg
Hi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg
|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server. On
> the test server I have a duplicate sql 2000 with a copy of the database from
> the production server. Which all are working fine. Both systems have a tape
> drive. It's a very old D.B. copy on the test server so I would like to copy a
> newer one over from the Prod. server. The tape drive on the production server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg
Wednesday, March 7, 2012
Copying a Log Shipped database
I have a production server in a distant city across our WAN. One warm
standy by is there in that city, and a second warm standy by is here with me
at Corp HQ.
I need a copy of our data, but I don't want to have to bring a copy of the
Production sever down over the WAN.. that'll take too long (~4gbs). I would
like to take a copy of the local warm standby databases that are read-only.
How can I get a copy of the read-only databases for uses locally?Eric
read topic in BOL about standby servers.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> I have a production server in a distant city across our WAN. One warm
> standy by is there in that city, and a second warm standy by is here with
me
> at Corp HQ.
> I need a copy of our data, but I don't want to have to bring a copy of the
> Production sever down over the WAN.. that'll take too long (~4gbs). I
would
> like to take a copy of the local warm standby databases that are
read-only.
> How can I get a copy of the read-only databases for uses locally?
>|||I've been all over BOL... this is why I'm asking! Because I'm unsure how
to do it.
I do not want to do a role change or anything.. Nor do I want to bring one
of my StandBy Servers online. I simply get a copy of all our data from my
local Destination servers/Standy server. I need to do this ASAP and would
appreciate some help rather than the standard "look in BOL".
If you know, please tell me a little.. I've done Google searchs on this
topic and looked in BOL, but its tough to narrow down apparently when you
don't know the answer. I've been trying to solve this on and off for a
couple weeks now.. Please help if you can. Sending me to BOL isn't any
help.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Eric
> read topic in BOL about standby servers.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
with
> me
the
> would
> read-only.
>|||Eric
I really want to help you.( don't angry)
Unless I missunderstand you can try to move your backup as zip or rar files
over a network. (4 gb is not so big)
Backup your database then create batch file like that
CD \Program Files\WinRAR
WINRAR a D:\databasename%1 D:\database%1.BAK
After running this file you will get rar/zip file you can easily transfer
over a network.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I've been all over BOL... this is why I'm asking! Because I'm unsure how
> to do it.
> I do not want to do a role change or anything.. Nor do I want to bring one
> of my StandBy Servers online. I simply get a copy of all our data from my
> local Destination servers/Standy server. I need to do this ASAP and would
> appreciate some help rather than the standard "look in BOL".
> If you know, please tell me a little.. I've done Google searchs on this
> topic and looked in BOL, but its tough to narrow down apparently when you
> don't know the answer. I've been trying to solve this on and off for a
> couple weeks now.. Please help if you can. Sending me to BOL isn't any
> help.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> with
> the
>|||No.. reread my Question. I'm well aware that you can backup up a database
and copy that from one place to another.
I do NOT want to backup the database and then copy over our LAN. That will
take many hours. I don't care if 4gb is considered a big database or not.
It's simply a big file to throw across the WAN. Uncompressed it will take 8
hours or something.. compressed it takes 5 hours.
I DO want to use my local warm standby copy. I can't back the database up
because it's in Warm standby mode. So.. can I use this local copy to
move/copy over to a another useable (such as in a development environment)
database. That is my question.
Eric Smith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> Eric
> I really want to help you.( don't angry)
> Unless I missunderstand you can try to move your backup as zip or rar
files
> over a network. (4 gb is not so big)
> Backup your database then create batch file like that
> CD \Program Files\WinRAR
> WINRAR a D:\databasename%1 D:\database%1.BAK
> After running this file you will get rar/zip file you can easily transfer
> over a network.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
how
one
my
would
you
warm
of
I
>|||Eric
I am not following you.
What do you mean by Warm standby mode?
Have you looked at "How to set up, maintain, and bring online a standby
server (Transact-SQL)"
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> No.. reread my Question. I'm well aware that you can backup up a database
> and copy that from one place to another.
> I do NOT want to backup the database and then copy over our LAN. That
will
> take many hours. I don't care if 4gb is considered a big database or not.
> It's simply a big file to throw across the WAN. Uncompressed it will take
8
> hours or something.. compressed it takes 5 hours.
> I DO want to use my local warm standby copy. I can't back the database up
> because it's in Warm standby mode. So.. can I use this local copy to
> move/copy over to a another useable (such as in a development environment)
> database. That is my question.
> Eric Smith
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> files
transfer
> how
> one
from
> my
> would
this
> you
any
> warm
here
copy
> of
(~4gbs).
> I
>|||Uri,
I think it is you that needs to go back and learn a bit more about Log
Shipping.
A destination server in a log shipping scheme is referred to as a "warm
standby". A hot server would be considered live... A cold standby would be
a box that is available and usable but would need the most recent backup
restored to be ready to use.
A warm standby refers to the fact that it's warm and ready to go.. it's not
live, but its not dormant and cold. Its warm, such as in a Log Shipping
scheme. It has all the data and is ready to take over as primary at most
any time.
If you go and try and back up a destination log shipped databsase (a
warm-standby database) you get the message:
"Database XXX is in warm-standby state (set by executing RESTORE WITIH
STANDBY) and cannot be backed up until the entire load sequence is
completed. BACKUP DATABASE is terminating abnormallhy" Hence my original
question of how do I get a backup of a warm-standby database so I can move
it to another server and play with it, such as in a development mode.
No offense, but since I'm having to educate you on this, I don't expect
you'll be able to help with answer. Here is a bit of advise, please only
try and answer Questions for which you have the answer, OK? I need to get
this solved, not convince you that I don't need to go back to BOL.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> Eric
> I am not following you.
> What do you mean by Warm standby mode?
> Have you looked at "How to set up, maintain, and bring online a standby
> server (Transact-SQL)"
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
database
> will
not.
take
> 8
up
environment)
> transfer
unsure
bring
> from
> this
when
a
> any
One
> here
> copy
> (~4gbs).
locally?
>|||This method may not be officially supported but it works for me.
1. Take the standby database offline.
2. Copy all database files to a new names.
3. Bring the standby database back online.
4. Attach new database using files created in step 2.
Eric Smith wrote:
> Uri,
> I think it is you that needs to go back and learn a bit more about
Log
> Shipping.
> A destination server in a log shipping scheme is referred to as a
"warm
> standby". A hot server would be considered live... A cold standby
would be
> a box that is available and usable but would need the most recent
backup
> restored to be ready to use.
> A warm standby refers to the fact that it's warm and ready to go..
it's not
> live, but its not dormant and cold. Its warm, such as in a Log
Shipping
> scheme. It has all the data and is ready to take over as primary at
most
> any time.
> If you go and try and back up a destination log shipped databsase (a
> warm-standby database) you get the message:
> "Database XXX is in warm-standby state (set by executing RESTORE
WITIH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed. BACKUP DATABASE is terminating abnormallhy" Hence my
original
> question of how do I get a backup of a warm-standby database so I
can move
> it to another server and play with it, such as in a development mode.
> No offense, but since I'm having to educate you on this, I don't
expect
> you'll be able to help with answer. Here is a bit of advise, please
only
> try and answer Questions for which you have the answer, OK? I need
to get[vbcol=seagreen]
> this solved, not convince you that I don't need to go back to BOL.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
standby[vbcol=seagreen]
> database
That[vbcol=seagreen]
database or[vbcol=seagreen]
> not.
will[vbcol=seagreen]
> take
database[vbcol=seagreen]
> up
copy to[vbcol=seagreen]
> environment)
or rar[vbcol=seagreen]
easily[vbcol=seagreen]
I'm[vbcol=seagreen]
> unsure
to[vbcol=seagreen]
> bring
data[vbcol=seagreen]
ASAP and[vbcol=seagreen]
searchs on[vbcol=seagreen]
apparently[vbcol=seagreen]
> when
off for[vbcol=seagreen]
> a
BOL isn't[vbcol=seagreen]
message[vbcol=seagreen]
WAN.[vbcol=seagreen]
> One
by is[vbcol=seagreen]
bring a[vbcol=seagreen]
long[vbcol=seagreen]
that are[vbcol=seagreen]
> locally?
standy by is there in that city, and a second warm standy by is here with me
at Corp HQ.
I need a copy of our data, but I don't want to have to bring a copy of the
Production sever down over the WAN.. that'll take too long (~4gbs). I would
like to take a copy of the local warm standby databases that are read-only.
How can I get a copy of the read-only databases for uses locally?Eric
read topic in BOL about standby servers.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> I have a production server in a distant city across our WAN. One warm
> standy by is there in that city, and a second warm standy by is here with
me
> at Corp HQ.
> I need a copy of our data, but I don't want to have to bring a copy of the
> Production sever down over the WAN.. that'll take too long (~4gbs). I
would
> like to take a copy of the local warm standby databases that are
read-only.
> How can I get a copy of the read-only databases for uses locally?
>|||I've been all over BOL... this is why I'm asking! Because I'm unsure how
to do it.
I do not want to do a role change or anything.. Nor do I want to bring one
of my StandBy Servers online. I simply get a copy of all our data from my
local Destination servers/Standy server. I need to do this ASAP and would
appreciate some help rather than the standard "look in BOL".
If you know, please tell me a little.. I've done Google searchs on this
topic and looked in BOL, but its tough to narrow down apparently when you
don't know the answer. I've been trying to solve this on and off for a
couple weeks now.. Please help if you can. Sending me to BOL isn't any
help.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Eric
> read topic in BOL about standby servers.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
with
> me
the
> would
> read-only.
>|||Eric
I really want to help you.( don't angry)
Unless I missunderstand you can try to move your backup as zip or rar files
over a network. (4 gb is not so big)
Backup your database then create batch file like that
CD \Program Files\WinRAR
WINRAR a D:\databasename%1 D:\database%1.BAK
After running this file you will get rar/zip file you can easily transfer
over a network.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I've been all over BOL... this is why I'm asking! Because I'm unsure how
> to do it.
> I do not want to do a role change or anything.. Nor do I want to bring one
> of my StandBy Servers online. I simply get a copy of all our data from my
> local Destination servers/Standy server. I need to do this ASAP and would
> appreciate some help rather than the standard "look in BOL".
> If you know, please tell me a little.. I've done Google searchs on this
> topic and looked in BOL, but its tough to narrow down apparently when you
> don't know the answer. I've been trying to solve this on and off for a
> couple weeks now.. Please help if you can. Sending me to BOL isn't any
> help.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> with
> the
>|||No.. reread my Question. I'm well aware that you can backup up a database
and copy that from one place to another.
I do NOT want to backup the database and then copy over our LAN. That will
take many hours. I don't care if 4gb is considered a big database or not.
It's simply a big file to throw across the WAN. Uncompressed it will take 8
hours or something.. compressed it takes 5 hours.
I DO want to use my local warm standby copy. I can't back the database up
because it's in Warm standby mode. So.. can I use this local copy to
move/copy over to a another useable (such as in a development environment)
database. That is my question.
Eric Smith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> Eric
> I really want to help you.( don't angry)
> Unless I missunderstand you can try to move your backup as zip or rar
files
> over a network. (4 gb is not so big)
> Backup your database then create batch file like that
> CD \Program Files\WinRAR
> WINRAR a D:\databasename%1 D:\database%1.BAK
> After running this file you will get rar/zip file you can easily transfer
> over a network.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
how
one
my
would
you
warm
of
I
>|||Eric
I am not following you.
What do you mean by Warm standby mode?
Have you looked at "How to set up, maintain, and bring online a standby
server (Transact-SQL)"
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> No.. reread my Question. I'm well aware that you can backup up a database
> and copy that from one place to another.
> I do NOT want to backup the database and then copy over our LAN. That
will
> take many hours. I don't care if 4gb is considered a big database or not.
> It's simply a big file to throw across the WAN. Uncompressed it will take
8
> hours or something.. compressed it takes 5 hours.
> I DO want to use my local warm standby copy. I can't back the database up
> because it's in Warm standby mode. So.. can I use this local copy to
> move/copy over to a another useable (such as in a development environment)
> database. That is my question.
> Eric Smith
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> files
transfer
> how
> one
from
> my
> would
this
> you
any
> warm
here
copy
> of
(~4gbs).
> I
>|||Uri,
I think it is you that needs to go back and learn a bit more about Log
Shipping.
A destination server in a log shipping scheme is referred to as a "warm
standby". A hot server would be considered live... A cold standby would be
a box that is available and usable but would need the most recent backup
restored to be ready to use.
A warm standby refers to the fact that it's warm and ready to go.. it's not
live, but its not dormant and cold. Its warm, such as in a Log Shipping
scheme. It has all the data and is ready to take over as primary at most
any time.
If you go and try and back up a destination log shipped databsase (a
warm-standby database) you get the message:
"Database XXX is in warm-standby state (set by executing RESTORE WITIH
STANDBY) and cannot be backed up until the entire load sequence is
completed. BACKUP DATABASE is terminating abnormallhy" Hence my original
question of how do I get a backup of a warm-standby database so I can move
it to another server and play with it, such as in a development mode.
No offense, but since I'm having to educate you on this, I don't expect
you'll be able to help with answer. Here is a bit of advise, please only
try and answer Questions for which you have the answer, OK? I need to get
this solved, not convince you that I don't need to go back to BOL.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> Eric
> I am not following you.
> What do you mean by Warm standby mode?
> Have you looked at "How to set up, maintain, and bring online a standby
> server (Transact-SQL)"
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
database
> will
not.
take
> 8
up
environment)
> transfer
unsure
bring
> from
> this
when
a
> any
One
> here
> copy
> (~4gbs).
locally?
>|||This method may not be officially supported but it works for me.
1. Take the standby database offline.
2. Copy all database files to a new names.
3. Bring the standby database back online.
4. Attach new database using files created in step 2.
Eric Smith wrote:
> Uri,
> I think it is you that needs to go back and learn a bit more about
Log
> Shipping.
> A destination server in a log shipping scheme is referred to as a
"warm
> standby". A hot server would be considered live... A cold standby
would be
> a box that is available and usable but would need the most recent
backup
> restored to be ready to use.
> A warm standby refers to the fact that it's warm and ready to go..
it's not
> live, but its not dormant and cold. Its warm, such as in a Log
Shipping
> scheme. It has all the data and is ready to take over as primary at
most
> any time.
> If you go and try and back up a destination log shipped databsase (a
> warm-standby database) you get the message:
> "Database XXX is in warm-standby state (set by executing RESTORE
WITIH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed. BACKUP DATABASE is terminating abnormallhy" Hence my
original
> question of how do I get a backup of a warm-standby database so I
can move
> it to another server and play with it, such as in a development mode.
> No offense, but since I'm having to educate you on this, I don't
expect
> you'll be able to help with answer. Here is a bit of advise, please
only
> try and answer Questions for which you have the answer, OK? I need
to get[vbcol=seagreen]
> this solved, not convince you that I don't need to go back to BOL.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
standby[vbcol=seagreen]
> database
That[vbcol=seagreen]
database or[vbcol=seagreen]
> not.
will[vbcol=seagreen]
> take
database[vbcol=seagreen]
> up
copy to[vbcol=seagreen]
> environment)
or rar[vbcol=seagreen]
easily[vbcol=seagreen]
I'm[vbcol=seagreen]
> unsure
to[vbcol=seagreen]
> bring
data[vbcol=seagreen]
ASAP and[vbcol=seagreen]
searchs on[vbcol=seagreen]
apparently[vbcol=seagreen]
> when
off for[vbcol=seagreen]
> a
BOL isn't[vbcol=seagreen]
message[vbcol=seagreen]
WAN.[vbcol=seagreen]
> One
by is[vbcol=seagreen]
bring a[vbcol=seagreen]
long[vbcol=seagreen]
that are[vbcol=seagreen]
> locally?
Copying a Log Shipped database
I have a production server in a distant city across our WAN. One warm
standy by is there in that city, and a second warm standy by is here with me
at Corp HQ.
I need a copy of our data, but I don't want to have to bring a copy of the
Production sever down over the WAN.. that'll take too long (~4gbs). I would
like to take a copy of the local warm standby databases that are read-only.
How can I get a copy of the read-only databases for uses locally?Eric
read topic in BOL about standby servers.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> I have a production server in a distant city across our WAN. One warm
> standy by is there in that city, and a second warm standy by is here with
me
> at Corp HQ.
> I need a copy of our data, but I don't want to have to bring a copy of the
> Production sever down over the WAN.. that'll take too long (~4gbs). I
would
> like to take a copy of the local warm standby databases that are
read-only.
> How can I get a copy of the read-only databases for uses locally?
>|||I've been all over BOL... this is why I'm asking! Because I'm unsure how
to do it.
I do not want to do a role change or anything.. Nor do I want to bring one
of my StandBy Servers online. I simply get a copy of all our data from my
local Destination servers/Standy server. I need to do this ASAP and would
appreciate some help rather than the standard "look in BOL".
If you know, please tell me a little.. I've done Google searchs on this
topic and looked in BOL, but its tough to narrow down apparently when you
don't know the answer. I've been trying to solve this on and off for a
couple weeks now.. Please help if you can. Sending me to BOL isn't any
help.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Eric
> read topic in BOL about standby servers.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > I have a production server in a distant city across our WAN. One warm
> > standy by is there in that city, and a second warm standy by is here
with
> me
> > at Corp HQ.
> >
> > I need a copy of our data, but I don't want to have to bring a copy of
the
> > Production sever down over the WAN.. that'll take too long (~4gbs). I
> would
> > like to take a copy of the local warm standby databases that are
> read-only.
> >
> > How can I get a copy of the read-only databases for uses locally?
> >
> >
>|||Eric
I really want to help you.( don't angry)
Unless I missunderstand you can try to move your backup as zip or rar files
over a network. (4 gb is not so big)
Backup your database then create batch file like that
CD \Program Files\WinRAR
WINRAR a D:\databasename%1 D:\database%1.BAK
After running this file you will get rar/zip file you can easily transfer
over a network.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I've been all over BOL... this is why I'm asking! Because I'm unsure how
> to do it.
> I do not want to do a role change or anything.. Nor do I want to bring one
> of my StandBy Servers online. I simply get a copy of all our data from my
> local Destination servers/Standy server. I need to do this ASAP and would
> appreciate some help rather than the standard "look in BOL".
> If you know, please tell me a little.. I've done Google searchs on this
> topic and looked in BOL, but its tough to narrow down apparently when you
> don't know the answer. I've been trying to solve this on and off for a
> couple weeks now.. Please help if you can. Sending me to BOL isn't any
> help.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > Eric
> > read topic in BOL about standby servers.
> >
> >
> >
> > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > I have a production server in a distant city across our WAN. One warm
> > > standy by is there in that city, and a second warm standy by is here
> with
> > me
> > > at Corp HQ.
> > >
> > > I need a copy of our data, but I don't want to have to bring a copy of
> the
> > > Production sever down over the WAN.. that'll take too long (~4gbs). I
> > would
> > > like to take a copy of the local warm standby databases that are
> > read-only.
> > >
> > > How can I get a copy of the read-only databases for uses locally?
> > >
> > >
> >
> >
>|||No.. reread my Question. I'm well aware that you can backup up a database
and copy that from one place to another.
I do NOT want to backup the database and then copy over our LAN. That will
take many hours. I don't care if 4gb is considered a big database or not.
It's simply a big file to throw across the WAN. Uncompressed it will take 8
hours or something.. compressed it takes 5 hours.
I DO want to use my local warm standby copy. I can't back the database up
because it's in Warm standby mode. So.. can I use this local copy to
move/copy over to a another useable (such as in a development environment)
database. That is my question.
Eric Smith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> Eric
> I really want to help you.( don't angry)
> Unless I missunderstand you can try to move your backup as zip or rar
files
> over a network. (4 gb is not so big)
> Backup your database then create batch file like that
> CD \Program Files\WinRAR
> WINRAR a D:\databasename%1 D:\database%1.BAK
> After running this file you will get rar/zip file you can easily transfer
> over a network.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > I've been all over BOL... this is why I'm asking! Because I'm unsure
how
> > to do it.
> >
> > I do not want to do a role change or anything.. Nor do I want to bring
one
> > of my StandBy Servers online. I simply get a copy of all our data from
my
> > local Destination servers/Standy server. I need to do this ASAP and
would
> > appreciate some help rather than the standard "look in BOL".
> >
> > If you know, please tell me a little.. I've done Google searchs on this
> > topic and looked in BOL, but its tough to narrow down apparently when
you
> > don't know the answer. I've been trying to solve this on and off for a
> > couple weeks now.. Please help if you can. Sending me to BOL isn't any
> > help.
> >
> > Eric
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > Eric
> > > read topic in BOL about standby servers.
> > >
> > >
> > >
> > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > I have a production server in a distant city across our WAN. One
warm
> > > > standy by is there in that city, and a second warm standy by is here
> > with
> > > me
> > > > at Corp HQ.
> > > >
> > > > I need a copy of our data, but I don't want to have to bring a copy
of
> > the
> > > > Production sever down over the WAN.. that'll take too long (~4gbs).
I
> > > would
> > > > like to take a copy of the local warm standby databases that are
> > > read-only.
> > > >
> > > > How can I get a copy of the read-only databases for uses locally?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Eric
I am not following you.
What do you mean by Warm standby mode?
Have you looked at "How to set up, maintain, and bring online a standby
server (Transact-SQL)"
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> No.. reread my Question. I'm well aware that you can backup up a database
> and copy that from one place to another.
> I do NOT want to backup the database and then copy over our LAN. That
will
> take many hours. I don't care if 4gb is considered a big database or not.
> It's simply a big file to throw across the WAN. Uncompressed it will take
8
> hours or something.. compressed it takes 5 hours.
> I DO want to use my local warm standby copy. I can't back the database up
> because it's in Warm standby mode. So.. can I use this local copy to
> move/copy over to a another useable (such as in a development environment)
> database. That is my question.
> Eric Smith
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> > Eric
> > I really want to help you.( don't angry)
> > Unless I missunderstand you can try to move your backup as zip or rar
> files
> > over a network. (4 gb is not so big)
> > Backup your database then create batch file like that
> > CD \Program Files\WinRAR
> > WINRAR a D:\databasename%1 D:\database%1.BAK
> >
> > After running this file you will get rar/zip file you can easily
transfer
> > over a network.
> >
> >
> > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > I've been all over BOL... this is why I'm asking! Because I'm unsure
> how
> > > to do it.
> > >
> > > I do not want to do a role change or anything.. Nor do I want to bring
> one
> > > of my StandBy Servers online. I simply get a copy of all our data
from
> my
> > > local Destination servers/Standy server. I need to do this ASAP and
> would
> > > appreciate some help rather than the standard "look in BOL".
> > >
> > > If you know, please tell me a little.. I've done Google searchs on
this
> > > topic and looked in BOL, but its tough to narrow down apparently when
> you
> > > don't know the answer. I've been trying to solve this on and off for a
> > > couple weeks now.. Please help if you can. Sending me to BOL isn't
any
> > > help.
> > >
> > > Eric
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > Eric
> > > > read topic in BOL about standby servers.
> > > >
> > > >
> > > >
> > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > > I have a production server in a distant city across our WAN. One
> warm
> > > > > standy by is there in that city, and a second warm standy by is
here
> > > with
> > > > me
> > > > > at Corp HQ.
> > > > >
> > > > > I need a copy of our data, but I don't want to have to bring a
copy
> of
> > > the
> > > > > Production sever down over the WAN.. that'll take too long
(~4gbs).
> I
> > > > would
> > > > > like to take a copy of the local warm standby databases that are
> > > > read-only.
> > > > >
> > > > > How can I get a copy of the read-only databases for uses locally?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Uri,
I think it is you that needs to go back and learn a bit more about Log
Shipping.
A destination server in a log shipping scheme is referred to as a "warm
standby". A hot server would be considered live... A cold standby would be
a box that is available and usable but would need the most recent backup
restored to be ready to use.
A warm standby refers to the fact that it's warm and ready to go.. it's not
live, but its not dormant and cold. Its warm, such as in a Log Shipping
scheme. It has all the data and is ready to take over as primary at most
any time.
If you go and try and back up a destination log shipped databsase (a
warm-standby database) you get the message:
"Database XXX is in warm-standby state (set by executing RESTORE WITIH
STANDBY) and cannot be backed up until the entire load sequence is
completed. BACKUP DATABASE is terminating abnormallhy" Hence my original
question of how do I get a backup of a warm-standby database so I can move
it to another server and play with it, such as in a development mode.
No offense, but since I'm having to educate you on this, I don't expect
you'll be able to help with answer. Here is a bit of advise, please only
try and answer Questions for which you have the answer, OK? I need to get
this solved, not convince you that I don't need to go back to BOL.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> Eric
> I am not following you.
> What do you mean by Warm standby mode?
> Have you looked at "How to set up, maintain, and bring online a standby
> server (Transact-SQL)"
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> > No.. reread my Question. I'm well aware that you can backup up a
database
> > and copy that from one place to another.
> >
> > I do NOT want to backup the database and then copy over our LAN. That
> will
> > take many hours. I don't care if 4gb is considered a big database or
not.
> > It's simply a big file to throw across the WAN. Uncompressed it will
take
> 8
> > hours or something.. compressed it takes 5 hours.
> >
> > I DO want to use my local warm standby copy. I can't back the database
up
> > because it's in Warm standby mode. So.. can I use this local copy to
> > move/copy over to a another useable (such as in a development
environment)
> > database. That is my question.
> >
> > Eric Smith
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> > > Eric
> > > I really want to help you.( don't angry)
> > > Unless I missunderstand you can try to move your backup as zip or rar
> > files
> > > over a network. (4 gb is not so big)
> > > Backup your database then create batch file like that
> > > CD \Program Files\WinRAR
> > > WINRAR a D:\databasename%1 D:\database%1.BAK
> > >
> > > After running this file you will get rar/zip file you can easily
> transfer
> > > over a network.
> > >
> > >
> > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > > I've been all over BOL... this is why I'm asking! Because I'm
unsure
> > how
> > > > to do it.
> > > >
> > > > I do not want to do a role change or anything.. Nor do I want to
bring
> > one
> > > > of my StandBy Servers online. I simply get a copy of all our data
> from
> > my
> > > > local Destination servers/Standy server. I need to do this ASAP and
> > would
> > > > appreciate some help rather than the standard "look in BOL".
> > > >
> > > > If you know, please tell me a little.. I've done Google searchs on
> this
> > > > topic and looked in BOL, but its tough to narrow down apparently
when
> > you
> > > > don't know the answer. I've been trying to solve this on and off for
a
> > > > couple weeks now.. Please help if you can. Sending me to BOL isn't
> any
> > > > help.
> > > >
> > > > Eric
> > > >
> > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > > Eric
> > > > > read topic in BOL about standby servers.
> > > > >
> > > > >
> > > > >
> > > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > > > I have a production server in a distant city across our WAN.
One
> > warm
> > > > > > standy by is there in that city, and a second warm standy by is
> here
> > > > with
> > > > > me
> > > > > > at Corp HQ.
> > > > > >
> > > > > > I need a copy of our data, but I don't want to have to bring a
> copy
> > of
> > > > the
> > > > > > Production sever down over the WAN.. that'll take too long
> (~4gbs).
> > I
> > > > > would
> > > > > > like to take a copy of the local warm standby databases that are
> > > > > read-only.
> > > > > >
> > > > > > How can I get a copy of the read-only databases for uses
locally?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||This method may not be officially supported but it works for me.
1. Take the standby database offline.
2. Copy all database files to a new names.
3. Bring the standby database back online.
4. Attach new database using files created in step 2.
Eric Smith wrote:
> Uri,
> I think it is you that needs to go back and learn a bit more about
Log
> Shipping.
> A destination server in a log shipping scheme is referred to as a
"warm
> standby". A hot server would be considered live... A cold standby
would be
> a box that is available and usable but would need the most recent
backup
> restored to be ready to use.
> A warm standby refers to the fact that it's warm and ready to go..
it's not
> live, but its not dormant and cold. Its warm, such as in a Log
Shipping
> scheme. It has all the data and is ready to take over as primary at
most
> any time.
> If you go and try and back up a destination log shipped databsase (a
> warm-standby database) you get the message:
> "Database XXX is in warm-standby state (set by executing RESTORE
WITIH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed. BACKUP DATABASE is terminating abnormallhy" Hence my
original
> question of how do I get a backup of a warm-standby database so I
can move
> it to another server and play with it, such as in a development mode.
> No offense, but since I'm having to educate you on this, I don't
expect
> you'll be able to help with answer. Here is a bit of advise, please
only
> try and answer Questions for which you have the answer, OK? I need
to get
> this solved, not convince you that I don't need to go back to BOL.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> > Eric
> > I am not following you.
> > What do you mean by Warm standby mode?
> > Have you looked at "How to set up, maintain, and bring online a
standby
> > server (Transact-SQL)"
> >
> >
> > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> > > No.. reread my Question. I'm well aware that you can backup up a
> database
> > > and copy that from one place to another.
> > >
> > > I do NOT want to backup the database and then copy over our LAN.
That
> > will
> > > take many hours. I don't care if 4gb is considered a big
database or
> not.
> > > It's simply a big file to throw across the WAN. Uncompressed it
will
> take
> > 8
> > > hours or something.. compressed it takes 5 hours.
> > >
> > > I DO want to use my local warm standby copy. I can't back the
database
> up
> > > because it's in Warm standby mode. So.. can I use this local
copy to
> > > move/copy over to a another useable (such as in a development
> environment)
> > > database. That is my question.
> > >
> > > Eric Smith
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> > > > Eric
> > > > I really want to help you.( don't angry)
> > > > Unless I missunderstand you can try to move your backup as zip
or rar
> > > files
> > > > over a network. (4 gb is not so big)
> > > > Backup your database then create batch file like that
> > > > CD \Program Files\WinRAR
> > > > WINRAR a D:\databasename%1 D:\database%1.BAK
> > > >
> > > > After running this file you will get rar/zip file you can
easily
> > transfer
> > > > over a network.
> > > >
> > > >
> > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > > news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > > > I've been all over BOL... this is why I'm asking! Because
I'm
> unsure
> > > how
> > > > > to do it.
> > > > >
> > > > > I do not want to do a role change or anything.. Nor do I want
to
> bring
> > > one
> > > > > of my StandBy Servers online. I simply get a copy of all our
data
> > from
> > > my
> > > > > local Destination servers/Standy server. I need to do this
ASAP and
> > > would
> > > > > appreciate some help rather than the standard "look in BOL".
> > > > >
> > > > > If you know, please tell me a little.. I've done Google
searchs on
> > this
> > > > > topic and looked in BOL, but its tough to narrow down
apparently
> when
> > > you
> > > > > don't know the answer. I've been trying to solve this on and
off for
> a
> > > > > couple weeks now.. Please help if you can. Sending me to
BOL isn't
> > any
> > > > > help.
> > > > >
> > > > > Eric
> > > > >
> > > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > > > Eric
> > > > > > read topic in BOL about standby servers.
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in
message
> > > > > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > > > > I have a production server in a distant city across our
WAN.
> One
> > > warm
> > > > > > > standy by is there in that city, and a second warm standy
by is
> > here
> > > > > with
> > > > > > me
> > > > > > > at Corp HQ.
> > > > > > >
> > > > > > > I need a copy of our data, but I don't want to have to
bring a
> > copy
> > > of
> > > > > the
> > > > > > > Production sever down over the WAN.. that'll take too
long
> > (~4gbs).
> > > I
> > > > > > would
> > > > > > > like to take a copy of the local warm standby databases
that are
> > > > > > read-only.
> > > > > > >
> > > > > > > How can I get a copy of the read-only databases for uses
> locally?
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
standy by is there in that city, and a second warm standy by is here with me
at Corp HQ.
I need a copy of our data, but I don't want to have to bring a copy of the
Production sever down over the WAN.. that'll take too long (~4gbs). I would
like to take a copy of the local warm standby databases that are read-only.
How can I get a copy of the read-only databases for uses locally?Eric
read topic in BOL about standby servers.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> I have a production server in a distant city across our WAN. One warm
> standy by is there in that city, and a second warm standy by is here with
me
> at Corp HQ.
> I need a copy of our data, but I don't want to have to bring a copy of the
> Production sever down over the WAN.. that'll take too long (~4gbs). I
would
> like to take a copy of the local warm standby databases that are
read-only.
> How can I get a copy of the read-only databases for uses locally?
>|||I've been all over BOL... this is why I'm asking! Because I'm unsure how
to do it.
I do not want to do a role change or anything.. Nor do I want to bring one
of my StandBy Servers online. I simply get a copy of all our data from my
local Destination servers/Standy server. I need to do this ASAP and would
appreciate some help rather than the standard "look in BOL".
If you know, please tell me a little.. I've done Google searchs on this
topic and looked in BOL, but its tough to narrow down apparently when you
don't know the answer. I've been trying to solve this on and off for a
couple weeks now.. Please help if you can. Sending me to BOL isn't any
help.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Eric
> read topic in BOL about standby servers.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > I have a production server in a distant city across our WAN. One warm
> > standy by is there in that city, and a second warm standy by is here
with
> me
> > at Corp HQ.
> >
> > I need a copy of our data, but I don't want to have to bring a copy of
the
> > Production sever down over the WAN.. that'll take too long (~4gbs). I
> would
> > like to take a copy of the local warm standby databases that are
> read-only.
> >
> > How can I get a copy of the read-only databases for uses locally?
> >
> >
>|||Eric
I really want to help you.( don't angry)
Unless I missunderstand you can try to move your backup as zip or rar files
over a network. (4 gb is not so big)
Backup your database then create batch file like that
CD \Program Files\WinRAR
WINRAR a D:\databasename%1 D:\database%1.BAK
After running this file you will get rar/zip file you can easily transfer
over a network.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I've been all over BOL... this is why I'm asking! Because I'm unsure how
> to do it.
> I do not want to do a role change or anything.. Nor do I want to bring one
> of my StandBy Servers online. I simply get a copy of all our data from my
> local Destination servers/Standy server. I need to do this ASAP and would
> appreciate some help rather than the standard "look in BOL".
> If you know, please tell me a little.. I've done Google searchs on this
> topic and looked in BOL, but its tough to narrow down apparently when you
> don't know the answer. I've been trying to solve this on and off for a
> couple weeks now.. Please help if you can. Sending me to BOL isn't any
> help.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > Eric
> > read topic in BOL about standby servers.
> >
> >
> >
> > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > I have a production server in a distant city across our WAN. One warm
> > > standy by is there in that city, and a second warm standy by is here
> with
> > me
> > > at Corp HQ.
> > >
> > > I need a copy of our data, but I don't want to have to bring a copy of
> the
> > > Production sever down over the WAN.. that'll take too long (~4gbs). I
> > would
> > > like to take a copy of the local warm standby databases that are
> > read-only.
> > >
> > > How can I get a copy of the read-only databases for uses locally?
> > >
> > >
> >
> >
>|||No.. reread my Question. I'm well aware that you can backup up a database
and copy that from one place to another.
I do NOT want to backup the database and then copy over our LAN. That will
take many hours. I don't care if 4gb is considered a big database or not.
It's simply a big file to throw across the WAN. Uncompressed it will take 8
hours or something.. compressed it takes 5 hours.
I DO want to use my local warm standby copy. I can't back the database up
because it's in Warm standby mode. So.. can I use this local copy to
move/copy over to a another useable (such as in a development environment)
database. That is my question.
Eric Smith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> Eric
> I really want to help you.( don't angry)
> Unless I missunderstand you can try to move your backup as zip or rar
files
> over a network. (4 gb is not so big)
> Backup your database then create batch file like that
> CD \Program Files\WinRAR
> WINRAR a D:\databasename%1 D:\database%1.BAK
> After running this file you will get rar/zip file you can easily transfer
> over a network.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > I've been all over BOL... this is why I'm asking! Because I'm unsure
how
> > to do it.
> >
> > I do not want to do a role change or anything.. Nor do I want to bring
one
> > of my StandBy Servers online. I simply get a copy of all our data from
my
> > local Destination servers/Standy server. I need to do this ASAP and
would
> > appreciate some help rather than the standard "look in BOL".
> >
> > If you know, please tell me a little.. I've done Google searchs on this
> > topic and looked in BOL, but its tough to narrow down apparently when
you
> > don't know the answer. I've been trying to solve this on and off for a
> > couple weeks now.. Please help if you can. Sending me to BOL isn't any
> > help.
> >
> > Eric
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > Eric
> > > read topic in BOL about standby servers.
> > >
> > >
> > >
> > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > I have a production server in a distant city across our WAN. One
warm
> > > > standy by is there in that city, and a second warm standy by is here
> > with
> > > me
> > > > at Corp HQ.
> > > >
> > > > I need a copy of our data, but I don't want to have to bring a copy
of
> > the
> > > > Production sever down over the WAN.. that'll take too long (~4gbs).
I
> > > would
> > > > like to take a copy of the local warm standby databases that are
> > > read-only.
> > > >
> > > > How can I get a copy of the read-only databases for uses locally?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Eric
I am not following you.
What do you mean by Warm standby mode?
Have you looked at "How to set up, maintain, and bring online a standby
server (Transact-SQL)"
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> No.. reread my Question. I'm well aware that you can backup up a database
> and copy that from one place to another.
> I do NOT want to backup the database and then copy over our LAN. That
will
> take many hours. I don't care if 4gb is considered a big database or not.
> It's simply a big file to throw across the WAN. Uncompressed it will take
8
> hours or something.. compressed it takes 5 hours.
> I DO want to use my local warm standby copy. I can't back the database up
> because it's in Warm standby mode. So.. can I use this local copy to
> move/copy over to a another useable (such as in a development environment)
> database. That is my question.
> Eric Smith
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> > Eric
> > I really want to help you.( don't angry)
> > Unless I missunderstand you can try to move your backup as zip or rar
> files
> > over a network. (4 gb is not so big)
> > Backup your database then create batch file like that
> > CD \Program Files\WinRAR
> > WINRAR a D:\databasename%1 D:\database%1.BAK
> >
> > After running this file you will get rar/zip file you can easily
transfer
> > over a network.
> >
> >
> > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > I've been all over BOL... this is why I'm asking! Because I'm unsure
> how
> > > to do it.
> > >
> > > I do not want to do a role change or anything.. Nor do I want to bring
> one
> > > of my StandBy Servers online. I simply get a copy of all our data
from
> my
> > > local Destination servers/Standy server. I need to do this ASAP and
> would
> > > appreciate some help rather than the standard "look in BOL".
> > >
> > > If you know, please tell me a little.. I've done Google searchs on
this
> > > topic and looked in BOL, but its tough to narrow down apparently when
> you
> > > don't know the answer. I've been trying to solve this on and off for a
> > > couple weeks now.. Please help if you can. Sending me to BOL isn't
any
> > > help.
> > >
> > > Eric
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > Eric
> > > > read topic in BOL about standby servers.
> > > >
> > > >
> > > >
> > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > > I have a production server in a distant city across our WAN. One
> warm
> > > > > standy by is there in that city, and a second warm standy by is
here
> > > with
> > > > me
> > > > > at Corp HQ.
> > > > >
> > > > > I need a copy of our data, but I don't want to have to bring a
copy
> of
> > > the
> > > > > Production sever down over the WAN.. that'll take too long
(~4gbs).
> I
> > > > would
> > > > > like to take a copy of the local warm standby databases that are
> > > > read-only.
> > > > >
> > > > > How can I get a copy of the read-only databases for uses locally?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Uri,
I think it is you that needs to go back and learn a bit more about Log
Shipping.
A destination server in a log shipping scheme is referred to as a "warm
standby". A hot server would be considered live... A cold standby would be
a box that is available and usable but would need the most recent backup
restored to be ready to use.
A warm standby refers to the fact that it's warm and ready to go.. it's not
live, but its not dormant and cold. Its warm, such as in a Log Shipping
scheme. It has all the data and is ready to take over as primary at most
any time.
If you go and try and back up a destination log shipped databsase (a
warm-standby database) you get the message:
"Database XXX is in warm-standby state (set by executing RESTORE WITIH
STANDBY) and cannot be backed up until the entire load sequence is
completed. BACKUP DATABASE is terminating abnormallhy" Hence my original
question of how do I get a backup of a warm-standby database so I can move
it to another server and play with it, such as in a development mode.
No offense, but since I'm having to educate you on this, I don't expect
you'll be able to help with answer. Here is a bit of advise, please only
try and answer Questions for which you have the answer, OK? I need to get
this solved, not convince you that I don't need to go back to BOL.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> Eric
> I am not following you.
> What do you mean by Warm standby mode?
> Have you looked at "How to set up, maintain, and bring online a standby
> server (Transact-SQL)"
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> > No.. reread my Question. I'm well aware that you can backup up a
database
> > and copy that from one place to another.
> >
> > I do NOT want to backup the database and then copy over our LAN. That
> will
> > take many hours. I don't care if 4gb is considered a big database or
not.
> > It's simply a big file to throw across the WAN. Uncompressed it will
take
> 8
> > hours or something.. compressed it takes 5 hours.
> >
> > I DO want to use my local warm standby copy. I can't back the database
up
> > because it's in Warm standby mode. So.. can I use this local copy to
> > move/copy over to a another useable (such as in a development
environment)
> > database. That is my question.
> >
> > Eric Smith
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> > > Eric
> > > I really want to help you.( don't angry)
> > > Unless I missunderstand you can try to move your backup as zip or rar
> > files
> > > over a network. (4 gb is not so big)
> > > Backup your database then create batch file like that
> > > CD \Program Files\WinRAR
> > > WINRAR a D:\databasename%1 D:\database%1.BAK
> > >
> > > After running this file you will get rar/zip file you can easily
> transfer
> > > over a network.
> > >
> > >
> > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > > I've been all over BOL... this is why I'm asking! Because I'm
unsure
> > how
> > > > to do it.
> > > >
> > > > I do not want to do a role change or anything.. Nor do I want to
bring
> > one
> > > > of my StandBy Servers online. I simply get a copy of all our data
> from
> > my
> > > > local Destination servers/Standy server. I need to do this ASAP and
> > would
> > > > appreciate some help rather than the standard "look in BOL".
> > > >
> > > > If you know, please tell me a little.. I've done Google searchs on
> this
> > > > topic and looked in BOL, but its tough to narrow down apparently
when
> > you
> > > > don't know the answer. I've been trying to solve this on and off for
a
> > > > couple weeks now.. Please help if you can. Sending me to BOL isn't
> any
> > > > help.
> > > >
> > > > Eric
> > > >
> > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > > Eric
> > > > > read topic in BOL about standby servers.
> > > > >
> > > > >
> > > > >
> > > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > > > I have a production server in a distant city across our WAN.
One
> > warm
> > > > > > standy by is there in that city, and a second warm standy by is
> here
> > > > with
> > > > > me
> > > > > > at Corp HQ.
> > > > > >
> > > > > > I need a copy of our data, but I don't want to have to bring a
> copy
> > of
> > > > the
> > > > > > Production sever down over the WAN.. that'll take too long
> (~4gbs).
> > I
> > > > > would
> > > > > > like to take a copy of the local warm standby databases that are
> > > > > read-only.
> > > > > >
> > > > > > How can I get a copy of the read-only databases for uses
locally?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||This method may not be officially supported but it works for me.
1. Take the standby database offline.
2. Copy all database files to a new names.
3. Bring the standby database back online.
4. Attach new database using files created in step 2.
Eric Smith wrote:
> Uri,
> I think it is you that needs to go back and learn a bit more about
Log
> Shipping.
> A destination server in a log shipping scheme is referred to as a
"warm
> standby". A hot server would be considered live... A cold standby
would be
> a box that is available and usable but would need the most recent
backup
> restored to be ready to use.
> A warm standby refers to the fact that it's warm and ready to go..
it's not
> live, but its not dormant and cold. Its warm, such as in a Log
Shipping
> scheme. It has all the data and is ready to take over as primary at
most
> any time.
> If you go and try and back up a destination log shipped databsase (a
> warm-standby database) you get the message:
> "Database XXX is in warm-standby state (set by executing RESTORE
WITIH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed. BACKUP DATABASE is terminating abnormallhy" Hence my
original
> question of how do I get a backup of a warm-standby database so I
can move
> it to another server and play with it, such as in a development mode.
> No offense, but since I'm having to educate you on this, I don't
expect
> you'll be able to help with answer. Here is a bit of advise, please
only
> try and answer Questions for which you have the answer, OK? I need
to get
> this solved, not convince you that I don't need to go back to BOL.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> > Eric
> > I am not following you.
> > What do you mean by Warm standby mode?
> > Have you looked at "How to set up, maintain, and bring online a
standby
> > server (Transact-SQL)"
> >
> >
> > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> > > No.. reread my Question. I'm well aware that you can backup up a
> database
> > > and copy that from one place to another.
> > >
> > > I do NOT want to backup the database and then copy over our LAN.
That
> > will
> > > take many hours. I don't care if 4gb is considered a big
database or
> not.
> > > It's simply a big file to throw across the WAN. Uncompressed it
will
> take
> > 8
> > > hours or something.. compressed it takes 5 hours.
> > >
> > > I DO want to use my local warm standby copy. I can't back the
database
> up
> > > because it's in Warm standby mode. So.. can I use this local
copy to
> > > move/copy over to a another useable (such as in a development
> environment)
> > > database. That is my question.
> > >
> > > Eric Smith
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> > > > Eric
> > > > I really want to help you.( don't angry)
> > > > Unless I missunderstand you can try to move your backup as zip
or rar
> > > files
> > > > over a network. (4 gb is not so big)
> > > > Backup your database then create batch file like that
> > > > CD \Program Files\WinRAR
> > > > WINRAR a D:\databasename%1 D:\database%1.BAK
> > > >
> > > > After running this file you will get rar/zip file you can
easily
> > transfer
> > > > over a network.
> > > >
> > > >
> > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > > news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > > > I've been all over BOL... this is why I'm asking! Because
I'm
> unsure
> > > how
> > > > > to do it.
> > > > >
> > > > > I do not want to do a role change or anything.. Nor do I want
to
> bring
> > > one
> > > > > of my StandBy Servers online. I simply get a copy of all our
data
> > from
> > > my
> > > > > local Destination servers/Standy server. I need to do this
ASAP and
> > > would
> > > > > appreciate some help rather than the standard "look in BOL".
> > > > >
> > > > > If you know, please tell me a little.. I've done Google
searchs on
> > this
> > > > > topic and looked in BOL, but its tough to narrow down
apparently
> when
> > > you
> > > > > don't know the answer. I've been trying to solve this on and
off for
> a
> > > > > couple weeks now.. Please help if you can. Sending me to
BOL isn't
> > any
> > > > > help.
> > > > >
> > > > > Eric
> > > > >
> > > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > > > Eric
> > > > > > read topic in BOL about standby servers.
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in
message
> > > > > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > > > > I have a production server in a distant city across our
WAN.
> One
> > > warm
> > > > > > > standy by is there in that city, and a second warm standy
by is
> > here
> > > > > with
> > > > > > me
> > > > > > > at Corp HQ.
> > > > > > >
> > > > > > > I need a copy of our data, but I don't want to have to
bring a
> > copy
> > > of
> > > > > the
> > > > > > > Production sever down over the WAN.. that'll take too
long
> > (~4gbs).
> > > I
> > > > > > would
> > > > > > > like to take a copy of the local warm standby databases
that are
> > > > > > read-only.
> > > > > > >
> > > > > > > How can I get a copy of the read-only databases for uses
> locally?
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
Subscribe to:
Posts (Atom)