bigint or uniqueidentifer performance metrics?

If the use of the uniqueidentifier is now preferred, across how many
relations is it optimal to use as the type for primary keys? Is there some
point other than performance why one should not worry about reverting to the
use of bigint for related tables? I've only read a couple of stories about
it so far. What do you think?
<%= Clinton GallagherClinton
I would try to avoid using GUID as a Primary Key.
GUIDs take up 16-bytes of storage, more than an Identify column, which in
turn make the index larger, which increases I/O reads, which can hurt
performance. While the performance hit will be minimal if you do decide to
add a clustered index to a GUID column, every little bit adds up
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
> If the use of the uniqueidentifier is now preferred, across how many
> relations is it optimal to use as the type for primary keys? Is there some
> point other than performance why one should not worry about reverting to
> the use of bigint for related tables? I've only read a couple of stories
> about it so far. What do you think?
> <%= Clinton Gallagher
>|||That's what I read Uri but then on the next article GUIDs are praised not to
mention their use in the aspnet_Users table as well as other tables in the
ASP.NET 2.0 Membership, Roles and Profiles implementation Microsoft has
provided.
I think I'll just use their GUID as a foreign key and revert to bigint for
primary keys. It seems there are benefits for grabbing the last inserted
record using @.@.IDENTITY which GUID does not support.
I'm also trying to figure out how to use GUID as a primary key -- anyway --
and auto-generate the value using the newid( ) default binding. I've tried
learning to use newid( ) by entering data into the table but all I get to
see is red circles with ! and error messages or the red circles and no error
messages.
Thanks for your attention...
<%= Clinton Gallagher
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqYqhBqOGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Clinton
> I would try to avoid using GUID as a Primary Key.
> GUIDs take up 16-bytes of storage, more than an Identify column, which in
> turn make the index larger, which increases I/O reads, which can hurt
> performance. While the performance hit will be minimal if you do decide to
> add a clustered index to a GUID column, every little bit adds up
>
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
>|||This is kind of a confusing question, since you really need to give us more
information. Do you have tables that you need to store > 4 billion row? In
2005 there is a way to create an always increasing guid using
newsequentialid that can help a bit with the problems that using a GUID for
a key can create, but GUIDs make fine surrogate keys for tables, other than
performance and typability (the latter of which is just a programmer
convienience, so should not be really important to the question of whether
to choose them or not)
If you need the benefits of a GUID (like the UI can create them and be
guaranteed uniqueness,) then it might be worth it, otherwise use the correct
integer type. If you are going to have > 4 billion rows in many of your
tables, I hope you have spent a lot of money on a fast disk subsystem anyhow
because you will need it!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
> If the use of the uniqueidentifier is now preferred, across how many
> relations is it optimal to use as the type for primary keys? Is there some
> point other than performance why one should not worry about reverting to
> the use of bigint for related tables? I've only read a couple of stories
> about it so far. What do you think?
> <%= Clinton Gallagher
>|||Well, good point. I should be comparing an int rather than bigint.
If its true that deleting a record can leave gaps in the sequence of the
key's
value when using an int which can impact sorting the same must be true
when using the uniqueidentifier.
Furthermore, as I understand it even when using newsequentialid( ) there
is no @.@.IDENTITY to get the value of the last inserted record. Do you
know if this is true?
Other than uniqueness, I see no reason to use uniqueidentifier yet..
That's the basis of this inquiry.
<%= Clinton Gallagher
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OwfDDvxOGHA.2040@.TK2MSFTNGP14.phx.gbl...
> This is kind of a confusing question, since you really need to give us
> more information. Do you have tables that you need to store > 4 billion
> row? In 2005 there is a way to create an always increasing guid using
> newsequentialid that can help a bit with the problems that using a GUID
> for a key can create, but GUIDs make fine surrogate keys for tables, other
> than performance and typability (the latter of which is just a programmer
> convienience, so should not be really important to the question of whether
> to choose them or not)
> If you need the benefits of a GUID (like the UI can create them and be
> guaranteed uniqueness,) then it might be worth it, otherwise use the
> correct integer type. If you are going to have > 4 billion rows in many
> of your tables, I hope you have spent a lot of money on a fast disk
> subsystem anyhow because you will need it!
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
>|||> Furthermore, as I understand it even when using newsequentialid( ) there
> is no @.@.IDENTITY to get the value of the last inserted record. Do you
> know if this is true?
One of the differences between uniqueidentifier and identity is that a
uniqueidentifier value can be assigned by the application instead of SQL
Server. This eliminates the need to retrieve the surrogate key value after
insertion.
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2313yEZzOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Well, good point. I should be comparing an int rather than bigint.
> If its true that deleting a record can leave gaps in the sequence of the
> key's
> value when using an int which can impact sorting the same must be true
> when using the uniqueidentifier.
> Furthermore, as I understand it even when using newsequentialid( ) there
> is no @.@.IDENTITY to get the value of the last inserted record. Do you
> know if this is true?
> Other than uniqueness, I see no reason to use uniqueidentifier yet..
> That's the basis of this inquiry.
> <%= Clinton Gallagher
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OwfDDvxOGHA.2040@.TK2MSFTNGP14.phx.gbl...
>|||That was helpful to be reminded of as the DNF will indeed generate a GUID
consistent with the uniqueidentitifer.
<%= Clinton Gallagher
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OOChgd0OGHA.2696@.TK2MSFTNGP14.phx.gbl...
> One of the differences between uniqueidentifier and identity is that a
> uniqueidentifier value can be assigned by the application instead of SQL
> Server. This eliminates the need to retrieve the surrogate key value
> after insertion.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2313yEZzOGHA.2624@.TK2MSFTNGP12.phx.gbl...
>

