Basic Question - Push & Pull subscription

Paul/Hillary;
Scenario:
servers in the same domain:
Create the publisher/distributor
Create publication "MyPublication"
Create push subscription to Server B "PushToB"
Logon onto Server B
Create Pull Subscription "PullFromA"
Cannot create Pull Subscription...
A quick google search and I find
"When you create a pull subscription and a push subscription for the
publication already exists for the Subscriber, an error message informs you
that the push subscription already exists and that you should drop any push
subscriptions before proceeding. When you create a pull subscription, and
another pull subscription to the same publication already exists, you will be
required to drop the existing subscription before adding the new one unless
the first subscription has expired."
Logon to Publisher
Delete Push subscription "PushToB" on publisher
logon to subscriber
Create Pull subscription "PullFromA" on subscriber
logon to publisher
Create Push subscription "PushToB" on publisher
Push subscription fails....
When I create a publication for each on the publisher, it works.
What am I missing?
MJ
Hi Pual;
No, you are dealing with ignorance on my end (thank you for your patience).
Context:
I need to changes at the publisher to push to the subscriber
I then need changes at the subscriber to push to the publisher.
Test envrironment Solution:
I set up a merge publication on the publisher then loged onto the
subscriber and created a pull subscription. Every thing is fine.
this is sql server 2000.
My biggest challenge has been reading so many sources and I am getting
confused:
Microsofts "Patterns and Practices" Data Patterns
Microsoft's High Availablitity Volume 1 and Volume 2
Hillary's "Transaction ad Snapshot"
I even pleaded Hillary to publish his promised 2nd book on Merge Replication.
"Paul Ibison" wrote:

> I'm not sure I follow. You are restricted from having a pull and push on the
> subscriber to the same publication and the same subscriber database. Your
> scenario arrives at this same point each time but in a different order
> (pushtob is still created after pullfroma) - or am I misreading something
> here.
> Cheers,
> Paul Ibison
>
>
|||Question: Merge or Transactional bidirectional replication?
I am looking for a Bi-directional merge replication solution
Does the subscriber go offline? Yes
Does the subscriber modify the same data as the publisher? Yes
What happens when they conflict (assuming the subscriber isn't connected to
the publisher)? Publisher has ultimate determination.
How many subscribers do you have?
A vague answer is 12
A better answer is I use a filter and create a separate publication for each.
Do the subscribers need to be distinguished? Yes
"Paul Ibison" wrote:

> OK - I see. The term Push and Pull refer to where the work is being done
> (simplified a bit). The actual work remains the same, so you need either a
> push or a pull subscription but not both. If you want bidirectional
> replication then the choice usually is between merge and transactional. The
> latter can be queued, immediate updating or bidirectional or peer-to-peer.
> Many posibilities here. Some questions will help narrow down a bit. Does the
> subscriber go offline? Does the subscriber modify the same data as the
> publisher? What happens when they conflict (assuming the subscriber isn't
> connected tot he publisher). How many subscribers do you have? Do the
> subscribers need to be distinguished?
> If you have clear answers to these questions and hopefully I've not left too
> many out, then we can probably tell you exactly what type of replication you
> need
> HTH,
> Paul Ibison
>
>
|||Thank you for patience and dedication from a dedicated Ibison and Cotter
follower.
MJ
"Paul Ibison" wrote:

> OK - it sounds like merge is your best option then. You don't necessarily
> need a separate publication per subscriber - dynamic subscriptions might do
> the trick for you.
> HTH,
> Paul Ibison
>
>
>

Basic data type question

What type of field do I create for OLE objects? (text,
ntext...) What field size?
thksJanice,
text,ntext and image sare the BLOB data types in SQL Server.ntext can
support unicode data and thats the only difference between ntext and text
while image datatype is used to store binary data .text and image support
upto 2GB and there isnt any need to specify the field size while ntext, in
terms of storage size, is two times the number of characters entered.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Janice Gilbert" <jgilburt@.yahoo.com> wrote in message
news:080501c33f26$d85d0db0$a001280a@.phx.gbl...
> What type of field do I create for OLE objects? (text,
> ntext...) What field size?
> thks

basic crystal

hy,

i know i work in c# and this is vb, but....

