Basic Question on SQL Server 2005 Backup Theory

I am replicating a large number of databases (the size changes
dynamically) from one sql server to another, all pragmatically.
Once a week, I do a full backup and restore it on the destination.
All other times I am just backing up the log files, copying them over,
and restoring them. Basically a glorified log ship.
About once a week, I get various exceptions stating the following:
********
System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
because there is no current database backup.
********
My question is: with this scenario, how often do I have to actually
perform a full backup? Does SQL Server 2005 really care?
Thanks a lot,
Michael Gorsuch
> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
Only the very first time. Assuming you ship all transaction log backups, and don't do anything with
the originating database that break the log backup sequence (like put it in simple recovery model).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1177597505.788472.212590@.t39g2000prd.googlegr oups.com...
>I am replicating a large number of databases (the size changes
> dynamically) from one sql server to another, all pragmatically.
> Once a week, I do a full backup and restore it on the destination.
> All other times I am just backing up the log files, copying them over,
> and restoring them. Basically a glorified log ship.
> About once a week, I get various exceptions stating the following:
> ********
> System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
> because there is no current database backup.
> ********
> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
> Thanks a lot,
> Michael Gorsuch
>

Basic Question on SQL Server 2005 Backup Theory

I am replicating a large number of databases (the size changes
dynamically) from one sql server to another, all pragmatically.
Once a week, I do a full backup and restore it on the destination.
All other times I am just backing up the log files, copying them over,
and restoring them. Basically a glorified log ship.
About once a week, I get various exceptions stating the following:
********
System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
because there is no current database backup.
********
My question is: with this scenario, how often do I have to actually
perform a full backup? Does SQL Server 2005 really care?
Thanks a lot,
Michael Gorsuch> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
Only the very first time. Assuming you ship all transaction log backups, and
don't do anything with
the originating database that break the log backup sequence (like put it in
simple recovery model).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1177597505.788472.212590@.t39g2000prd.googlegroups.com...
>I am replicating a large number of databases (the size changes
> dynamically) from one sql server to another, all pragmatically.
> Once a week, I do a full backup and restore it on the destination.
> All other times I am just backing up the log files, copying them over,
> and restoring them. Basically a glorified log ship.
> About once a week, I get various exceptions stating the following:
> ********
> System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
> because there is no current database backup.
> ********
> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
> Thanks a lot,
> Michael Gorsuch
>

Basic Question on SQL Server 2005 Backup Theory

I am replicating a large number of databases (the size changes
dynamically) from one sql server to another, all pragmatically.
Once a week, I do a full backup and restore it on the destination.
All other times I am just backing up the log files, copying them over,
and restoring them. Basically a glorified log ship.
About once a week, I get various exceptions stating the following:
********
System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
because there is no current database backup.
********
My question is: with this scenario, how often do I have to actually
perform a full backup? Does SQL Server 2005 really care?
Thanks a lot,
Michael Gorsuch> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
Only the very first time. Assuming you ship all transaction log backups, and don't do anything with
the originating database that break the log backup sequence (like put it in simple recovery model).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1177597505.788472.212590@.t39g2000prd.googlegroups.com...
>I am replicating a large number of databases (the size changes
> dynamically) from one sql server to another, all pragmatically.
> Once a week, I do a full backup and restore it on the destination.
> All other times I am just backing up the log files, copying them over,
> and restoring them. Basically a glorified log ship.
> About once a week, I get various exceptions stating the following:
> ********
> System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
> because there is no current database backup.
> ********
> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
> Thanks a lot,
> Michael Gorsuch
>sql

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

Bar Graph's Y Axis as 10k, 20k, 30k

Hi,
The Y-axis value on my Bar Graph displays as 10000, 20000, 30000 . . not good aesthetics and not legible when figure goes beyond 6 in size
I need to show it as 10k, 20k, 30k, like Crystal Reports does by default...
How can i do this ?
ThanksRight-click on the chart to open the chart properties dialog. Go to the
Y-axis tab, and use the following formatcode:
0,k
This will give you 10k, 20k, etc. Further information is available on MSDN:
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:D6FF9A5C-8350-434D-A80D-7977F35D5614@.microsoft.com...
> Hi,
> The Y-axis value on my Bar Graph displays as 10000, 20000, 30000 . . not
good aesthetics and not legible when figure goes beyond 6 in size
> I need to show it as 10k, 20k, 30k, like Crystal Reports does by
default...
> How can i do this ?
> Thanks

Bar Chart - Bar Width

Does RS provide help specifying the width of a Bar in a Simple Bar Chart ?
Right now, it creates even sized bar for a given size of a Chart.
p.s. I've already applied SP1 (jfyi) -- which allows me to color code
individual bar appearing on the Chart (I must say, that is really GOOD!!)
Thanks.See related post:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=8eda1408-dffc-459e-8cb3-c52e718c2358&sloc=en-us.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:E81BF3CB-27A6-46F7-9A13-61C6B5D4FA0B@.microsoft.com...
> Does RS provide help specifying the width of a Bar in a Simple Bar Chart ?
> Right now, it creates even sized bar for a given size of a Chart.
> p.s. I've already applied SP1 (jfyi) -- which allows me to color code
> individual bar appearing on the Chart (I must say, that is really GOOD!!)
> Thanks.sql

