bad page ID

How i can repair database ms-sql when attach file i have:
error 823 I/O error (bad page ID) detected during read at offset 0x itd...
Robert LisHi

If you are trying to attach a DB, and during the attach process you get this
error, you can't do much but to revert to a backup as you can't attach a DB
that SQL Server finds to be suspect.

On the other hand, if the DB is online, then you can run DBCC CHECKDB.

--
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Robert-SoftMAR" <robert@.softmar.com.pl> wrote in message
news:d6qu0d$ude$1@.news.lublin.pl...
> How i can repair database ms-sql when attach file i have:
> error 823 I/O error (bad page ID) detected during read at offset 0x itd...
> Robert Lis|||Robert-SoftMAR (robert@.softmar.com.pl) writes:
> How i can repair database ms-sql when attach file i have:
> error 823 I/O error (bad page ID) detected during read at offset 0x itd...

Books Online says this about eror 823:

Check the accessibility and condition of the device in question.

Run hardware diagnostics and correct problems, if possible.

Restore damaged files from the latest database backup. Restoring from a
database backup should always be considered the primary means of fixing
a damaged database.

If you don't have a backup or if the errors detected are very isolated,
the repair functionality of DBCC CHECKDB may be useful. However, using
DBCC CHECKDB can be more time consuming than restoring the damaged
files from a backup, and you may not be able to recover all your data.

Caution If running DBCC CHECKDB with one of the repair clauses does
not correct the problem or if you are unsure how this process may
affect your data, contact your primary support provider.

The way to find this information is simply to search for 823.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks
If I tray DBCC CHECKDB, I have error:
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'gg_system'. No entry
found with that name. Make sure that the name is entered correctly.
This DB is not in list sysdatabases and I can't add this DB. And I don't
have any backup of DB. Maybe another repair software?
Robert Lis

Uytkownik "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> napisa w
wiadomoci news:4290ff5e$1_3@.news.bluewin.ch...
> Hi
> If you are trying to attach a DB, and during the attach process you get
> this error, you can't do much but to revert to a backup as you can't
> attach a DB that SQL Server finds to be suspect.
> On the other hand, if the DB is online, then you can run DBCC CHECKDB.
> --
> ----------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Robert-SoftMAR" <robert@.softmar.com.pl> wrote in message
> news:d6qu0d$ude$1@.news.lublin.pl...
>> How i can repair database ms-sql when attach file i have:
>> error 823 I/O error (bad page ID) detected during read at offset 0x
>> itd...
>> Robert Lis
>>|||"Robert-SoftMAR" <robert@.softmar.com.pl> wrote in message
news:d6r1oa$1vb$1@.news.lublin.pl...
> Thanks
> If I tray DBCC CHECKDB, I have error:
> Server: Msg 911, Level 16, State 1, Line 1
> Could not locate entry in sysdatabases for database 'gg_system'. No entry
> found with that name. Make sure that the name is entered correctly.
> This DB is not in list sysdatabases and I can't add this DB. And I don't
> have any backup of DB. Maybe another repair software?

Sounds like you're pretty much up a creek w/o a paddle then.

I don't know of any tools that can read/repair off-line files for SQL
Server.|||Yes, I now this massage in Books Online but i can't do:
DBCC CHECKDB
beacouse i have error:
Could not locate entry in sysdatabases for database 'gg_system'. No entry
found with that name. Make sure that the name is entered correctly.
Robert Lis

Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.se> napisal w wiadomosci
news:Xns965F261413F3Yazorman@.127.0.0.1...
> Robert-SoftMAR (robert@.softmar.com.pl) writes:
>> How i can repair database ms-sql when attach file i have:
>> error 823 I/O error (bad page ID) detected during read at offset 0x
>> itd...
> Books Online says this about eror 823:
> Check the accessibility and condition of the device in question.
> Run hardware diagnostics and correct problems, if possible.
> Restore damaged files from the latest database backup. Restoring from a
> database backup should always be considered the primary means of fixing
> a damaged database.
> If you don't have a backup or if the errors detected are very isolated,
> the repair functionality of DBCC CHECKDB may be useful. However, using
> DBCC CHECKDB can be more time consuming than restoring the damaged
> files from a backup, and you may not be able to recover all your data.
>
> Caution If running DBCC CHECKDB with one of the repair clauses does
> not correct the problem or if you are unsure how this process may
> affect your data, contact your primary support provider.
>
> The way to find this information is simply to search for 823.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Robert-SoftMAR (robert@.softmar.com.pl) writes:
> Yes, I now this massage in Books Online but i can't do:
> DBCC CHECKDB
> beacouse i have error:
> Could not locate entry in sysdatabases for database 'gg_system'. No entry
> found with that name. Make sure that the name is entered correctly.
> Robert Lis

