Hello and thank you for your help in advance.
I have Windows 2000 SP4 and when I go to install RS, the system check
returns "Authz is not installed on this computer". When I take the install
to the next step, there is only the option to install Client Components and
not Server Components which I need. Any ideas?
Thanks again.
--Nevermind, I figured it out thank you!
"Richard Cranium" <mgreco40@.hotmail.com> wrote in message
news:%23qizsRUOFHA.3880@.tk2msftngp13.phx.gbl...
> Hello and thank you for your help in advance.
> I have Windows 2000 SP4 and when I go to install RS, the system check
> returns "Authz is not installed on this computer". When I take the install
> to the next step, there is only the option to install Client Components
> and not Server Components which I need. Any ideas?
> Thanks again.
>
> --
>
>|||Nevermid, I figured it out, thank you!
"Richard Cranium" <mgreco40@.hotmail.com> wrote in message
news:%23qizsRUOFHA.3880@.tk2msftngp13.phx.gbl...
> Hello and thank you for your help in advance.
> I have Windows 2000 SP4 and when I go to install RS, the system check
> returns "Authz is not installed on this computer". When I take the install
> to the next step, there is only the option to install Client Components
> and not Server Components which I need. Any ideas?
> Thanks again.
>
> --
>
>|||How did you figure this problem out as I am also getting the same error?
--
Message posted via http://www.sqlmonster.com|||How did you figure this problem out as I am also getting the same error?
--
Message posted via http://www.sqlmonster.com
Showing posts with label install. Show all posts
Showing posts with label install. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
authentication on SQL 2005
I have an installation program that during install was able to authenticate
to SQL 2000 and validate "sa" and password. But it won't do that on SQL
2005. The program installs directly on the same box as that the SQL runs. Is
there any reason why authentication should fail?
Comments Please,
Michael ValeCruzAre you getting an error back? Did you set an SA password when you
installed SQL2k5?|||No error other than invalid user name and password.
Yes the password is valid, I use it to connect with the SQL Server
Management Studio using SQL Server Authentication and the SA is set with
server role "sysadmin"
I am just wondering if there is something in SQL 2000 that is automatically
on that is not turned on in SQL 2005 for this authentication to take place.
"PSPDBA" <williambr@.state.pa.us> wrote in message
news:1147285864.526093.215500@.v46g2000cwv.googlegroups.com...
> Are you getting an error back? Did you set an SA password when you
> installed SQL2k5?
>|||You might want to check that the program isn't sending MachineName\sa
instead of sa. When connecting from the same machine, I can't think of
anything that needs to be turned on. Look through the Surface Area
Configuration Tool and see if theres something that stands out..|||I met install experience when I intalled first SQL Server 2005 and second
SQL Server 2000.
In this situation I think that SA direct to S2K5 by default
"PSPDBA"?? ??? ??:
> You might want to check that the program isn't sending MachineName\sa
> instead of sa. When connecting from the same machine, I can't think of
> anything that needs to be turned on. Look through the Surface Area
> Configuration Tool and see if theres something that stands out..
>
to SQL 2000 and validate "sa" and password. But it won't do that on SQL
2005. The program installs directly on the same box as that the SQL runs. Is
there any reason why authentication should fail?
Comments Please,
Michael ValeCruzAre you getting an error back? Did you set an SA password when you
installed SQL2k5?|||No error other than invalid user name and password.
Yes the password is valid, I use it to connect with the SQL Server
Management Studio using SQL Server Authentication and the SA is set with
server role "sysadmin"
I am just wondering if there is something in SQL 2000 that is automatically
on that is not turned on in SQL 2005 for this authentication to take place.
"PSPDBA" <williambr@.state.pa.us> wrote in message
news:1147285864.526093.215500@.v46g2000cwv.googlegroups.com...
> Are you getting an error back? Did you set an SA password when you
> installed SQL2k5?
>|||You might want to check that the program isn't sending MachineName\sa
instead of sa. When connecting from the same machine, I can't think of
anything that needs to be turned on. Look through the Surface Area
Configuration Tool and see if theres something that stands out..|||I met install experience when I intalled first SQL Server 2005 and second
SQL Server 2000.
In this situation I think that SA direct to S2K5 by default
"PSPDBA"?? ??? ??:
> You might want to check that the program isn't sending MachineName\sa
> instead of sa. When connecting from the same machine, I can't think of
> anything that needs to be turned on. Look through the Surface Area
> Configuration Tool and see if theres something that stands out..
>
Authentication Modes
I am trying to install and configure Office Server Extensions on our web server, but when I configure them and put the information about the discussion server and press next it bombs on me. I already created the database and gave it a user name and password. I tried to look up the error message and found only one that seemed like it was close. Our SQL server is set up with SQL Server and Windows authentication mode. The fix for the error I found says that I thought edit SQL Server Registration properties and change under Connection from Windows Authentication to Use SQL Server authentication. I want to try this and see if this fix works to configure the OSEs correctly. My question is can I change SQL server to Use SQL server authentication without any problems with the other databases or will someone logging into the server be blocked from access the database they need? ThanksAccording to the info you provided, your server is already configured to handle SQL Server Authentication. There is no conflct in haveing both athentication modes active at the same time.
Sunday, February 12, 2012
Attaching a database
We have an install created with Wise that installs the .net framework and
MSDE 2000 silently. Everything seems to work fine on the install of these
two packages.
After we install MSDE and we make sure the service has started, we need to
attach a database. We do that by executing a batch file that looks like
this:
osql -E -S(local)\<myinstance> -Q "sp_attach_db @.dbname = N'X2', @.filename1
= N'C:\Program Files\<appname>\x2.mdf', @.filename2 = N'C:\Program
Files\<appname>\x2.ldf'"
After all of this executes and I try to run my application it says that the
login fails because the sa password is incorrect. We are executing the MSDE
install setup via command line like:
SETUP INSTANCENAME="<myinstance>" SECURITYMODE=sql SAPWD="password" /qn
In order to get the app to run, we have to go doubleclick on the batch file
(shown above).
Any ideas why the execution of this batch file might not be working the
first time?
Thanks.
STom
hi Stom,
"STom" <stombiztalker@.hotmail.com> ha scritto nel messaggio
news:ejRS$aM9EHA.1300@.TK2MSFTNGP14.phx.gbl
> We have an install created with Wise that installs the .net framework
> and MSDE 2000 silently. Everything seems to work fine on the install
> of these two packages.
> After we install MSDE and we make sure the service has started, we
> need to attach a database. We do that by executing a batch file that
> looks like this:
> osql -E -S(local)\<myinstance> -Q "sp_attach_db @.dbname = N'X2',
> @.filename1 = N'C:\Program Files\<appname>\x2.mdf', @.filename2 =
> N'C:\Program Files\<appname>\x2.ldf'"
> After all of this executes and I try to run my application it says
> that the login fails because the sa password is incorrect. We are
> executing the MSDE install setup via command line like:
> SETUP INSTANCENAME="<myinstance>" SECURITYMODE=sql SAPWD="password"
> /qn
> In order to get the app to run, we have to go doubleclick on the
> batch file (shown above).
> Any ideas why the execution of this batch file might not be working
> the first time?
> Thanks.
> STom
I do no clearly understand your question... I prepared a .Bat file as
following,
osql -E -S(local) -Q"EXEC sp_attach_db @.dbname = N'a', @.filename1 =
N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a.mdf', @.filename2 =
N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a_log.LDF'" >c:\err.txt
and it works as expected...
you say
> After all of this executes and I try to run my application it says
> that the login fails because the sa password is incorrect.
and this seems to me not related with the execution of sp_attach_db, but
perhaps my english is to poor to understand your meaning...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Sorry for the confusion.
What I mean is that when that batch file is initially executed, by the Wise
utility, it does not appear to work, because when I run the app, it says if
fails for login 'sa'.
If I then go double-click on this batch file, and run the app, the app runs
fine.
Notice that the batch file does not set any passwords. It just attaches the
database, so I don't think the problem is with the login, its with the
attaching.
The question is, why would it not work if the Wise utility executes it?
STom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:347n53F431apgU1@.individual.net...
> hi Stom,
> "STom" <stombiztalker@.hotmail.com> ha scritto nel messaggio
> news:ejRS$aM9EHA.1300@.TK2MSFTNGP14.phx.gbl
> I do no clearly understand your question... I prepared a .Bat file as
> following,
> osql -E -S(local) -Q"EXEC sp_attach_db @.dbname = N'a', @.filename1 =
> N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a.mdf', @.filename2 =
> N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a_log.LDF'" >c:\err.txt
> and it works as expected...
> you say
> and this seems to me not related with the execution of sp_attach_db, but
> perhaps my english is to poor to understand your meaning...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
"STom" <stombiztalker@.hotmail.com> ha scritto nel messaggio
news:%23KK1qaN9EHA.3616@.TK2MSFTNGP11.phx.gbl
> Sorry for the confusion.
> What I mean is that when that batch file is initially executed, by
> the Wise utility, it does not appear to work, because when I run the
> app, it says if fails for login 'sa'.
thi is another problem, I think...
osql -E indicates to log in MSDE using osql with a trusted WinNT
connections... and this is a thing..
> If I then go double-click on this batch file, and run the app, the
> app runs fine.
> Notice that the batch file does not set any passwords. It just
> attaches the database, so I don't think the problem is with the
> login, its with the attaching.
> The question is, why would it not work if the Wise utility executes
> it?
I actually do not know how Wise installs MSDE... perhaps the MSDE
installation is still pending and not finished... you've probably better
check this with Wise people...
sorry
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||STom wrote:
> What I mean is that when that batch file is initially executed, by the Wise
> utility, it does not appear to work, because when I run the app, it says if
> fails for login 'sa'.
>
Hi STom,
try to capture the output which is produced by the batch file
to see. Maybe osql is not yet in the path or something similar.
HTH,
Gerald
MSDE 2000 silently. Everything seems to work fine on the install of these
two packages.
After we install MSDE and we make sure the service has started, we need to
attach a database. We do that by executing a batch file that looks like
this:
osql -E -S(local)\<myinstance> -Q "sp_attach_db @.dbname = N'X2', @.filename1
= N'C:\Program Files\<appname>\x2.mdf', @.filename2 = N'C:\Program
Files\<appname>\x2.ldf'"
After all of this executes and I try to run my application it says that the
login fails because the sa password is incorrect. We are executing the MSDE
install setup via command line like:
SETUP INSTANCENAME="<myinstance>" SECURITYMODE=sql SAPWD="password" /qn
In order to get the app to run, we have to go doubleclick on the batch file
(shown above).
Any ideas why the execution of this batch file might not be working the
first time?
Thanks.
STom
hi Stom,
"STom" <stombiztalker@.hotmail.com> ha scritto nel messaggio
news:ejRS$aM9EHA.1300@.TK2MSFTNGP14.phx.gbl
> We have an install created with Wise that installs the .net framework
> and MSDE 2000 silently. Everything seems to work fine on the install
> of these two packages.
> After we install MSDE and we make sure the service has started, we
> need to attach a database. We do that by executing a batch file that
> looks like this:
> osql -E -S(local)\<myinstance> -Q "sp_attach_db @.dbname = N'X2',
> @.filename1 = N'C:\Program Files\<appname>\x2.mdf', @.filename2 =
> N'C:\Program Files\<appname>\x2.ldf'"
> After all of this executes and I try to run my application it says
> that the login fails because the sa password is incorrect. We are
> executing the MSDE install setup via command line like:
> SETUP INSTANCENAME="<myinstance>" SECURITYMODE=sql SAPWD="password"
> /qn
> In order to get the app to run, we have to go doubleclick on the
> batch file (shown above).
> Any ideas why the execution of this batch file might not be working
> the first time?
> Thanks.
> STom
I do no clearly understand your question... I prepared a .Bat file as
following,
osql -E -S(local) -Q"EXEC sp_attach_db @.dbname = N'a', @.filename1 =
N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a.mdf', @.filename2 =
N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a_log.LDF'" >c:\err.txt
and it works as expected...
you say
> After all of this executes and I try to run my application it says
> that the login fails because the sa password is incorrect.
and this seems to me not related with the execution of sp_attach_db, but
perhaps my english is to poor to understand your meaning...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Sorry for the confusion.
What I mean is that when that batch file is initially executed, by the Wise
utility, it does not appear to work, because when I run the app, it says if
fails for login 'sa'.
If I then go double-click on this batch file, and run the app, the app runs
fine.
Notice that the batch file does not set any passwords. It just attaches the
database, so I don't think the problem is with the login, its with the
attaching.
The question is, why would it not work if the Wise utility executes it?
STom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:347n53F431apgU1@.individual.net...
> hi Stom,
> "STom" <stombiztalker@.hotmail.com> ha scritto nel messaggio
> news:ejRS$aM9EHA.1300@.TK2MSFTNGP14.phx.gbl
> I do no clearly understand your question... I prepared a .Bat file as
> following,
> osql -E -S(local) -Q"EXEC sp_attach_db @.dbname = N'a', @.filename1 =
> N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a.mdf', @.filename2 =
> N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a_log.LDF'" >c:\err.txt
> and it works as expected...
> you say
> and this seems to me not related with the execution of sp_attach_db, but
> perhaps my english is to poor to understand your meaning...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
"STom" <stombiztalker@.hotmail.com> ha scritto nel messaggio
news:%23KK1qaN9EHA.3616@.TK2MSFTNGP11.phx.gbl
> Sorry for the confusion.
> What I mean is that when that batch file is initially executed, by
> the Wise utility, it does not appear to work, because when I run the
> app, it says if fails for login 'sa'.
thi is another problem, I think...
osql -E indicates to log in MSDE using osql with a trusted WinNT
connections... and this is a thing..
> If I then go double-click on this batch file, and run the app, the
> app runs fine.
> Notice that the batch file does not set any passwords. It just
> attaches the database, so I don't think the problem is with the
> login, its with the attaching.
> The question is, why would it not work if the Wise utility executes
> it?
I actually do not know how Wise installs MSDE... perhaps the MSDE
installation is still pending and not finished... you've probably better
check this with Wise people...
sorry
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||STom wrote:
> What I mean is that when that batch file is initially executed, by the Wise
> utility, it does not appear to work, because when I run the app, it says if
> fails for login 'sa'.
>
Hi STom,
try to capture the output which is produced by the batch file
to see. Maybe osql is not yet in the path or something similar.
HTH,
Gerald
Attached Northwind to SQL Express but no data in tables
I installed Northwind sample database by running the install script within server management sudio express and the database and tables show up, but there are no records. Once executed, it says it was successful.
Am I missing something?
The data is there. I had built some code to extract the customer data to a datagrid and it was only pulling the column headers and no data. I opened the managment studio and asked it to open a table and the records were there. Sorry for the post.
Thursday, February 9, 2012
attach vs restore
When moving all yous database to a new server where you install Sql Server,
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
Tom
On Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent
|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Steen Schlter Persson (DK) wrote:[vbcol=seagreen]
> tshad wrote:
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
[vbcol=seagreen]
...
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
Tom
On Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent
|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Steen Schlter Persson (DK) wrote:[vbcol=seagreen]
> tshad wrote:
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
[vbcol=seagreen]
...
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).
attach vs restore
When moving all yous database to a new server where you install Sql Server,
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
TomOn Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server
,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server
,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Steen Schlter Persson (DK) wrote:
> tshad wrote:
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
[vbcol=seagreen]
...[vbcol=seagreen]
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
TomOn Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server
,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server
,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Steen Schlter Persson (DK) wrote:
> tshad wrote:
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
[vbcol=seagreen]
...[vbcol=seagreen]
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).
attach vs restore
When moving all yous database to a new server where you install Sql Server,
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
TomOn Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Steen Schlüter Persson (DK) wrote:
> tshad wrote:
>> When moving all yous database to a new server where you install Sql
>> Server, is it better to use detach, copy database to new server than
>> attach it or backup on the old server and restore the backup.
>> I assume that I don't really need to move the Master or do I?
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
...
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
TomOn Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Steen Schlüter Persson (DK) wrote:
> tshad wrote:
>> When moving all yous database to a new server where you install Sql
>> Server, is it better to use detach, copy database to new server than
>> attach it or backup on the old server and restore the backup.
>> I assume that I don't really need to move the Master or do I?
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
...
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).
Subscribe to:
Posts (Atom)