backups interfering with log shipping?

I have some questions on backups and log shipping.

Before I get to them, though, the goal:

Back up a SQL 2000 database (~5GB in size) completely every day.
Back up its transaction log hourly.
Maintain a warm backup DB server using log shipping.

Here is what I have done so far, which has led to my questions:

I have stopped using maintenance plans for backups, feeling that
then the process would be less 'black boxy'

I first created two jobs to perform the daily db and hourly tlog backups
that saved the backups in files using the naming formats
<name>_db_<yymmdd>.bak and <name>_tlog_<yymmddnnss>.bak.

Issue 1: doing backups this way means that it's a little difficult for the
log shipping jobs to figure out the filenames (esp. the tlog ones), if log
shipping is going to use the files generated by the backup jobs.

Issue 2: I thought maybe I'd have log shipping generate its OWN backup
files, but would that cause problems with the transaction log? Say the
normal tlog backup fires, then fifteen minutes later the shipping tlog
backup fires. Would the shipping tlog backup file be missing the transactions
that were backed up during the normal log backup?

Issue 3: To try to help with the naming issue, I tried switching the backups from
creating new files each time to file devices whose names would be constant.
This worked, but since my database is about 5GB in size, that meant
that, with expiring backups after 7 days, the database backup device
would settle in at about 25GB (I skip weekends) and I'd have to copy
or cab-copy-uncab that file over to my warm server every day. That
seemed a little inefficient. Does anybody have alternative ideas?

My last question is for general info:

Where does SQL Server keep the information on the backups that exist?
Whether I was using individual files or file devices, I was able to go to
Database-All Tasks-Restore Database... in Enterprise Manager and it
would show me the backups that existed. I imagine these must be
stored in a system table somewhere, but I did not see any obvious place
to look (no 'sysbackups' table, etc.).

Many thanks in advance for your input!

GeoffBoiling my above messgae down to bite-size questions:

1. Where does SQL store information related to the backups that currently exist? Or does SQL scan for backup files and figure out what's in them on the fly (seems unlikely to me)?

2. Say I want to run two transaction log backups per hour. One is included in the normal backup plan and one occurs, say, 15 minutes later for log shipping purposes. Is my log shipping going to fail because the transactions that were backed up during the normal bacup will not be in my log shipping backup?|||It's in the msdb in a table called backupset.

I run log shipping where I backup the transaction log to a file name like DBNAME_TRAN.TRN. I then copy one to the SHIP TO server and apply it while copying the other to a directory where I add the data and time. I have scripts that determine which logs to apply in case I have to restore the database from the Full, Incrimental and then apply transaction log backups.

Backups doubling and halfing in size

This is probably something really obvious, but can someone tell me
whats going on.
I do daily full backups of a database to zip and looking at the sizes,
they are growing at the normal rate but every other day halfing:
day 1 4Mb
day 2 7Mb
day 3 4Mb
day 4 9Mb
day 5 5Mb
day 6 11Mb
etc
Is this normal ?
The database is at 18Mb now & has approx 5% of the annual transaction
volume - 2000 of 100,000pa in invoices and 10,000 of 500,000 in invoice
lines.
I plan to keep 3ys of data in these tables, removing the oldest
transactions to an archive database of the same structure on a
different IP. Does this sound reasonable, can I predict performance of
the app with 3ys data ? Is this a small database for SQL2K ?
Thanks.Hi
You may have some additional task that shrinks/reorganizes the data/log
files on these days.
John
"hals_left" wrote:

> This is probably something really obvious, but can someone tell me
> whats going on.
> I do daily full backups of a database to zip and looking at the sizes,
> they are growing at the normal rate but every other day halfing:
> day 1 4Mb
> day 2 7Mb
> day 3 4Mb
> day 4 9Mb
> day 5 5Mb
> day 6 11Mb
> etc
> Is this normal ?
> The database is at 18Mb now & has approx 5% of the annual transaction
> volume - 2000 of 100,000pa in invoices and 10,000 of 500,000 in invoice
> lines.
> I plan to keep 3ys of data in these tables, removing the oldest
> transactions to an archive database of the same structure on a
> different IP. Does this sound reasonable, can I predict performance of
> the app with 3ys data ? Is this a small database for SQL2K ?
> Thanks.
>|||hals_left (cc900630@.ntu.ac.uk) writes:
> This is probably something really obvious, but can someone tell me
> whats going on.
> I do daily full backups of a database to zip and looking at the sizes,
> they are growing at the normal rate but every other day halfing:
How do you perform these backups? The BACKUP command in SQL Server?
Something else?

> The database is at 18Mb now & has approx 5% of the annual transaction
> volume - 2000 of 100,000pa in invoices and 10,000 of 500,000 in invoice
> lines.
> I plan to keep 3ys of data in these tables, removing the oldest
> transactions to an archive database of the same structure on a
> different IP. Does this sound reasonable, can I predict performance of
> the app with 3ys data ? Is this a small database for SQL2K ?
18 MB is a very small database indeed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Backups