It appears that that .mdf files is a useless pile of bits. If that database
is important to you, by all means open a case with Microsoft. This is
nothing that can be sorted out over a newsgroup.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Backup/Restore vs Detach/Attach 2000 Databases to 2005

I'm trying to see the differences between Backup/Restore and Attach/Detach. I
backup and detach a database from SQL Server 2000 SP3 and then attach and
restore it to SQL Server 2005 SP1.
The differences I noticed are:
1. The restored database has a much larger initial size (database size is
same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is
showing owned by the database owner of the database restored over but
syslogins and sysusers do not match.
I don't understand why #1 happens.
Are there any other differences between Backup/Restore and Attach/Detach?
Thanks,
PeterMy best guess on #1 would be that the database size was increased because
the database was upgraded to 2005 and so the file expanded to accomidate the
necessary operations. Or because additional objects were added (system sp's,
tables or views) which are part of the 2005 functionality.
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
> I'm trying to see the differences between Backup/Restore and
> Attach/Detach. I
> backup and detach a database from SQL Server 2000 SP3 and then attach and
> restore it to SQL Server 2005 SP1.
> The differences I noticed are:
> 1. The restored database has a much larger initial size (database size is
> same) for data and log.
> 2. The attached database has a last backup date
> 3. If the backup is restored over a database, the restored database is
> showing owned by the database owner of the database restored over but
> syslogins and sysusers do not match.
> I don't understand why #1 happens.
> Are there any other differences between Backup/Restore and Attach/Detach?
>
> Thanks,
> Peter
>|||Hi Mark,
If your guess is correct, should it happen to both cases: restored database
and attached database not just restored database.
Peter
"Mark Miller" wrote:
> My best guess on #1 would be that the database size was increased because
> the database was upgraded to 2005 and so the file expanded to accomidate the
> necessary operations. Or because additional objects were added (system sp's,
> tables or views) which are part of the 2005 functionality.
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
> > I'm trying to see the differences between Backup/Restore and
> > Attach/Detach. I
> > backup and detach a database from SQL Server 2000 SP3 and then attach and
> > restore it to SQL Server 2005 SP1.
> >
> > The differences I noticed are:
> >
> > 1. The restored database has a much larger initial size (database size is
> > same) for data and log.
> > 2. The attached database has a last backup date
> > 3. If the backup is restored over a database, the restored database is
> > showing owned by the database owner of the database restored over but
> > syslogins and sysusers do not match.
> >
> > I don't understand why #1 happens.
> >
> > Are there any other differences between Backup/Restore and Attach/Detach?
> >
> >
> >
> > Thanks,
> >
> > Peter
> >
>
>

Backup/Restore vs Detach/Attach 2000 Databases to 2005

I'm trying to see the differences between Backup/Restore and Attach/Detach.
I
backup and detach a database from SQL Server 2000 SP3 and then attach and
restore it to SQL Server 2005 SP1.
The differences I noticed are:
1. The restored database has a much larger initial size (database size is
same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is
showing owned by the database owner of the database restored over but
syslogins and sysusers do not match.
I don't understand why #1 happens.
Are there any other differences between Backup/Restore and Attach/Detach?
Thanks,
PeterMy best guess on #1 would be that the database size was increased because
the database was upgraded to 2005 and so the file expanded to accomidate the
necessary operations. Or because additional objects were added (system sp's,
tables or views) which are part of the 2005 functionality.
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
> I'm trying to see the differences between Backup/Restore and
> Attach/Detach. I
> backup and detach a database from SQL Server 2000 SP3 and then attach and
> restore it to SQL Server 2005 SP1.
> The differences I noticed are:
> 1. The restored database has a much larger initial size (database size is
> same) for data and log.
> 2. The attached database has a last backup date
> 3. If the backup is restored over a database, the restored database is
> showing owned by the database owner of the database restored over but
> syslogins and sysusers do not match.
> I don't understand why #1 happens.
> Are there any other differences between Backup/Restore and Attach/Detach?
>
> Thanks,
> Peter
>|||Hi Mark,
If your guess is correct, should it happen to both cases: restored database
and attached database not just restored database.
Peter
"Mark Miller" wrote:

> My best guess on #1 would be that the database size was increased because
> the database was upgraded to 2005 and so the file expanded to accomidate t
he
> necessary operations. Or because additional objects were added (system sp'
s,
> tables or views) which are part of the 2005 functionality.
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
>
>

Backup/Restore vs Attach/Detach 2000 Database to 2005

I'm trying to see the differences between Backup/Restore and Attach/Detach. I backup and detach a database from SQL Server 2000 SP3 and then attach and restore it to SQL Server 2005 SP1.

The differences I noticed are:

1. The restored database has a much larger initial size (database size is same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is showing owned by the database owner of the database restored over but syslogins and sysusers do not match.

I don't understand why #1 happens.

Are there any other differences between Backup/Restore and Attach/Detach?

Thanks,

Peter

I guess I don't understand what your #1 means. You seem to be saying that (1) the database is much larger if you restore it vs. an attach but (2) that the database size is the same for both. That seems contradictory...

As for other differences, the primary differences for most people relate to speed. If I'm just moving the log file from the F:\ drive to the E:\ drive, a backup/restore operation for a large database would take much, much longer than an detach/attach. I don't mean to be too simplistic as it sounds, from your testing, that you're very comfortable with SQL Server. Outside of that, there's not going to be a difference between RECOVERY, the orphaned users (#3 above), or anything else that I'm aware of. There's probably some niggling things here and there that are minor differences but nothing major that I know of.

/****************************************************

Check out our Sql Server 2005 and 2000 tutorials

****************************************************/

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
>

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
>

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
>

Backup/Restore Versus Detach/Attach

Hi All,
when I've to deploy my applications (c# and MSDE), generally I make a backup
on th edevelopment machine of the SQL Database the on the deployment machine
I make a restore and restoring from the backup I get my database up and
running (so boiring that I've to change the data/log file path at any
restore...).
Are there any real differences if instead of backup/restore I detach the
database file from the development machine (running sql developer edition)
then copy the file on the deployment machine and then make an attach to that
file?
which one is the best way?
Thanks, Davide.
Davide Piras
Naxo Software
hi Davide,
Davide Piras wrote:
> Hi All,
> when I've to deploy my applications (c# and MSDE), generally I make a
> backup on th edevelopment machine of the SQL Database the on the
> deployment machine I make a restore and restoring from the backup I
> get my database up and running (so boiring that I've to change the
> data/log file path at any restore...).
> Are there any real differences if instead of backup/restore I detach
> the database file from the development machine (running sql developer
> edition) then copy the file on the deployment machine and then make
> an attach to that file?
> which one is the best way?
please remember to add the log file to your distribution too, as it's part
of the dabase files set... and reattaching without the log file can end in
attaching problems...
the restore scenario only requires a little more physical space as you have
to consider the backup size in addition to the database files size...
both scenarios are simple to run but you have to consider some potential
issues..
MSDE usually set the "autoclose" database property, where the full blown SQL
Server editions do not, so you'll end up with a database that does not
reflect this standard MSDE setting, but it could be a choice...
you do not inherit database settings/options/objects from the end user model
database but from yours...
this can bring up some problems regarding orphaned users you will have to
fix by use of the sp_change_users_login system stored procedure
(http://msdn.microsoft.com/library/de...ca-cz_8qzy.asp
, http://www.sqlservercentral.com/colu...okenlogins.asp )
personally I go this way, http://tinyurl.com/dyjjg , that will even help in
structure upgrade to, and can grant a database deployment based on version
control check ... and the best article I found for this kind of situation is
http://msdn.microsoft.com/msdnmag/is...baseinstaller/
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Ciao Andrea,
thanks for you help, I will read those articles BEFORE the next deployment
loop...
you know I use your DbaManager 0.12.0 as a Client User Interface?! :-)
Nice and useful Tool, thanks!
Davide.