DB Backup on a compressed drive

Is this a good idea to use compressed drive (Windows NTFS compression) for DB
backup files?
What is the common practice and what are the risks?
--
Thanks,
VladVlad,
See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended."
HTH
Jerry
"Vlad" <Vlad@.discussions.microsoft.com> wrote in message
news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
> Is this a good idea to use compressed drive (Windows NTFS compression) for
> DB
> backup files?
> What is the common practice and what are the risks?
> --
> Thanks,
> Vlad|||That article applies to the database files themselves, not the backups.
Personally, I prefer not to use compressed drives for backup. I prefer
using a commercial SQL backup compression tool. This decreases the time to
get the data out of SQL, thus minimizing the impact on the host system.
Since I also prefer backing up directly to a network file share, this has
the additional benefit of reducing the network traffic.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Vlad,
> See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
> "Performing SQL Server database backups to disk files on compressed
> volumes is also not recommended."
> HTH
> Jerry
> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>> Is this a good idea to use compressed drive (Windows NTFS compression)
>> for DB
>> backup files?
>> What is the common practice and what are the risks?
>> --
>> Thanks,
>> Vlad
>|||Geoff,
From the article:
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended. The failure modes are still under investigation,
but some evidence indicates that this backup is less reliable than backing
up to regular non-compressed volumes. If backups to compressed volumes are
performed, a complete DBCC should be run before each backup and after each
restore. The time required to run the DBCC should be factored into the
decision to use this backup technique. "
Jerry
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OCyqJnWuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> That article applies to the database files themselves, not the backups.
> Personally, I prefer not to use compressed drives for backup. I prefer
> using a commercial SQL backup compression tool. This decreases the time
> to get the data out of SQL, thus minimizing the impact on the host system.
> Since I also prefer backing up directly to a network file share, this has
> the additional benefit of reducing the network traffic.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
>> Vlad,
>> See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
>> "Performing SQL Server database backups to disk files on compressed
>> volumes is also not recommended."
>> HTH
>> Jerry
>> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
>> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>> Is this a good idea to use compressed drive (Windows NTFS compression)
>> for DB
>> backup files?
>> What is the common practice and what are the risks?
>> --
>> Thanks,
>> Vlad
>>
>

DB Backup from 1 PC & Restore to diff. PC

Friends,

I have taken backup of a database, of which Data (MDF) & Log (LDF) files are located on "E:\...." directory.

Now, I am using the same backup file to restore on another PC which is having no partitions at all. I mean now I have to restore the database using same backup file, of which Data & Log files should be located on "C:\...." directory only, as it has no partitions.

This restore process gives me error :

Restore failed for Server 'HSVM\MICROFIT'.
Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\MicroFit\HealthStar v6.0 Server Edition\Data\MICROFIT_HealthStar_Data.mdf'.

What is to be done, in order to restore the database from the same back up file successfully, regardless whether the original physical location of the MDF & LDF file, is existing on current PC ?

Please try to solve the same for me.... W8ing...

Whe u move database from one machine to another , u can either do it by Detach/attach or backup /resotre method. U mentioned that u have backup of MDF and LDF. How have u taken this backup. Did u detached the database first and then copied or u stoped the services and then copied. Please let us know the process u carried out to take backup of MDF and LDF

If u have taken backup of database using Backup Database statement, then u must restore the database from this backup using With MOVE Option . Read about Restore Database with MOVE Option in BOL

From BOL

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

Madhu

|||
I have taken Backup from PC(1) without stopping any services, from SQL Management Studio 2005 directly, using UI.
Database files were on "E:\...." directory.

Copied that "xyz.bak" file into other PC(2), which is having only "C:\..." Drive.|||

Now u follow these steps

(a) RESTORE FILELISTONLY FROM DISK = 'c:\xyz.bak' :- This command will give u the logical file names in the backup set

(b) RESTORE DATABASE YourNewDatabaseName
FROM DISK = 'c:\xyz.bak'
WITH MOVE 'LogicaldataFilename got from Step (a) ' TO 'c:\yourfolder\YourNewDatabaseName.mdf',
MOVE 'LogicalLogfile name got from Step(a)' TO 'c:\yourfolder\YourNewDatabaseName.ldf'

Above statement will restore the database to new location.

Madhu

|||
Practically I just tried with your suggession.... and ofcourse it worked. Thank you for that.

