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

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]

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]

Correct backup procedure

Hi all, I just havea general question I hope you guys could answer.
What is the best way to both backup my Report Development (i.e. visual
studio report project) and also my deployment on the Report Server?
Thanks for any advice.
Kind regards
TazBackup Report Project:
Do file backup (NTBackup Utility) of the folder containing both project
(rptproj), solution (sln), data source (rds) and report definition (rdl)
files.
Or use Visual Source Safe or similar products to have a centralized code
base, and backup that according to best practices for that product.
Backup Report Server:
Do file backup on the config files for Report Manager and Report Server
Do backup of IIS metadata for the virtual directories
Extract the encryption key and save this, using RSKeyMgmt.exe
These two only needs to be done when making changes to the Report Server,
which shouldn't be too often.
Then do SQL backup of the ReportServer database once a day.
Kaisa M. Lindahl Lervik
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:OpTQd092GHA.4924@.TK2MSFTNGP05.phx.gbl...
> Hi all, I just havea general question I hope you guys could answer.
> What is the best way to both backup my Report Development (i.e. visual
> studio report project) and also my deployment on the Report Server?
> Thanks for any advice.
> Kind regards
> Taz
>|||Fantastic,
excellent post.
Taz
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%23KpG4f%232GHA.476@.TK2MSFTNGP06.phx.gbl...
> Backup Report Project:
> Do file backup (NTBackup Utility) of the folder containing both project
> (rptproj), solution (sln), data source (rds) and report definition (rdl)
> files.
> Or use Visual Source Safe or similar products to have a centralized code
> base, and backup that according to best practices for that product.
> Backup Report Server:
> Do file backup on the config files for Report Manager and Report Server
> Do backup of IIS metadata for the virtual directories
> Extract the encryption key and save this, using RSKeyMgmt.exe
> These two only needs to be done when making changes to the Report Server,
> which shouldn't be too often.
> Then do SQL backup of the ReportServer database once a day.
> Kaisa M. Lindahl Lervik
>
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:OpTQd092GHA.4924@.TK2MSFTNGP05.phx.gbl...
>> Hi all, I just havea general question I hope you guys could answer.
>> What is the best way to both backup my Report Development (i.e. visual
>> studio report project) and also my deployment on the Report Server?
>> Thanks for any advice.
>> Kind regards
>> Taz
>

Copying my SQL Database to another server

Hi guys,

I have created a new SQL database (named “DB”) on my personal pc at home.

I want to copy “DB” to my job’s computer, which works on a local net with SQL server.

What should I do?

Thanks a lot,

Aldo.

you need a connection between the machines first of. If you have VPN to your work's LAN you can use it or create a backup of the database or copy the database files in the sql server directories. If you take the offline/copy approach simply bring the file(s) via removable media into work and restore/attach the database file(s). If you can get a valid remote connection then you could use maintenance plans/SSIS to copy over the database between sql servers assuming you have valid login credentiuals to the remote "work" server.|||

Hi Derek, Which / where is the file I should copy in order to get an offline copy?

I've tried copying my ".mdf" file from this path: "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\DB.mdf" but getting something strange...

Thanks in advance,

Aldo.

|||sorry, you need to detach the database first and then reattach it.|||Where can I see how to "detach" and "reatach" a dababase?|||

Hi ajliks,

I would suggest , first you take a back up of your Database (Db) from your personal pc . Copy the back up file to a CD or some portable storage device. Copy it to your Job's computer. And the restore the database from there on to your SQL Server directly. This would be the easiest way to do it.

Thanks & Regards

Mathew

|||

Hi Mathew, thanks for anwering. I want to know how to make the SQL database copy. I've tried copying the mdf file but getting some strange stuff. (I want to copy the table and views configuration).

Thanks,

Aldo.

|||

1. Open your SQL Server Enterprise Manager

2. Expand and Point to your database.

3. Right Click on your database (Db), and navigate to "All Task " -- "Backup Database" -- "Add" -- "File Name"

4. Give a proper file name and note the location where the backup file is being created.

5. Copy This file to your Job Computer

6. For Restoring on your Job computer check the following steps

7. Open Enterprise Manager on your job computer.

8. Point to Database Node and Right Click on "Restore Database"

9 . Type your Database Name "Db" on the drop down box "Restore Database as"

