install SQL on WinXP: moving

Showing posts with label moving. Show all posts

Sunday, February 12, 2012

Can I pass '=' or '>' as parameters?

Hi
I'm moving SQL in my app to Stored Procedures.

My SQL string is currently created within my app, and I have two check boxes and this C# code...

if(chkPartTime.Checked&&!chkFullTime.Checked) { sql+=" AND P0_Hours = 0"; }

if(!chkPartTime.Checked&&chkFullTime.Checked) { sql+=" AND P0_Hours > 0"; }

where sql is my query string

If both boxes are checked I don't care what value P0_Hours is, but at least one of them will be checked.

What I want to know is how I transfer such logic into a Stored Procedure, or whether I will need to creat 2 similar sp's and choose which to call in my app.

It will combined with a query (currently 60 lines) comprising of 3 UNIONs

TIA

Sure why not...

you could literally pass a char(1) of '>' or '=' and use it to build the sql string

However wouldn't it be better to pass 'bit' fields like this:

SP paremeters:

@.chkPartTime bit

@.chkFullTime bit

then in the code:

in the Where clause:

Code Snippet

and ((p0_hours = 0 and @.chkPartTime = 1 and @.chkFullTime = 0) or (p0Hours > 0 and @.chkFullTime = 1 and @.chkPartTime = 0))

or better yet... just have one parameter:

@.chkFullTime bit and then 0 = Parttime 1 = FullTime

so then the code would look like:

Code Snippet

and ((p0_hours = 0 and @.chkFullTime = 0) or (p0Hours > 0 and @.chkFullTime = 1))

-Robert

|||

use the flag parameter (new param) & pass the appropriate flag value to the sp.

for example on your code,

Code Snippet

int iFlag = 0;

if(chkPartTime.Checked && !chkFullTime.Checked) { iFlag = 1; }

if(!chkPartTime.Checked && chkFullTime.Checked) { iFlag = 2; }

//Pass this flag variable as parameter while calling the SP

on your SP,

Check the flag using IF condition are on where condition to get the required result.

Code Snippet

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

@.Flag = 0

(@.Flag = 1 And P0_Hours =0)

(@.Flag = 2 And P0_Hours >0)

--or

If @.Flag = 0

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

If @.Flag = 1

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

P0_Hours =0

If @.Flag = 2

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

P0_Hours > 0

|||

I don't know, I usually would just suggest a dynamic SQL stored procedure in these cases, particularly if there is any real heft to your data. The other solutions suggested are a good possibilities too, but the complexities of coding tend to turn things like this into a nightmare both to maintain, and to get a decent query plan anyhow.

Louis

|||

Thank you all

It was difficult to choose which to mark as the answer!

I had not heard of dynamic sp's (I come from a DB2 background, now using SQL Server) and I have learnt from all 3 answers, still not made my mind up which way to go, but thanks.

Can I move System database log files?

