Can someone please clarify the difference between Detach / Attach of MDF/LDF
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
Showing posts with label attach. Show all posts
Showing posts with label attach. Show all posts
Sunday, March 25, 2012
Difference between Backup/Restore & Attach/Detach
Can someone please clarify the difference between Detach / Attach of MDF/LDF
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Can someone please clarify the difference between Detach / Attach of
>> MDF/LDF files versus the Backup / Restore of backup to device/file
>> feature? Which would be ideal for restores of actual databases?
>> Thank you.
>|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
>> recovery. Databases can be backed up while online. The backup file size
>> is usually smaller than the database files since only used pages are
>> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can
>> reduce potential data loss by performing transaction log backups.
>> Detaching a database removes the database from SQL Server while leaving
>> the physical database files intact. This allows you to rename or move
>> the physical files and then re-attach. Although one could perform cold
>> backups using this technique, detach/attach isn't really intended to be
>> used as a backup/recovery process.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "KP" <kp@.msn.com> wrote in message
>> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Can someone please clarify the difference between Detach / Attach of
>> MDF/LDF files versus the Backup / Restore of backup to device/file
>> feature? Which would be ideal for restores of actual databases?
>> Thank you.
>>
>
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Can someone please clarify the difference between Detach / Attach of
>> MDF/LDF files versus the Backup / Restore of backup to device/file
>> feature? Which would be ideal for restores of actual databases?
>> Thank you.
>|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
>> recovery. Databases can be backed up while online. The backup file size
>> is usually smaller than the database files since only used pages are
>> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can
>> reduce potential data loss by performing transaction log backups.
>> Detaching a database removes the database from SQL Server while leaving
>> the physical database files intact. This allows you to rename or move
>> the physical files and then re-attach. Although one could perform cold
>> backups using this technique, detach/attach isn't really intended to be
>> used as a backup/recovery process.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "KP" <kp@.msn.com> wrote in message
>> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Can someone please clarify the difference between Detach / Attach of
>> MDF/LDF files versus the Backup / Restore of backup to device/file
>> feature? Which would be ideal for restores of actual databases?
>> Thank you.
>>
>
Difference between Backup/Restore & Attach/Detach
Can someone please clarify the difference between Detach / Attach of MDF/LDF
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.
Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>
|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>
|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
sql
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.
Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>
|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>
|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
sql
Thursday, March 22, 2012
Difference between Attach / Detach and Backup / Restore
I am new to SQL Server and we are using SQL Server 2005.
We recently upgrade a finance system by consultant. He detaches the
database from the old server (Running SQL Server 2000), copy the mdf file
across and attached in the new SQL Server 2005.
I would like to know is there any benefit for detaching / attaching than
make a backup and restore on the SQL Server 2005 ?
Someone has mentioned that we have to recreate SQL Server Login though the
Database Login exists on the database copied across. Is there any tools /
reference that makes it easy ?
ThanksPeter
It is just faster.
Search on interenet for 'sp_help_revlogin' for SQL Server 2005
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>|||I have explained the step to move the logins as well here:
http://sqlguy.it-craft.dk
As well as links to the stored procedures for both 2000 and 2005
Regards
Rasmus Glibstrup
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>sql
We recently upgrade a finance system by consultant. He detaches the
database from the old server (Running SQL Server 2000), copy the mdf file
across and attached in the new SQL Server 2005.
I would like to know is there any benefit for detaching / attaching than
make a backup and restore on the SQL Server 2005 ?
Someone has mentioned that we have to recreate SQL Server Login though the
Database Login exists on the database copied across. Is there any tools /
reference that makes it easy ?
ThanksPeter
It is just faster.
Search on interenet for 'sp_help_revlogin' for SQL Server 2005
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>|||I have explained the step to move the logins as well here:
http://sqlguy.it-craft.dk
As well as links to the stored procedures for both 2000 and 2005
Regards
Rasmus Glibstrup
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>sql
Difference between Attach / Detach and Backup / Restore
I am new to SQL Server and we are using SQL Server 2005.
We recently upgrade a finance system by consultant. He detaches the
database from the old server (Running SQL Server 2000), copy the mdf file
across and attached in the new SQL Server 2005.
I would like to know is there any benefit for detaching / attaching than
make a backup and restore on the SQL Server 2005 ?
Someone has mentioned that we have to recreate SQL Server Login though the
Database Login exists on the database copied across. Is there any tools /
reference that makes it easy ?
ThanksPeter
It is just faster.
Search on interenet for 'sp_help_revlogin' for SQL Server 2005
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>|||I have explained the step to move the logins as well here:
http://sqlguy.it-craft.dk
As well as links to the stored procedures for both 2000 and 2005
Regards
Rasmus Glibstrup
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>
We recently upgrade a finance system by consultant. He detaches the
database from the old server (Running SQL Server 2000), copy the mdf file
across and attached in the new SQL Server 2005.
I would like to know is there any benefit for detaching / attaching than
make a backup and restore on the SQL Server 2005 ?
Someone has mentioned that we have to recreate SQL Server Login though the
Database Login exists on the database copied across. Is there any tools /
reference that makes it easy ?
ThanksPeter
It is just faster.
Search on interenet for 'sp_help_revlogin' for SQL Server 2005
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>|||I have explained the step to move the logins as well here:
http://sqlguy.it-craft.dk
As well as links to the stored procedures for both 2000 and 2005
Regards
Rasmus Glibstrup
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>
Wednesday, March 21, 2012
Diff between xxx_data.mdf and xxx.mdf files
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004I don't think there is any specific logic behind the file names really, it's
just what the naming convention was at the time the databases were created.
The actual database name is independent of the file name (and a database ca
n consist of multiple files anyway, so it wouldn't make much sense to name t
he files after the database).
--
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3
988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004I don't think there is any specific logic behind the file names really, it's
just what the naming convention was at the time the databases were created.
The actual database name is independent of the file name (and a database ca
n consist of multiple files anyway, so it wouldn't make much sense to name t
he files after the database).
--
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3
988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
Diff between xxx_data.mdf and xxx.mdf files
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
Diff between xxx_data.mdf and xxx.mdf files
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
There is no special significance to the suffixes _data and _log. They are
just the naming conventions used when the database was created. You can name
database files as anything you like, however the standard extensions for SQL
Server files are .MDF for data files and .LDF for log files.
MDB is the standard name for Access databases and isn't normally used for
SQL Server files.
(I've ignored your irrelevant cross-postings. Please don't cross-post. This
clearly has nothing to do with DTS, Clients or Connections so why post
there?)
David Portas
SQL Server MVP
|||I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
sql
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
There is no special significance to the suffixes _data and _log. They are
just the naming conventions used when the database was created. You can name
database files as anything you like, however the standard extensions for SQL
Server files are .MDF for data files and .LDF for log files.
MDB is the standard name for Access databases and isn't normally used for
SQL Server files.
(I've ignored your irrelevant cross-postings. Please don't cross-post. This
clearly has nothing to do with DTS, Clients or Connections so why post
there?)
David Portas
SQL Server MVP
|||I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
sql
Diff between xxx_data.mdf and xxx.mdf files
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
I don't think there is any specific logic behind the file names really, it's just what the naming convention was at the time the databases were created. The actual database name is independent of the file name (and a database can consist of multiple files anyway, so it wouldn't make much sense to name the files after the database).
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I created, attach and detach database manytimes. SOme time its shows the database file name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file name as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows database na as "xxx".
I just want to know difference these file name conventions, what was the logic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
Diff between xxx_data.mdf and xxx.mdf files
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004There is no special significance to the suffixes _data and _log. They are
just the naming conventions used when the database was created. You can name
database files as anything you like, however the standard extensions for SQL
Server files are .MDF for data files and .LDF for log files.
MDB is the standard name for Access databases and isn't normally used for
SQL Server files.
(I've ignored your irrelevant cross-postings. Please don't cross-post. This
clearly has nothing to do with DTS, Clients or Connections so why post
there?)
David Portas
SQL Server MVP
--|||I don't think there is any specific logic behind the file names really, it's
just what the naming convention was at the time the databases were created.
The actual database name is independent of the file name (and a database ca
n consist of multiple files anyway, so it wouldn't make much sense to name t
he files after the database).
--
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3
988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004There is no special significance to the suffixes _data and _log. They are
just the naming conventions used when the database was created. You can name
database files as anything you like, however the standard extensions for SQL
Server files are .MDF for data files and .LDF for log files.
MDB is the standard name for Access databases and isn't normally used for
SQL Server files.
(I've ignored your irrelevant cross-postings. Please don't cross-post. This
clearly has nothing to do with DTS, Clients or Connections so why post
there?)
David Portas
SQL Server MVP
--|||I don't think there is any specific logic behind the file names really, it's
just what the naming convention was at the time the databases were created.
The actual database name is independent of the file name (and a database ca
n consist of multiple files anyway, so it wouldn't make much sense to name t
he files after the database).
--
Jacco Schalkwijk
SQL Server MVP
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:e8c%23paoYEHA.3
988@.tk2msftngp13.phx.gbl...
What is difference between "xxx_data.mdf" and "xxxx.mdf"? IN Sql2k, I create
d, attach and detach database manytimes. SOme time its shows the database fi
le name as "xxx_data.mdb and xxx_log.mdb" and sometimes it only shows file n
ame as "xxx.mdf and xxx.ldf", why? However in Enterprise manager it shows da
tabase na as "xxx".
I just want to know difference these file name conventions, what was the log
ic behind this?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 07/04/2004
Friday, February 17, 2012
dettach SQL 7 and attach SQL 2000 ?
Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
server?
Gav
Yes, you can do it this way, it is going to be upgraded to 2000
automatically. But leave a copy in SQL 7.0 just in case!
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>
|||Hi,
Yes, you can. Detach the database from SQL 7 server, copy the MDF and LDF to
SQL 2000 server and
attach it. After attaching , To achieve optimal performance, update all
statistics on the upgraded database by running sp_updatestats
FYI,
The SP_ATTACH_DB will automatically upgrade the database structure from SQL
7 to sql 2000.
Look into the sample result of sp_attach_db. Here I attached a database
named GCC to SQL 2000. The source was database was SQL 7.0
Converting database 'gcc' from version 515 to the current version 539.
Database 'gcc' running the upgrade step from version 515 to version 524.
Database 'gcc' running the upgrade step from version 524 to version 525.
Database 'gcc' running the upgrade step from version 525 to version 526.
Database 'gcc' running the upgrade step from version 526 to version 527.
Database 'gcc' running the upgrade step from version 527 to version 528.
Database 'gcc' running the upgrade step from version 528 to version 529.
Database 'gcc' running the upgrade step from version 529 to version 530.
Database 'gcc' running the upgrade step from version 530 to version 531.
Database 'gcc' running the upgrade step from version 531 to version 532.
Database 'gcc' running the upgrade step from version 532 to version 533.
Database 'gcc' running the upgrade step from version 533 to version 534.
Database 'gcc' running the upgrade step from version 534 to version 535.
Database 'gcc' running the upgrade step from version 535 to version 536.
Database 'gcc' running the upgrade step from version 536 to version 537.
Database 'gcc' running the upgrade step from version 537 to version 538.
Database 'gcc' running the upgrade step from version 538 to version 539.
To achieve optimal performance, update all statistics on the 'gcc' database
by running sp_updatestats.
Thanks
Hari
MCDBA
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>
|||Yes, with sp_detach_db and sp_attach_db. The database will be upgraded
automatically when you attach it. Make sure you have a backup first though.
Remember you may need to re-create your logins on the SQL2000 server.
Also, refer to:
http://support.microsoft.com/default...b;en-us;314546
David Portas
SQL Server MVP
|||Thanks for all your input. I've tried it on my test machine and it worked
spot on. Just didn't want to try it and find I had to spend hours restoring
the 220GB database.
Gav
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>
server?
Gav
Yes, you can do it this way, it is going to be upgraded to 2000
automatically. But leave a copy in SQL 7.0 just in case!
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>
|||Hi,
Yes, you can. Detach the database from SQL 7 server, copy the MDF and LDF to
SQL 2000 server and
attach it. After attaching , To achieve optimal performance, update all
statistics on the upgraded database by running sp_updatestats
FYI,
The SP_ATTACH_DB will automatically upgrade the database structure from SQL
7 to sql 2000.
Look into the sample result of sp_attach_db. Here I attached a database
named GCC to SQL 2000. The source was database was SQL 7.0
Converting database 'gcc' from version 515 to the current version 539.
Database 'gcc' running the upgrade step from version 515 to version 524.
Database 'gcc' running the upgrade step from version 524 to version 525.
Database 'gcc' running the upgrade step from version 525 to version 526.
Database 'gcc' running the upgrade step from version 526 to version 527.
Database 'gcc' running the upgrade step from version 527 to version 528.
Database 'gcc' running the upgrade step from version 528 to version 529.
Database 'gcc' running the upgrade step from version 529 to version 530.
Database 'gcc' running the upgrade step from version 530 to version 531.
Database 'gcc' running the upgrade step from version 531 to version 532.
Database 'gcc' running the upgrade step from version 532 to version 533.
Database 'gcc' running the upgrade step from version 533 to version 534.
Database 'gcc' running the upgrade step from version 534 to version 535.
Database 'gcc' running the upgrade step from version 535 to version 536.
Database 'gcc' running the upgrade step from version 536 to version 537.
Database 'gcc' running the upgrade step from version 537 to version 538.
Database 'gcc' running the upgrade step from version 538 to version 539.
To achieve optimal performance, update all statistics on the 'gcc' database
by running sp_updatestats.
Thanks
Hari
MCDBA
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>
|||Yes, with sp_detach_db and sp_attach_db. The database will be upgraded
automatically when you attach it. Make sure you have a backup first though.
Remember you may need to re-create your logins on the SQL2000 server.
Also, refer to:
http://support.microsoft.com/default...b;en-us;314546
David Portas
SQL Server MVP
|||Thanks for all your input. I've tried it on my test machine and it worked
spot on. Just didn't want to try it and find I had to spend hours restoring
the 220GB database.
Gav
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>
dettach SQL 7 and attach SQL 2000 ?
Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
server?
GavYes, you can do it this way, it is going to be upgraded to 2000
automatically. But leave a copy in SQL 7.0 just in case!
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>|||Hi,
Yes, you can. Detach the database from SQL 7 server, copy the MDF and LDF to
SQL 2000 server and
attach it. After attaching , To achieve optimal performance, update all
statistics on the upgraded database by running sp_updatestats
FYI,
The SP_ATTACH_DB will automatically upgrade the database structure from SQL
7 to sql 2000.
Look into the sample result of sp_attach_db. Here I attached a database
named GCC to SQL 2000. The source was database was SQL 7.0
Converting database 'gcc' from version 515 to the current version 539.
Database 'gcc' running the upgrade step from version 515 to version 524.
Database 'gcc' running the upgrade step from version 524 to version 525.
Database 'gcc' running the upgrade step from version 525 to version 526.
Database 'gcc' running the upgrade step from version 526 to version 527.
Database 'gcc' running the upgrade step from version 527 to version 528.
Database 'gcc' running the upgrade step from version 528 to version 529.
Database 'gcc' running the upgrade step from version 529 to version 530.
Database 'gcc' running the upgrade step from version 530 to version 531.
Database 'gcc' running the upgrade step from version 531 to version 532.
Database 'gcc' running the upgrade step from version 532 to version 533.
Database 'gcc' running the upgrade step from version 533 to version 534.
Database 'gcc' running the upgrade step from version 534 to version 535.
Database 'gcc' running the upgrade step from version 535 to version 536.
Database 'gcc' running the upgrade step from version 536 to version 537.
Database 'gcc' running the upgrade step from version 537 to version 538.
Database 'gcc' running the upgrade step from version 538 to version 539.
To achieve optimal performance, update all statistics on the 'gcc' database
by running sp_updatestats.
Thanks
Hari
MCDBA
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>|||Yes, with sp_detach_db and sp_attach_db. The database will be upgraded
automatically when you attach it. Make sure you have a backup first though.
Remember you may need to re-create your logins on the SQL2000 server.
Also, refer to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
--
David Portas
SQL Server MVP
--|||Thanks for all your input. I've tried it on my test machine and it worked
spot on. Just didn't want to try it and find I had to spend hours restoring
the 220GB database.
Gav
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>
server?
GavYes, you can do it this way, it is going to be upgraded to 2000
automatically. But leave a copy in SQL 7.0 just in case!
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>|||Hi,
Yes, you can. Detach the database from SQL 7 server, copy the MDF and LDF to
SQL 2000 server and
attach it. After attaching , To achieve optimal performance, update all
statistics on the upgraded database by running sp_updatestats
FYI,
The SP_ATTACH_DB will automatically upgrade the database structure from SQL
7 to sql 2000.
Look into the sample result of sp_attach_db. Here I attached a database
named GCC to SQL 2000. The source was database was SQL 7.0
Converting database 'gcc' from version 515 to the current version 539.
Database 'gcc' running the upgrade step from version 515 to version 524.
Database 'gcc' running the upgrade step from version 524 to version 525.
Database 'gcc' running the upgrade step from version 525 to version 526.
Database 'gcc' running the upgrade step from version 526 to version 527.
Database 'gcc' running the upgrade step from version 527 to version 528.
Database 'gcc' running the upgrade step from version 528 to version 529.
Database 'gcc' running the upgrade step from version 529 to version 530.
Database 'gcc' running the upgrade step from version 530 to version 531.
Database 'gcc' running the upgrade step from version 531 to version 532.
Database 'gcc' running the upgrade step from version 532 to version 533.
Database 'gcc' running the upgrade step from version 533 to version 534.
Database 'gcc' running the upgrade step from version 534 to version 535.
Database 'gcc' running the upgrade step from version 535 to version 536.
Database 'gcc' running the upgrade step from version 536 to version 537.
Database 'gcc' running the upgrade step from version 537 to version 538.
Database 'gcc' running the upgrade step from version 538 to version 539.
To achieve optimal performance, update all statistics on the 'gcc' database
by running sp_updatestats.
Thanks
Hari
MCDBA
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>|||Yes, with sp_detach_db and sp_attach_db. The database will be upgraded
automatically when you attach it. Make sure you have a backup first though.
Remember you may need to re-create your logins on the SQL2000 server.
Also, refer to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
--
David Portas
SQL Server MVP
--|||Thanks for all your input. I've tried it on my test machine and it worked
spot on. Just didn't want to try it and find I had to spend hours restoring
the 220GB database.
Gav
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>
dettach SQL 7 and attach SQL 2000 ?
Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
server?
GavYes, you can do it this way, it is going to be upgraded to 2000
automatically. But leave a copy in SQL 7.0 just in case!
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>|||Hi,
Yes, you can. Detach the database from SQL 7 server, copy the MDF and LDF to
SQL 2000 server and
attach it. After attaching , To achieve optimal performance, update all
statistics on the upgraded database by running sp_updatestats
FYI,
The SP_ATTACH_DB will automatically upgrade the database structure from SQL
7 to sql 2000.
Look into the sample result of sp_attach_db. Here I attached a database
named GCC to SQL 2000. The source was database was SQL 7.0
Converting database 'gcc' from version 515 to the current version 539.
Database 'gcc' running the upgrade step from version 515 to version 524.
Database 'gcc' running the upgrade step from version 524 to version 525.
Database 'gcc' running the upgrade step from version 525 to version 526.
Database 'gcc' running the upgrade step from version 526 to version 527.
Database 'gcc' running the upgrade step from version 527 to version 528.
Database 'gcc' running the upgrade step from version 528 to version 529.
Database 'gcc' running the upgrade step from version 529 to version 530.
Database 'gcc' running the upgrade step from version 530 to version 531.
Database 'gcc' running the upgrade step from version 531 to version 532.
Database 'gcc' running the upgrade step from version 532 to version 533.
Database 'gcc' running the upgrade step from version 533 to version 534.
Database 'gcc' running the upgrade step from version 534 to version 535.
Database 'gcc' running the upgrade step from version 535 to version 536.
Database 'gcc' running the upgrade step from version 536 to version 537.
Database 'gcc' running the upgrade step from version 537 to version 538.
Database 'gcc' running the upgrade step from version 538 to version 539.
To achieve optimal performance, update all statistics on the 'gcc' database
by running sp_updatestats.
Thanks
Hari
MCDBA
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>|||Yes, with sp_detach_db and sp_attach_db. The database will be upgraded
automatically when you attach it. Make sure you have a backup first though.
Remember you may need to re-create your logins on the SQL2000 server.
Also, refer to:
http://support.microsoft.com/defaul...kb;en-us;314546
David Portas
SQL Server MVP
--|||Thanks for all your input. I've tried it on my test machine and it worked
spot on. Just didn't want to try it and find I had to spend hours restoring
the 220GB database.
Gav
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>
server?
GavYes, you can do it this way, it is going to be upgraded to 2000
automatically. But leave a copy in SQL 7.0 just in case!
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>|||Hi,
Yes, you can. Detach the database from SQL 7 server, copy the MDF and LDF to
SQL 2000 server and
attach it. After attaching , To achieve optimal performance, update all
statistics on the upgraded database by running sp_updatestats
FYI,
The SP_ATTACH_DB will automatically upgrade the database structure from SQL
7 to sql 2000.
Look into the sample result of sp_attach_db. Here I attached a database
named GCC to SQL 2000. The source was database was SQL 7.0
Converting database 'gcc' from version 515 to the current version 539.
Database 'gcc' running the upgrade step from version 515 to version 524.
Database 'gcc' running the upgrade step from version 524 to version 525.
Database 'gcc' running the upgrade step from version 525 to version 526.
Database 'gcc' running the upgrade step from version 526 to version 527.
Database 'gcc' running the upgrade step from version 527 to version 528.
Database 'gcc' running the upgrade step from version 528 to version 529.
Database 'gcc' running the upgrade step from version 529 to version 530.
Database 'gcc' running the upgrade step from version 530 to version 531.
Database 'gcc' running the upgrade step from version 531 to version 532.
Database 'gcc' running the upgrade step from version 532 to version 533.
Database 'gcc' running the upgrade step from version 533 to version 534.
Database 'gcc' running the upgrade step from version 534 to version 535.
Database 'gcc' running the upgrade step from version 535 to version 536.
Database 'gcc' running the upgrade step from version 536 to version 537.
Database 'gcc' running the upgrade step from version 537 to version 538.
Database 'gcc' running the upgrade step from version 538 to version 539.
To achieve optimal performance, update all statistics on the 'gcc' database
by running sp_updatestats.
Thanks
Hari
MCDBA
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>|||Yes, with sp_detach_db and sp_attach_db. The database will be upgraded
automatically when you attach it. Make sure you have a backup first though.
Remember you may need to re-create your logins on the SQL2000 server.
Also, refer to:
http://support.microsoft.com/defaul...kb;en-us;314546
David Portas
SQL Server MVP
--|||Thanks for all your input. I've tried it on my test machine and it worked
spot on. Just didn't want to try it and find I had to spend hours restoring
the 220GB database.
Gav
"Gav" <gavin.metcalfe@.nospam.btinternet.com> wrote in message
news:cbgo1r$agk$1@.hercules.btinternet.com...
> Can I dettach a database from a SQL 7 server and attach it to a SQL 2000
> server?
> Gav
>
Subscribe to:
Posts (Atom)