10 Navigate through "From device" -- "Select devices" -- "Add" - "File Name"

11 Select the backup file and click ok.

You would get ur database created on your machine

Does this makes sense to you now

|||Thanks a lot man!

Copying Data to a File

Hi Guys,
I am trying to fix the following problem:
I need to create a .txt for input into a unix based system (sybase).
This has been done using BCP, and not DTS. Once BCP'ing data into the
.txt file, there are <CR><LF> data at the end of every row. Once the
data is imported into the unix system, these are showing up in the
database.
I've tried BCP with the:
-r \n
-r \r
-r \n \r
With no success (the characters still appear in sybase).
Is there any way to do this with BCP, and not have the characters
appear?
thanks,
Justinjustin.drennan@.gmail.com wrote:
> Hi Guys,
> I am trying to fix the following problem:
> I need to create a .txt for input into a unix based system (sybase).
> This has been done using BCP, and not DTS. Once BCP'ing data into the
> .txt file, there are <CR><LF> data at the end of every row. Once the
> data is imported into the unix system, these are showing up in the
> database.
> I've tried BCP with the:
> -r \n
> -r \r
> -r \n \r
> With no success (the characters still appear in sybase).
> Is there any way to do this with BCP, and not have the characters
> appear?
You might want to try the tr command in *nix:
in_file | tr -d \r > out_file
Delete the carriage return character from the in_file and output to the
out_file.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Copy Table with new Name in same Database

Hi Guys,
Just wondering if anyone knows how to copy a SQL Table into the same
database, whilst still maintaining all if its extended properties.
I tried migrating from Access XP with different table name in Access,
however it doesn't like it with regard to ext. properties, indexes, etc.
Thanks
MarkWhile generating scipts in Enterprise Manager, there's an option in the
'Formatting' tab to script extended properties.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"MarkCapo" <MarkCapo@.discussions.microsoft.com> wrote in message
news:CFDF46ED-0976-4F84-A8D0-A28BA0655710@.microsoft.com...
Hi Guys,
Just wondering if anyone knows how to copy a SQL Table into the same
database, whilst still maintaining all if its extended properties.
I tried migrating from Access XP with different table name in Access,
however it doesn't like it with regard to ext. properties, indexes, etc.
Thanks
Mark|||Mark
SELECT <column list> INTO NewTable FROM OldTable
Note : If you want to copy table with the data you can add WHERE 1=1
otherwise add WHERE 1=2
Also this tecnique does not move PK,FK ,Idenity Property. Check it out.
"MarkCapo" <MarkCapo@.discussions.microsoft.com> wrote in message
news:CFDF46ED-0976-4F84-A8D0-A28BA0655710@.microsoft.com...
> Hi Guys,
> Just wondering if anyone knows how to copy a SQL Table into the same
> database, whilst still maintaining all if its extended properties.
> I tried migrating from Access XP with different table name in Access,
> however it doesn't like it with regard to ext. properties, indexes, etc.
> Thanks
> Mark|||Just a small correction. If the original table has an identity column,
then the new table will also have it. In any case since the original
question was how to create the table with all of its properties (I
guess that by properties he meant constraints), select into is not a
good option.
Adi|||Thanks
CREATE TABLE t1
(
col INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
)
INSERT t1 DEFAULT VALUES
IF OBJECTPROPERTY ( object_id('t1'),'TABLEHASIDENTITY') = 1
print 'Yes'
GO
SELECT * INTO t2 FROM t1
IF OBJECTPROPERTY ( object_id('t2'),'TABLEHASIDENTITY') = 1
print 'Yes'
GO
"Adi" <adico@.clalit.org.il> wrote in message
news:1116850585.889504.289900@.g14g2000cwa.googlegroups.com...
> Just a small correction. If the original table has an identity column,
> then the new table will also have it. In any case since the original
> question was how to create the table with all of its properties (I
> guess that by properties he meant constraints), select into is not a
> good option.
> Adi
>

Copy table values between different database