I am trying to improve performance on my sql server by moving the log files
to a different drive than my mdf files. I have done this sucessfuly with my
user databases, but wanted some reassurance before trying the system
databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
detach, and a attach database from Enterprise Administrator (I'm not actuall
y
a dba ).
Is there a recommened method (if it's possible) to move the log files of the
system databases?
TIA,
JerameSorry, the link is here
21120120" target="_blank">http://support.microsoft.com/defaul...br />
21120120
Regards.
"Jerame" wrote:

> I am trying to improve performance on my sql server by moving the log file
s
> to a different drive than my mdf files. I have done this sucessfuly with m
y
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not actua
lly
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of t
he
> system databases?
> TIA,
> Jerame|||Hi
Master, Model and MSDB are so very low thoughput DB's that they are not an
issue with performance.
Temp DB:
From BOL:
I. Move the tempdb database
This example moves tempdb from its current location on disk to another disk
location.
Note This example is applicable to tempdb only. To move user databases, use
sp_detach_db and sp_attach_db. For more information, see Attaching and
Detaching a Database.
Determine the logical file names for the tempdb database.
USE tempdb
GO
EXEC sp_helpfile
GO
--The logical name for each file is contained in the NAME column.
Change the location of each file using ALTER DATABASE.
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
GO
Stop and restart SQL Server.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jerame" <Jerame@.discussions.microsoft.com> wrote in message
news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with
> my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not
> actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of
> the
> system databases?
> TIA,
> Jerame|||There's a code error in the BOL example that I should point out. The
Filename specified should have a \ after the drive letter (E:\...)
You may also want to look at this KB article:
http://support.microsoft.com/?id=224071
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:umLWgcjZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another
> disk location.
> Note This example is applicable to tempdb only. To move user databases,
> use sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>|||Thanks Mike. I guess I'm looking for some ways to improve the perofrmance,
which may now lead to a new post.
I have already seperated my user databases mdf and ldf files, but I still
see high Ave. Disk Queue Length in Perf Mon. I'm connected to a SAN, so I
have my mdf files on one logical drive, which is using one path (hba) to the
SAN. I have the ldf files now on another logical disk, using my other patch
(second hba) back to the SAN.
Durring Transaction Log backups (trn) or full backups (bak) I see the Ave.
Queue Length skyrockets to 50 and 60 (sometimes higher) for a few minutes
(the duration of the backup). I am also receiving traps from my server the
the logical disk is busy ( I set thresholds of warnings at 80%, and critical
at 90%).
I have a seperate mirrored OS drive. More than enough physical ram, as well
as swap file space. Any suggestions?
Thanks,
Jerame
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another dis
k
> location.
> Note This example is applicable to tempdb only. To move user databases, u
se
> sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>
>

Can I move System database log files?

I am trying to improve performance on my sql server by moving the log files
to a different drive than my mdf files. I have done this sucessfuly with my
user databases, but wanted some reassurance before trying the system
databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
detach, and a attach database from Enterprise Administrator (I'm not actually
a dba ).
Is there a recommened method (if it's possible) to move the log files of the
system databases?
TIA,
JerameThere is how to move user DBs as well as system DBs and trans logs
between devices.
Regards.
"Jerame" wrote:
> I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of the
> system databases?
> TIA,
> Jerame|||Sorry, the link is here
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071#XSLTH3188121122120121120120
Regards.
"Jerame" wrote:
> I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of the
> system databases?
> TIA,
> Jerame|||Hi
Master, Model and MSDB are so very low thoughput DB's that they are not an
issue with performance.
Temp DB:
From BOL:
I. Move the tempdb database
This example moves tempdb from its current location on disk to another disk
location.
Note This example is applicable to tempdb only. To move user databases, use
sp_detach_db and sp_attach_db. For more information, see Attaching and
Detaching a Database.
Determine the logical file names for the tempdb database.
USE tempdb
GO
EXEC sp_helpfile
GO
--The logical name for each file is contained in the NAME column.
Change the location of each file using ALTER DATABASE.
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
GO
Stop and restart SQL Server.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jerame" <Jerame@.discussions.microsoft.com> wrote in message
news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with
> my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not
> actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of
> the
> system databases?
> TIA,
> Jerame|||There's a code error in the BOL example that I should point out. The
Filename specified should have a \ after the drive letter (E:\...)
You may also want to look at this KB article:
http://support.microsoft.com/?id=224071
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:umLWgcjZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another
> disk location.
> Note This example is applicable to tempdb only. To move user databases,
> use sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>>I am trying to improve performance on my sql server by moving the log
>>files
>> to a different drive than my mdf files. I have done this sucessfuly with
>> my
>> user databases, but wanted some reassurance before trying the system
>> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
>> detach, and a attach database from Enterprise Administrator (I'm not
>> actually
>> a dba ).
>> Is there a recommened method (if it's possible) to move the log files of
>> the
>> system databases?
>> TIA,
>> Jerame
>|||Thanks Mike. I guess I'm looking for some ways to improve the perofrmance,
which may now lead to a new post.
I have already seperated my user databases mdf and ldf files, but I still
see high Ave. Disk Queue Length in Perf Mon. I'm connected to a SAN, so I
have my mdf files on one logical drive, which is using one path (hba) to the
SAN. I have the ldf files now on another logical disk, using my other patch
(second hba) back to the SAN.
Durring Transaction Log backups (trn) or full backups (bak) I see the Ave.
Queue Length skyrockets to 50 and 60 (sometimes higher) for a few minutes
(the duration of the backup). I am also receiving traps from my server the
the logical disk is busy ( I set thresholds of warnings at 80%, and critical
at 90%).
I have a seperate mirrored OS drive. More than enough physical ram, as well
as swap file space. Any suggestions?
Thanks,
Jerame
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another disk
> location.
> Note This example is applicable to tempdb only. To move user databases, use
> sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
> >I am trying to improve performance on my sql server by moving the log files
> > to a different drive than my mdf files. I have done this sucessfuly with
> > my
> > user databases, but wanted some reassurance before trying the system
> > databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> > detach, and a attach database from Enterprise Administrator (I'm not
> > actually
> > a dba ).
> >
> > Is there a recommened method (if it's possible) to move the log files of
> > the
> > system databases?
> >
> > TIA,
> > Jerame
>
>

Can I move System database log files?

I am trying to improve performance on my sql server by moving the log files
to a different drive than my mdf files. I have done this sucessfuly with my
user databases, but wanted some reassurance before trying the system
databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
detach, and a attach database from Enterprise Administrator (I'm not actually
a dba ).
Is there a recommened method (if it's possible) to move the log files of the
system databases?
TIA,
Jerame
Sorry, the link is here
http://support.microsoft.com/default...22120121120120
Regards.
"Jerame" wrote:

> I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of the
> system databases?
> TIA,
> Jerame
|||Hi
Master, Model and MSDB are so very low thoughput DB's that they are not an
issue with performance.
Temp DB:
From BOL:
I. Move the tempdb database
This example moves tempdb from its current location on disk to another disk
location.
Note This example is applicable to tempdb only. To move user databases, use
sp_detach_db and sp_attach_db. For more information, see Attaching and
Detaching a Database.
Determine the logical file names for the tempdb database.
USE tempdb
GO
EXEC sp_helpfile
GO
--The logical name for each file is contained in the NAME column.
Change the location of each file using ALTER DATABASE.
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
GO
Stop and restart SQL Server.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jerame" <Jerame@.discussions.microsoft.com> wrote in message
news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with
> my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not
> actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of
> the
> system databases?
> TIA,
> Jerame
|||There's a code error in the BOL example that I should point out. The
Filename specified should have a \ after the drive letter (E:\...)
You may also want to look at this KB article:
http://support.microsoft.com/?id=224071
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:umLWgcjZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another
> disk location.
> Note This example is applicable to tempdb only. To move user databases,
> use sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>
|||Thanks Mike. I guess I'm looking for some ways to improve the perofrmance,
which may now lead to a new post.
I have already seperated my user databases mdf and ldf files, but I still
see high Ave. Disk Queue Length in Perf Mon. I'm connected to a SAN, so I
have my mdf files on one logical drive, which is using one path (hba) to the
SAN. I have the ldf files now on another logical disk, using my other patch
(second hba) back to the SAN.
Durring Transaction Log backups (trn) or full backups (bak) I see the Ave.
Queue Length skyrockets to 50 and 60 (sometimes higher) for a few minutes
(the duration of the backup). I am also receiving traps from my server the
the logical disk is busy ( I set thresholds of warnings at 80%, and critical
at 90%).
I have a seperate mirrored OS drive. More than enough physical ram, as well
as swap file space. Any suggestions?
Thanks,
Jerame
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another disk
> location.
> Note This example is applicable to tempdb only. To move user databases, use
> sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>
>

Can I move SQL Server to a new machine (including master/model/msd

Hi
We are moving a SQLServer environment from one machine to another. (SQL2000
sp3a)
Can I
- Install SQLServer on the new machine
- backup all databases including master, model , msdb + all user databases
on the source machine
- restore all databases (including mast , model, msdb + user databases) on
the destination machine
and expect SQL server to work as it did on my source machine? (Note in my
case the instance name will be different on the destination machine since the
instance name was the same as the machine name in the orig source :
npboffsql2\npboffsql2
Or do I need to follow article 314546 (i.e. only move user databases and
rebuild the missing logins etc?)
Tia,
FloraHi Flora
You could follow http://support.microsoft.com/kb/224071/
If you wish to rename the server itself then you can follow
http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
John
"Flora Seymour" wrote:
> Hi
> We are moving a SQLServer environment from one machine to another. (SQL2000
> sp3a)
> Can I
> - Install SQLServer on the new machine
> - backup all databases including master, model , msdb + all user databases
> on the source machine
> - restore all databases (including mast , model, msdb + user databases) on
> the destination machine
> and expect SQL server to work as it did on my source machine? (Note in my
> case the instance name will be different on the destination machine since the
> instance name was the same as the machine name in the orig source :
> npboffsql2\npboffsql2
> Or do I need to follow article 314546 (i.e. only move user databases and
> rebuild the missing logins etc?)
> Tia,
> Flora|||Thanks John! Thats exactly what I am looking for!
--
Flora
"John Bell" wrote:
> Hi Flora
> You could follow http://support.microsoft.com/kb/224071/
> If you wish to rename the server itself then you can follow
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
> John
> "Flora Seymour" wrote:
> > Hi
> >
> > We are moving a SQLServer environment from one machine to another. (SQL2000
> > sp3a)
> >
> > Can I
> > - Install SQLServer on the new machine
> > - backup all databases including master, model , msdb + all user databases
> > on the source machine
> > - restore all databases (including mast , model, msdb + user databases) on
> > the destination machine
> >
> > and expect SQL server to work as it did on my source machine? (Note in my
> > case the instance name will be different on the destination machine since the
> > instance name was the same as the machine name in the orig source :
> > npboffsql2\npboffsql2
> >
> > Or do I need to follow article 314546 (i.e. only move user databases and
> > rebuild the missing logins etc?)
> >
> > Tia,
> > Flora

Sunday, February 12, 2012

Can I pass '=' or '>' as parameters?

Can I move System database log files?

Can I move System database log files?

Can I move System database log files?

Can I move SQL Server to a new machine (including master/model/msd

Can I move SQL Server to a new machine (including master/model/msd

Can I move SQL Server to a new machine (including master/model/msd

install SQL on WinXP

Blog Archive

About Me