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 mdf. Show all posts
Showing posts with label mdf. 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
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
Wednesday, March 7, 2012
Device activation error
Hi All,
Currently I have a PC that has MSDE installed on it and is attached to database (MyData.MDF and a log file MYLog.LDF) located on its hard drive at c:\data\.When I detach from the database, place a copy of the two files noted above on my networkdrive @. u:\data and try to attach I get the following error’s:
SQL[1] exec error = -1: Changed database context to 'master'.
“Device activation error. The physical file name u:\data\MyData.MDF may be incorrect.”
I have done some testing a have found that I can attach to a copy of my database if I move it anywhere on the c: drive, and or even to a 1Gb USB key attached to the system(e:\).So far it seems to only be an issue if I move it to a mapped network drive.If anyone could please provide me with any info it would be greatly appreciated.
Thanks.
James D.
First, your data must be stored on an approved network server/storage you want to be supported. Second, you will have to use a special trace flag to force sqlserver to mount a networked db file. See the following article:
http://support.microsoft.com/kb/304261|||
It's because your U: drive isn't a local drive. By default, it's not support to place data files on mapped shares or UNC paths. You can find more information in the following article:
Description of support for network database files in SQL Server
http://support.microsoft.com/?id=304261
-Sue
|||Excellent, thank you for the response.
After I use the trace flag mentioned and get my MS SQL Server Desktop Engine to mount to the networked db files, will it be possible to have a second PC with MS SQL Server Desktop Engine also installed on it to connect to the same networked DB files at the same time as the first unit, or will it be limited to just one PC at a time due to file locks? We require that at least two units(possibly up to 3 more in the future) can all share, write, and read to same networked db files at the same time. If you could please let me know when you have a moment it would be great.
Thanks.
James D.
|||Sqlserver is a shared nothing technology. I.e. only one instance can access the data at any time. So, no, you cannot have another server hits that network database file(s). As it stands, unless your hardware is part of the qualified HCL, you're running in an unsupported platform.
Btw, sqlserver has a feature called Scalable Shared Database (SSD) which allows multiple instances to share the same database file. However, this requires a SAN.
Subscribe to:
Posts (Atom)