Hi guys,
I've a problem...
I've a SQL Server database (DB1) with a table (T1) and I want to copy all
the values in T1 to the same table (T2) located on the SQL Server database
DB2.
I want to perform this task by using a stored procedure launched by the
server DB1.
How can I perform this task?
If I have the 2 table on the same db I'll do something like this:
SELECT ....
INTO T2
FROM T1 WHERE ....
but how can I write this by saying that the 2 tables are on differen
databases?
Thanks in advance for all that can help me!
Steve,
Use the database name as part of the object identifier. See "Using
Identifiers as Object Names" in BOL.
Example:
use northwind
go
select * into pubs..t from orders
select * from pubs..t
drop table pubs..t
go
AMB
"Steve" wrote:

> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!
|||Qualify the source or destination table (whichever is applicable) such as
dbname.owner.tablename
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!
|||select ...
INTO DB2.owner.TableName
FROM DB1.owner.TableName
Your stored procedure needs appropriate permission on DB1 and DB2. Look for
ownership chains in BOL.
Ana
"Steve" wrote:

> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!
|||Thanks a lot guys... more easy than what I think
"Tibor Karaszi" wrote:

> Qualify the source or destination table (whichever is applicable) such as
> dbname.owner.tablename
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
>
>

Copy table values between different database

Hi guys,
I've a problem...
I've a SQL Server database (DB1) with a table (T1) and I want to copy all
the values in T1 to the same table (T2) located on the SQL Server database
DB2.
I want to perform this task by using a stored procedure launched by the
server DB1.
How can I perform this task?
If I have the 2 table on the same db I'll do something like this:
SELECT ....
INTO T2
FROM T1 WHERE ....
but how can I write this by saying that the 2 tables are on differen
databases?
Thanks in advance for all that can help me!Steve,
Use the database name as part of the object identifier. See "Using
Identifiers as Object Names" in BOL.
Example:
use northwind
go
select * into pubs..t from orders
select * from pubs..t
drop table pubs..t
go
AMB
"Steve" wrote:
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||Qualify the source or destination table (whichever is applicable) such as
dbname.owner.tablename
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||select ...
INTO DB2.owner.TableName
FROM DB1.owner.TableName
Your stored procedure needs appropriate permission on DB1 and DB2. Look for
ownership chains in BOL.
Ana
"Steve" wrote:
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||Thanks a lot guys... more easy than what I think :)
"Tibor Karaszi" wrote:
> Qualify the source or destination table (whichever is applicable) such as
> dbname.owner.tablename
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
> > Hi guys,
> > I've a problem...
> > I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> > the values in T1 to the same table (T2) located on the SQL Server database
> > DB2.
> > I want to perform this task by using a stored procedure launched by the
> > server DB1.
> > How can I perform this task?
> > If I have the 2 table on the same db I'll do something like this:
> > SELECT ....
> > INTO T2
> > FROM T1 WHERE ....
> > but how can I write this by saying that the 2 tables are on differen
> > databases?
> > Thanks in advance for all that can help me!
>
>

Copy table values between different database

Hi guys,
I've a problem...
I've a SQL Server database (DB1) with a table (T1) and I want to copy all
the values in T1 to the same table (T2) located on the SQL Server database
DB2.
I want to perform this task by using a stored procedure launched by the
server DB1.
How can I perform this task?
If I have the 2 table on the same db I'll do something like this:
SELECT ....
INTO T2
FROM T1 WHERE ....
but how can I write this by saying that the 2 tables are on differen
databases?
Thanks in advance for all that can help me!Steve,
Use the database name as part of the object identifier. See "Using
Identifiers as Object Names" in BOL.
Example:
use northwind
go
select * into pubs..t from orders
select * from pubs..t
drop table pubs..t
go
AMB
"Steve" wrote:

> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||Qualify the source or destination table (whichever is applicable) such as
dbname.owner.tablename
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||select ...
INTO DB2.owner.TableName
FROM DB1.owner.TableName
Your stored procedure needs appropriate permission on DB1 and DB2. Look for
ownership chains in BOL.
Ana
"Steve" wrote:

> Hi guys,
> I've a problem...
> I've a SQL Server database (DB1) with a table (T1) and I want to copy all
> the values in T1 to the same table (T2) located on the SQL Server database
> DB2.
> I want to perform this task by using a stored procedure launched by the
> server DB1.
> How can I perform this task?
> If I have the 2 table on the same db I'll do something like this:
> SELECT ....
> INTO T2
> FROM T1 WHERE ....
> but how can I write this by saying that the 2 tables are on differen
> databases?
> Thanks in advance for all that can help me!|||Thanks a lot guys... more easy than what I think
"Tibor Karaszi" wrote:

