compressed backups

Is there any way to automate compression of timed database backups from a
T-SQL script? I don't see any compression options for the BACKUP command in
BOL. Something analogous to the -Fc option for pg_dump in PostgreSQL.
A timed command-line batch file using a file compression program could be
set to execute after the script, but the large data file would have to be
created first.
Thanks,
David P. Lurie
David,
There's nothing inside SQL Server to do compression. You could execute a command after the backup that uses
ZIP or similar to do compression. Or use SQL Lite Speed (probably misspelled), which uses an extended stored
procedure to do backup, and this does compression.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David P. Lurie" <abc@.def.net> wrote in message news:%23IaE$vFKEHA.3628@.TK2MSFTNGP12.phx.gbl...
> Is there any way to automate compression of timed database backups from a
> T-SQL script? I don't see any compression options for the BACKUP command in
> BOL. Something analogous to the -Fc option for pg_dump in PostgreSQL.
> A timed command-line batch file using a file compression program could be
> set to execute after the script, but the large data file would have to be
> created first.
> Thanks,
> David P. Lurie
>

Compress SQL Server Backup File.

I have a Windows 2003 Server, what my plan is to compress sql server backup
file every night and ship in to different SQL Server Server, the question
is, is there any builtin zip/compress facility in windows which I can use it
for this purpose.
Thanks
If you are using the NTFS file system, you can just configure a directory to
be compressed in the advanced properties but when you read the file to send
it to the backup server, Windows will uncompress it so if you want the data
to be compressed on the wire, you will probably need to use some kind of zip
utility. Depending on how big the files are, compressing on the wire might
not make that big a difference so it might be enough to make the backup
directories compressed. Be sure you don't accidentally compress the
database files - this will break SQL Server.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Rogers" <naissani@.hotmail.com> wrote in message
news:eZves4UPGHA.1312@.TK2MSFTNGP09.phx.gbl...
>I have a Windows 2003 Server, what my plan is to compress sql server backup
>file every night and ship in to different SQL Server Server, the question
>is, is there any builtin zip/compress facility in windows which I can use
>it for this purpose.
> Thanks
>
|||Rogers wrote:
> I have a Windows 2003 Server, what my plan is to compress sql server
> backup file every night and ship in to different SQL Server Server,
> the question is, is there any builtin zip/compress facility in
> windows which I can use it for this purpose.
> Thanks
I would recommend you consider using a SQL Server backup and recovery
program that compresses and optionally encrypts backups in memory.
You'll save yourself a lot of backup (and recovery) time and won't have
to worry about post-backup compression. There are a number of companies
who have this type of software.
David Gugick - SQL Server MVP
Quest Software
|||Hi,
of course there are several tools in the market making a good job, but
sometimes you are really interested in a subset of their functionality
or your budget is really reduced. Four these cases, I would suggest
take a look here
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart =blogview&_c=blogpart#permalink
Regards

Compress full backup file

Hello,

Is it possible to compress a full backup file? I've noticed that the backup file size is usually the same size of the working database.
My goal is to compress and break in small parts the backup file to be send to another location.

Does anyone knows a program to do that?

Thanks for the help!
Diogo SantosRefer to http://www.sqllitespeed.com/ (SQL Litespeed) for more information which is lot quicker and reliable.

compress backup file

One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
Claudia
Hello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.

> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.

> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)

>
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.

>
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

compress backup file

One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
ClaudiaHello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.

> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.

> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)

>
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.

>
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

compress backup file