Is there a max size of database till which complete SQL
backups can be taken
Is it that for db's > 60GB only filegroup backups can be
taken
SanjayWe have a database thats over 175gig although its split
into two datafiles, the largest datafile is 114gig. I'm
not sure if there is a max size to backup.
>--Original Message--
>Is there a max size of database till which complete SQL
>backups can be taken
>Is it that for db's > 60GB only filegroup backups can be
>taken
>Sanjay
>.
>|||There is no size limit that I know of for sql server. It's usually limited
by how fast the hardware can handle it. 60GB is not a large db to back up
at all.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:067901c356ee$4fdaa440$a301280a@.phx.gbl...
> Is there a max size of database till which complete SQL
> backups can be taken
> Is it that for db's > 60GB only filegroup backups can be
> taken
> Sanjay

Backup/Restore SQL 6.5 databases

Group,
If i remember correct, when restoring an SQL 6.5 database file that had been
extended in size, you first had to re-create the database file on the same
chuck sizes and there also were a SQL command that showed the sizes of that
"chunks".
1. Is this correct or does my mind play with me?
2. What was this SQL command (stored procedure or?)
Regards /PeterYou have to get the same database fragment allocation (the sequence and size of allocation of
database and log fragments from devices). The allocations doesn't have to be from the same device as
it was originally, but the number, size and type (data or log)of allocations has to be the same. On
the source server, run sp_help_revdatabase.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter" <Peter.nospam@.news.com> wrote in message news:OYpMwlWxDHA.1704@.TK2MSFTNGP10.phx.gbl...
> Group,
> If i remember correct, when restoring an SQL 6.5 database file that had been
> extended in size, you first had to re-create the database file on the same
> chuck sizes and there also were a SQL command that showed the sizes of that
> "chunks".
> 1. Is this correct or does my mind play with me?
> 2. What was this SQL command (stored procedure or?)
> Regards /Peter
>

backup/restore

hi,
I want to know if there is a way - other than backup and restore database (because restore takes too much time as the DB size grows) - to transfer all the data from one database on the server to another on a local SQLServer. it has to be done daily, so that the manager will always have an updated copy of the data on his machine.

Any help?
Thanks in advance

you can configure replication / log shipping / database mirroing etc. but it will impact the production server performance and it nees to be monitored/administered constantly

Madhu

backup/recovery strategy

Currently we have about 60 SQL Servers all shape and size (the biggest is
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...
Hi,
See the below URL's
http://www.microsoft.com/technet/pro...ackuprest.mspx
http://www1.us.dell.com/content/topi...555&l=en&s=biz
http://vyaskn.tripod.com/sql_server_..._practices.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...

backup/recovery strategy

Currently we have about 60 SQL Servers all shape and size (the biggest is
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...Hi,
See the below URL's
http://www.microsoft.com/technet/pr...
555&l=en&s=biz
http://vyaskn.tripod.com/ sql_serve...ices
.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...

backup/recovery strategy

Currently we have about 60 SQL Servers all shape and size (the biggest is
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...Hi,
See the below URL's
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www1.us.dell.com/content/topics/global.aspx/power/en/ps4q00_martin?c=us&cs=555&l=en&s=biz
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...

Backup to UNC path failing

Hi,

I have been trying to set up log shipping on a database with a backup size of ~190MB. However, I have been getting two error messages as stated below during the initial backup of the DB before it is restored on the warm standby.

1st Error
-----
Windows was unable to save all of the data for the file \Device\LanmanRedirector\<<UNC Path>>. The data has been lost. This error may be caused by a failure of your computer hardware or network connection. Please try to save the file elsewhere.

2nd Error
----
Write on '<<UNC path>>' failed, status 64. See the Sql Server eror log for more details. BACKUP database is terminating abnormally.

Backing up the database out side of the logshipping set up runs fine if I use a mapped drive, but it fails again when I use a UNC path. All the permissions are correct (I have backed up a smaller DB to the UNC path which completes successfully) so I am a little stumped.

Any help would be greatly appreciated

Thanks

RichI think this is a caching issue - check to see if you have network redirector caching enabled. The following link shows you how to disable:

link (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q163401)

The following is another link that might be helpful if the above information does not solve your problem:

link (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q321733&)|||Thanks for coming back so quickly. I thought that the cache could be the problem but noticed from the link you sent that it was specific to NT. I forgot to mention in my initial post that I am running on Win 2k SP2 Adv. Server boxes. Could this still be a problem with the more recent OS?

The second link you mentioned relates to a very specific record in the event log. The error I am experiencing is almost identical to that mentioned in the KB article all exceppt for the final staus code. I do not have c0000022 in my event log. Am I correct in therefore assuming that the workaround will not make any difference.

I know this is just more questions, but I do not have access to the boxes for the next couple of days and am trying to workout the best approach so as to try to reolve the problem when I get on site as quickly as possible.

Cheers

Rich