hello,
I am not an administrator, please if I want to know the name of my sql
server, how can i find it?
I have sql server 2000 which I installed long time ago, I forgot what I
named it during the installation, i think it is the default name.
Is it local or (local) or something else?
Thank you.
Hi,
Login to Query analyzer and execute the below command
select @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"Philip Germanos" <PhilipGermanos@.discussions.microsoft.com> wrote in
message news:D659E5D9-F809-4C0E-8DE4-F6DCD98BFC66@.microsoft.com...
> hello,
> I am not an administrator, please if I want to know the name of my sql
> server, how can i find it?
> I have sql server 2000 which I installed long time ago, I forgot what I
> named it during the installation, i think it is the default name.
> Is it local or (local) or something else?
> Thank you.
Showing posts with label administrator. Show all posts
Showing posts with label administrator. Show all posts
Tuesday, March 27, 2012
Basic question
hello,
I am not an administrator, please if I want to know the name of my sql
server, how can i find it?
I have sql server 2000 which I installed long time ago, I forgot what I
named it during the installation, i think it is the default name.
Is it local or (local) or something else?
Thank you.Hi,
Login to Query analyzer and execute the below command
select @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"Philip Germanos" <PhilipGermanos@.discussions.microsoft.com> wrote in
message news:D659E5D9-F809-4C0E-8DE4-F6DCD98BFC66@.microsoft.com...
> hello,
> I am not an administrator, please if I want to know the name of my sql
> server, how can i find it?
> I have sql server 2000 which I installed long time ago, I forgot what I
> named it during the installation, i think it is the default name.
> Is it local or (local) or something else?
> Thank you.
I am not an administrator, please if I want to know the name of my sql
server, how can i find it?
I have sql server 2000 which I installed long time ago, I forgot what I
named it during the installation, i think it is the default name.
Is it local or (local) or something else?
Thank you.Hi,
Login to Query analyzer and execute the below command
select @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"Philip Germanos" <PhilipGermanos@.discussions.microsoft.com> wrote in
message news:D659E5D9-F809-4C0E-8DE4-F6DCD98BFC66@.microsoft.com...
> hello,
> I am not an administrator, please if I want to know the name of my sql
> server, how can i find it?
> I have sql server 2000 which I installed long time ago, I forgot what I
> named it during the installation, i think it is the default name.
> Is it local or (local) or something else?
> Thank you.
Basic question
hello,
I am not an administrator, please if I want to know the name of my sql
server, how can i find it?
I have sql server 2000 which I installed long time ago, I forgot what I
named it during the installation, i think it is the default name.
Is it local or (local) or something else?
Thank you.Hi,
Login to Query analyzer and execute the below command
select @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"Philip Germanos" <PhilipGermanos@.discussions.microsoft.com> wrote in
message news:D659E5D9-F809-4C0E-8DE4-F6DCD98BFC66@.microsoft.com...
> hello,
> I am not an administrator, please if I want to know the name of my sql
> server, how can i find it?
> I have sql server 2000 which I installed long time ago, I forgot what I
> named it during the installation, i think it is the default name.
> Is it local or (local) or something else?
> Thank you.sql
I am not an administrator, please if I want to know the name of my sql
server, how can i find it?
I have sql server 2000 which I installed long time ago, I forgot what I
named it during the installation, i think it is the default name.
Is it local or (local) or something else?
Thank you.Hi,
Login to Query analyzer and execute the below command
select @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"Philip Germanos" <PhilipGermanos@.discussions.microsoft.com> wrote in
message news:D659E5D9-F809-4C0E-8DE4-F6DCD98BFC66@.microsoft.com...
> hello,
> I am not an administrator, please if I want to know the name of my sql
> server, how can i find it?
> I have sql server 2000 which I installed long time ago, I forgot what I
> named it during the installation, i think it is the default name.
> Is it local or (local) or something else?
> Thank you.sql
Friday, February 24, 2012
backup/restore vs attach/detach
Hi... I've recently had to pick up being an SQL Administrator again after
about 5 years without touching it.
I have just installed SQL Server 2005 on a new cluster and it is all up and
running and ready to test our CRM application against it.
I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
I want to copy the data from our SQL Server 2000 instance to our SQL Server
2005 instance. I seem to be able to do this by detaching the database,
copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
server and re-attaching the original data to the SQL2000 copy which will
remain live until we finish testing compatibility. At which point we would
then copy the live data again from the SQL2000 instance to the SQL2005
instance and the use the SQL2005 version from there on.
THe problem is that when I detach, users can't get at the data. I've got
200 some internal users and lord knows how many external users accessing this
data. It doesn't seem right that there is no way to do this without
detaching the database interrupting the users? Can't I use a
backup/restore? I've tried but can't seem to get the restore to work. Is
there compatibility difference between SQL2000 backup and SQL2005 backup? In
which case is there someway I an get a copy of all the data on the SQL2000
server to the SQL2005 Server without interrupting users on the SQL2000
server?
Sorry, might seem a basic question, but I've been out of the line of fire
for a long time on this sort of thing, so I appreciate your patience.
Thanks
Bill
A backup and restore should work just fine and be a totally on-line
operation for the 2000 db. When you say you can't get it to work what
exactly is happening? Is there an error message?
Andrew J. Kelly SQL MVP
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up
> and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach
> functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL
> Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test
> SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we
> would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing
> this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
> In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
|||billd wrote:
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup? In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
Hi Bill
A Backup/Restore shouldn't cause a problem, and is actually it's a more
"safe" procedure. When you detach your database, you haven't got
anything until you get it attached again. Not even your source is
available. This means that you have a potential risk that the attach
fails and in that case you've lost your database. I know it's not very
likely that it really happens, but the risk is always there.
If you use backup/restore, you'll keep your source online and working.
Then if the backup file is corrupt in one way or the other, you can
always go back and do a new backup.
As Andrew mentions, you'll have to provide some more info on what it is
that goes wrong with your restore - then it's easier to help you...;-).
Regards
Steen
|||Backup and restore is the most obvious option...
However, if you have the SQL script to create your database on the SQL 2005
server, you could create it and then use DTS to copy across the data . This
gives you the opportunity to also review some of the data types used and
swap them for the SQL 2005 types alternatives (if preferred) in the SQL
script before creating the database.
Griff
|||In addition to the other posts, you might want to read about sp_change_users_login (and also search
KB for sp_help_revlogins).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup? In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
|||Thanks Steen... I feel the same way that you do about detach/attach! It took
a really long time to detach the northwind database and I was very nervous
the whole while that it might take down the SQL engine itself.. I hate when
things take a long time... so, anyway, the failure: My SQL2000 data is on
the F: drive, which is a clustered resource. I backup up to base.bak in the
temp dir. Then I copy this to my S: drive which is the clustered resource on
my SQL2005 server. I then try to restore this base.bak file (it only has one
backup in it) I think get the error message: (grrrr, why don't then let you
cut and paste the text from error messages?)
... and as I did this, I realized... just this very minute, that I hadn't
gone to the database page and checked the overwrite database box... was the
problem, so yes I have found the answer...
Thanks to all for your help! I really only needed to know that
backup/restore should work and then could figure it out myself I guess.
restore failed for server SQLDB. (Microsoft.SQLServer.SMO)
Additional Information
System.data.sqlclient.sqlerror cannot use file:
"Steen Persson (DK)" wrote:
> billd wrote:
> Hi Bill
> A Backup/Restore shouldn't cause a problem, and is actually it's a more
> "safe" procedure. When you detach your database, you haven't got
> anything until you get it attached again. Not even your source is
> available. This means that you have a potential risk that the attach
> fails and in that case you've lost your database. I know it's not very
> likely that it really happens, but the risk is always there.
> If you use backup/restore, you'll keep your source online and working.
> Then if the backup file is corrupt in one way or the other, you can
> always go back and do a new backup.
> As Andrew mentions, you'll have to provide some more info on what it is
> that goes wrong with your restore - then it's easier to help you...;-).
> Regards
> Steen
>
about 5 years without touching it.
I have just installed SQL Server 2005 on a new cluster and it is all up and
running and ready to test our CRM application against it.
I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
I want to copy the data from our SQL Server 2000 instance to our SQL Server
2005 instance. I seem to be able to do this by detaching the database,
copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
server and re-attaching the original data to the SQL2000 copy which will
remain live until we finish testing compatibility. At which point we would
then copy the live data again from the SQL2000 instance to the SQL2005
instance and the use the SQL2005 version from there on.
THe problem is that when I detach, users can't get at the data. I've got
200 some internal users and lord knows how many external users accessing this
data. It doesn't seem right that there is no way to do this without
detaching the database interrupting the users? Can't I use a
backup/restore? I've tried but can't seem to get the restore to work. Is
there compatibility difference between SQL2000 backup and SQL2005 backup? In
which case is there someway I an get a copy of all the data on the SQL2000
server to the SQL2005 Server without interrupting users on the SQL2000
server?
Sorry, might seem a basic question, but I've been out of the line of fire
for a long time on this sort of thing, so I appreciate your patience.
Thanks
Bill
A backup and restore should work just fine and be a totally on-line
operation for the 2000 db. When you say you can't get it to work what
exactly is happening? Is there an error message?
Andrew J. Kelly SQL MVP
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up
> and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach
> functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL
> Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test
> SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we
> would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing
> this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
> In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
|||billd wrote:
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup? In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
Hi Bill
A Backup/Restore shouldn't cause a problem, and is actually it's a more
"safe" procedure. When you detach your database, you haven't got
anything until you get it attached again. Not even your source is
available. This means that you have a potential risk that the attach
fails and in that case you've lost your database. I know it's not very
likely that it really happens, but the risk is always there.
If you use backup/restore, you'll keep your source online and working.
Then if the backup file is corrupt in one way or the other, you can
always go back and do a new backup.
As Andrew mentions, you'll have to provide some more info on what it is
that goes wrong with your restore - then it's easier to help you...;-).
Regards
Steen
|||Backup and restore is the most obvious option...
However, if you have the SQL script to create your database on the SQL 2005
server, you could create it and then use DTS to copy across the data . This
gives you the opportunity to also review some of the data types used and
swap them for the SQL 2005 types alternatives (if preferred) in the SQL
script before creating the database.
Griff
|||In addition to the other posts, you might want to read about sp_change_users_login (and also search
KB for sp_help_revlogins).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup? In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
|||Thanks Steen... I feel the same way that you do about detach/attach! It took
a really long time to detach the northwind database and I was very nervous
the whole while that it might take down the SQL engine itself.. I hate when
things take a long time... so, anyway, the failure: My SQL2000 data is on
the F: drive, which is a clustered resource. I backup up to base.bak in the
temp dir. Then I copy this to my S: drive which is the clustered resource on
my SQL2005 server. I then try to restore this base.bak file (it only has one
backup in it) I think get the error message: (grrrr, why don't then let you
cut and paste the text from error messages?)
... and as I did this, I realized... just this very minute, that I hadn't
gone to the database page and checked the overwrite database box... was the
problem, so yes I have found the answer...
Thanks to all for your help! I really only needed to know that
backup/restore should work and then could figure it out myself I guess.
restore failed for server SQLDB. (Microsoft.SQLServer.SMO)
Additional Information
System.data.sqlclient.sqlerror cannot use file:
"Steen Persson (DK)" wrote:
> billd wrote:
> Hi Bill
> A Backup/Restore shouldn't cause a problem, and is actually it's a more
> "safe" procedure. When you detach your database, you haven't got
> anything until you get it attached again. Not even your source is
> available. This means that you have a potential risk that the attach
> fails and in that case you've lost your database. I know it's not very
> likely that it really happens, but the risk is always there.
> If you use backup/restore, you'll keep your source online and working.
> Then if the backup file is corrupt in one way or the other, you can
> always go back and do a new backup.
> As Andrew mentions, you'll have to provide some more info on what it is
> that goes wrong with your restore - then it's easier to help you...;-).
> Regards
> Steen
>
backup/restore vs attach/detach
Hi... I've recently had to pick up being an SQL Administrator again after
about 5 years without touching it.
I have just installed SQL Server 2005 on a new cluster and it is all up and
running and ready to test our CRM application against it.
I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
I want to copy the data from our SQL Server 2000 instance to our SQL Server
2005 instance. I seem to be able to do this by detaching the database,
copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
server and re-attaching the original data to the SQL2000 copy which will
remain live until we finish testing compatibility. At which point we would
then copy the live data again from the SQL2000 instance to the SQL2005
instance and the use the SQL2005 version from there on.
THe problem is that when I detach, users can't get at the data. I've got
200 some internal users and lord knows how many external users accessing this
data. It doesn't seem right that there is no way to do this without
detaching the database interrupting the users? Can't I use a
backup/restore? I've tried but can't seem to get the restore to work. Is
there compatibility difference between SQL2000 backup and SQL2005 backup? In
which case is there someway I an get a copy of all the data on the SQL2000
server to the SQL2005 Server without interrupting users on the SQL2000
server?
Sorry, might seem a basic question, but I've been out of the line of fire
for a long time on this sort of thing, so I appreciate your patience.
Thanks
BillA backup and restore should work just fine and be a totally on-line
operation for the 2000 db. When you say you can't get it to work what
exactly is happening? Is there an error message?
--
Andrew J. Kelly SQL MVP
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up
> and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach
> functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL
> Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test
> SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we
> would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing
> this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
> In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>|||billd wrote:
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup? In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
Hi Bill
A Backup/Restore shouldn't cause a problem, and is actually it's a more
"safe" procedure. When you detach your database, you haven't got
anything until you get it attached again. Not even your source is
available. This means that you have a potential risk that the attach
fails and in that case you've lost your database. I know it's not very
likely that it really happens, but the risk is always there.
If you use backup/restore, you'll keep your source online and working.
Then if the backup file is corrupt in one way or the other, you can
always go back and do a new backup.
As Andrew mentions, you'll have to provide some more info on what it is
that goes wrong with your restore - then it's easier to help you...;-).
Regards
Steen|||Backup and restore is the most obvious option...
However, if you have the SQL script to create your database on the SQL 2005
server, you could create it and then use DTS to copy across the data . This
gives you the opportunity to also review some of the data types used and
swap them for the SQL 2005 types alternatives (if preferred) in the SQL
script before creating the database.
Griff|||In addition to the other posts, you might want to read about sp_change_users_login (and also search
KB for sp_help_revlogins).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup? In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>|||Thanks Steen... I feel the same way that you do about detach/attach! It took
a really long time to detach the northwind database and I was very nervous
the whole while that it might take down the SQL engine itself.. I hate when
things take a long time... so, anyway, the failure: My SQL2000 data is on
the F: drive, which is a clustered resource. I backup up to base.bak in the
temp dir. Then I copy this to my S: drive which is the clustered resource on
my SQL2005 server. I then try to restore this base.bak file (it only has one
backup in it) I think get the error message: (grrrr, why don't then let you
cut and paste the text from error messages')
... and as I did this, I realized... just this very minute, that I hadn't
gone to the database page and checked the overwrite database box... was the
problem, so yes I have found the answer...
Thanks to all for your help! I really only needed to know that
backup/restore should work and then could figure it out myself I guess.
restore failed for server SQLDB. (Microsoft.SQLServer.SMO)
Additional Information
System.data.sqlclient.sqlerror cannot use file:
"Steen Persson (DK)" wrote:
> billd wrote:
> > Hi... I've recently had to pick up being an SQL Administrator again after
> > about 5 years without touching it.
> >
> > I have just installed SQL Server 2005 on a new cluster and it is all up and
> > running and ready to test our CRM application against it.
> >
> > I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> >
> > I want to copy the data from our SQL Server 2000 instance to our SQL Server
> > 2005 instance. I seem to be able to do this by detaching the database,
> > copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> > server and re-attaching the original data to the SQL2000 copy which will
> > remain live until we finish testing compatibility. At which point we would
> > then copy the live data again from the SQL2000 instance to the SQL2005
> > instance and the use the SQL2005 version from there on.
> >
> > THe problem is that when I detach, users can't get at the data. I've got
> > 200 some internal users and lord knows how many external users accessing this
> > data. It doesn't seem right that there is no way to do this without
> > detaching the database interrupting the users? Can't I use a
> > backup/restore? I've tried but can't seem to get the restore to work. Is
> > there compatibility difference between SQL2000 backup and SQL2005 backup? In
> > which case is there someway I an get a copy of all the data on the SQL2000
> > server to the SQL2005 Server without interrupting users on the SQL2000
> > server?
> >
> > Sorry, might seem a basic question, but I've been out of the line of fire
> > for a long time on this sort of thing, so I appreciate your patience.
> >
> > Thanks
> > Bill
> >
> Hi Bill
> A Backup/Restore shouldn't cause a problem, and is actually it's a more
> "safe" procedure. When you detach your database, you haven't got
> anything until you get it attached again. Not even your source is
> available. This means that you have a potential risk that the attach
> fails and in that case you've lost your database. I know it's not very
> likely that it really happens, but the risk is always there.
> If you use backup/restore, you'll keep your source online and working.
> Then if the backup file is corrupt in one way or the other, you can
> always go back and do a new backup.
> As Andrew mentions, you'll have to provide some more info on what it is
> that goes wrong with your restore - then it's easier to help you...;-).
> Regards
> Steen
>
about 5 years without touching it.
I have just installed SQL Server 2005 on a new cluster and it is all up and
running and ready to test our CRM application against it.
I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
I want to copy the data from our SQL Server 2000 instance to our SQL Server
2005 instance. I seem to be able to do this by detaching the database,
copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
server and re-attaching the original data to the SQL2000 copy which will
remain live until we finish testing compatibility. At which point we would
then copy the live data again from the SQL2000 instance to the SQL2005
instance and the use the SQL2005 version from there on.
THe problem is that when I detach, users can't get at the data. I've got
200 some internal users and lord knows how many external users accessing this
data. It doesn't seem right that there is no way to do this without
detaching the database interrupting the users? Can't I use a
backup/restore? I've tried but can't seem to get the restore to work. Is
there compatibility difference between SQL2000 backup and SQL2005 backup? In
which case is there someway I an get a copy of all the data on the SQL2000
server to the SQL2005 Server without interrupting users on the SQL2000
server?
Sorry, might seem a basic question, but I've been out of the line of fire
for a long time on this sort of thing, so I appreciate your patience.
Thanks
BillA backup and restore should work just fine and be a totally on-line
operation for the 2000 db. When you say you can't get it to work what
exactly is happening? Is there an error message?
--
Andrew J. Kelly SQL MVP
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up
> and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach
> functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL
> Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test
> SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we
> would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing
> this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
> In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>|||billd wrote:
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup? In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
Hi Bill
A Backup/Restore shouldn't cause a problem, and is actually it's a more
"safe" procedure. When you detach your database, you haven't got
anything until you get it attached again. Not even your source is
available. This means that you have a potential risk that the attach
fails and in that case you've lost your database. I know it's not very
likely that it really happens, but the risk is always there.
If you use backup/restore, you'll keep your source online and working.
Then if the backup file is corrupt in one way or the other, you can
always go back and do a new backup.
As Andrew mentions, you'll have to provide some more info on what it is
that goes wrong with your restore - then it's easier to help you...;-).
Regards
Steen|||Backup and restore is the most obvious option...
However, if you have the SQL script to create your database on the SQL 2005
server, you could create it and then use DTS to copy across the data . This
gives you the opportunity to also review some of the data types used and
swap them for the SQL 2005 types alternatives (if preferred) in the SQL
script before creating the database.
Griff|||In addition to the other posts, you might want to read about sp_change_users_login (and also search
KB for sp_help_revlogins).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup? In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>|||Thanks Steen... I feel the same way that you do about detach/attach! It took
a really long time to detach the northwind database and I was very nervous
the whole while that it might take down the SQL engine itself.. I hate when
things take a long time... so, anyway, the failure: My SQL2000 data is on
the F: drive, which is a clustered resource. I backup up to base.bak in the
temp dir. Then I copy this to my S: drive which is the clustered resource on
my SQL2005 server. I then try to restore this base.bak file (it only has one
backup in it) I think get the error message: (grrrr, why don't then let you
cut and paste the text from error messages')
... and as I did this, I realized... just this very minute, that I hadn't
gone to the database page and checked the overwrite database box... was the
problem, so yes I have found the answer...
Thanks to all for your help! I really only needed to know that
backup/restore should work and then could figure it out myself I guess.
restore failed for server SQLDB. (Microsoft.SQLServer.SMO)
Additional Information
System.data.sqlclient.sqlerror cannot use file:
"Steen Persson (DK)" wrote:
> billd wrote:
> > Hi... I've recently had to pick up being an SQL Administrator again after
> > about 5 years without touching it.
> >
> > I have just installed SQL Server 2005 on a new cluster and it is all up and
> > running and ready to test our CRM application against it.
> >
> > I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> >
> > I want to copy the data from our SQL Server 2000 instance to our SQL Server
> > 2005 instance. I seem to be able to do this by detaching the database,
> > copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> > server and re-attaching the original data to the SQL2000 copy which will
> > remain live until we finish testing compatibility. At which point we would
> > then copy the live data again from the SQL2000 instance to the SQL2005
> > instance and the use the SQL2005 version from there on.
> >
> > THe problem is that when I detach, users can't get at the data. I've got
> > 200 some internal users and lord knows how many external users accessing this
> > data. It doesn't seem right that there is no way to do this without
> > detaching the database interrupting the users? Can't I use a
> > backup/restore? I've tried but can't seem to get the restore to work. Is
> > there compatibility difference between SQL2000 backup and SQL2005 backup? In
> > which case is there someway I an get a copy of all the data on the SQL2000
> > server to the SQL2005 Server without interrupting users on the SQL2000
> > server?
> >
> > Sorry, might seem a basic question, but I've been out of the line of fire
> > for a long time on this sort of thing, so I appreciate your patience.
> >
> > Thanks
> > Bill
> >
> Hi Bill
> A Backup/Restore shouldn't cause a problem, and is actually it's a more
> "safe" procedure. When you detach your database, you haven't got
> anything until you get it attached again. Not even your source is
> available. This means that you have a potential risk that the attach
> fails and in that case you've lost your database. I know it's not very
> likely that it really happens, but the risk is always there.
> If you use backup/restore, you'll keep your source online and working.
> Then if the backup file is corrupt in one way or the other, you can
> always go back and do a new backup.
> As Andrew mentions, you'll have to provide some more info on what it is
> that goes wrong with your restore - then it's easier to help you...;-).
> Regards
> Steen
>
backup/restore vs attach/detach
Hi... I've recently had to pick up being an SQL Administrator again after
about 5 years without touching it.
I have just installed SQL Server 2005 on a new cluster and it is all up and
running and ready to test our CRM application against it.
I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
I want to copy the data from our SQL Server 2000 instance to our SQL Server
2005 instance. I seem to be able to do this by detaching the database,
copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
server and re-attaching the original data to the SQL2000 copy which will
remain live until we finish testing compatibility. At which point we would
then copy the live data again from the SQL2000 instance to the SQL2005
instance and the use the SQL2005 version from there on.
THe problem is that when I detach, users can't get at the data. I've got
200 some internal users and lord knows how many external users accessing thi
s
data. It doesn't seem right that there is no way to do this without
detaching the database interrupting the users? Can't I use a
backup/restore? I've tried but can't seem to get the restore to work. Is
there compatibility difference between SQL2000 backup and SQL2005 backup? I
n
which case is there someway I an get a copy of all the data on the SQL2000
server to the SQL2005 Server without interrupting users on the SQL2000
server?
Sorry, might seem a basic question, but I've been out of the line of fire
for a long time on this sort of thing, so I appreciate your patience.
Thanks
BillA backup and restore should work just fine and be a totally on-line
operation for the 2000 db. When you say you can't get it to work what
exactly is happening? Is there an error message?
Andrew J. Kelly SQL MVP
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up
> and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach
> functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL
> Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test
> SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we
> would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing
> this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
> In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>|||billd wrote:
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up an
d
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality
.
> I want to copy the data from our SQL Server 2000 instance to our SQL Serve
r
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL200
5
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we woul
d
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing t
his
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
Hi Bill
A Backup/Restore shouldn't cause a problem, and is actually it's a more
"safe" procedure. When you detach your database, you haven't got
anything until you get it attached again. Not even your source is
available. This means that you have a potential risk that the attach
fails and in that case you've lost your database. I know it's not very
likely that it really happens, but the risk is always there.
If you use backup/restore, you'll keep your source online and working.
Then if the backup file is corrupt in one way or the other, you can
always go back and do a new backup.
As Andrew mentions, you'll have to provide some more info on what it is
that goes wrong with your restore - then it's easier to help you...;-).
Regards
Steen|||Backup and restore is the most obvious option...
However, if you have the SQL script to create your database on the SQL 2005
server, you could create it and then use DTS to copy across the data . This
gives you the opportunity to also review some of the data types used and
swap them for the SQL 2005 types alternatives (if preferred) in the SQL
script before creating the database.
Griff|||In addition to the other posts, you might want to read about sp_change_users
_login (and also search
KB for sp_help_revlogins).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up an
d
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality
.
> I want to copy the data from our SQL Server 2000 instance to our SQL Serve
r
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL200
5
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we woul
d
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing t
his
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>|||Thanks Steen... I feel the same way that you do about detach/attach! It too
k
a really long time to detach the northwind database and I was very nervous
the whole while that it might take down the SQL engine itself.. I hate when
things take a long time... so, anyway, the failure: My SQL2000 data is on
the F: drive, which is a clustered resource. I backup up to base.bak in the
temp dir. Then I copy this to my S: drive which is the clustered resource o
n
my SQL2005 server. I then try to restore this base.bak file (it only has on
e
backup in it) I think get the error message: (grrrr, why don't then let yo
u
cut and paste the text from error messages')
... and as I did this, I realized... just this very minute, that I hadn't
gone to the database page and checked the overwrite database box... was the
problem, so yes I have found the answer...
Thanks to all for your help! I really only needed to know that
backup/restore should work and then could figure it out myself I guess.
restore failed for server SQLDB. (Microsoft.SQLServer.SMO)
Additional Information
System.data.sqlclient.sqlerror cannot use file:
"Steen Persson (DK)" wrote:
> billd wrote:
> Hi Bill
> A Backup/Restore shouldn't cause a problem, and is actually it's a more
> "safe" procedure. When you detach your database, you haven't got
> anything until you get it attached again. Not even your source is
> available. This means that you have a potential risk that the attach
> fails and in that case you've lost your database. I know it's not very
> likely that it really happens, but the risk is always there.
> If you use backup/restore, you'll keep your source online and working.
> Then if the backup file is corrupt in one way or the other, you can
> always go back and do a new backup.
> As Andrew mentions, you'll have to provide some more info on what it is
> that goes wrong with your restore - then it's easier to help you...;-).
> Regards
> Steen
>
about 5 years without touching it.
I have just installed SQL Server 2005 on a new cluster and it is all up and
running and ready to test our CRM application against it.
I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
I want to copy the data from our SQL Server 2000 instance to our SQL Server
2005 instance. I seem to be able to do this by detaching the database,
copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
server and re-attaching the original data to the SQL2000 copy which will
remain live until we finish testing compatibility. At which point we would
then copy the live data again from the SQL2000 instance to the SQL2005
instance and the use the SQL2005 version from there on.
THe problem is that when I detach, users can't get at the data. I've got
200 some internal users and lord knows how many external users accessing thi
s
data. It doesn't seem right that there is no way to do this without
detaching the database interrupting the users? Can't I use a
backup/restore? I've tried but can't seem to get the restore to work. Is
there compatibility difference between SQL2000 backup and SQL2005 backup? I
n
which case is there someway I an get a copy of all the data on the SQL2000
server to the SQL2005 Server without interrupting users on the SQL2000
server?
Sorry, might seem a basic question, but I've been out of the line of fire
for a long time on this sort of thing, so I appreciate your patience.
Thanks
BillA backup and restore should work just fine and be a totally on-line
operation for the 2000 db. When you say you can't get it to work what
exactly is happening? Is there an error message?
Andrew J. Kelly SQL MVP
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up
> and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach
> functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL
> Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test
> SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we
> would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing
> this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
> In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>|||billd wrote:
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up an
d
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality
.
> I want to copy the data from our SQL Server 2000 instance to our SQL Serve
r
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL200
5
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we woul
d
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing t
his
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
Hi Bill
A Backup/Restore shouldn't cause a problem, and is actually it's a more
"safe" procedure. When you detach your database, you haven't got
anything until you get it attached again. Not even your source is
available. This means that you have a potential risk that the attach
fails and in that case you've lost your database. I know it's not very
likely that it really happens, but the risk is always there.
If you use backup/restore, you'll keep your source online and working.
Then if the backup file is corrupt in one way or the other, you can
always go back and do a new backup.
As Andrew mentions, you'll have to provide some more info on what it is
that goes wrong with your restore - then it's easier to help you...;-).
Regards
Steen|||Backup and restore is the most obvious option...
However, if you have the SQL script to create your database on the SQL 2005
server, you could create it and then use DTS to copy across the data . This
gives you the opportunity to also review some of the data types used and
swap them for the SQL 2005 types alternatives (if preferred) in the SQL
script before creating the database.
Griff|||In addition to the other posts, you might want to read about sp_change_users
_login (and also search
KB for sp_help_revlogins).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up an
d
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality
.
> I want to copy the data from our SQL Server 2000 instance to our SQL Serve
r
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL200
5
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we woul
d
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing t
his
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>|||Thanks Steen... I feel the same way that you do about detach/attach! It too
k
a really long time to detach the northwind database and I was very nervous
the whole while that it might take down the SQL engine itself.. I hate when
things take a long time... so, anyway, the failure: My SQL2000 data is on
the F: drive, which is a clustered resource. I backup up to base.bak in the
temp dir. Then I copy this to my S: drive which is the clustered resource o
n
my SQL2005 server. I then try to restore this base.bak file (it only has on
e
backup in it) I think get the error message: (grrrr, why don't then let yo
u
cut and paste the text from error messages')
... and as I did this, I realized... just this very minute, that I hadn't
gone to the database page and checked the overwrite database box... was the
problem, so yes I have found the answer...
Thanks to all for your help! I really only needed to know that
backup/restore should work and then could figure it out myself I guess.
restore failed for server SQLDB. (Microsoft.SQLServer.SMO)
Additional Information
System.data.sqlclient.sqlerror cannot use file:
"Steen Persson (DK)" wrote:
> billd wrote:
> Hi Bill
> A Backup/Restore shouldn't cause a problem, and is actually it's a more
> "safe" procedure. When you detach your database, you haven't got
> anything until you get it attached again. Not even your source is
> available. This means that you have a potential risk that the attach
> fails and in that case you've lost your database. I know it's not very
> likely that it really happens, but the risk is always there.
> If you use backup/restore, you'll keep your source online and working.
> Then if the backup file is corrupt in one way or the other, you can
> always go back and do a new backup.
> As Andrew mentions, you'll have to provide some more info on what it is
> that goes wrong with your restore - then it's easier to help you...;-).
> Regards
> Steen
>
Sunday, February 19, 2012
Backup/restore Databases remotely.
Hi i've tried a few remote administration tools including Microsofts own Web Data Administrator for SQL server... but none of them seem to do what is essential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
Thanks
I don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>
|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/featu...0894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "
|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/featu...0894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "
|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself?
Thanks for your help.
|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE
|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
Thanks
I don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>
|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/featu...0894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "
|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/featu...0894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "
|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself?
Thanks for your help.
|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE
|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Backup/restore Databases remotely.
Hi i've tried a few remote administration tools including Microsofts own Web Data Administrator for SQL server... but none of them seem to do what is essential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
ThanksI don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/features/mssql/article.php/10894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/features/mssql/article.php/10894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "|||Hi Sue
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself? :
Thanks for your help.|||No matter found it myself on MSDN
BACKUP DATABASE DBNameGoesHer
TO DISK = 'C:\temp\Somefilename.bak
WITH INI
RESTORE DATABASE DBNameGoesHer
FROM DISK = 'C:\temp\Somefilename.bak
WITH REPLACE|||Books Online is available within the SQL Server program group (assuming =that you have SQL Server installed).
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
-- Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
> > BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
> > RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
ThanksI don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/features/mssql/article.php/10894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/features/mssql/article.php/10894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "|||Hi Sue
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself? :
Thanks for your help.|||No matter found it myself on MSDN
BACKUP DATABASE DBNameGoesHer
TO DISK = 'C:\temp\Somefilename.bak
WITH INI
RESTORE DATABASE DBNameGoesHer
FROM DISK = 'C:\temp\Somefilename.bak
WITH REPLACE|||Books Online is available within the SQL Server program group (assuming =that you have SQL Server installed).
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
-- Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
> > BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
> > RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Backup/restore Databases remotely.
Hi i've tried a few remote administration tools including Microsofts own Web
Data Administrator for SQL server... but none of them seem to do what is es
sential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remot
ely. I dont really want to have to use export/import data as things like tri
ggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But php would also
be fine.
ThanksI don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But php would also be fine.
>Thanks
>.
>|||SQL server itself can backup/restore yes. But the remote web admin tool cann
ot.. it can only import/export data which does not include things like trigg
ers.
See this page:
http://www.databasejournal.com/feat...10894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool can
not.. it can only import/export data which does not include things like trig
gers.
>See this page:
>http://www.databasejournal.com/feat...10894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration too
l
>- Can't create a database backup and restore a database "|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link w
here I can find this out myself?
Thanks for your help.|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techin.../2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Data Administrator for SQL server... but none of them seem to do what is es
sential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remot
ely. I dont really want to have to use export/import data as things like tri
ggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But php would also
be fine.
ThanksI don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But php would also be fine.
>Thanks
>.
>|||SQL server itself can backup/restore yes. But the remote web admin tool cann
ot.. it can only import/export data which does not include things like trigg
ers.
See this page:
http://www.databasejournal.com/feat...10894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool can
not.. it can only import/export data which does not include things like trig
gers.
>See this page:
>http://www.databasejournal.com/feat...10894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration too
l
>- Can't create a database backup and restore a database "|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link w
here I can find this out myself?
Thanks for your help.|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techin.../2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Friday, February 10, 2012
Backup to a share
Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
a session on both machines is "Administrator", "password". SQL server account
is the same (administrator).
I on the sql box, I create a share to the 2nd box. I can create, deleteor do
anything on this share. However, when I create a maintenance plan to backup
some DBs to this share, the job fails.
I tried UNC,it did not work neither.
How can or is it possible to backup to a share?
ThanksOf course this is possible. Start by giving permissions on that share to the
Windows account used by the SQL Server service, that is, that account must be
able to write files to that share. Connect as that account and make sure you
have access to the share.
If the job still does not work go to the View History section of the job and
look for error messages. Also take a look at the SQL Server error log.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"SalamElias" wrote:
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> a session on both machines is "Administrator", "password". SQL server account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> anything on this share. However, when I create a maintenance plan to backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||SQL Server itself (and SQL Server Agent) are not running as Administrator,
are they? When you map a drive letter, it is mapped for YOUR username only.
This drive letter is not visible to other users (e.g. the user that SQL
services run as).
When you tried a UNC path, what does "did not work" mean? Do you get an
error message? If so, what is it?
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
For SQL 2000, but it works just the same for SQL 2005 as far as required
permissions.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||As I said, both credetnials are the same and I can create, delete modify any
file in the share which are used to run both services SQL server and agnet
When editing the MP, in the backup task page, I click on the button with 3
dots opposite to folder, I see only the local disks C:\ and E:\ not the
mapped share Z:\backup.
Regards
"Ben Nevarez" wrote:
> Of course this is possible. Start by giving permissions on that share to the
> Windows account used by the SQL Server service, that is, that account must be
> able to write files to that share. Connect as that account and make sure you
> have access to the share.
> If the job still does not work go to the View History section of the job and
> look for error messages. Also take a look at the SQL Server error log.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "SalamElias" wrote:
> > Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> > a session on both machines is "Administrator", "password". SQL server account
> > is the same (administrator).
> > I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> > anything on this share. However, when I create a maintenance plan to backup
> > some DBs to this share, the job fails.
> > I tried UNC,it did not work neither.
> > How can or is it possible to backup to a share?
> >
> > Thanks
a session on both machines is "Administrator", "password". SQL server account
is the same (administrator).
I on the sql box, I create a share to the 2nd box. I can create, deleteor do
anything on this share. However, when I create a maintenance plan to backup
some DBs to this share, the job fails.
I tried UNC,it did not work neither.
How can or is it possible to backup to a share?
ThanksOf course this is possible. Start by giving permissions on that share to the
Windows account used by the SQL Server service, that is, that account must be
able to write files to that share. Connect as that account and make sure you
have access to the share.
If the job still does not work go to the View History section of the job and
look for error messages. Also take a look at the SQL Server error log.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"SalamElias" wrote:
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> a session on both machines is "Administrator", "password". SQL server account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> anything on this share. However, when I create a maintenance plan to backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||SQL Server itself (and SQL Server Agent) are not running as Administrator,
are they? When you map a drive letter, it is mapped for YOUR username only.
This drive letter is not visible to other users (e.g. the user that SQL
services run as).
When you tried a UNC path, what does "did not work" mean? Do you get an
error message? If so, what is it?
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
For SQL 2000, but it works just the same for SQL 2005 as far as required
permissions.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||As I said, both credetnials are the same and I can create, delete modify any
file in the share which are used to run both services SQL server and agnet
When editing the MP, in the backup task page, I click on the button with 3
dots opposite to folder, I see only the local disks C:\ and E:\ not the
mapped share Z:\backup.
Regards
"Ben Nevarez" wrote:
> Of course this is possible. Start by giving permissions on that share to the
> Windows account used by the SQL Server service, that is, that account must be
> able to write files to that share. Connect as that account and make sure you
> have access to the share.
> If the job still does not work go to the View History section of the job and
> look for error messages. Also take a look at the SQL Server error log.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "SalamElias" wrote:
> > Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> > a session on both machines is "Administrator", "password". SQL server account
> > is the same (administrator).
> > I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> > anything on this share. However, when I create a maintenance plan to backup
> > some DBs to this share, the job fails.
> > I tried UNC,it did not work neither.
> > How can or is it possible to backup to a share?
> >
> > Thanks
Backup to a share
Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
a session on both machines is "Administrator", "password". SQL server account
is the same (administrator).
I on the sql box, I create a share to the 2nd box. I can create, deleteor do
anything on this share. However, when I create a maintenance plan to backup
some DBs to this share, the job fails.
I tried UNC,it did not work neither.
How can or is it possible to backup to a share?
Thanks
Of course this is possible. Start by giving permissions on that share to the
Windows account used by the SQL Server service, that is, that account must be
able to write files to that share. Connect as that account and make sure you
have access to the share.
If the job still does not work go to the View History section of the job and
look for error messages. Also take a look at the SQL Server error log.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"SalamElias" wrote:
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> a session on both machines is "Administrator", "password". SQL server account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> anything on this share. However, when I create a maintenance plan to backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||SQL Server itself (and SQL Server Agent) are not running as Administrator,
are they? When you map a drive letter, it is mapped for YOUR username only.
This drive letter is not visible to other users (e.g. the user that SQL
services run as).
When you tried a UNC path, what does "did not work" mean? Do you get an
error message? If so, what is it?
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
For SQL 2000, but it works just the same for SQL 2005 as far as required
permissions.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||As I said, both credetnials are the same and I can create, delete modify any
file in the share which are used to run both services SQL server and agnet
When editing the MP, in the backup task page, I click on the button with 3
dots opposite to folder, I see only the local disks C:\ and E:\ not the
mapped share Z:\backup.
Regards
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Of course this is possible. Start by giving permissions on that share to the
> Windows account used by the SQL Server service, that is, that account must be
> able to write files to that share. Connect as that account and make sure you
> have access to the share.
> If the job still does not work go to the View History section of the job and
> look for error messages. Also take a look at the SQL Server error log.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "SalamElias" wrote:
a session on both machines is "Administrator", "password". SQL server account
is the same (administrator).
I on the sql box, I create a share to the 2nd box. I can create, deleteor do
anything on this share. However, when I create a maintenance plan to backup
some DBs to this share, the job fails.
I tried UNC,it did not work neither.
How can or is it possible to backup to a share?
Thanks
Of course this is possible. Start by giving permissions on that share to the
Windows account used by the SQL Server service, that is, that account must be
able to write files to that share. Connect as that account and make sure you
have access to the share.
If the job still does not work go to the View History section of the job and
look for error messages. Also take a look at the SQL Server error log.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"SalamElias" wrote:
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> a session on both machines is "Administrator", "password". SQL server account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> anything on this share. However, when I create a maintenance plan to backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||SQL Server itself (and SQL Server Agent) are not running as Administrator,
are they? When you map a drive letter, it is mapped for YOUR username only.
This drive letter is not visible to other users (e.g. the user that SQL
services run as).
When you tried a UNC path, what does "did not work" mean? Do you get an
error message? If so, what is it?
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
For SQL 2000, but it works just the same for SQL 2005 as far as required
permissions.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||As I said, both credetnials are the same and I can create, delete modify any
file in the share which are used to run both services SQL server and agnet
When editing the MP, in the backup task page, I click on the button with 3
dots opposite to folder, I see only the local disks C:\ and E:\ not the
mapped share Z:\backup.
Regards
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Of course this is possible. Start by giving permissions on that share to the
> Windows account used by the SQL Server service, that is, that account must be
> able to write files to that share. Connect as that account and make sure you
> have access to the share.
> If the job still does not work go to the View History section of the job and
> look for error messages. Also take a look at the SQL Server error log.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "SalamElias" wrote:
Subscribe to:
Posts (Atom)