i am trying to create a very simple crystal report, with data from a database.
everything seems to work just fine, i have my data sorces in crystal, i have the fiels.... but when i run the application the report is empty, the fields from the database are empty :(

pls help

thank you very muchImpossible to say without knowing what is the type of the datasource, where the files or databases are located, how you call the report from the application, what parameters or selectionformula you pass etc. It's like writing a letter to car support "help my car won't start, what do I do to make it start".
There should be some code samples for C# in Crystal directory, maybe those will help? You can also check the Knowledge base at businessobjects.com|||so

i work with c#2005 and sql server express 2005

the database is a sql server, created from visual c# (through add conection, in server explorer, with a new data base).
database location : the win application directory
i call the report through a crystal report viewer
the report contains only a few fields from a table of the database

i can add that everything else works fine (datagridview, updates)

thank you very much

i attched the solution|||the solution|||the db|||Have you tried using SQL Profiler to find out what happens between SQL server and the client at run time? This should help you isolate the cause of problem, whether is it bad query or no query at all.|||thank you

your hint to search in examples worked.
i found out that i was not giving data to my report because of the misssing code for filling the tableadaptors that the report needed.

thank you very much

sorry for the delay

Base Database implementation

I am currently looking to buy or build software that enables my firm to:

Run a set of scripts to create a base database - empty database with minimum amounts of data (i.e. lookup data and seed data). So that when a new development project begins all that is needed to build the database is to run these scripts.

The challenges we face is managing these scripts. It would be easier from a manual perspective to only have 5-6 scripts, so that we do not need to manually open and execute each script (representing an object). The problem with this is when you modify a stored procedure, you modify it in your version control platform (which is single object based) and then you need to find and replace the object in your script.

What we are looking to do is buy or build an application to manage these files for us. I was wondering if anyone has purchased a solution to accomodate this?

We currently have ApexSQL Diff - which works well to compare contents of databases (structurally it does well, seems to have a problem comparing data but that is another discussion). What it does not do well is script out an entire database, and if it were to do this, it would put it all in one file.The aPress book Real-World SQL-DMO for SQL-Server, by Mitchell & Allison, describes (and includes source on CD) the VB code to perform this very task. You would create your "empty" database once. Then, run the tool against the database to script, not only the database structure, but the data as well.

Then, just run the script on a new server to create your database.|||Thanks I just purchased that book based on your advice, we had a meeting to further discuss, this is what we came up with:

I. Structural
We agreed using a tool like Erwin is the best approach, have 1 point person (with a backup in place) that would own the database. When development teams are ready to merge up they would tell the point person which DB their project uses. The point person would then use Erwin to merge their structural changes into the master data model. Once that is complete, scripts can easily be generated to serve as master install scripts for new projects into empty DBs.

II. Functional
Currently we use subversion to store our views, UDFs and stored procedures. Regarding this piece we discussed creating a simple program to manage the creation of these scripts using the individual Subversion files as the repository. I purchased a book that comes with a CD that has VB code that utilizes the SQL DMO in order to create the necessary scripts. Ideally we would want an application that takes an INI type file with a list of objects and generates the necessary files to create those objects. This could be used in two situations the first would serve as a solution to the base DB install for UDFs, Views and Stored Procs. The second situation would be to aid in managing changes in the Development environment when we need to push changes to QA in the form of install scripts. Currently we have a very tedious process of generating the necessary SQL per object and having to find the object in our original install script, delete what is in the script and paste our new code into the script. By having an application in place to automate this process would be a great aid in generating the Base DB scripts and even more so with regards to ongoing developing.

III. Data
Depending on how the Functional phase described above goes (the aforementioned book also has code to perform similar actions on data) we would look to apply similar functionality to seed the Data. As an alternative we could look at automation of BCP file creation. There are two types of data mandatory lookup data that would also be used for QA / Production releases, and non-mandatory data, i.e. seed data, that would be used to improve basic functionality of the 3 sites.

IV. Test Scripts
This portion would need to be revisited when the time is appropriate I would like to look at solutions that would help in automating test scripts. Ideally the purpose of this phase would be to ensure all three sites are working post-merge. The easiest way to ensure proper functionality is to run automated test scripts that can return a PASS or FAIL result, rather then for each project having to manually follow a test plan.

Barcode font "shrinks" during PDF export

Hi all,

I'm using a free barcode font so i can create scannable tickets via reporting services 2k5. When I print the tickets, everything seems fine. But when i export to PDF though, it looks like the barcode font shrunk. All the lines are pulled together, making scanning impossible. Is there a certain setting or so that i can use to ensure the font's width ?

-Update-
The weird thing is: when i export it inside visual studio, the barcode is shown as it is supposed to in the pdf !?

Thanks in advance!

Hi,

I am facing the same issue.

Microsoft please reply.

Regards and TIA,

Ashish

|||Did you try to change the Report Properties to set the margin to 0 ?
And make sure your design area does not exceed the paper size set in the property box and exactly fit the paper format on the printer?
I am myself having trouble with pdf page exported from ReportManager not be not too accurate. Kind of a hit and miss trial and error page set-up formating process until it gets right.
Reporting Services is not doing an excellent job at printing things, not from a typography point of view.
Any improvement would be more than welcome.
Philippe|||

sorry for the late reply !

yep i tried that, but hlas, no changes :(

|||

Hi,

I hate to say this, but we really did nothing to resolve the issue. Nor did I get any replies for this issue.

We just restarted the SSRS server for some reason and after that we found that it was working fine. I would suggest that you give it a shot restart the server and it may click in your case also.

HTH

Regards,

Ashish

|||

I have seen several barcode fonts that do not include the correct spacing or at least do not report the correct spacing through the Win32 APIs our PDF renderer calls. It's also very possible we have a bug in that area.

If you file a bug at http://connect.microsoft.com and include the rdl and font, I'll gladly take a look.

Thanks,

Donovan Smith

|||

In my own experience, the barcod39.ttf TrueType font renders properly in every renderer except pdf. In pdf, it appears to arbitrarily expand spaces to fill the control.

I'd love to see that fixed.

Tim Bailey
Energy Laboratories, Inc.

Barcode font "shrinks" during PDF export

Hi all,

I'm using a free barcode font so i can create scannable tickets via reporting services 2k5. When I print the tickets, everything seems fine. But when i export to PDF though, it looks like the barcode font shrunk. All the lines are pulled together, making scanning impossible. Is there a certain setting or so that i can use to ensure the font's width ?

-Update-
The weird thing is: when i export it inside visual studio, the barcode is shown as it is supposed to in the pdf !?

Thanks in advance!

Hi,

I am facing the same issue.

Microsoft please reply.

Regards and TIA,

Ashish

|||Did you try to change the Report Properties to set the margin to 0 ?
And make sure your design area does not exceed the paper size set in the property box and exactly fit the paper format on the printer?
I am myself having trouble with pdf page exported from ReportManager not be not too accurate. Kind of a hit and miss trial and error page set-up formating process until it gets right.
Reporting Services is not doing an excellent job at printing things, not from a typography point of view.
Any improvement would be more than welcome.
Philippe
|||

sorry for the late reply !

yep i tried that, but hlas, no changes :(

|||

Hi,

I hate to say this, but we really did nothing to resolve the issue. Nor did I get any replies for this issue.

We just restarted the SSRS server for some reason and after that we found that it was working fine. I would suggest that you give it a shot restart the server and it may click in your case also.

HTH

Regards,

Ashish

|||

I have seen several barcode fonts that do not include the correct spacing or at least do not report the correct spacing through the Win32 APIs our PDF renderer calls. It's also very possible we have a bug in that area.

If you file a bug at http://connect.microsoft.com and include the rdl and font, I'll gladly take a look.

Thanks,

Donovan Smith

|||

In my own experience, the barcod39.ttf TrueType font renders properly in every renderer except pdf. In pdf, it appears to arbitrarily expand spaces to fill the control.

I'd love to see that fixed.

Tim Bailey
Energy Laboratories, Inc.

Barcode font "shrinks" during PDF export

Hi all,

I'm using a free barcode font so i can create scannable tickets via reporting services 2k5. When I print the tickets, everything seems fine. But when i export to PDF though, it looks like the barcode font shrunk. All the lines are pulled together, making scanning impossible. Is there a certain setting or so that i can use to ensure the font's width ?

-Update-
The weird thing is: when i export it inside visual studio, the barcode is shown as it is supposed to in the pdf !?

Thanks in advance!

Hi,

I am facing the same issue.

Microsoft please reply.

Regards and TIA,

Ashish

|||Did you try to change the Report Properties to set the margin to 0 ?
And make sure your design area does not exceed the paper size set in the property box and exactly fit the paper format on the printer?
I am myself having trouble with pdf page exported from ReportManager not be not too accurate. Kind of a hit and miss trial and error page set-up formating process until it gets right.
Reporting Services is not doing an excellent job at printing things, not from a typography point of view.
Any improvement would be more than welcome.
Philippe|||

sorry for the late reply !

yep i tried that, but hlas, no changes :(

|||

Hi,

I hate to say this, but we really did nothing to resolve the issue. Nor did I get any replies for this issue.

We just restarted the SSRS server for some reason and after that we found that it was working fine. I would suggest that you give it a shot restart the server and it may click in your case also.

HTH

Regards,

Ashish

|||

I have seen several barcode fonts that do not include the correct spacing or at least do not report the correct spacing through the Win32 APIs our PDF renderer calls. It's also very possible we have a bug in that area.

If you file a bug at http://connect.microsoft.com and include the rdl and font, I'll gladly take a look.

Thanks,

Donovan Smith

|||

In my own experience, the barcod39.ttf TrueType font renders properly in every renderer except pdf. In pdf, it appears to arbitrarily expand spaces to fill the control.

I'd love to see that fixed.

Tim Bailey
Energy Laboratories, Inc.

Bar Charts in Reporting Services

Hi,
can any one help me about bar charts.how to create bar charts in
reporting services.
Thanks in advance
Malli"How to add, move, or delete a chart (Report Designer)" in Reporting Services
Books Online would be a good place to start.
"Malli" wrote:
> Hi,
> can any one help me about bar charts.how to create bar charts in
> reporting services.
>
> Thanks in advance
> Malli

Bar chart with row data

Hi,
I have a dataset with values for each month.
For example:
Jan Feb Mar Apr May Jun Jul
1200 1500 1420 1540 1450 1470 1467
I would like to create a Bar chart from this data. Is there a way to supply
the row as a source data? Since my data is in 12 columns, i have to add each
column as a data field to the chart and all bars show up in different colors
because of this.
Is there a way to provide the row a single series?
Thanks,If you have RS 2000 SP1 or SP2 installed, you could set the color to the
same identical value for all values (under appearance - series styles).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prash" <Prash@.discussions.microsoft.com> wrote in message
news:06121C94-6D66-4BD6-9AFC-CA3E787723C7@.microsoft.com...
> Hi,
> I have a dataset with values for each month.
> For example:
> Jan Feb Mar Apr May Jun Jul
> 1200 1500 1420 1540 1450 1470 1467
> I would like to create a Bar chart from this data. Is there a way to
> supply
> the row as a source data? Since my data is in 12 columns, i have to add
> each
> column as a data field to the chart and all bars show up in different
> colors
> because of this.
> Is there a way to provide the row a single series?
> Thanks,

Bar chart target value

Hi, I need to create a bar chart with values for quality indicators. Is there
a way to draw a horizontal line to indicate target or min/max value in RS
2000 (2005)?You could just add another data series and plot that series as line (select
the "plot data as line" checkbox on the appearance tab). Set the data value
expression a "constant" value e.g. =150 and set the BorderColor property
accordingly.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mattsson" <Mattsson@.discussions.microsoft.com> wrote in message
news:1EBA6156-A090-455E-A348-9FB40F344CD9@.microsoft.com...
> Hi, I need to create a bar chart with values for quality indicators. Is
> there
> a way to draw a horizontal line to indicate target or min/max value in RS
> 2000 (2005)?
>sql

Bar and Line Chart

Anyone know how to create a chart that has both a bar and line chart format. I'm using crystal reports XI.Or does anyone know how to create any kind of combination chart in crystal reports?

BAFFLED! Why won't this code work?!?!

Hello,
The below code is intended to create a sp, then loop through a set of
values, each time running the sp with the new value. The CREATE PROC
executes fine, but I get an error when attempting the loop: "Invalid
column name 'tblIR_LB_2'."
Your help appreciated!
CREATE PROC sp_CreateIRTables ( @.tbl smallint)
AS
DECLARE @.SQL varchar(999)
SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
[dteDate] [smalldatetime] NULL ,
[sglSTDEV(upper)] [float] NULL ,
[sglSTDEV(lower)] [float] NULL ,
[intMA] [smallint] NULL ,
[dblPL] [float] NULL ,
[dblSTDEV] [float] NULL ,
[dblIR] [float] NULL ,
[intTotalDays] [int] NULL ,
[intExposure] [int] NULL ) ON [PRIMARY]'
EXEC (@.SQL)
DECLARE @.i smallint
SET @.i = 1
WHILE (@.i <= 32)
BEGIN
EXECUTE sp_CreateIRTables @.i
SET @.i = @.i + 1
END> CREATE PROC sp_CreateIRTables ( @.tbl smallint)
DON'T USE sp_ PREFIX!

> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
Is it @.tbl or @.tblname? Pick one.|||> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
@.tblname is, for some reason, a SMALLINT, not any kind of string.
First, I suggest not using this kind of unmanageable scheme.
But to solve your issue in the short term, you can convert to a character
type, e.g.
SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + CONVERT(VARCHAR(12), @.tblname)
+ '] (|||You can't have a variable in the table name of a CREATE TABLE unless you use
dynamic SQL to create
the table. Why not create a temp table instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Butaambala" <brogers75@.yahoo.com> wrote in message
news:1131645127.077192.239150@.f14g2000cwb.googlegroups.com...
> Hello,
> The below code is intended to create a sp, then loop through a set of
> values, each time running the sp with the new value. The CREATE PROC
> executes fine, but I get an error when attempting the loop: "Invalid
> column name 'tblIR_LB_2'."
> Your help appreciated!
>
> CREATE PROC sp_CreateIRTables ( @.tbl smallint)
> AS
> DECLARE @.SQL varchar(999)
> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
> [dteDate] [smalldatetime] NULL ,
> [sglSTDEV(upper)] [float] NULL ,
> [sglSTDEV(lower)] [float] NULL ,
> [intMA] [smallint] NULL ,
> [dblPL] [float] NULL ,
> [dblSTDEV] [float] NULL ,
> [dblIR] [float] NULL ,
> [intTotalDays] [int] NULL ,
> [intExposure] [int] NULL ) ON [PRIMARY]'
> EXEC (@.SQL)
>
> DECLARE @.i smallint
> SET @.i = 1
> WHILE (@.i <= 32)
> BEGIN
> EXECUTE sp_CreateIRTables @.i
> SET @.i = @.i + 1
> END
>

Bad data

Some knuckleheads have used a cutom indexing app to create several thousand records with this problem: The indexing app permitted field entries longer than the db receiving the index. IE, a 100-char entry when the DB only accepted 50 characters. The app had no safeguard against this error.

Does Oracle or PL/SQL have a function that will allow me to select all records where the content exceeds a certain number of characters in length? Actually in this case it would be better to find strings exactly 50 chars long. The data has been imported to our Oracle db, and I need to find these bad records!Try:

select * from myTable
where length(col_x) = 50;

This will list all rows where col_x is 50 characters long.

:cool:|||That's it! Thanks.

bp

Backups: Syncing File and Database backups

Hi,
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
RossHi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:

> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or i
s
> there a better way to do this?
> Thanks!
> Ross

Backups: Syncing File and Database backups

Hi,
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
Ross
Hi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:

> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or is
> there a better way to do this?
> Thanks!
> Ross

Backups from SQLcmd ?

I am working with SQL Express and found myself surprised when I tried to automate a backup... No Agent.

So I am trying to create a simple backup script to run from the command line and probably schedule through the scheduled task manager.

here is my problem. I get the following error :

Msg 2812, Level 16, State 62, Server FIREFLY\SQLEXPRESS, Line 1
Could not find stored procedure 'B'.

Here is the batch file

sqlcmd -i c:\temp\test\DBbackup.sql -o c:\temp\test\output.txt -S FIREFLY\SQLEXPRESS

And here is the sql input file :

BACKUP DATABASE [DNNDEV] TO DISK = 'C:\temp\test\dnndev.bak' WITH NOFORMAT, NOINIT, NAME = 'dnndev-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

If I put a USE statement in front of my backup statement - the error message changes to

Could not find stored procedure 'U'.

So it would appear to list the first character encountered.

As a sanity check I created a similar process which does a select * from a table and I do not get an error saying :Could not find stored procedure 'S'. - instead all is well and I get my output and no error

If I try the backup command in SQLCMD interactively it works.

so what the heck am I missing ?

Thank you for any help...

What editor did you use to save the SQL file? Sounds like you generated a unicode file without BOM (byte order mark) so it is treated as ANSI file. Check the save options of your editor. Notepad for example has a combo box in its save dialog where you should choose ANSI. Unicode will save 2 or more byte per character. For standard letters it is often the ANSI code followed by a 0 byte, which is a terminator for ASCII c strings - so I guess sqlcmd stops after hitting the first 0 byte which follows your first letter.

--
SvenC

|||

Yep - That did it. Somehow my other test using the select statement must have been saved in ANSI - my backup file was saved in unicode

Thanks a million !

Backups and LSNs

Hi,
My question:
Is it possible to create the full backup so that taped copies can be
made and then rather than restoring the full backup on the target
machine, the incrementals resume and dovetail with the last
incremental prior to the full backup?
Current Scenario:
1. A sequence of incremental logs backed up and shipped to another
server (reporting) where they are restored (i.e. the target database
is in standby)
2. Each night the creation of incremental backups is suspended while a
full backup takes place. This full backup is also restored on the
other server.
3. Incrementals resume after a certain period of time which is
sufficient to allow the full backup to be restored.
4. The full backup is also copied to tape.
Motivation:
I want to avoid moving large backup files around on the network. I
want to decrease the time lag with the live data from the reporting
perspective. (I know about transactional replication and want/have to
stay with the current approach. Thanks)
Background:
There was an occasion when the full backup failed to move to the
target machine due to a space issue there. However, I noticed that the
incrementals post-(full backup) were still being copied across and
being restored even though the full backup didn't make it. This kind
of answers my question but the thing is that I was a bit confused at
the time and just wanted to get the problem sorted asap without doing
root cause analysis. Was it my imagination or it this possible?
I would have thought that the LSN count would have been reset by the
full backup. Here is a little pictorial of the sequence that I would
like.
Production Reporting
Box Box
... ...
i1 i1
i2 i2
i3 i3
fbup i4
i4 i5
i5 i6
... ...
Regards
Liam Caffrey
Liam,
If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the tlog
when you perform a database backup. This allow you for instance to pick you second newest db backup
and restore it and then apply *all* subsequent log backups.
In other words, a database backup does not break the sequence of transaction log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060154.7bc1bebf@.posting.google.c om...
> Hi,
> My question:
> Is it possible to create the full backup so that taped copies can be
> made and then rather than restoring the full backup on the target
> machine, the incrementals resume and dovetail with the last
> incremental prior to the full backup?
> Current Scenario:
> 1. A sequence of incremental logs backed up and shipped to another
> server (reporting) where they are restored (i.e. the target database
> is in standby)
> 2. Each night the creation of incremental backups is suspended while a
> full backup takes place. This full backup is also restored on the
> other server.
> 3. Incrementals resume after a certain period of time which is
> sufficient to allow the full backup to be restored.
> 4. The full backup is also copied to tape.
> Motivation:
> I want to avoid moving large backup files around on the network. I
> want to decrease the time lag with the live data from the reporting
> perspective. (I know about transactional replication and want/have to
> stay with the current approach. Thanks)
> Background:
> There was an occasion when the full backup failed to move to the
> target machine due to a space issue there. However, I noticed that the
> incrementals post-(full backup) were still being copied across and
> being restored even though the full backup didn't make it. This kind
> of answers my question but the thing is that I was a bit confused at
> the time and just wanted to get the problem sorted asap without doing
> root cause analysis. Was it my imagination or it this possible?
> I would have thought that the LSN count would have been reset by the
> full backup. Here is a little pictorial of the sequence that I would
> like.
> Production Reporting
> Box Box
> ... ...
> i1 i1
> i2 i2
> i3 i3
> fbup i4
> i4 i5
> i5 i6
> ... ...
>
> Regards
> Liam Caffrey
|||Tibor,
Does this mean, for example, that in one scheduled job I can have
incremental backups running all day long at, say, 15 minute intervals,
and then a separate scheduled job can have a full backup running once
per night and that the two jobs won't interfere with each other (apart
from performance) as long as the transaction log is not truncated by
anything.
The full backup normally takes about 40 minutes to run.
If so, I could then run incrementals to the reporting box for weeks on
end or until the transaction log is truncated. Is this correct. Is it
a good idea to run log ships for so long. Any disadvantages?
Regards
Liam
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
> Liam,
> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the tlog
> when you perform a database backup. This allow you for instance to pick you second newest db backup
> and restore it and then apply *all* subsequent log backups.
> In other words, a database backup does not break the sequence of transaction log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
|||> If so, I could then run incrementals to the reporting box for weeks on
> end or until the transaction log is truncated. Is this correct.
Correct.

> Any disadvantages?
Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some log
truncation in the production db for some reason...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060629.48f1584f@.posting.google.c om...[vbcol=seagreen]
> Tibor,
> Does this mean, for example, that in one scheduled job I can have
> incremental backups running all day long at, say, 15 minute intervals,
> and then a separate scheduled job can have a full backup running once
> per night and that the two jobs won't interfere with each other (apart
> from performance) as long as the transaction log is not truncated by
> anything.
> The full backup normally takes about 40 minutes to run.
> If so, I could then run incrementals to the reporting box for weeks on
> end or until the transaction log is truncated. Is this correct. Is it
> a good idea to run log ships for so long. Any disadvantages?
> Regards
> Liam
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
|||Interesting.
What happens if a full backup job is running and a separate
incremental log backup is kicked off during the middle of the full
backup? Or say SQLServer attempts to start a full backup just as an
incremental is running.
Will it gracefully prevent the incremental from running or will it go
ahead and create the incremental while the full is running? Are there
any issues with doing this? Is it better to create time windows for
the full backup.
Say then, in such a context, I wanted to restore a full backup plus
all subsequent incremental backups. How do I know which incremental to
apply after the full backup has been restored? The earliest? How can
the LSNs in the earliest post-(full backup) incremental file be
consistent with both the full backup file and the last pre-(full
backup) incremental file.
If you could refer me to some detailed info on this, I would
appreciate it. BOL doesn't seem to have anything in this direction.
Regards and thanks,
Liam Caffrey
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OkiDVP7qEHA.1296@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Correct.
>
> Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some log
> truncation in the production db for some reason...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060629.48f1584f@.posting.google.c om...
|||Super-short background:
When backup starts, SQL Server does a checkpoint to record in the log the starting point for the
backup. Then it simply copies the pages to the storage media. At the end-time (this is what the
database will look like when you restore), the database backup also picks up the log pages produced
during the backup. This is so SQL Server can just apply all the pages and then re-do the
modifications that took place during the backup page copying. Possibly also a checkpoint is
performed at end of the backup process.
Check out RESTORE HEADERONLY and the four columns:
FirstLsn
LastLsn
CheckpintLsn
DifferentialBaseLsn
Lsn is "Log Sequence Number", each log record is given such a sequence number. My guess is that a
log backup "synchronizes" to the following log backup using the database backups CheckpointLsn.
Something like the log backups FirstLsn should match the database backup's CheckpointLsn. This is
only from looking at a couple of backups... I haven't found an elaboration of this topic, but to be
honest, I haven't looked for such either.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410070334.7ab44ccf@.posting.google.c om...[vbcol=seagreen]
> Interesting.
> What happens if a full backup job is running and a separate
> incremental log backup is kicked off during the middle of the full
> backup? Or say SQLServer attempts to start a full backup just as an
> incremental is running.
> Will it gracefully prevent the incremental from running or will it go
> ahead and create the incremental while the full is running? Are there
> any issues with doing this? Is it better to create time windows for
> the full backup.
> Say then, in such a context, I wanted to restore a full backup plus
> all subsequent incremental backups. How do I know which incremental to
> apply after the full backup has been restored? The earliest? How can
> the LSNs in the earliest post-(full backup) incremental file be
> consistent with both the full backup file and the last pre-(full
> backup) incremental file.
> If you could refer me to some detailed info on this, I would
> appreciate it. BOL doesn't seem to have anything in this direction.
> Regards and thanks,
> Liam Caffrey
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<OkiDVP7qEHA.1296@.TK2MSFTNGP12.phx.gbl>...

