backup/restore vs attach/detach

microsoft.public.sqlserver.server

14 Mar 2006 11:55 AM

billd

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

14 Mar 2006 12:21 PM

Andrew J. Kelly

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

Show quoteHide quote

"billd" <bi***@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
>

Are all your drivers up to date? click for free checkup

14 Mar 2006 12:44 PM

Steen Persson (DK)

billd wrote:
Show quoteHide quote

> 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

14 Mar 2006 3:10 PM

billd

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:

Show quoteHide quote

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

14 Mar 2006 1:13 PM

Griff

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

14 Mar 2006 1:43 PM

Tibor Karaszi

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/

Show quoteHide quote

"billd" <bi***@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
>