Thursday, March 29, 2012
Directly generate PDF file
come in the HTML page and then eport to PDF.
I hope someone knows how.
Kind regards
A. MoeskerSure, you can do this with either web services or URL integration. You
specify the render method. The default is HTML. If you have a link in your
report use the jump to URL instead of jump to report. Look at books online
and search on URL
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A. Moesker" <a.moesker@.home.nl> wrote in message
news:OAk2oUqRFHA.3140@.tk2msftngp13.phx.gbl...
> Is it possible to generate directly a PDF file with a name, and not first
> come in the HTML page and then eport to PDF.
> I hope someone knows how.
> Kind regards
> A. Moesker
>|||I use the following JavaScript to determine the output from a standard asp
search results page.
The URL is built up and then calls a window to open up the new URL which
automatically causes the document to either open or be saved (users selection
via dialogue box).
var HLINK="";
HLINK = HLINK + "http://{RS Server Name}/ReportServer"
HLINK = HLINK + "?%2f{Folder Name}%2f"+sReportName
HLINK = HLINK + "&rs%3aClearSession=true"
HLINK = HLINK + "&rs%3aCommand=Render"
HLINK = HLINK + "&rs%3aFormat=" + aFormat //HTML4.0 - PDF - EXCEL
HLINK = HLINK + "&S_Customer="+S_Customer //Params go here
HLINK = HLINK + "&rc%3aToolbar=True"
HLINK = HLINK + "&rc%3aJavaScript=True"
HLINK = HLINK + "&rc%3aLinkTarget=_top"
HLINK = HLINK + "&rc%3aArea=Toolbar"
HLINK = HLINK + ""
You can see how the URL is built up. I use some 12 or so params with this
page and thay can be bolted on where I have indicated.
Best of luck,
Tony
"Bruce L-C [MVP]" wrote:
> Sure, you can do this with either web services or URL integration. You
> specify the render method. The default is HTML. If you have a link in your
> report use the jump to URL instead of jump to report. Look at books online
> and search on URL
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "A. Moesker" <a.moesker@.home.nl> wrote in message
> news:OAk2oUqRFHA.3140@.tk2msftngp13.phx.gbl...
> > Is it possible to generate directly a PDF file with a name, and not first
> > come in the HTML page and then eport to PDF.
> >
> > I hope someone knows how.
> >
> > Kind regards
> >
> > A. Moesker
> >
> >
>
>
Direct Connection to Local SQL express gives error
I'm just starting out here.
I created a simple formview connected to a local file copy of the database by adding the database into the project. It worked fine.
However, I noticed that this copy of the database was not synch'ing with the database with the same name in SQL express. So I created a new connections string to access the SQL database directly out of SQL express. Its just a simple Select * from column. However, this gives an error. Why?
Server Error in '/NETCatMgr' Application.
The data types ntext and nvarchar are incompatible in the equal to operator.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details:System.Data.SqlClient.SqlException: The data types ntext and nvarchar are incompatible in the equal to operator.
This is the connection string
<
addname="CatalogMgrConnectionString"connectionString="Data Source=mymachine\sqlexpress;Initial Catalog=CatalogSQL;Integrated Security=True"providerName="System.Data.SqlClient" />
Doesn't make sense. How can the formview work fine with the SQL integrated into the project but not working with a connection string to the SQL Express proper?
The error seems to be a data operation error other than a connection error. It indicates your code was trying to compare nvarchar value and ntext using "=" operator. Please make sure you choose proper data for comparation. To manipulate text/ntext/image data in SQL Server, you should use specific functions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_0gs3.asp
|||Its not me -- the problem appears to be some kind of bug in the GridView. The database column is a type nText and the GridView doesn't seem to be able to write code for SQL databases with column types nText. It writes the code with a string comparison and then it crashes... I didn't write any code at all.Tuesday, March 27, 2012
Direct access of MDF file?
his programmer.
I've just received an alpha version of a program that appears to
access the .mdf file directly, maybe without a connection string. I
have not seen the source code yet.
I can picture a half dozen reasons not to do this mostly related to
server directory permissions, IT ownership, etc.
Is there any reason to do it? I'd like to do my homework before
telling the contractor to do what I tell him regardless of the cost.
Thanks in advance,
DennisWhat do you mean, "access the MDF file directly"? Why do you think the
program does this, if you haven't even seen the source code?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Hussman" <hussman_30@.hotmail.com> wrote in message
news:9c4bd326.0411160813.6865b58a@.posting.google.com...
> I am a project manager with a contractor that is very protective of
> his programmer.
> I've just received an alpha version of a program that appears to
> access the .mdf file directly, maybe without a connection string. I
> have not seen the source code yet.
> I can picture a half dozen reasons not to do this mostly related to
> server directory permissions, IT ownership, etc.
> Is there any reason to do it? I'd like to do my homework before
> telling the contractor to do what I tell him regardless of the cost.
> Thanks in advance,
> Dennis|||Not sure what you mean by direct access to an mdf file - the
only thing I can think of would be something possibly using
sp_attach_db, sp_detach_db.
-Sue
On 16 Nov 2004 08:13:20 -0800, hussman_30@.hotmail.com
(Hussman) wrote:
>I am a project manager with a contractor that is very protective of
>his programmer.
>I've just received an alpha version of a program that appears to
>access the .mdf file directly, maybe without a connection string. I
>have not seen the source code yet.
>I can picture a half dozen reasons not to do this mostly related to
>server directory permissions, IT ownership, etc.
>Is there any reason to do it? I'd like to do my homework before
>telling the contractor to do what I tell him regardless of the cost.
>Thanks in advance,
>Dennis|||Also, there's an option in the ODBC DSN for filenames to attach. Perhaps such an option also exists
in an ADO(.NET) connection string and this is used?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:alakp0hh4da9ga6dkoskrbt3okbd5s0ec4@.4ax.com...
> Not sure what you mean by direct access to an mdf file - the
> only thing I can think of would be something possibly using
> sp_attach_db, sp_detach_db.
> -Sue
> On 16 Nov 2004 08:13:20 -0800, hussman_30@.hotmail.com
> (Hussman) wrote:
> >I am a project manager with a contractor that is very protective of
> >his programmer.
> >
> >I've just received an alpha version of a program that appears to
> >access the .mdf file directly, maybe without a connection string. I
> >have not seen the source code yet.
> >
> >I can picture a half dozen reasons not to do this mostly related to
> >server directory permissions, IT ownership, etc.
> >
> >Is there any reason to do it? I'd like to do my homework before
> >telling the contractor to do what I tell him regardless of the cost.
> >
> >Thanks in advance,
> >Dennis
>
Sunday, March 25, 2012
dimension table population-out of fact table
I have a large flat file that comes to me. I first import the flat data in to a SQL table for ease of use. Then i put it into a more permanent table with the proper references to dimension tables. I want to build a dimension table out of information from my flat file. I have a dimension table with columns, [Org Client], and [Client#] where [org client] is the name of the client. Both of these columns appear in my flat file but i want to use only the client# in my permanent table. How extract distinct values of client # and [org client] into a dimension table?
My idea was to select distinct values of client# and use some type of foreach loop to go through each client# and use a query to select the TOP(1) values of [org client] where client# = x. Would this work and if so how do I go about setting this up?
I'm really hoping there is a simpler way than this. Thank you all for your time.
A sort transformation in a data flow can get rid of duplicates for you. Then from there you can go into whatever table you want.|||That is very impressive. Thank you very much. Works better than i could've imagined.sqlThursday, March 22, 2012
Difficulty on inserting data into several tables from XML file using Bulk Insert
Hi, I am a newbie for SQL's XML section and need some help from you guys. I just go through some simple articles with simple sample record. However, I found confusing when I want to inserting data into several tables from a prefer XML file.
My scenario is like that :
Tables :
1) trade ( id int)
2) trans ( id int , startdate datetime , enddate datetime , status bit ,comment varchar(50), tranCode int)
* tranCode = id from trade table
3) transInfo ( id int , userlogin int , amount money , transID int ) * transID = id from trans table
4) trandetails ( id int, transID int ) * transID = id from trans table
Preferred XML :
<?xml version='1.0' encoding='utf-8'?>
<ROOT>
<trade code="1">
<trans id = '123' startdate = '2007-08-02 11:35:00' enddate='2007-04-29 11:36:00' status = '1' >
<transinfo>
<clienttrans login="Alex" amount="1000">
</clienttrans>
</transinfo>
<trandetails>
<trandetail><value>Shipping</value></trandetail>
</trandetails>
<comments><comment>Done</comment></comments>
</trans>
<trans id = '123' startdate = '2007-08-02 11:35:00' enddate='2007-04-29 11:36:00' status = '1' >
<transinfo>
<clienttrans login="Ken" amount="20000">
</clienttrans>
</transinfo>
<trandetails>
<trandetail><value>Transportation</value></trandetail>
</trandetails>
<comments><comment>Done</comment></comments>
</trans>
</trade>
</ROOT>
I am confusing on how to arrange up the elements based on the tables which were created. Or have to re-fix my table ? Any suggestion based on my scenario. Hope able to get any ideas/opinions from here asap. Thanks alot.
Best Regards,
Hans
Problem solved although kinda confusing at the early stage when doing the XML schema.
Difficulty installing and upgrading to MSDE SP3a
settings. I supplied values for the following settings:
[Options]
ALLOWXDBCHAINING=3D0
BLANKSAPWD=3D1
COLLATION=3DLatin1_General_CI_AS
DATADIR=3DC:\Program Files\Microsoft SQL Server\MSSQL$HipMSDE\Data\
DISABLENETWORKPROTOCOLS=3D1
INSTANCENAME=3D"HipMSDE" SAPWD=3D"XXXX" SECURITYMODE=3DSQL
TARGETDIR=3DC:\Program Files\Microsoft SQL Server\MSSQL$HipMSDE\Binn\
Since I was unable to perform this install using the command line:
"Setup.exe /settings Setup.ini"
because it kept telling me the path was not valid, I double clicked on
Setup.exe and it took all the settings in Setup.ini anyway.
In attempting to upgrade to SP3a I attempted to supply the following
settings in an ini file.
[Options]
ALLOWXDBCHAINING=3D0
BLANKSAPWD=3D1
COLLATION=3DLatin1_General_CI_AS
DATADIR=3DC:\Program Files\Microsoft SQL Server\MSSQL$HipMSDE\Data\
DISABLENETWORKPROTOCOLS=3D1
INSTANCENAME=3D"HipMSDE" SAPWD=3D"XXXX" SECURITYMODE=3DSQL
TARGETDIR=3DC:\Program Files\Microsoft SQL Server\MSSQL$HipMSDE\Binn\
UPGRADE=3D1 UPGRADEUSER=3D"XXXXUpgr" UPGRADEPWD=3D"XXXXUpgr"
Using the same command line but this time for the SP3a setup file, an
installation does not occur. Trying the same double click too does not
work. Instead I get is the following instructions:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D
Windows =AE Installer. V 3.00.3790.2180
msiexec /Option <Required Parameter> [Optional Parameter]
Install Options
</package | /i> <Product.msi>
Installs or configures a product
/a <Product.msi>
Administrative install - Installs a product on the network
/j<u|m> <Product.msi> [/t <Transform List>] [/g <Language ID>]
Advertises a product - m to all users, u to current user
</uninstall | /x> <Product.msi | ProductCode>
Uninstalls the product
Display Options
/quiet
Quiet mode, no user interaction
/passive
Unattended mode - progress bar only
/q[n|b|r|f]
Sets user interface level
n - No UI
b - Basic UI
r - Reduced UI
f - Full UI (default)
/help
Help information
Restart Options
/norestart
Do not restart after the installation is complete
/promptrestart
Prompts the user for restart if necessary
/forcerestart
Always restart the computer after installation
Logging Options
/l[i|w|e|a|r|u|c|m|o|p|v|x|+|!|*] <LogFile>
i - Status messages
w - Nonfatal warnings
e - All error messages
a - Start up of actions
r - Action-specific records
u - User requests
c - Initial UI parameters
m - Out-of-memory or fatal exit information
o - Out-of-disk-space messages
p - Terminal properties
v - Verbose output
x - Extra debugging information
+ - Append to existing log file
! - Flush each line to the log
* - Log all information, except for v and x options
/log <LogFile>
Equivalent of /l* <LogFile>
Update Options
/update <Update1.msp>[;Update2.msp]
Applies update(s)
/uninstall <PatchCodeGuid>[;Update2.msp] /package <Product.msi |
ProductCode>
Remove update(s) for a product
Repair Options
/f[p|e|c|m|s|o|d|a|u|v] <Product.msi | ProductCode>
Repairs a product
p - only if file is missing
o - if file is missing or an older version is installed (default)
e - if file is missing or an equal or older version is installed
d - if file is missing or a different version is installed
c - if file is missing or checksum does not match the calculated
value
a - forces all files to be reinstalled
u - all required user-specific registry entries (default)
m - all required computer-specific registry entries (default)
s - all existing shortcuts (default)
v - runs from source and recaches local package
Setting Public Properties
[PROPERTY=3DPropertyValue]
Consult the Windows =AE Installer SDK for additional documentation on
the
command line syntax.
Copyright =A9 Microsoft Corporation. All rights reserved.
Portions of this software are based in part on the work of the
Independent JPEG Group.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D
These windows installer instructions and the wealth of SP3a
documentation only leads me to more confusion. I've spent two days
trying to meticulously decypher and make sense of it. I'm lost! Can
someone please help sort this out for me?
BTW I have successfully updated to SP3a for both of my SQL Server 2000
database installations. It's this MSDE that I am having difficulty
undersatnding
hi,
dbuchanan wrote:
> I have installed MSDE 2000A using the ini file method of supplying
> settings. I supplied values for the following settings:
> [Options]
> ALLOWXDBCHAINING=0
> BLANKSAPWD=1
> COLLATION=Latin1_General_CI_AS
> DATADIR=C:\Program Files\Microsoft SQL Server\MSSQL$HipMSDE\Data\
> DISABLENETWORKPROTOCOLS=1
> INSTANCENAME="HipMSDE" SAPWD="XXXX" SECURITYMODE=SQL
> TARGETDIR=C:\Program Files\Microsoft SQL Server\MSSQL$HipMSDE\Binn\
> Since I was unable to perform this install using the command line:
> "Setup.exe /settings Setup.ini"
> because it kept telling me the path was not valid, I double clicked on
> Setup.exe and it took all the settings in Setup.ini anyway.
> In attempting to upgrade to SP3a I attempted to supply the following
> settings in an ini file.
> [Options]
> ALLOWXDBCHAINING=0
> BLANKSAPWD=1
> COLLATION=Latin1_General_CI_AS
> DATADIR=C:\Program Files\Microsoft SQL Server\MSSQL$HipMSDE\Data\
> DISABLENETWORKPROTOCOLS=1
> INSTANCENAME="HipMSDE" SAPWD="XXXX" SECURITYMODE=SQL
> TARGETDIR=C:\Program Files\Microsoft SQL Server\MSSQL$HipMSDE\Binn\
> UPGRADE=1 UPGRADEUSER="XXXXUpgr" UPGRADEPWD="XXXXUpgr"
> Using the same command line but this time for the SP3a setup file, an
> installation does not occur. Trying the same double click too does not
> work. Instead I get is the following instructions:
>...
MSDE 2000A is already updated at sp3a level and you do not need to upgrade
it..
if you are experimenting troubles with the ini file, you can directly
provide those parameters to the setup.exe bootstrap installer in the form
c:\..\>setup.exe ALLOWXDBCHAINING=0 COLLATION="Latin1_General_CI_AS"
DATADIR="C:\Program Files\Microsoft SQL Server\MSSQL$HipMSDE\Data\"
DISABLENETWORKPROTOCOLS=1 INSTANCENAME="HipMSDE" SAPWD="XXXX"
SECURITYMODE=SQL TARGETDIR="C:\Program Files\Microsoft SQL
Server\MSSQL$HipMSDE\Binn\"
all on the same line
you provide BLANKSAPWD=1 and SAPWD="XXXX" parameters, and those should be
mutual exclusive, so please try removing BLANKSAPWD=1
> UPGRADE=1 UPGRADEUSER="XXXXUpgr" UPGRADEPWD="XXXXUpgr"
are not required as you do not have to upgrade...
please always add the
/L*v "c:\MsdeLog.txt"
parameter too in order to verbose log all the installation process, that
will result in an about 2mb txt log file you can inspect, in case of
problems, for
RETURN VALUE 3
entries, that indicate problems and, about 10/20 lines before or after each
entry a more descriptive (often cryptic) explanation of the problem is
reported
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Wednesday, March 21, 2012
Differntial Backups
I wrote a script that theoretically does the following:
Does a weekly full backup to a file
Does a nightly differential backup to a file
Does hourly transaction logs to a seperate file per hour.
As I understand it, the differential is supposed to be based off of the full
backup. After the first nightly, is each differential still based off of
the original full back up? or is it based of the previous nights
differential, which means I'd have to keep each daily differential until the
week cycled
Thanks,
GaryA diff backup is always based on the latest full backup. This is why you do
the restore like: latest full backup, *latest* diff backup and each
subsequent log backups.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Gary" <n> wrote in message news:%23mUwyzp6DHA.1556@.tk2msftngp13.phx.gbl...
> Hi,
> I wrote a script that theoretically does the following:
> Does a weekly full backup to a file
> Does a nightly differential backup to a file
> Does hourly transaction logs to a seperate file per hour.
> As I understand it, the differential is supposed to be based off of the
full
> backup. After the first nightly, is each differential still based off of
> the original full back up? or is it based of the previous nights
> differential, which means I'd have to keep each daily differential until
the
> week cycled
> Thanks,
> Gary
>|||Thanks.
I was pretty sure that's how it worked, I just wanted a second opinion
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%231UIe4p6DHA.3648@.TK2MSFTNGP11.phx.gbl...
> A diff backup is always based on the latest full backup. This is why you
do
> the restore like: latest full backup, *latest* diff backup and each
> subsequent log backups.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Gary" <n> wrote in message
news:%23mUwyzp6DHA.1556@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I wrote a script that theoretically does the following:
> >
> > Does a weekly full backup to a file
> > Does a nightly differential backup to a file
> > Does hourly transaction logs to a seperate file per hour.
> >
> > As I understand it, the differential is supposed to be based off of the
> full
> > backup. After the first nightly, is each differential still based off
of
> > the original full back up? or is it based of the previous nights
> > differential, which means I'd have to keep each daily differential until
> the
> > week cycled
> >
> > Thanks,
> > Gary
> >
> >
>
Differntial Backup doesn't work
work. The backup file added up 1G in every back up and
end up with running out of space.
Can anybody tell me what's wrong?
Frankprobably you are adding backup into existing backup over and over, causing
it increases until no more empty sapce. You need to review your backup
strategy.
"Frank Jiang" <frank@.discussions.microsoft.com> wrote in message
news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
quote:|||I do a complete backup once eveyday and differential
> I setup Differntial Backup through EM but it doesn't
> work. The backup file added up 1G in every back up and
> end up with running out of space.
> Can anybody tell me what's wrong?
> Frank
backup every 20 minutes, append to media.
I check all the setting of the differential backup in
Enterprise Manager and didn't find anything wrong.
Frank
quote:
>--Original Message--
>probably you are adding backup into existing backup over
and over, causing
quote:
>it increases until no more empty sapce. You need to
review your backup
quote:
>strategy.
>"Frank Jiang" <frank@.discussions.microsoft.com> wrote in
message
quote:|||with differential backup you don't have the option to restore DB to a
>news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
>
>.
>
certain time.
Can you do differential backup every 4 hrs and do log backup every 20
minutes to a separate backup device? It may help.
"Frank J" <frank@.discussions.microsoft.com> wrote in message
news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...[QUOTE]
> I do a complete backup once eveyday and differential
> backup every 20 minutes, append to media.
> I check all the setting of the differential backup in
> Enterprise Manager and didn't find anything wrong.
> Frank
>
> and over, causing
> review your backup
> message|||It never happened so far that we need to restore DB. But
theoretically, we can resote DB from a complete backup
and on top of that we do recover from differential
backup. Thats why we do a complete backup everyday and
followed by 20 minutes differential backup.
Transaction log is huge and should we backup it?
Frank
quote:
>--Original Message--
>with differential backup you don't have the option to
restore DB to a
quote:
>certain time.
>Can you do differential backup every 4 hrs and do log
backup every 20
quote:
>minutes to a separate backup device? It may help.
>
>"Frank J" <frank@.discussions.microsoft.com> wrote in
message
quote:|||from BOL: A differential backup creates a copy of all the pages in a
>news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
over[QUOTE]
in[QUOTE]
and[QUOTE]
>
>.
>
database modified after the last database backup.
so to restore you just need a full backup and the last differential backup.
You keep adding differential backup into same backup device every 20 minutes
then no wonder it keeps growing 'til you are out of space.
quote:
> Transaction log is huge and should we backup it?
depend on your needs. I always backup the log.
"Frank J" <anonymous@.discussions.microsoft.com> wrote in message
news:2cbc01c3e1e7$7aef6470$a101280a@.phx.gbl...[QUOTE]
> It never happened so far that we need to restore DB. But
> theoretically, we can resote DB from a complete backup
> and on top of that we do recover from differential
> backup. Thats why we do a complete backup everyday and
> followed by 20 minutes differential backup.
> Transaction log is huge and should we backup it?
> Frank
>
> restore DB to a
> backup every 20
> message
> over
> in
> and
Differntial Backup doesn't work
work. The backup file added up 1G in every back up and
end up with running out of space.
Can anybody tell me what's wrong?
Frankprobably you are adding backup into existing backup over and over, causing
it increases until no more empty sapce. You need to review your backup
strategy.
"Frank Jiang" <frank@.discussions.microsoft.com> wrote in message
news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
> I setup Differntial Backup through EM but it doesn't
> work. The backup file added up 1G in every back up and
> end up with running out of space.
> Can anybody tell me what's wrong?
> Frank|||I do a complete backup once eveyday and differential
backup every 20 minutes, append to media.
I check all the setting of the differential backup in
Enterprise Manager and didn't find anything wrong.
Frank
>--Original Message--
>probably you are adding backup into existing backup over
and over, causing
>it increases until no more empty sapce. You need to
review your backup
>strategy.
>"Frank Jiang" <frank@.discussions.microsoft.com> wrote in
message
>news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
>> I setup Differntial Backup through EM but it doesn't
>> work. The backup file added up 1G in every back up and
>> end up with running out of space.
>> Can anybody tell me what's wrong?
>> Frank
>
>.
>|||with differential backup you don't have the option to restore DB to a
certain time.
Can you do differential backup every 4 hrs and do log backup every 20
minutes to a separate backup device? It may help.
"Frank J" <frank@.discussions.microsoft.com> wrote in message
news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
> I do a complete backup once eveyday and differential
> backup every 20 minutes, append to media.
> I check all the setting of the differential backup in
> Enterprise Manager and didn't find anything wrong.
> Frank
> >--Original Message--
> >probably you are adding backup into existing backup over
> and over, causing
> >it increases until no more empty sapce. You need to
> review your backup
> >strategy.
> >
> >"Frank Jiang" <frank@.discussions.microsoft.com> wrote in
> message
> >news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
> >> I setup Differntial Backup through EM but it doesn't
> >> work. The backup file added up 1G in every back up and
> >> end up with running out of space.
> >>
> >> Can anybody tell me what's wrong?
> >>
> >> Frank
> >
> >
> >.
> >|||It never happened so far that we need to restore DB. But
theoretically, we can resote DB from a complete backup
and on top of that we do recover from differential
backup. Thats why we do a complete backup everyday and
followed by 20 minutes differential backup.
Transaction log is huge and should we backup it?
Frank
>--Original Message--
>with differential backup you don't have the option to
restore DB to a
>certain time.
>Can you do differential backup every 4 hrs and do log
backup every 20
>minutes to a separate backup device? It may help.
>
>"Frank J" <frank@.discussions.microsoft.com> wrote in
message
>news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
>> I do a complete backup once eveyday and differential
>> backup every 20 minutes, append to media.
>> I check all the setting of the differential backup in
>> Enterprise Manager and didn't find anything wrong.
>> Frank
>> >--Original Message--
>> >probably you are adding backup into existing backup
over
>> and over, causing
>> >it increases until no more empty sapce. You need to
>> review your backup
>> >strategy.
>> >
>> >"Frank Jiang" <frank@.discussions.microsoft.com> wrote
in
>> message
>> >news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
>> >> I setup Differntial Backup through EM but it doesn't
>> >> work. The backup file added up 1G in every back up
and
>> >> end up with running out of space.
>> >>
>> >> Can anybody tell me what's wrong?
>> >>
>> >> Frank
>> >
>> >
>> >.
>> >
>
>.
>|||from BOL: A differential backup creates a copy of all the pages in a
database modified after the last database backup.
so to restore you just need a full backup and the last differential backup.
You keep adding differential backup into same backup device every 20 minutes
then no wonder it keeps growing 'til you are out of space.
> Transaction log is huge and should we backup it?
depend on your needs. I always backup the log.
"Frank J" <anonymous@.discussions.microsoft.com> wrote in message
news:2cbc01c3e1e7$7aef6470$a101280a@.phx.gbl...
> It never happened so far that we need to restore DB. But
> theoretically, we can resote DB from a complete backup
> and on top of that we do recover from differential
> backup. Thats why we do a complete backup everyday and
> followed by 20 minutes differential backup.
> Transaction log is huge and should we backup it?
> Frank
> >--Original Message--
> >with differential backup you don't have the option to
> restore DB to a
> >certain time.
> >Can you do differential backup every 4 hrs and do log
> backup every 20
> >minutes to a separate backup device? It may help.
> >
> >
> >"Frank J" <frank@.discussions.microsoft.com> wrote in
> message
> >news:330801c3e1da$b4ee92c0$a501280a@.phx.gbl...
> >> I do a complete backup once eveyday and differential
> >> backup every 20 minutes, append to media.
> >> I check all the setting of the differential backup in
> >> Enterprise Manager and didn't find anything wrong.
> >>
> >> Frank
> >>
> >> >--Original Message--
> >> >probably you are adding backup into existing backup
> over
> >> and over, causing
> >> >it increases until no more empty sapce. You need to
> >> review your backup
> >> >strategy.
> >> >
> >> >"Frank Jiang" <frank@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:2b6401c3e1d6$bbf8c6c0$a301280a@.phx.gbl...
> >> >> I setup Differntial Backup through EM but it doesn't
> >> >> work. The backup file added up 1G in every back up
> and
> >> >> end up with running out of space.
> >> >>
> >> >> Can anybody tell me what's wrong?
> >> >>
> >> >> Frank
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >sql
Monday, March 19, 2012
differentials backups
file/device as the original backup? or can I make a
seperate file/device'not necessarily, you can create it on the seperate device as well.
--
- Vishal
"susan" <susanbauer@.yahoo.com> wrote in message
news:057801c37e23$7f417fe0$a301280a@.phx.gbl...
> Do differential backups have to be backed up into the same
> file/device as the original backup? or can I make a
> seperate file/device'
>
Sunday, March 11, 2012
Differential Backups Produce 55GB Files
I should mention that our full backup is typically 10GB, log file backups are typically 100 to 500MB, and the diff backup is generally 1GB to 3GB.
Has anyone experienced this issue before?
What causes it?
How do we resolve it?
Thank you in advance for your help,
Greg
if the log backups occurs every 1 hr you dont need to schedule the differential backup.......moreover check if any process is running at the time of backup......generally if you schedule the backups frequently the log files shudnt grow as high in your case........|||
I'm not worries about the size of the log files. The logs are fine. This post regards the size of the differential backup file itself (55GB). We don't really need it, true, but its much easier to restore one diff backup over six logs in my humble opinion, but this is really beside the point.
|||Was there any unusual activity over the weekend? Someone doing a bunch of bulks loads or index rebuilds for instance?|||Are you appending your differential backups to the same file, or creating a new file for each differential backup?|||Yes. Over the weekend we usually do an index rebuild, and some heavy inserts. Our database is mirrored as well, so we're not able to actually do the heavy inserts as a true bulk insert.|||Each backup creates a new file. A secondary process on another machine copies the files over to another server.|||most probably this is coz of the activities in the weekend. When u rebiult index it take extraspace to create the fresh index . the existing index are not overwritten beacuse if system needs the existing index to rollback if necessary. Differential backup take the backup of all the extents changed from last full backup. So what i would prefer is... take full backup after the weekend maintenance instead of Differential(if you have maintenance window).
Madhu
Differential Backup Script
particular named file and to replace the existing file contents?
Here's an example:
BACKUP DATABASE pubs TO DISK='G:\Pubs.BAK' WITH INIT, DIFFERENTIAL
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<robin9876@.hotmail.com> wrote in message
news:1122993046.668119.116470@.o13g2000cwo.googlegr oups.com...
In SQL 2000 how can you script a differential backup to backup to a
particular named file and to replace the existing file contents?
Differential Backup Script
particular named file and to replace the existing file contents?Here's an example:
BACKUP DATABASE pubs TO DISK='G:\Pubs.BAK' WITH INIT, DIFFERENTIAL
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<robin9876@.hotmail.com> wrote in message
news:1122993046.668119.116470@.o13g2000cwo.googlegroups.com...
In SQL 2000 how can you script a differential backup to backup to a
particular named file and to replace the existing file contents?
Differential Backup Script
particular named file and to replace the existing file contents?Here's an example:
BACKUP DATABASE pubs TO DISK='G:\Pubs.BAK' WITH INIT, DIFFERENTIAL
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<robin9876@.hotmail.com> wrote in message
news:1122993046.668119.116470@.o13g2000cwo.googlegroups.com...
In SQL 2000 how can you script a differential backup to backup to a
particular named file and to replace the existing file contents?
Differential Backup Files
declare @.hour varchar(2), @.date varchar(8)
set @.hour = substring(convert(char(2), getdate(), 108), 1, 2)
set @.date = convert(varchar(8), getdate(), 112)
exec ('use master backup database xxx to disk = ''D:\backup\xxx_db_' + @.date + @.hour + '.bak '' with init')
Differential Backup File Size
files will remain, hours, days, weeks... before deletion.
With my differential backup jobs, I don't seem to have the ability to
control the residency of a differential backup file to disk, at least I am
not aware of that capability yet, and I am new to SQL Server, and curious as
all get out about its nice features. The differential backup file just
keeps growing and growing and growing. Right now, I manually take the large
file, relocate it to another folder, then when the next differential backup
file is written to the disk location, I then delete the removed large
differential file and continue to let the new backup set grow, repeating the
process as necessary.
Is there a better way to control this process or at least automate it
somehow with T-SQL statements so the large diff file will be transferred to
a new folder until a new file is successfully generated, then deleted. I do
understand the need to maintain the differential file in correspondence with
a current full backup file and corresponding transaction logs with a
corressponding differential file got recovery purpose.
Thanks for your assistance.
Hi,
This is what
You can create two backup jobs for the differential Backups.
Let one run on Mon - Wed - Fri and the other run on Tue - Thu - Sat
On Sunday you can schedule a complete backup.
In Each of these Jobs add one more step that would get executed only if the
preceeding backup step was successful.
Let this new step be of "Operating System Command"
In the Process add the OS command to delete the file from the previous
day's differential backup or even copy the file to a different location.
You can have two separate folders, one each for the Backup Job.
Folder A for Job A and Folder B for job B
When you execute Job A on Monday, let the next step delete/move the backup
file located in the Folder B, after the diff backup is over.
Similarly when you execute Job B on Tuesday, let the next step delete/move
the backup file located in the Folder A, after the diff backup is over.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||That gives a direction to pursue, thank you.
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:i81O3gMMEHA.3364@.cpmsftngxa10.phx.gbl...
> Hi,
> This is what
> You can create two backup jobs for the differential Backups.
> Let one run on Mon - Wed - Fri and the other run on Tue - Thu - Sat
> On Sunday you can schedule a complete backup.
> In Each of these Jobs add one more step that would get executed only if
the
> preceeding backup step was successful.
> Let this new step be of "Operating System Command"
> In the Process add the OS command to delete the file from the previous
> day's differential backup or even copy the file to a different location.
> You can have two separate folders, one each for the Backup Job.
> Folder A for Job A and Folder B for job B
> When you execute Job A on Monday, let the next step delete/move the backup
> file located in the Folder B, after the diff backup is over.
> Similarly when you execute Job B on Tuesday, let the next step delete/move
> the backup file located in the Folder A, after the diff backup is over.
> HTH
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
Differential Backup file fails to restore.
We are doing the following steps:-
1. Weekly Full Bckup
2.Daily Differential backup
3.hr Log back up
The following command works fine for all the Backups. it means backup file is fine.
RESTORE FILELISTONLY from DISK = 'D:\Backup\new\DB_ full.BAK'
RESTORE HEADERONLY FROM DISK = 'D:\Backup\new\DB_ ull.BAK'
RESTORE LABELONLY FROM DISK = 'D:\Backup\new\Test_full.BAK'
RESTORE VERIFYONLY FROM DISK = 'D:\Backup\new\Test_full.BAK'
Also The full back up restoration works fine:
RESTORE DATABASE Test3 FROM DISK = 'D:\Backup\new\Test_full.BAK'
WITH MOVE 'Test2_Data' TO 'F:\MSSQL2K\MSSQL\data\Test2Net_Data.MDF',
MOVE 'Test2_Log' TO 'F:\MSSQL2K\MSSQL\data\Test2Net_Log.LDF',
NORECOVERY
GO
/* RESULT :
Processed 1032 pages for database 'Test3', file 'test2_Data' on file 1.
Processed 1 pages for database 'Test3', file 'test2_Log' on file 1.
RESTORE DATABASE successfully processed 1033 pages in 1.907 seconds (4.433 MB/sec).
*/
But While restoring the Differential file it throws error:
RESTORE DATABASE Test3 from DISK = 'D:\Backup\new\test2_Diff1.bak'
WITH NORECOVERY
GO
Msg 3136, Level 16, State 0, Line 1
Cannot apply the backup on device 'D:\Backup\new\test2_Diff1.bak' to database 'Test3'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I checked the SQL Server Log no error msg corresponding Differential backup restore.
I don't know how to proceed further.Can any one guide me how to over come this..
Thanks !
is the differential backup appended or overwritten ? ? ?........if appended choose the exact differential backup and try to restore....also try using GUI and see........|||Differential backup is a separate file...
Differential backup file almost as big as a full backup
slightly under 1.4G. The last full backup was Tuesday night. On Wednesday,
I did my first diffential backup, and the file size was 1.25G.
We are a very small company and there is little daily input. Yesterday, we
might have received a couple of checks, paid a couple bills, ordered a couple
parts, entered a few timecard transactions. So I really expected the
differential backup to show a substantial savings in disk space.
In looking for reasons:
1. The backup plan is put together using the maintenance plan wizard, and
one of the things it does is create separate files that bear the database
name, date and time. Is it possible that Sql 2005 doesn't realize that there
was a previous full backup if the differential is being put in a different
file?
2. As part of the nightly maintenance, I run tasks to check integrity,
rebuid indexes and update statistics. Could these tasks be making database
changes that would therefore cause practically the entire database to be
included in the differential backup, even though the actual data has not
changed?I would suggest that you take a copy of the database and run some backups
manually. Your logic is sound, but your results don't make sense.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:330C9832-3606-4037-8CB5-F01BDFE8C505@.microsoft.com...
> We've been during daily full backups, with the size of the bak files at
> slightly under 1.4G. The last full backup was Tuesday night. On
> Wednesday,
> I did my first diffential backup, and the file size was 1.25G.
> We are a very small company and there is little daily input. Yesterday,
> we
> might have received a couple of checks, paid a couple bills, ordered a
> couple
> parts, entered a few timecard transactions. So I really expected the
> differential backup to show a substantial savings in disk space.
> In looking for reasons:
> 1. The backup plan is put together using the maintenance plan wizard, and
> one of the things it does is create separate files that bear the database
> name, date and time. Is it possible that Sql 2005 doesn't realize that
> there
> was a previous full backup if the differential is being put in a different
> file?
> 2. As part of the nightly maintenance, I run tasks to check integrity,
> rebuid indexes and update statistics. Could these tasks be making
> database
> changes that would therefore cause practically the entire database to be
> included in the differential backup, even though the actual data has not
> changed?|||Bev,
Yes, rebuilding indexes updates your database. So every page (or is it
extent) that is updated by the process will be backed up. Doing all of that
nightly is not compatible with getting small differential backups.
With very low velocity of data change, the nightly index rebuilds should not
be necessary.
RLF
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:330C9832-3606-4037-8CB5-F01BDFE8C505@.microsoft.com...
> We've been during daily full backups, with the size of the bak files at
> slightly under 1.4G. The last full backup was Tuesday night. On
> Wednesday,
> I did my first diffential backup, and the file size was 1.25G.
> We are a very small company and there is little daily input. Yesterday,
> we
> might have received a couple of checks, paid a couple bills, ordered a
> couple
> parts, entered a few timecard transactions. So I really expected the
> differential backup to show a substantial savings in disk space.
> In looking for reasons:
> 1. The backup plan is put together using the maintenance plan wizard, and
> one of the things it does is create separate files that bear the database
> name, date and time. Is it possible that Sql 2005 doesn't realize that
> there
> was a previous full backup if the differential is being put in a different
> file?
> 2. As part of the nightly maintenance, I run tasks to check integrity,
> rebuid indexes and update statistics. Could these tasks be making
> database
> changes that would therefore cause practically the entire database to be
> included in the differential backup, even though the actual data has not
> changed?|||That is just what my tests have shown me. I have removed that task from the
plan and hope to see better results in the morning. There was no particular
reason I was doing that anyway, just an erroneous belief that you can't have
too much of a good thing.
"Russell Fields" wrote:
> Bev,
> Yes, rebuilding indexes updates your database. So every page (or is it
> extent) that is updated by the process will be backed up. Doing all of that
> nightly is not compatible with getting small differential backups.
> With very low velocity of data change, the nightly index rebuilds should not
> be necessary.
> RLF
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:330C9832-3606-4037-8CB5-F01BDFE8C505@.microsoft.com...
> > We've been during daily full backups, with the size of the bak files at
> > slightly under 1.4G. The last full backup was Tuesday night. On
> > Wednesday,
> > I did my first diffential backup, and the file size was 1.25G.
> > We are a very small company and there is little daily input. Yesterday,
> > we
> > might have received a couple of checks, paid a couple bills, ordered a
> > couple
> > parts, entered a few timecard transactions. So I really expected the
> > differential backup to show a substantial savings in disk space.
> >
> > In looking for reasons:
> > 1. The backup plan is put together using the maintenance plan wizard, and
> > one of the things it does is create separate files that bear the database
> > name, date and time. Is it possible that Sql 2005 doesn't realize that
> > there
> > was a previous full backup if the differential is being put in a different
> > file?
> >
> > 2. As part of the nightly maintenance, I run tasks to check integrity,
> > rebuid indexes and update statistics. Could these tasks be making
> > database
> > changes that would therefore cause practically the entire database to be
> > included in the differential backup, even though the actual data has not
> > changed?
>
>
Friday, March 9, 2012
Differential backup "standard" file extension?
extensions for Differential backups? Like a BAK file is a
Full BAckup and a TRN is a Trans Log backup. I'm using a
DIF filename extension currently, but would like to use a
common file extension, if there is one?!? THanks, BruceI believe it's probably just .bak but your way sounds great
for easily identifying differential backups. As with the data
and log files themselves you can give them any extension
you want and they will work - MDF/NDF/LDF are just
the defaults.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:017801c34bb2$d906f6c0$a101280a@.phx.gbl...
Hello. Just wondering if there's a MS standard for file
extensions for Differential backups? Like a BAK file is a
Full BAckup and a TRN is a Trans Log backup. I'm using a
DIF filename extension currently, but would like to use a
common file extension, if there is one?!? THanks, Bruce|||THanks Jasper. Yes, was jsut looking for standard
defaults, like MS does with BAK and TRN. I wouldn't want
to call a Differential backup and BAK file... ok, new
standard>> DIF... ha... THanks, Bruce
>--Original Message--
>I believe it's probably just .bak but your way sounds
great
>for easily identifying differential backups. As with the
data
>and log files themselves you can give them any extension
>you want and they will work - MDF/NDF/LDF are just
>the defaults.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:017801c34bb2$d906f6c0$a101280a@.phx.gbl...
>Hello. Just wondering if there's a MS standard for file
>extensions for Differential backups? Like a BAK file is a
>Full BAckup and a TRN is a Trans Log backup. I'm using a
>DIF filename extension currently, but would like to use a
>common file extension, if there is one?!? THanks, Bruce
>
>.
>
Different version of SQL Server (DTS)
Server Enterprise Manager 2000 (Based Version 8.00.194) on Windows XP Service
Pack 1 would run using SQL Server DTSRun.exe (Based Version 8.00.194) on
Windows 2000 Server SP3, if I can confirm that the DTS packages worked O on
DTSRUN.exe (Based version 8.00.194) on the same Windows XP Professional SP1
workstation?
Furthermore, if the DTSClients are upgraded later to SP2 or SP3, would there
be much issues?
Am I also right in thinking that the DTSRun.exe version can be of a
different (lower) version from the SQL Server 2000 database to which the DTS
packages will access and update?
That worked for me.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Patrick" <questions@.newsgroup.nospam> schrieb im Newsbeitrag
news:BE1EF7BB-F9FF-4AD6-9D3F-5D4A919BA10D@.microsoft.com...
> Can I get high confidence that DTS packages (Saved as a *.DTS File) using
> SQL
> Server Enterprise Manager 2000 (Based Version 8.00.194) on Windows XP
> Service
> Pack 1 would run using SQL Server DTSRun.exe (Based Version 8.00.194) on
> Windows 2000 Server SP3, if I can confirm that the DTS packages worked O
> on
> DTSRUN.exe (Based version 8.00.194) on the same Windows XP Professional
> SP1
> workstation?
> Furthermore, if the DTSClients are upgraded later to SP2 or SP3, would
> there
> be much issues?
> Am I also right in thinking that the DTSRun.exe version can be of a
> different (lower) version from the SQL Server 2000 database to which the
> DTS
> packages will access and update?
|||> Can I get high confidence that DTS packages (Saved as a *.DTS File) using
> SQL
> Server Enterprise Manager 2000 (Based Version 8.00.194) on Windows XP
> Service
> Pack 1 would run using SQL Server DTSRun.exe (Based Version 8.00.194) on
> Windows 2000 Server SP3, if I can confirm that the DTS packages worked O
> on
> DTSRUN.exe (Based version 8.00.194) on the same Windows XP Professional
> SP1
> workstation?
Yes
> Furthermore, if the DTSClients are upgraded later to SP2 or SP3, would
> there
> be much issues?
No
> Am I also right in thinking that the DTSRun.exe version can be of a
> different (lower) version from the SQL Server 2000 database to which the
> DTS
> packages will access and update?
Yes, you're right.
Regards,
Kevin
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BE1EF7BB-F9FF-4AD6-9D3F-5D4A919BA10D@.microsoft.com...
> Can I get high confidence that DTS packages (Saved as a *.DTS File) using
> SQL
> Server Enterprise Manager 2000 (Based Version 8.00.194) on Windows XP
> Service
> Pack 1 would run using SQL Server DTSRun.exe (Based Version 8.00.194) on
> Windows 2000 Server SP3, if I can confirm that the DTS packages worked O
> on
> DTSRUN.exe (Based version 8.00.194) on the same Windows XP Professional
> SP1
> workstation?
> Furthermore, if the DTSClients are upgraded later to SP2 or SP3, would
> there
> be much issues?
> Am I also right in thinking that the DTSRun.exe version can be of a
> different (lower) version from the SQL Server 2000 database to which the
> DTS
> packages will access and update?
|||Why have you not applied any services packs? Build 8.00.194 works, but there
are of course bugs otherwise MS would not have released 4 service packs
since then. You should apply service packs to clients as well as the server,
so that you get the benefit for the client components such as DTS.
Darren Green
http://www.sqldts.com
http://www.sqlis.com
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:eAsFI7oWFHA.1240@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
using[vbcol=seagreen]
> Yes
>
> No
>
> Yes, you're right.
> Regards,
> Kevin
> "Patrick" <questions@.newsgroup.nospam> wrote in message
> news:BE1EF7BB-F9FF-4AD6-9D3F-5D4A919BA10D@.microsoft.com...
using
>