Are you aware of VB.NET ? and if yes, then MicroSoft.SqlServer.Management.Smo Object Model ?

Bcoz I have taken backup file manually, but restoring from .NET implementation.

If you aware of same, then plz guide me to use Restore Class of SMO Object Model.|||

check this

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx

Madhu

|||
still i am not satisfied.

while restoring DB it is giving me the error :

Exception >> Restore failed for Server 'HSVM\MICROFIT'.
Inner Exception >> Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\MicroFit\HealthStar v6.0 Server Edition\Data\MICROFIT_HealthStar_Data.mdf'.

where HSVM is computer name & MICROFIT is instance on SQL server.

DB attaches as read only

Hello all,
I detached one of our DB's from one of our servers, copied the data & log fi
les to another server, and then attached it there. It
attaches as Read Only.
When I go into the DB's properties and try to change the Read-only option, I
get an error message:
Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 5105: Device activation error. The physical file name '(Data file's d
rive, path, & file name)' may be incorrect.
Device activation error. The physical file name '(Log file's drive, path, &
file name)' may be incorrect.
Could not restart database '(DB's name)'. Reverting back to old status.
ALTER DATABASE statement failed.
Device activation error. The physical file name '(Log file's drive, path, &
file name)' may be incorrect.
sp_dboption command failed.
What am I doing wrong? How do I successfully detach from one & reattach to
another?
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> I detached one of our DB's from one of our servers, copied the data & log
files to another server, and then attached it there. It
> attaches as Read Only.
> When I go into the DB's properties and try to change the Read-only option,
I get an error message:
> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
> Error 5105: Device activation error. The physical file name '(Data file's
drive, path, & file name)' may be incorrect.
> Device activation error. The physical file name '(Log file's drive, path,
& file name)' may be incorrect.
> Could not restart database '(DB's name)'. Reverting back to old status.
> ALTER DATABASE statement failed.
> Device activation error. The physical file name '(Log file's drive, path,
& file name)' may be incorrect.
> sp_dboption command failed.
> What am I doing wrong? How do I successfully detach from one & reattach t
o another?
>
Check the service account that SQL Server is running under, make sure
that account has read/write permissions on the folder that those
attached files are in.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Kelly,
Check what permissions are set for users on the folder where the
database files are placed. If the permissions are inadequate, then
grant the necessary permissions to the user account you logged in with.
Having done that you can proceed to attach the DB files again.|||Tracy,
Thank you for the help.
It does look like permissions is the issue. When we moved the log/data file
s to SQL Servers default location (where tempdb, master,
model, Northwind, and pubs live), we can attach it just fine from there.
On further inspection, it appears that the SQL Server services are not runni
ng under its own (or even another's) domain user
account. It looks like it is running under the Local System account.
What are all the pro's & con's for using its own Domain User account vs usin
g the Local System account?
I remember in my SQL Server Admin class, our instructor had us set up its ow
n Domain User account and use that. I don't
think/remember if he explained why or if the book explains why, though.
Thanks again for all of your help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A4A41.6030007@.realsqlguy.co
m...
> Conan Kelly wrote:
> Check the service account that SQL Server is running under, make sure that
account has read/write permissions on the folder that
> those attached files are in.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Conan Kelly wrote:
> Tracy,
> Thank you for the help.
> It does look like permissions is the issue. When we moved the log/data fi
les to SQL Servers default location (where tempdb, master,
> model, Northwind, and pubs live), we can attach it just fine from there.
> On further inspection, it appears that the SQL Server services are not run
ning under its own (or even another's) domain user
> account. It looks like it is running under the Local System account.
> What are all the pro's & con's for using its own Domain User account vs us
ing the Local System account?
> I remember in my SQL Server Admin class, our instructor had us set up its
own Domain User account and use that. I don't
> think/remember if he explained why or if the book explains why, though.
> Thanks again for all of your help,
> Conan
By running SQL under a domain account, you have greater control over
permissions. Also, if you need to access any network resources, for
doing backups to a remote drive for instance, you'll need to be running
under a domain account.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DB attaches as read only

Hello all,
I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
attaches as Read Only.
When I go into the DB's properties and try to change the Read-only option, I get an error message:
Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
Could not restart database '(DB's name)'. Reverting back to old status.
ALTER DATABASE statement failed.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
sp_dboption command failed.
What am I doing wrong? How do I successfully detach from one & reattach to another?
Thanks for any help anyone can provide,
Conan Kelly
Conan Kelly wrote:
> Hello all,
> I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
> attaches as Read Only.
> When I go into the DB's properties and try to change the Read-only option, I get an error message:
> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
> Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> Could not restart database '(DB's name)'. Reverting back to old status.
> ALTER DATABASE statement failed.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> sp_dboption command failed.
> What am I doing wrong? How do I successfully detach from one & reattach to another?
>
Check the service account that SQL Server is running under, make sure
that account has read/write permissions on the folder that those
attached files are in.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Kelly,
Check what permissions are set for users on the folder where the
database files are placed. If the permissions are inadequate, then
grant the necessary permissions to the user account you logged in with.
Having done that you can proceed to attach the DB files again.
|||Tracy,
Thank you for the help.
It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
model, Northwind, and pubs live), we can attach it just fine from there.
On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
account. It looks like it is running under the Local System account.
What are all the pro's & con's for using its own Domain User account vs using the Local System account?
I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
think/remember if he explained why or if the book explains why, though.
Thanks again for all of your help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A4A41.6030007@.realsqlguy.com...
> Conan Kelly wrote:
> Check the service account that SQL Server is running under, make sure that account has read/write permissions on the folder that
> those attached files are in.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Conan Kelly wrote:
> Tracy,
> Thank you for the help.
> It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
> model, Northwind, and pubs live), we can attach it just fine from there.
> On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
> account. It looks like it is running under the Local System account.
> What are all the pro's & con's for using its own Domain User account vs using the Local System account?
> I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
> think/remember if he explained why or if the book explains why, though.
> Thanks again for all of your help,
> Conan
By running SQL under a domain account, you have greater control over
permissions. Also, if you need to access any network resources, for
doing backups to a remote drive for instance, you'll need to be running
under a domain account.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DB attaches as read only