Big Problems

I've come across an issue which i believe is related to Windows 2003 populating database elements, notably date/time types, and having those fields read on another platform (eg. winXP, win98) and, although everything would seem to match, comparissons fail.

For example, if i populate a datetime field with the value 22:22 (eg. 10:22 pm) from a Windows 2003 pc, then read the value of the field on another pc (eg. WinXP), and perform a comparisson in code (vb6sp5), the values are not equal.

I have observed this against Access database using DAO 2.5, 3.51 and 3.6 on both sides. Also observed against SQL Server 2000.

All i can figure is that windows 2003 is causing the problem, but i don't know how to resolve it.

I understand this is not a forum for operating system questions, but my intent would be to generate a routine which would correctly populate the database elements on the win2003 side, for proper execution on the other pcs in use.

Any help would be greatly appreciated.Do you perform your comparison in code or visually? I had to use my 2003 with client tools against 2K SQL Server and had no prob. If you're doing visual comparison then you need to also account for regional settings.|||visually everything looks the same
in code, i've compared stored results vs expected results. here's a sample of what i found:

If i stored, from the Win2003 pc, the value 0:01 (12:01am, no date) in either an access or sql server field, then attempted from a winxp pro machine to compare the stored number to winxp's interpretation of 12:01 am, the values would look to be the same, but vb would not claim them to be equal. if i check the difference between the two, surprisingly, the difference is 1.0842021724855E-19, and that's enough to cause this symptom. the difference fluctuates, with 12:23 am working and being equal when the above steps are followed. the pattern of equal/not equal is very strange indeed.

i have verified regional settings on both pcs. no problems.

this all works fine with all pc's being win2003. it seems that 2003 is treating something different, so comparisons to a value stored from the win2003, then compared on another os, it all fails.|||This is not a SQL issue. It's a VB issue. I dont get any problems like this in VB.NET. It's been so long for VB6 and I dont really feel like installing it so I wont be able to do any VB6 testing. Have you tried using VB.NET instead? I'm sure you'll be cursing at VB6 in no time once you switch to .NET. :D

Also what version of Access? 97, 2k?|||hahahaha

oh yeah, i've used vb.net and it's a much better development platform for sure. i will test this scenario in vb.net and see what the results are.

unfortunetly, i am supporting legacy applications and bumping up to vb.net does not give me the immediate solution i require.

has nobody else observed this behaviour?

access 97 and 2000
sql server 2000 (msde and enterprise) v8.00.760

Best way to mirror data?

I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
I'm constantly exporting/importing across from one to the other using the
export/import tool within SQL Enterprise Manager. I'm consistently having
to update "default values" etc. as they are wiped clean when I
import/export.
I could do a script dump in SQL Query Analyzer for each table, but I was
figuring there had to be a better way to do this...?How about a job to do a backup on one, copy it across the network, then
restore on server 2? You also have the copy database wizard in DTS although
I've never used it
Ray Higdon MCSE, MCDBA, CCNA
--
"Jesse Bilsten" <jesse@.vreo.com> wrote in message
news:OaExfP%23$DHA.688@.tk2msftngp13.phx.gbl...
> I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
> I'm constantly exporting/importing across from one to the other using the
> export/import tool within SQL Enterprise Manager. I'm consistently having
> to update "default values" etc. as they are wiped clean when I
> import/export.
> I could do a script dump in SQL Query Analyzer for each table, but I was
> figuring there had to be a better way to do this...?
>
>|||You can try SQL Compare from Red-Gate. It isn't perfect, but it is better
than most 'roll your own' systems that I have seen, including my own.
www.red-gate.com
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jesse Bilsten" <jesse@.vreo.com> wrote in message
news:OaExfP%23$DHA.688@.tk2msftngp13.phx.gbl...
> I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
> I'm constantly exporting/importing across from one to the other using the
> export/import tool within SQL Enterprise Manager. I'm consistently having
> to update "default values" etc. as they are wiped clean when I
> import/export.
> I could do a script dump in SQL Query Analyzer for each table, but I was
> figuring there had to be a better way to do this...?
>
>|||Check out DB Ghost from Innovartis at http://www.dbghost.com
Darren Fuller
SQL Server DBA MCSE
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Best way to mirror data?