One KB article (http://support.microsoft.com/kb/231347/en-us) says that
backing up databases onto compressed volumes is not recommended and not
supported. My question is: Is it ok/supported if we backup the databases
regularly and then zip the backup files?
Thanks in advance.
ClaudiaHello,
You can do that. As a precausion once in while just unzip the file and
restore the database in your testing environment and make sure
that UNZIP process and RESTORE work fine.
Thanks
Hari
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia,
Or move the files to a compressed volume after the backup is complete. Move
them back to an uncompressed volume if you have to restore.
-- Bill
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:4FFA3AD7-AA60-4FEA-BE8F-D58C8AE4039E@.microsoft.com...
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia|||Claudia wrote:
> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
> backing up databases onto compressed volumes is not recommended and not
> supported. My question is: Is it ok/supported if we backup the databases
> regularly and then zip the backup files?
> Thanks in advance.
> Claudia
I've backed up to compressed volumes for years, never had a problem.
Definately don't put the live data files on a compressed volume, but the
backups seem to work without a hitch.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E4D86.6000705@.realsqlguy.com...
> Claudia wrote:
>> One KB article (http://support.microsoft.com/kb/231347/en-us) says that
>> backing up databases onto compressed volumes is not recommended and not
>> supported. My question is: Is it ok/supported if we backup the databases
>> regularly and then zip the backup files?
>> Thanks in advance.
>> Claudia
> I've backed up to compressed volumes for years, never had a problem.
> Definately don't put the live data files on a compressed volume, but the
> backups seem to work without a hitch.
Sure, but have you restored from the compressed volume? ;-)
Seriously, I think there's a few reasons MS recommends against this, but I
haven't seen problems either.
Also, another option is to look at some of the 3rd party backup tools out
there that will compress on the fly as they backup.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg D. Moore (Strider) wrote:
> Sure, but have you restored from the compressed volume? ;-)
Yep - each backup gets restored to three different servers - DR,
Standby, and Reporting. Full backups every night, logs every 5 minutes,
haven't had one fail yet.
> Seriously, I think there's a few reasons MS recommends against this, but I
> haven't seen problems either.
To be honest, I always viewed it as a "CYA" statement.
> Also, another option is to look at some of the 3rd party backup tools out
> there that will compress on the fly as they backup.
Definately an option. As much as we exercise our backups, I'm pretty
confident that the compressed volumes are safe though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E5E0C.6040402@.realsqlguy.com...
> Greg D. Moore (Strider) wrote:
>> Sure, but have you restored from the compressed volume? ;-)
> Yep - each backup gets restored to three different servers - DR, Standby,
> and Reporting. Full backups every night, logs every 5 minutes, haven't
> had one fail yet.
Actually to be honest, knowing you, I sort of assumed you did. I've just
seen too many people who say, "Oh the backups work fine..." only to find
out the restores don't. ;-)
>> Seriously, I think there's a few reasons MS recommends against this, but
>> I haven't seen problems either.
> To be honest, I always viewed it as a "CYA" statement.
I recall it having to do something with aligning writes on sector boundaries
and the like for speed, but that was about it.
>> Also, another option is to look at some of the 3rd party backup tools out
>> there that will compress on the fly as they backup.
> Definately an option. As much as we exercise our backups, I'm pretty
> confident that the compressed volumes are safe though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.comsqlsql

Complicated Backup Strategy Help Requested