Backups and LSNs

Hi,
My question:
Is it possible to create the full backup so that taped copies can be
made and then rather than restoring the full backup on the target
machine, the incrementals resume and dovetail with the last
incremental prior to the full backup?
Current Scenario:
1. A sequence of incremental logs backed up and shipped to another
server (reporting) where they are restored (i.e. the target database
is in standby)
2. Each night the creation of incremental backups is suspended while a
full backup takes place. This full backup is also restored on the
other server.
3. Incrementals resume after a certain period of time which is
sufficient to allow the full backup to be restored.
4. The full backup is also copied to tape.
Motivation:
I want to avoid moving large backup files around on the network. I
want to decrease the time lag with the live data from the reporting
perspective. (I know about transactional replication and want/have to
stay with the current approach. Thanks)
Background:
There was an occasion when the full backup failed to move to the
target machine due to a space issue there. However, I noticed that the
incrementals post-(full backup) were still being copied across and
being restored even though the full backup didn't make it. This kind
of answers my question but the thing is that I was a bit confused at
the time and just wanted to get the problem sorted asap without doing
root cause analysis. Was it my imagination or it this possible?
I would have thought that the LSN count would have been reset by the
full backup. Here is a little pictorial of the sequence that I would
like.
Production Reporting
Box Box
... ...
i1 i1
i2 i2
i3 i3
fbup i4
i4 i5
i5 i6
... ...
Regards
Liam CaffreyLiam,
If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the tlog
when you perform a database backup. This allow you for instance to pick you second newest db backup
and restore it and then apply *all* subsequent log backups.
In other words, a database backup does not break the sequence of transaction log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060154.7bc1bebf@.posting.google.com...
> Hi,
> My question:
> Is it possible to create the full backup so that taped copies can be
> made and then rather than restoring the full backup on the target
> machine, the incrementals resume and dovetail with the last
> incremental prior to the full backup?
> Current Scenario:
> 1. A sequence of incremental logs backed up and shipped to another
> server (reporting) where they are restored (i.e. the target database
> is in standby)
> 2. Each night the creation of incremental backups is suspended while a
> full backup takes place. This full backup is also restored on the
> other server.
> 3. Incrementals resume after a certain period of time which is
> sufficient to allow the full backup to be restored.
> 4. The full backup is also copied to tape.
> Motivation:
> I want to avoid moving large backup files around on the network. I
> want to decrease the time lag with the live data from the reporting
> perspective. (I know about transactional replication and want/have to
> stay with the current approach. Thanks)
> Background:
> There was an occasion when the full backup failed to move to the
> target machine due to a space issue there. However, I noticed that the
> incrementals post-(full backup) were still being copied across and
> being restored even though the full backup didn't make it. This kind
> of answers my question but the thing is that I was a bit confused at
> the time and just wanted to get the problem sorted asap without doing
> root cause analysis. Was it my imagination or it this possible?
> I would have thought that the LSN count would have been reset by the
> full backup. Here is a little pictorial of the sequence that I would
> like.
> Production Reporting
> Box Box
> ... ...
> i1 i1
> i2 i2
> i3 i3
> fbup i4
> i4 i5
> i5 i6
> ... ...
>
> Regards
> Liam Caffrey|||Tibor,
Does this mean, for example, that in one scheduled job I can have
incremental backups running all day long at, say, 15 minute intervals,
and then a separate scheduled job can have a full backup running once
per night and that the two jobs won't interfere with each other (apart
from performance) as long as the transaction log is not truncated by
anything.
The full backup normally takes about 40 minutes to run.
If so, I could then run incrementals to the reporting box for weeks on
end or until the transaction log is truncated. Is this correct. Is it
a good idea to run log ships for so long. Any disadvantages?
Regards
Liam
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
> Liam,
> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the tlog
> when you perform a database backup. This allow you for instance to pick you second newest db backup
> and restore it and then apply *all* subsequent log backups.
> In other words, a database backup does not break the sequence of transaction log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||> If so, I could then run incrementals to the reporting box for weeks on
> end or until the transaction log is truncated. Is this correct.
Correct.
> Any disadvantages?
Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some log
truncation in the production db for some reason...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060629.48f1584f@.posting.google.com...
> Tibor,
> Does this mean, for example, that in one scheduled job I can have
> incremental backups running all day long at, say, 15 minute intervals,
> and then a separate scheduled job can have a full backup running once
> per night and that the two jobs won't interfere with each other (apart
> from performance) as long as the transaction log is not truncated by
> anything.
> The full backup normally takes about 40 minutes to run.
> If so, I could then run incrementals to the reporting box for weeks on
> end or until the transaction log is truncated. Is this correct. Is it
> a good idea to run log ships for so long. Any disadvantages?
> Regards
> Liam
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
>> Liam,
>> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the
>> tlog
>> when you perform a database backup. This allow you for instance to pick you second newest db
>> backup
>> and restore it and then apply *all* subsequent log backups.
>> In other words, a database backup does not break the sequence of transaction log backups.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>|||Interesting.
What happens if a full backup job is running and a separate
incremental log backup is kicked off during the middle of the full
backup? Or say SQLServer attempts to start a full backup just as an
incremental is running.
Will it gracefully prevent the incremental from running or will it go
ahead and create the incremental while the full is running? Are there
any issues with doing this? Is it better to create time windows for
the full backup.
Say then, in such a context, I wanted to restore a full backup plus
all subsequent incremental backups. How do I know which incremental to
apply after the full backup has been restored? The earliest? How can
the LSNs in the earliest post-(full backup) incremental file be
consistent with both the full backup file and the last pre-(full
backup) incremental file.
If you could refer me to some detailed info on this, I would
appreciate it. BOL doesn't seem to have anything in this direction.
Regards and thanks,
Liam Caffrey
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OkiDVP7qEHA.1296@.TK2MSFTNGP12.phx.gbl>...
> > If so, I could then run incrementals to the reporting box for weeks on
> > end or until the transaction log is truncated. Is this correct.
> Correct.
>
> > Any disadvantages?
> Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some log
> truncation in the production db for some reason...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060629.48f1584f@.posting.google.com...
> > Tibor,
> >
> > Does this mean, for example, that in one scheduled job I can have
> > incremental backups running all day long at, say, 15 minute intervals,
> > and then a separate scheduled job can have a full backup running once
> > per night and that the two jobs won't interfere with each other (apart
> > from performance) as long as the transaction log is not truncated by
> > anything.
> >
> > The full backup normally takes about 40 minutes to run.
> >
> > If so, I could then run incrementals to the reporting box for weeks on
> > end or until the transaction log is truncated. Is this correct. Is it
> > a good idea to run log ships for so long. Any disadvantages?
> >
> > Regards
> >
> > Liam
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
> >> Liam,
> >>
> >> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the
> >> tlog
> >> when you perform a database backup. This allow you for instance to pick you second newest db
> >> backup
> >> and restore it and then apply *all* subsequent log backups.
> >>
> >> In other words, a database backup does not break the sequence of transaction log backups.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>|||Super-short background:
When backup starts, SQL Server does a checkpoint to record in the log the starting point for the
backup. Then it simply copies the pages to the storage media. At the end-time (this is what the
database will look like when you restore), the database backup also picks up the log pages produced
during the backup. This is so SQL Server can just apply all the pages and then re-do the
modifications that took place during the backup page copying. Possibly also a checkpoint is
performed at end of the backup process.
Check out RESTORE HEADERONLY and the four columns:
FirstLsn
LastLsn
CheckpintLsn
DifferentialBaseLsn
Lsn is "Log Sequence Number", each log record is given such a sequence number. My guess is that a
log backup "synchronizes" to the following log backup using the database backups CheckpointLsn.
Something like the log backups FirstLsn should match the database backup's CheckpointLsn. This is
only from looking at a couple of backups... I haven't found an elaboration of this topic, but to be
honest, I haven't looked for such either.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410070334.7ab44ccf@.posting.google.com...
> Interesting.
> What happens if a full backup job is running and a separate
> incremental log backup is kicked off during the middle of the full
> backup? Or say SQLServer attempts to start a full backup just as an
> incremental is running.
> Will it gracefully prevent the incremental from running or will it go
> ahead and create the incremental while the full is running? Are there
> any issues with doing this? Is it better to create time windows for
> the full backup.
> Say then, in such a context, I wanted to restore a full backup plus
> all subsequent incremental backups. How do I know which incremental to
> apply after the full backup has been restored? The earliest? How can
> the LSNs in the earliest post-(full backup) incremental file be
> consistent with both the full backup file and the last pre-(full
> backup) incremental file.
> If you could refer me to some detailed info on this, I would
> appreciate it. BOL doesn't seem to have anything in this direction.
> Regards and thanks,
> Liam Caffrey
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<OkiDVP7qEHA.1296@.TK2MSFTNGP12.phx.gbl>...
>> > If so, I could then run incrementals to the reporting box for weeks on
>> > end or until the transaction log is truncated. Is this correct.
>> Correct.
>>
>> > Any disadvantages?
>> Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some
>> log
>> truncation in the production db for some reason...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "LCaffrey" <lxc@.eosys.ie> wrote in message
>> news:4a6dded0.0410060629.48f1584f@.posting.google.com...
>> > Tibor,
>> >
>> > Does this mean, for example, that in one scheduled job I can have
>> > incremental backups running all day long at, say, 15 minute intervals,
>> > and then a separate scheduled job can have a full backup running once
>> > per night and that the two jobs won't interfere with each other (apart
>> > from performance) as long as the transaction log is not truncated by
>> > anything.
>> >
>> > The full backup normally takes about 40 minutes to run.
>> >
>> > If so, I could then run incrementals to the reporting box for weeks on
>> > end or until the transaction log is truncated. Is this correct. Is it
>> > a good idea to run log ships for so long. Any disadvantages?
>> >
>> > Regards
>> >
>> > Liam
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> > news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
>> >> Liam,
>> >>
>> >> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate
>> >> the
>> >> tlog
>> >> when you perform a database backup. This allow you for instance to pick you second newest db
>> >> backup
>> >> and restore it and then apply *all* subsequent log backups.
>> >>
>> >> In other words, a database backup does not break the sequence of transaction log backups.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>

Backups - 2000 -> 2005

If I create a backup in SQL Server 2000 can I restore that database to SQL
Server 2005?
If not, is there some tool to convert a 2K DB to 2005?
Thanks."Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
Yes.
Note it's one way. You can't restore a 2005 backup to 2000.
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>|||Yes, you can restore a SQL Server 2000 backup to a 2005 server. You can also
detach from SQL Server 2000 and attach to 2005. However, you can not do the
same from SQL Server 2005 to 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Don Miller" wrote:
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>
>|||Thanks. That saved me an hour or two looking through BOL.
"Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>

Backups - 2000 -> 2005

If I create a backup in SQL Server 2000 can I restore that database to SQL
Server 2005?
If not, is there some tool to convert a 2K DB to 2005?
Thanks."Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
Yes.
Note it's one way. You can't restore a 2005 backup to 2000.

> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>|||Yes, you can restore a SQL Server 2000 backup to a 2005 server. You can also
detach from SQL Server 2000 and attach to 2005. However, you can not do the
same from SQL Server 2005 to 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Don Miller" wrote:

> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>
>|||Thanks. That saved me an hour or two looking through BOL.
"Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>