I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
I'm constantly exporting/importing across from one to the other using the
export/import tool within SQL Enterprise Manager. I'm consistently having
to update "default values" etc. as they are wiped clean when I
import/export.
I could do a script dump in SQL Query Analyzer for each table, but I was
figuring there had to be a better way to do this...?How about a job to do a backup on one, copy it across the network, then
restore on server 2? You also have the copy database wizard in DTS although
I've never used it
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Jesse Bilsten" <jesse@.vreo.com> wrote in message
news:OaExfP%23$DHA.688@.tk2msftngp13.phx.gbl...
> I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
> I'm constantly exporting/importing across from one to the other using the
> export/import tool within SQL Enterprise Manager. I'm consistently having
> to update "default values" etc. as they are wiped clean when I
> import/export.
> I could do a script dump in SQL Query Analyzer for each table, but I was
> figuring there had to be a better way to do this...?
>
>|||You can try SQL Compare from Red-Gate. It isn't perfect, but it is better
than most 'roll your own' systems that I have seen, including my own.
www.red-gate.com
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jesse Bilsten" <jesse@.vreo.com> wrote in message
news:OaExfP%23$DHA.688@.tk2msftngp13.phx.gbl...
> I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
> I'm constantly exporting/importing across from one to the other using the
> export/import tool within SQL Enterprise Manager. I'm consistently having
> to update "default values" etc. as they are wiped clean when I
> import/export.
> I could do a script dump in SQL Query Analyzer for each table, but I was
> figuring there had to be a better way to do this...?
>
>|||You can easily create a job in SQL Server Agent.
>--Original Message--
>I've got two SQL servers (sql server 2000 version 8.0)
(dev) and (live).
>I'm constantly exporting/importing across from one to the
other using the
>export/import tool within SQL Enterprise Manager. I'm
consistently having
>to update "default values" etc. as they are wiped clean
when I
>import/export.
>I could do a script dump in SQL Query Analyzer for each
table, but I was
>figuring there had to be a better way to do this...?
>
>.
>

Best way to format a letter so that text can break across a page

I'm currently using SQL reporting services to produce letters. Most of the
formatting can be achieved however I'm having problems with page breaks.
I've currently set up the report using Text Boxes in the following format:
Name
Address
Salutation
Main Content
From
I need to ensure that the Main Content follows on directly from the
Salutation field. This works fine if the letter is 1 or 3 pages long.
However, in the case where the Main Content requires a few lines on page 2 it
places all the main content onto Page 2.
How can I ensure that the Main Content always follows directly on from the
Salutation? Is it possible to do this using a table instead of text boxes?
Any help would be greatly appreciated!I should have mentioned that the report is being produced in PDF format.
I read in a newsgroup article that PDF won't split a single row between 2
pages unless the row itself is longer than one page. Is this still the case
or is there a workaround? If this it true, then one solution might be to
bring back the main content text in sections although this isn't too
appealing!
"SV" wrote:
> I'm currently using SQL reporting services to produce letters. Most of the
> formatting can be achieved however I'm having problems with page breaks.
> I've currently set up the report using Text Boxes in the following format:
> Name
> Address
> Salutation
> Main Content
> From
> I need to ensure that the Main Content follows on directly from the
> Salutation field. This works fine if the letter is 1 or 3 pages long.
> However, in the case where the Main Content requires a few lines on page 2 it
> places all the main content onto Page 2.
> How can I ensure that the Main Content always follows directly on from the
> Salutation? Is it possible to do this using a table instead of text boxes?
> Any help would be greatly appreciated!

Best way to copy large number of databases to new server?

We're in the process of migrating to a new database server, and have a large
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
ChrisIf they are on the same network and you can´t reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a large
> number of databases to move across (approx 100). I found the 'copy database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>

Best way to copy large number of databases to new server?

We're in the process of migrating to a new database server, and have a large
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
Chris
If they are on the same network and you cant reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:

> We're in the process of migrating to a new database server, and have a large
> number of databases to move across (approx 100). I found the 'copy database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>
|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
spam999free@.rrohio.com
remove 999 in order to email me
|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>

Best way to copy large number of databases to new server?

We're in the process of migrating to a new database server, and have a large
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
ChrisIf they are on the same network and you cant reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:

> We're in the process of migrating to a new database server, and have a lar
ge
> number of databases to move across (approx 100). I found the 'copy databas
e
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into
a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
spam999free@.rrohio.com
remove 999 in order to email me|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>