I have a fairly complicated backup scenario I could use some help with.
We have a SQL 2000 database loaded up (by batch processing) to monthly
tables (Table_200507, Table_200508, Table_200509, etc.). Once the
month is complete, the data in that table NEVER changes. Additionally,
once the month is complete, we alter the indexes on that table to 100%
fill factors to conserve space. Because we do batch processing, and it
is easy to recreate lost data, we use SIMPLE recovery mode. So, in
summary, we have one live monthly table, and a slew of older, read-only
monthly archive tables.
I want to implement a solution where after each month is over, I can
backup the data for that single month only. Additionally, I want to
support the ability to restore any single month to a new database.
I thought I would be able to do this with filegroups and filegroup
backups; however, SQL appears to be holding me back. I see 2 problems.
First problem - Can't use SIMPLE recovery model with filegroup backups.
I tried to get around that by setting my database to FULL, creating
the backup, then resetting the database back to SIMPLE. The problem
there is that upon restoring the backup, it wants all the subsequent
log backups (which don't exist) even though the data in that particular
filegroup is unchanged.
Second problem - Need to restore all files to a new database before
database will come online. I tried restoring my single filegroup
backup to a new database. SQL told me it first needed the PRIMARY
filegroup. Fine. I made a backup and restored that PRIMARY filegroup
first. Then I restored my single filegroup backup. Fine. Only
problem is that the database has been left in "Loading" status. I
tried issuing a RESTORE DATABASE WITH RECOVERY and got:
Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 2 in database '<db name>'.
Is there any way to use sql to accomplish my goal of usable monthly
backups? One other limitation is that I do not want to create multiple
databases (complicated business reasoning - please trust me). Any help
or advice with similar situations would be much appreciated.
Thanks.
If you only have one table that has data you need to backup why not BCP out
the data each night and back that file up? If you have a way to reproduce
the database and all the objects in it you can rebuild by bcping the data
back in.
Andrew J. Kelly SQL MVP
<jalbenberg@.yahoo.com> wrote in message
news:1126291436.212696.148970@.g44g2000cwa.googlegr oups.com...
>I have a fairly complicated backup scenario I could use some help with.
> We have a SQL 2000 database loaded up (by batch processing) to monthly
> tables (Table_200507, Table_200508, Table_200509, etc.). Once the
> month is complete, the data in that table NEVER changes. Additionally,
> once the month is complete, we alter the indexes on that table to 100%
> fill factors to conserve space. Because we do batch processing, and it
> is easy to recreate lost data, we use SIMPLE recovery mode. So, in
> summary, we have one live monthly table, and a slew of older, read-only
> monthly archive tables.
> I want to implement a solution where after each month is over, I can
> backup the data for that single month only. Additionally, I want to
> support the ability to restore any single month to a new database.
> I thought I would be able to do this with filegroups and filegroup
> backups; however, SQL appears to be holding me back. I see 2 problems.
> First problem - Can't use SIMPLE recovery model with filegroup backups.
> I tried to get around that by setting my database to FULL, creating
> the backup, then resetting the database back to SIMPLE. The problem
> there is that upon restoring the backup, it wants all the subsequent
> log backups (which don't exist) even though the data in that particular
> filegroup is unchanged.
> Second problem - Need to restore all files to a new database before
> database will come online. I tried restoring my single filegroup
> backup to a new database. SQL told me it first needed the PRIMARY
> filegroup. Fine. I made a backup and restored that PRIMARY filegroup
> first. Then I restored my single filegroup backup. Fine. Only
> problem is that the database has been left in "Loading" status. I
> tried issuing a RESTORE DATABASE WITH RECOVERY and got:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 2 in database '<db name>'.
> Is there any way to use sql to accomplish my goal of usable monthly
> backups? One other limitation is that I do not want to create multiple
> databases (complicated business reasoning - please trust me). Any help
> or advice with similar situations would be much appreciated.
> Thanks.
>
|||>I want to implement a solution where after each month is over, I can
>backup the data for that single month only. Additionally, I want to
>support the ability to restore any single month to a new database.
Create a "backup DB" on another server and export it there once a month.
ChrisR
"jalbenberg@.yahoo.com" wrote:

> I have a fairly complicated backup scenario I could use some help with.
> We have a SQL 2000 database loaded up (by batch processing) to monthly
> tables (Table_200507, Table_200508, Table_200509, etc.). Once the
> month is complete, the data in that table NEVER changes. Additionally,
> once the month is complete, we alter the indexes on that table to 100%
> fill factors to conserve space. Because we do batch processing, and it
> is easy to recreate lost data, we use SIMPLE recovery mode. So, in
> summary, we have one live monthly table, and a slew of older, read-only
> monthly archive tables.
> I want to implement a solution where after each month is over, I can
> backup the data for that single month only. Additionally, I want to
> support the ability to restore any single month to a new database.
> I thought I would be able to do this with filegroups and filegroup
> backups; however, SQL appears to be holding me back. I see 2 problems.
> First problem - Can't use SIMPLE recovery model with filegroup backups.
> I tried to get around that by setting my database to FULL, creating
> the backup, then resetting the database back to SIMPLE. The problem
> there is that upon restoring the backup, it wants all the subsequent
> log backups (which don't exist) even though the data in that particular
> filegroup is unchanged.
> Second problem - Need to restore all files to a new database before
> database will come online. I tried restoring my single filegroup
> backup to a new database. SQL told me it first needed the PRIMARY
> filegroup. Fine. I made a backup and restored that PRIMARY filegroup
> first. Then I restored my single filegroup backup. Fine. Only
> problem is that the database has been left in "Loading" status. I
> tried issuing a RESTORE DATABASE WITH RECOVERY and got:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 2 in database '<db name>'.
> Is there any way to use sql to accomplish my goal of usable monthly
> backups? One other limitation is that I do not want to create multiple
> databases (complicated business reasoning - please trust me). Any help
> or advice with similar situations would be much appreciated.
> Thanks.
>
|||Great idea - sometimes the "low tech" solutions are the best ones.
Thanks.

Complicated Backup Strategy Help Requested

I have a fairly complicated backup scenario I could use some help with.
We have a SQL 2000 database loaded up (by batch processing) to monthly
tables (Table_200507, Table_200508, Table_200509, etc.). Once the
month is complete, the data in that table NEVER changes. Additionally,
once the month is complete, we alter the indexes on that table to 100%
fill factors to conserve space. Because we do batch processing, and it
is easy to recreate lost data, we use SIMPLE recovery mode. So, in
summary, we have one live monthly table, and a slew of older, read-only
monthly archive tables.
I want to implement a solution where after each month is over, I can
backup the data for that single month only. Additionally, I want to
support the ability to restore any single month to a new database.
I thought I would be able to do this with filegroups and filegroup
backups; however, SQL appears to be holding me back. I see 2 problems.
First problem - Can't use SIMPLE recovery model with filegroup backups.
I tried to get around that by setting my database to FULL, creating
the backup, then resetting the database back to SIMPLE. The problem
there is that upon restoring the backup, it wants all the subsequent
log backups (which don't exist) even though the data in that particular
filegroup is unchanged.
Second problem - Need to restore all files to a new database before
database will come online. I tried restoring my single filegroup
backup to a new database. SQL told me it first needed the PRIMARY
filegroup. Fine. I made a backup and restored that PRIMARY filegroup
first. Then I restored my single filegroup backup. Fine. Only
problem is that the database has been left in "Loading" status. I
tried issuing a RESTORE DATABASE WITH RECOVERY and got:
Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 2 in database '<db name>'.
Is there any way to use sql to accomplish my goal of usable monthly
backups? One other limitation is that I do not want to create multiple
databases (complicated business reasoning - please trust me). Any help
or advice with similar situations would be much appreciated.
Thanks.If you only have one table that has data you need to backup why not BCP out
the data each night and back that file up? If you have a way to reproduce
the database and all the objects in it you can rebuild by bcping the data
back in.
Andrew J. Kelly SQL MVP
<jalbenberg@.yahoo.com> wrote in message
news:1126291436.212696.148970@.g44g2000cwa.googlegroups.com...
>I have a fairly complicated backup scenario I could use some help with.
> We have a SQL 2000 database loaded up (by batch processing) to monthly
> tables (Table_200507, Table_200508, Table_200509, etc.). Once the
> month is complete, the data in that table NEVER changes. Additionally,
> once the month is complete, we alter the indexes on that table to 100%
> fill factors to conserve space. Because we do batch processing, and it
> is easy to recreate lost data, we use SIMPLE recovery mode. So, in
> summary, we have one live monthly table, and a slew of older, read-only
> monthly archive tables.
> I want to implement a solution where after each month is over, I can
> backup the data for that single month only. Additionally, I want to
> support the ability to restore any single month to a new database.
> I thought I would be able to do this with filegroups and filegroup
> backups; however, SQL appears to be holding me back. I see 2 problems.
> First problem - Can't use SIMPLE recovery model with filegroup backups.
> I tried to get around that by setting my database to FULL, creating
> the backup, then resetting the database back to SIMPLE. The problem
> there is that upon restoring the backup, it wants all the subsequent
> log backups (which don't exist) even though the data in that particular
> filegroup is unchanged.
> Second problem - Need to restore all files to a new database before
> database will come online. I tried restoring my single filegroup
> backup to a new database. SQL told me it first needed the PRIMARY
> filegroup. Fine. I made a backup and restored that PRIMARY filegroup
> first. Then I restored my single filegroup backup. Fine. Only
> problem is that the database has been left in "Loading" status. I
> tried issuing a RESTORE DATABASE WITH RECOVERY and got:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 2 in database '<db name>'.
> Is there any way to use sql to accomplish my goal of usable monthly
> backups? One other limitation is that I do not want to create multiple
> databases (complicated business reasoning - please trust me). Any help
> or advice with similar situations would be much appreciated.
> Thanks.
>|||>I want to implement a solution where after each month is over, I can
>backup the data for that single month only. Additionally, I want to
>support the ability to restore any single month to a new database.
Create a "backup DB" on another server and export it there once a month.
ChrisR
"jalbenberg@.yahoo.com" wrote:

> I have a fairly complicated backup scenario I could use some help with.
> We have a SQL 2000 database loaded up (by batch processing) to monthly
> tables (Table_200507, Table_200508, Table_200509, etc.). Once the
> month is complete, the data in that table NEVER changes. Additionally,
> once the month is complete, we alter the indexes on that table to 100%
> fill factors to conserve space. Because we do batch processing, and it
> is easy to recreate lost data, we use SIMPLE recovery mode. So, in
> summary, we have one live monthly table, and a slew of older, read-only
> monthly archive tables.
> I want to implement a solution where after each month is over, I can
> backup the data for that single month only. Additionally, I want to
> support the ability to restore any single month to a new database.
> I thought I would be able to do this with filegroups and filegroup
> backups; however, SQL appears to be holding me back. I see 2 problems.
> First problem - Can't use SIMPLE recovery model with filegroup backups.
> I tried to get around that by setting my database to FULL, creating
> the backup, then resetting the database back to SIMPLE. The problem
> there is that upon restoring the backup, it wants all the subsequent
> log backups (which don't exist) even though the data in that particular
> filegroup is unchanged.
> Second problem - Need to restore all files to a new database before
> database will come online. I tried restoring my single filegroup
> backup to a new database. SQL told me it first needed the PRIMARY
> filegroup. Fine. I made a backup and restored that PRIMARY filegroup
> first. Then I restored my single filegroup backup. Fine. Only
> problem is that the database has been left in "Loading" status. I
> tried issuing a RESTORE DATABASE WITH RECOVERY and got:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 2 in database '<db name>'.
> Is there any way to use sql to accomplish my goal of usable monthly
> backups? One other limitation is that I do not want to create multiple
> databases (complicated business reasoning - please trust me). Any help
> or advice with similar situations would be much appreciated.
> Thanks.
>|||Great idea - sometimes the "low tech" solutions are the best ones.
Thanks.

Complicated Backup Strategy Help Requested

I have a fairly complicated backup scenario I could use some help with.
We have a SQL 2000 database loaded up (by batch processing) to monthly
tables (Table_200507, Table_200508, Table_200509, etc.). Once the
month is complete, the data in that table NEVER changes. Additionally,
once the month is complete, we alter the indexes on that table to 100%
fill factors to conserve space. Because we do batch processing, and it
is easy to recreate lost data, we use SIMPLE recovery mode. So, in
summary, we have one live monthly table, and a slew of older, read-only
monthly archive tables.
I want to implement a solution where after each month is over, I can
backup the data for that single month only. Additionally, I want to
support the ability to restore any single month to a new database.
I thought I would be able to do this with filegroups and filegroup
backups; however, SQL appears to be holding me back. I see 2 problems.
First problem - Can't use SIMPLE recovery model with filegroup backups.
I tried to get around that by setting my database to FULL, creating
the backup, then resetting the database back to SIMPLE. The problem
there is that upon restoring the backup, it wants all the subsequent
log backups (which don't exist) even though the data in that particular
filegroup is unchanged.
Second problem - Need to restore all files to a new database before
database will come online. I tried restoring my single filegroup
backup to a new database. SQL told me it first needed the PRIMARY
filegroup. Fine. I made a backup and restored that PRIMARY filegroup
first. Then I restored my single filegroup backup. Fine. Only
problem is that the database has been left in "Loading" status. I
tried issuing a RESTORE DATABASE WITH RECOVERY and got:
Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 2 in database '<db name>'.
Is there any way to use sql to accomplish my goal of usable monthly
backups? One other limitation is that I do not want to create multiple
databases (complicated business reasoning - please trust me). Any help
or advice with similar situations would be much appreciated.
Thanks.If you only have one table that has data you need to backup why not BCP out
the data each night and back that file up? If you have a way to reproduce
the database and all the objects in it you can rebuild by bcping the data
back in.
--
Andrew J. Kelly SQL MVP
<jalbenberg@.yahoo.com> wrote in message
news:1126291436.212696.148970@.g44g2000cwa.googlegroups.com...
>I have a fairly complicated backup scenario I could use some help with.
> We have a SQL 2000 database loaded up (by batch processing) to monthly
> tables (Table_200507, Table_200508, Table_200509, etc.). Once the
> month is complete, the data in that table NEVER changes. Additionally,
> once the month is complete, we alter the indexes on that table to 100%
> fill factors to conserve space. Because we do batch processing, and it
> is easy to recreate lost data, we use SIMPLE recovery mode. So, in
> summary, we have one live monthly table, and a slew of older, read-only
> monthly archive tables.
> I want to implement a solution where after each month is over, I can
> backup the data for that single month only. Additionally, I want to
> support the ability to restore any single month to a new database.
> I thought I would be able to do this with filegroups and filegroup
> backups; however, SQL appears to be holding me back. I see 2 problems.
> First problem - Can't use SIMPLE recovery model with filegroup backups.
> I tried to get around that by setting my database to FULL, creating
> the backup, then resetting the database back to SIMPLE. The problem
> there is that upon restoring the backup, it wants all the subsequent
> log backups (which don't exist) even though the data in that particular
> filegroup is unchanged.
> Second problem - Need to restore all files to a new database before
> database will come online. I tried restoring my single filegroup
> backup to a new database. SQL told me it first needed the PRIMARY
> filegroup. Fine. I made a backup and restored that PRIMARY filegroup
> first. Then I restored my single filegroup backup. Fine. Only
> problem is that the database has been left in "Loading" status. I
> tried issuing a RESTORE DATABASE WITH RECOVERY and got:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 2 in database '<db name>'.
> Is there any way to use sql to accomplish my goal of usable monthly
> backups? One other limitation is that I do not want to create multiple
> databases (complicated business reasoning - please trust me). Any help
> or advice with similar situations would be much appreciated.
> Thanks.
>|||>I want to implement a solution where after each month is over, I can
>backup the data for that single month only. Additionally, I want to
>support the ability to restore any single month to a new database.
Create a "backup DB" on another server and export it there once a month.
--
ChrisR
"jalbenberg@.yahoo.com" wrote:
> I have a fairly complicated backup scenario I could use some help with.
> We have a SQL 2000 database loaded up (by batch processing) to monthly
> tables (Table_200507, Table_200508, Table_200509, etc.). Once the
> month is complete, the data in that table NEVER changes. Additionally,
> once the month is complete, we alter the indexes on that table to 100%
> fill factors to conserve space. Because we do batch processing, and it
> is easy to recreate lost data, we use SIMPLE recovery mode. So, in
> summary, we have one live monthly table, and a slew of older, read-only
> monthly archive tables.
> I want to implement a solution where after each month is over, I can
> backup the data for that single month only. Additionally, I want to
> support the ability to restore any single month to a new database.
> I thought I would be able to do this with filegroups and filegroup
> backups; however, SQL appears to be holding me back. I see 2 problems.
> First problem - Can't use SIMPLE recovery model with filegroup backups.
> I tried to get around that by setting my database to FULL, creating
> the backup, then resetting the database back to SIMPLE. The problem
> there is that upon restoring the backup, it wants all the subsequent
> log backups (which don't exist) even though the data in that particular
> filegroup is unchanged.
> Second problem - Need to restore all files to a new database before
> database will come online. I tried restoring my single filegroup
> backup to a new database. SQL told me it first needed the PRIMARY
> filegroup. Fine. I made a backup and restored that PRIMARY filegroup
> first. Then I restored my single filegroup backup. Fine. Only
> problem is that the database has been left in "Loading" status. I
> tried issuing a RESTORE DATABASE WITH RECOVERY and got:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 2 in database '<db name>'.
> Is there any way to use sql to accomplish my goal of usable monthly
> backups? One other limitation is that I do not want to create multiple
> databases (complicated business reasoning - please trust me). Any help
> or advice with similar situations would be much appreciated.
> Thanks.
>|||Great idea - sometimes the "low tech" solutions are the best ones.
Thanks.

Complete, Differential, Transaction Log -- More backup questions

Greetings all,
I am new to this Newsgroup, so forgive me if my question seems stupid.
We currently have a payroll system that runs on SQL Server 2000. We are
doing a full backup (to a local hard disk) every night of the main database,
which is about 55GB and then sending the SQL Backup off to tape. We can keep
2, maybe 3 days backup online, but space is getting tight.
I have suggested that we do 1 complete backup at the beginning of the
week, then a Differential backup every night, and a transaction log backup
every couple of hours during the day.
My question for you people. If I do the complete backup on Sunday
night, a differential on Monday and Tuesday night, and a couple of
Transaction Log backups on Wednesday, and the System crashes late Wednesday
do I have to restore both Mon and Tue differential and all the Wed
Transaction Logs and restoring the complete from Sun, or can I just restore
the Sun complete, the Tue night differential and the last Transaction Log
from Wed?
I think my actual question is: "Is the Differential backup all the
differences from the last complete or the last Differential, and does the
Transaction Log backup get everything from the last Differential?"
Thanks much
Raymond Fantasia
Fry''s Electronics
ISD DepartmentHi
answered in microsoft.public.sqlserver.newusers
Please do not multi-post as it is harder to know if the question has been
answered yet.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ray Fantasia" <RayFantasia@.discussions.microsoft.com> wrote in message
news:A8241F2A-6630-4B96-A9A0-2CB30BEEA7E9@.microsoft.com...
> Greetings all,
> I am new to this Newsgroup, so forgive me if my question seems stupid.
> We currently have a payroll system that runs on SQL Server 2000. We are
> doing a full backup (to a local hard disk) every night of the main
> database,
> which is about 55GB and then sending the SQL Backup off to tape. We can
> keep
> 2, maybe 3 days backup online, but space is getting tight.
> I have suggested that we do 1 complete backup at the beginning of the
> week, then a Differential backup every night, and a transaction log backup
> every couple of hours during the day.
> My question for you people. If I do the complete backup on Sunday
> night, a differential on Monday and Tuesday night, and a couple of
> Transaction Log backups on Wednesday, and the System crashes late
> Wednesday
> do I have to restore both Mon and Tue differential and all the Wed
> Transaction Logs and restoring the complete from Sun, or can I just
> restore
> the Sun complete, the Tue night differential and the last Transaction Log
> from Wed?
> I think my actual question is: "Is the Differential backup all the
> differences from the last complete or the last Differential, and does the
> Transaction Log backup get everything from the last Differential?"
> Thanks much
> Raymond Fantasia
> Fry''s Electronics
> ISD Department

Complete, Differential, Transaction Log -- More backup questions

Greetings all,
I am new to this Newsgroup, so forgive me if my question seems stupid.
We currently have a payroll system that runs on SQL Server 2000. We are
doing a full backup (to a local hard disk) every night of the main database,
which is about 55GB and then sending the SQL Backup off to tape. We can kee
p
2, maybe 3 days backup online, but space is getting tight.
I have suggested that we do 1 complete backup at the beginning of the
week, then a Differential backup every night, and a transaction log backup
every couple of hours during the day.
My question for you people. If I do the complete backup on Sunday
night, a differential on Monday and Tuesday night, and a couple of
Transaction Log backups on Wednesday, and the System crashes late Wednesday
do I have to restore both Mon and Tue differential and all the Wed
Transaction Logs and restoring the complete from Sun, or can I just restore
the Sun complete, the Tue night differential and the last Transaction Log
from Wed?
I think my actual question is: "Is the Differential backup all the
differences from the last complete or the last Differential, and does the
Transaction Log backup get everything from the last Differential?"
Thanks much
Raymond Fantasia
Fry''s Electronics
ISD DepartmentHi
answered in microsoft.public.sqlserver.newusers
Please do not multi-post as it is harder to know if the question has been
answered yet.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ray Fantasia" <RayFantasia@.discussions.microsoft.com> wrote in message
news:A8241F2A-6630-4B96-A9A0-2CB30BEEA7E9@.microsoft.com...
> Greetings all,
> I am new to this Newsgroup, so forgive me if my question seems stupid.
> We currently have a payroll system that runs on SQL Server 2000. We are
> doing a full backup (to a local hard disk) every night of the main
> database,
> which is about 55GB and then sending the SQL Backup off to tape. We can
> keep
> 2, maybe 3 days backup online, but space is getting tight.
> I have suggested that we do 1 complete backup at the beginning of the
> week, then a Differential backup every night, and a transaction log backup
> every couple of hours during the day.
> My question for you people. If I do the complete backup on Sunday
> night, a differential on Monday and Tuesday night, and a couple of
> Transaction Log backups on Wednesday, and the System crashes late
> Wednesday
> do I have to restore both Mon and Tue differential and all the Wed
> Transaction Logs and restoring the complete from Sun, or can I just
> restore
> the Sun complete, the Tue night differential and the last Transaction Log
> from Wed?
> I think my actual question is: "Is the Differential backup all the
> differences from the last complete or the last Differential, and does the
> Transaction Log backup get everything from the last Differential?"
> Thanks much
> Raymond Fantasia
> Fry''s Electronics
> ISD Department

Complete, Differential, Transaction Log -- More backup questions

Greetings all,
I am new to this Newsgroup, so forgive me if my question seems stupid.
We currently have a payroll system that runs on SQL Server 2000. We are
doing a full backup (to a local hard disk) every night of the main database,
which is about 55GB and then sending the SQL Backup off to tape. We can keep
2, maybe 3 days backup online, but space is getting tight.
I have suggested that we do 1 complete backup at the beginning of the
week, then a Differential backup every night, and a transaction log backup
every couple of hours during the day.
My question for you people. If I do the complete backup on Sunday
night, a differential on Monday and Tuesday night, and a couple of
Transaction Log backups on Wednesday, and the System crashes late Wednesday
do I have to restore both Mon and Tue differential and all the Wed
Transaction Logs and restoring the complete from Sun, or can I just restore
the Sun complete, the Tue night differential and the last Transaction Log
from Wed?
I think my actual question is: "Is the Differential backup all the
differences from the last complete or the last Differential, and does the
Transaction Log backup get everything from the last Differential?"
Thanks much
Raymond Fantasia
Fry''s Electronics
ISD Department
Hi
answered in microsoft.public.sqlserver.newusers
Please do not multi-post as it is harder to know if the question has been
answered yet.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ray Fantasia" <RayFantasia@.discussions.microsoft.com> wrote in message
news:A8241F2A-6630-4B96-A9A0-2CB30BEEA7E9@.microsoft.com...
> Greetings all,
> I am new to this Newsgroup, so forgive me if my question seems stupid.
> We currently have a payroll system that runs on SQL Server 2000. We are
> doing a full backup (to a local hard disk) every night of the main
> database,
> which is about 55GB and then sending the SQL Backup off to tape. We can
> keep
> 2, maybe 3 days backup online, but space is getting tight.
> I have suggested that we do 1 complete backup at the beginning of the
> week, then a Differential backup every night, and a transaction log backup
> every couple of hours during the day.
> My question for you people. If I do the complete backup on Sunday
> night, a differential on Monday and Tuesday night, and a couple of
> Transaction Log backups on Wednesday, and the System crashes late
> Wednesday
> do I have to restore both Mon and Tue differential and all the Wed
> Transaction Logs and restoring the complete from Sun, or can I just
> restore
> the Sun complete, the Tue night differential and the last Transaction Log
> from Wed?
> I think my actual question is: "Is the Differential backup all the
> differences from the last complete or the last Differential, and does the
> Transaction Log backup get everything from the last Differential?"
> Thanks much
> Raymond Fantasia
> Fry''s Electronics
> ISD Department

complete bkup & log size

Hi ,
does a complete backup automatically reduce a log file size by removing
the inactive entries and those already written to the database ?
i have created a completed database backup on a weeklt basis but it still
seem that the log file is quite huge around 11 GB ?
could anyone kindly advise how i can approach this backup issue ?
tks & rdgsBackups do not reduce the size of any files. You must issue regular log
backups in addition to FULL backups if your database recover model is set to
FULL. or Bulk Logged. If you don't wish to do log backups you should change
it to simple mode. To shrink the file once you have fixed the process you
can use DBCC SHRINKFILE.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:4BF03439-3AA2-4B9C-9E90-D9C046F88A29@.microsoft.com...
> Hi ,
> does a complete backup automatically reduce a log file size by removing
> the inactive entries and those already written to the database ?
> i have created a completed database backup on a weeklt basis but it still
> seem that the log file is quite huge around 11 GB ?
> could anyone kindly advise how i can approach this backup issue ?
> tks & rdgs

complete bkup & log size

Hi ,
does a complete backup automatically reduce a log file size by removing
the inactive entries and those already written to the database ?
i have created a completed database backup on a weeklt basis but it still
seem that the log file is quite huge around 11 GB ?
could anyone kindly advise how i can approach this backup issue ?
tks & rdgsBackups do not reduce the size of any files. You must issue regular log
backups in addition to FULL backups if your database recover model is set to
FULL. or Bulk Logged. If you don't wish to do log backups you should change
it to simple mode. To shrink the file once you have fixed the process you
can use DBCC SHRINKFILE.
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:4BF03439-3AA2-4B9C-9E90-D9C046F88A29@.microsoft.com...
> Hi ,
> does a complete backup automatically reduce a log file size by removing
> the inactive entries and those already written to the database ?
> i have created a completed database backup on a weeklt basis but it still
> seem that the log file is quite huge around 11 GB ?
> could anyone kindly advise how i can approach this backup issue ?
> tks & rdgs

complete bkup & log size

Hi ,
does a complete backup automatically reduce a log file size by removing
the inactive entries and those already written to the database ?
i have created a completed database backup on a weeklt basis but it still
seem that the log file is quite huge around 11 GB ?
could anyone kindly advise how i can approach this backup issue ?
tks & rdgs
Backups do not reduce the size of any files. You must issue regular log
backups in addition to FULL backups if your database recover model is set to
FULL. or Bulk Logged. If you don't wish to do log backups you should change
it to simple mode. To shrink the file once you have fixed the process you
can use DBCC SHRINKFILE.
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:4BF03439-3AA2-4B9C-9E90-D9C046F88A29@.microsoft.com...
> Hi ,
> does a complete backup automatically reduce a log file size by removing
> the inactive entries and those already written to the database ?
> i have created a completed database backup on a weeklt basis but it still
> seem that the log file is quite huge around 11 GB ?
> could anyone kindly advise how i can approach this backup issue ?
> tks & rdgs

complete backup of database except values in a particular column of a table

What if we want to take complete backup of
production database except values in a particular column of a table.
Suppose if
we don't want to reveal social Security number of employees . Can we
take a backup to send the offshore team with no data in SSN column of
employee table.Unfortunately, you can't do that in a backup.
What I have done before is make a copy of the database, and then execute
some 'scrubbing' scripts that would make the appropriate data alterations.
Things like changing Staff names to 'Staff', SSN to 'XXX-XX-XXXX', etc.
With SQL 2005, there are several encryption options available. But if
someone will have physical possession of the database, and a lot of time,
I'm still not ready to completely trust encryption.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arun" <toarunmishra@.gmail.com> wrote in message
news:1156974085.063812.206780@.m79g2000cwm.googlegroups.com...
> What if we want to take complete backup of
> production database except values in a particular column of a table.
> Suppose if
> we don't want to reveal social Security number of employees . Can we
> take a backup to send the offshore team with no data in SSN column of
> employee table.
>|||Hi,
For client specific data we normally take a back of the database and restore
in a new name and do some data masking manually
and then take a backup and send to offsite. Ensure that you mask the
critical info before you send.
Thanks
Hari
SQL Server MVP
"Arun" <toarunmishra@.gmail.com> wrote in message
news:1156974085.063812.206780@.m79g2000cwm.googlegroups.com...
> What if we want to take complete backup of
> production database except values in a particular column of a table.
> Suppose if
> we don't want to reveal social Security number of employees . Can we
> take a backup to send the offshore team with no data in SSN column of
> employee table.
>

complete backup of database except values in a particular column of a table

What if we want to take complete backup of
production database except values in a particular column of a table.
Suppose if
we don't want to reveal social Security number of employees . Can we
take a backup to send the offshore team with no data in SSN column of
employee table.Unfortunately, you can't do that in a backup.
What I have done before is make a copy of the database, and then execute
some 'scrubbing' scripts that would make the appropriate data alterations.
Things like changing Staff names to 'Staff', SSN to 'XXX-XX-XXXX', etc.
With SQL 2005, there are several encryption options available. But if
someone will have physical possession of the database, and a lot of time,
I'm still not ready to completely trust encryption.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arun" <toarunmishra@.gmail.com> wrote in message
news:1156974085.063812.206780@.m79g2000cwm.googlegroups.com...
> What if we want to take complete backup of
> production database except values in a particular column of a table.
> Suppose if
> we don't want to reveal social Security number of employees . Can we
> take a backup to send the offshore team with no data in SSN column of
> employee table.
>|||Hi,
For client specific data we normally take a back of the database and restore
in a new name and do some data masking manually
and then take a backup and send to offsite. Ensure that you mask the
critical info before you send.
Thanks
Hari
SQL Server MVP
"Arun" <toarunmishra@.gmail.com> wrote in message
news:1156974085.063812.206780@.m79g2000cwm.googlegroups.com...
> What if we want to take complete backup of
> production database except values in a particular column of a table.
> Suppose if
> we don't want to reveal social Security number of employees . Can we
> take a backup to send the offshore team with no data in SSN column of
> employee table.
>

Compatibility

Hi,

I work with database administration and I migrated some databases from 2000 to 2005 (simply backup and restore options).

I have tested issues about compatibility. The idea is migrating without update the application.

However, I have already seen some problems with compatibility. After migration, all databases stay with compatibility 80 inside de environment 2005 to avoid problems as, for exemple, the sintax to use hints, with 2005 the with clause isn't opptionaly.

The problem is that somethings isn't ok this way. We can't extract reports as disk usage, backup and restore events etc or generate diagrams for databases with compatibility 80 inside sql management studio.

The reccomendation is change de level for 90, repeat the action and after came back for 80 but we can't do this without impact the current activities. Do you know others problems with databases level 80 and 2005? I read that it's recommended that all databases are updtade for level 90, so I should update my application for changging. What do you think about this?

Thanks,

Karine.

If you want the full features of SQL 2005 Management Studio, then you need to set db compatibility for the database to 90. If not updating your app is more important, then keep it at 80. There's a lot of really cool new UI features and other functionality that depend on compat being at 90, so if it's possible to change your app, I'd do it. Just my opinion.|||

Thanks Greg, but do you know some documentation or paper that show or list UI features couldn't be used with database level (80) inside sql management studio?