> Qualify the source or destination table (whichever is applicable) such as
> dbname.owner.tablename
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:FC0C0FA8-E57D-42A9-9706-BBEDBB145DB5@.microsoft.com...
>
>

copy table from a server to another server

hi guys,
I have a question about copying a table's data from one server to
another server. I want the fastest way to do it. basically, I'm using DTS
wizard to copy but it takes more than 10 minutes because there are a lot of
rows. I wonder if I use BCP by copy data to a file and do bulk insert from a
file, then will it be faster? I 'm not sure because it seems like I'm doing
2 steps instead of 1 step via DTS wizard.
I want to copy data over as fast as possible. Wondering what you guys do in
my situation.
thanks
KevinIs the server on the same network? If so, maybe try this:
INSERT [server].[database].[owner].[table]
SELECT [columns] FROM [table]
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23O7r4yEwFHA.2864@.TK2MSFTNGP10.phx.gbl...
> hi guys,
> I have a question about copying a table's data from one server to
> another server. I want the fastest way to do it. basically, I'm using
> DTS wizard to copy but it takes more than 10 minutes because there are a
> lot of rows. I wonder if I use BCP by copy data to a file and do bulk
> insert from a file, then will it be faster? I 'm not sure because it
> seems like I'm doing 2 steps instead of 1 step via DTS wizard.
> I want to copy data over as fast as possible. Wondering what you guys do
> in my situation.
> thanks
> Kevin
>|||BCP should be faster
I mean try it out see what the time difference is
http://sqlservercode.blogspot.com/
"Kevin" wrote:

> hi guys,
> I have a question about copying a table's data from one server to
> another server. I want the fastest way to do it. basically, I'm using DT
S
> wizard to copy but it takes more than 10 minutes because there are a lot o
f
> rows. I wonder if I use BCP by copy data to a file and do bulk insert from
a
> file, then will it be faster? I 'm not sure because it seems like I'm doi
ng
> 2 steps instead of 1 step via DTS wizard.
> I want to copy data over as fast as possible. Wondering what you guys do
in
> my situation.
> thanks
> Kevin
>
>|||yes, the same network.
but why is your way different from dts or better than DTS?
I don't think it's faster.
"Yosh" <yoshi@.nospam.com> wrote in message
news:eTpbw1EwFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Is the server on the same network? If so, maybe try this:
> INSERT [server].[database].[owner].[table]
> SELECT [columns] FROM [table]
>
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23O7r4yEwFHA.2864@.TK2MSFTNGP10.phx.gbl...
>|||I thought there are some kinds of table setting I can tweak so I can avoid
transaction log. I know one thing, I can remove indexes to speed up the
copying, but what other tricks do you have?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:487F4412-7B52-4B5A-A8C4-8B57A6512E15@.microsoft.com...
> BCP should be faster
> I mean try it out see what the time difference is
> http://sqlservercode.blogspot.com/
>
> "Kevin" wrote:
>

copy row within same table

Gud day guys...i have this problem of copying records within a table...pls help me

I have tried the code below but returned an error.......
"violation of PRIMARY or UNIQUE KEY constraint "INTEG_29" on table "SERVICE" "

Insert into Service (GroupNo,ServiceCode,ServiceName,SystemNo,Upload,timestampstr)
SELECT 6,ServiceCode,ServiceName,SystemNo,Upload,TIMESTAMPSTR
FROM Service
WHERE GroupNo=1

is this possible? pls help me with the sql statement to make this work......

note: timestampstr is the primary key with a not null value ...

Short answer: if timestampstr is your primary key, you need to have different values in there. I don't know what the definition of your timestampstr or what the semantics of its values are, so I can't tell you how to generate a unique value for it for the new rows.

HTH,

Mostafa Elhemali - SQL Engine

|||Its a datatype in Firebird which holds the timestamp value wherein date and time is stored = it is equivalent to datetime datatype in other languages...

my purpose in doing this is that i wanted to copy all records in a table(service) with groupno = 1 and insert in the same table changing only the groupno field in to 6 and all the other records are the same.....any alternative solution for this if primary doesnt allow the same value for a field? thanks.....|||you can not insert the value in the TimeStamp datatype explicitly, the Server will automatically insert the value into it when you do insert operation.