Hello all,
I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
attaches as Read Only.
When I go into the DB's properties and try to change the Read-only option, I get an error message:
Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
Could not restart database '(DB's name)'. Reverting back to old status.
ALTER DATABASE statement failed.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
sp_dboption command failed.
What am I doing wrong? How do I successfully detach from one & reattach to another?
--
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
> attaches as Read Only.
> When I go into the DB's properties and try to change the Read-only option, I get an error message:
> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
> Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> Could not restart database '(DB's name)'. Reverting back to old status.
> ALTER DATABASE statement failed.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> sp_dboption command failed.
> What am I doing wrong? How do I successfully detach from one & reattach to another?
>
Check the service account that SQL Server is running under, make sure
that account has read/write permissions on the folder that those
attached files are in.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Kelly,
Check what permissions are set for users on the folder where the
database files are placed. If the permissions are inadequate, then
grant the necessary permissions to the user account you logged in with.
Having done that you can proceed to attach the DB files again.|||Tracy,
Thank you for the help.
It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
model, Northwind, and pubs live), we can attach it just fine from there.
On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
account. It looks like it is running under the Local System account.
What are all the pro's & con's for using its own Domain User account vs using the Local System account?
I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
think/remember if he explained why or if the book explains why, though.
Thanks again for all of your help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A4A41.6030007@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there.
>> It attaches as Read Only.
>> When I go into the DB's properties and try to change the Read-only option, I get an error message:
>> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
>> Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
>> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
>> Could not restart database '(DB's name)'. Reverting back to old status.
>> ALTER DATABASE statement failed.
>> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
>> sp_dboption command failed.
>> What am I doing wrong? How do I successfully detach from one & reattach to another?
> Check the service account that SQL Server is running under, make sure that account has read/write permissions on the folder that
> those attached files are in.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Conan Kelly wrote:
> Tracy,
> Thank you for the help.
> It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
> model, Northwind, and pubs live), we can attach it just fine from there.
> On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
> account. It looks like it is running under the Local System account.
> What are all the pro's & con's for using its own Domain User account vs using the Local System account?
> I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
> think/remember if he explained why or if the book explains why, though.
> Thanks again for all of your help,
> Conan
By running SQL under a domain account, you have greater control over
permissions. Also, if you need to access any network resources, for
doing backups to a remote drive for instance, you'll need to be running
under a domain account.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql