sql backup files: backed

Showing posts with label backed. Show all posts

Thursday, March 29, 2012

Backup Strategy

I am a newbie to SQL.
I am running SQL 2000 on a SBS 2003 R2 box. What is the best way to make
sure our databases are backed up in the event of a data loss or a crash?
Thanks,
Rich
The easiest way is to create a maintenance plan using the SQL Enterprise
Manager.
Or you can create jobs that use T-SQL statement to backup the databases.
Always make sure that the backup's are stored on offline storage, eg another
server or tapes.
The best way depends or your businessneeds and databasesize.
Robert Hartskeerl, MCTS
http://hartskeerl.nl/blogs/robert/
"Rikellent" wrote:

> I am a newbie to SQL.
> I am running SQL 2000 on a SBS 2003 R2 box. What is the best way to make
> sure our databases are backed up in the event of a data loss or a crash?
> Thanks,
> Rich
|||Hello,
You could use the maintenence wizard to create a backup plan and schedule
it. But I had lot of issues with maintenanceplans and so i went for TSQL
backup
script and schedule it using SQL Server agent.
Take a look into the below article:-
http://www.microsoft.com/india/msdn/articles/185.aspx
Thanks
Hari
"Rikellent" <Rikellent@.discussions.microsoft.com> wrote in message
news:BB10385E-8923-4AF7-B5B8-75F7B44CD993@.microsoft.com...
>I am a newbie to SQL.
> I am running SQL 2000 on a SBS 2003 R2 box. What is the best way to make
> sure our databases are backed up in the event of a data loss or a crash?
> Thanks,
> Rich

Monday, March 19, 2012

BackUp Question

When you do a full back-up of a sql server db, does that include the transaction logs or do they still have to be backed up seperately?
Thanks!Depends what your question is - does a full backup include all transactions in T-log? Yes. Does full backup checkpoint the T-log and truncate it? No. Does it shrink the T-log in a full backup? No.

To test this run this command:

dbcc sqlperf('logspace')

this will show you the percentage of your T-log used. Run a full backup then run the command again and you will see that the percentage did not go down. If you are having T-log growth problems, you probably want to back it up periodically.

Another good command to know for the T-log is this one:

select * from ::fn_dblog(null,null)

This will show you the entries in your active T-log.

HTH

Sunday, March 11, 2012

backup problem

I successfully deployed the AdventureWorks OLAP database to my 2005 Analysis Server and Backed it up under SQL Server Management Studio. When I tried to restore the backup to another sql 2005 Analysis Server the restore failed.

I went back to the "Backup" dialog box and noted that it would only "store database metadata to a specific location". Metadata only? How do I create a full backup that can be restored on another server and is ready for use?

TIA,

Barkingdog

Hi Barkingdog,

You should be able to backup AdventureWorks and restore it to another server. Can you let me know of what is the failure message when you restore the database on another server?

Yan Li

|||What is the storage mode used for your cube - if you are using ROLAP or HOLAP, the cube data is not backed up because cube data remains in the underlying data source - for all data and meta data to be backed up in the AS backup file in AS2K5, you must be using MOLAP are your storage mode.

Backup Problem

My backup fails with:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up.
[SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
(Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
[SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
(Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the SQL
Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP LOG
is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
When I look at the log I get:
BackupDiskFile::RequestDurableMedia: failure on backup device
'\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
error 64(The specified network name is no longer available.).
I can get to the folder where this file resides, so I have ruled out
security. What does this mean?This looks like you have glitches in the network or somewhere in the
communication path of the server you are backing up to. Backup is very
touchy about errors in the communication (as it should be) between the DB
server and the destination. Most normal network traffic will just resend
the packet but the backup will usually fail.
--
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:361DA327-4899-4CD2-9BAC-CCE310D9A76C@.microsoft.com...
> My backup fails with:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed
> up.
> [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
> (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
> percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
> [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
> (Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the
> SQL
> Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP
> LOG
> is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
> failed.
> When I look at the log I get:
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
> error 64(The specified network name is no longer available.).
> I can get to the folder where this file resides, so I have ruled out
> security. What does this mean?|||You most likely ran out of disk space during the backup. Is there
enough free space on the disk to contain the entire DB? I'm guessing
not, since only about 70% of your DB gets backed up before the OS whinges.
Cheers,
Mike.
Jack wrote:
> My backup fails with:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up.
> [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
> (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
> percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
> [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
> (Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the SQL
> Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP LOG
> is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
> When I look at the log I get:
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
> error 64(The specified network name is no longer available.).
> I can get to the folder where this file resides, so I have ruled out
> security. What does this mean?

Backup Problem

My backup fails with:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up.
[SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
(Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
[SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
(Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the SQL
Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP LOG
is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
When I look at the log I get:
BackupDiskFile::RequestDurableMedia: failure on backup device
'\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
error 64(The specified network name is no longer available.).
I can get to the folder where this file resides, so I have ruled out
security. What does this mean?
This looks like you have glitches in the network or somewhere in the
communication path of the server you are backing up to. Backup is very
touchy about errors in the communication (as it should be) between the DB
server and the destination. Most normal network traffic will just resend
the packet but the backup will usually fail.
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:361DA327-4899-4CD2-9BAC-CCE310D9A76C@.microsoft.com...
> My backup fails with:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed
> up.
> [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
> (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
> percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
> [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
> (Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the
> SQL
> Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP
> LOG
> is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
> failed.
> When I look at the log I get:
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
> error 64(The specified network name is no longer available.).
> I can get to the folder where this file resides, so I have ruled out
> security. What does this mean?
|||You most likely ran out of disk space during the backup. Is there
enough free space on the disk to contain the entire DB? I'm guessing
not, since only about 70% of your DB gets backed up before the OS whinges.
Cheers,
Mike.
Jack wrote:
> My backup fails with:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up.
> [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
> (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
> percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
> [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
> (Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the SQL
> Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP LOG
> is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
> When I look at the log I get:
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
> error 64(The specified network name is no longer available.).
> I can get to the folder where this file resides, so I have ruled out
> security. What does this mean?

Friday, February 24, 2012

backup not working

In Enterprise Manager, I created a schedule to backup the
database. But the database is not being backed up.
I am able to manually take backup of my database.
What is the best option to take complete backup.
Should I append to the media or overwrite?
Thank you in advance,
LindaDo you have Server Agent runing?
Bojidar Alexnadrov|||Hi,
Check "SQLServer Agent" service is running. If not start the service and
check if the schedule is working.
What is the best option to take complete backup. Should I append to the
media or overwrite?
Both options are fine, But due to hard disk space limitations mostly people
choose the overwrite options (WITH INIT).
But ensure that old backup file is copied / moved to a remote hard disk or
Tape before overwriting.
Thanks
Hari
MCDBA
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1931e01c44ca6$7bc051a0$a501280a@.phx.gbl...
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
> What is the best option to take complete backup.
> Should I append to the media or overwrite?
> Thank you in advance,
> Linda|||Linda,
Answers inline.
Linda wrote:
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
Is SQL Server Agent running? Do you get any errors? What does the
history for the job say?
> What is the best option to take complete backup.
> Should I append to the media or overwrite?
Depends. If backing up to disk I like to create a new file each time and
not overwrite the previous, or append. If going directly to tape you may
wish to append. What are you doing?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html|||Hari,
Thank you very much for your reply!
It works now.
-Linda
>--Original Message--
>Hi,
>Check "SQLServer Agent" service is running. If not start
the service and
>check if the schedule is working.
>What is the best option to take complete backup. Should
I append to the
>media or overwrite?
>Both options are fine, But due to hard disk space
limitations mostly people
>choose the overwrite options (WITH INIT).
>But ensure that old backup file is copied / moved to a
remote hard disk or
>Tape before overwriting.
>Thanks
>Hari
>MCDBA
>
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1931e01c44ca6$7bc051a0$a501280a@.phx.gbl...
>> In Enterprise Manager, I created a schedule to backup
the
>> database. But the database is not being backed up.
>> I am able to manually take backup of my database.
>> What is the best option to take complete backup.
>> Should I append to the media or overwrite?
>> Thank you in advance,
>> Linda
>
>.
>|||Mark,
Thank you very much for your reply! The agent wasn't up.
It works now.
-Linda
>--Original Message--
>Linda,
>Answers inline.
>
>Linda wrote:
>> In Enterprise Manager, I created a schedule to backup
the
>> database. But the database is not being backed up.
>> I am able to manually take backup of my database.
>Is SQL Server Agent running? Do you get any errors? What
does the
>history for the job say?
>> What is the best option to take complete backup.
>> Should I append to the media or overwrite?
>Depends. If backing up to disk I like to create a new
file each time and
>not overwrite the previous, or append. If going directly
to tape you may
>wish to append. What are you doing?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>.
>|||Bojidar,
Thank you very much for your reply! The Agent wasn't
running. It works now.
-Linda
>--Original Message--
>Do you have Server Agent runing?
>Bojidar Alexnadrov
>
>.
>

backup not working

In Enterprise Manager, I created a schedule to backup the
database. But the database is not being backed up.
I am able to manually take backup of my database.
What is the best option to take complete backup.
Should I append to the media or overwrite?
Thank you in advance,
Linda
Do you have Server Agent runing?
Bojidar Alexnadrov
|||Hi,
Check "SQLServer Agent" service is running. If not start the service and
check if the schedule is working.
What is the best option to take complete backup. Should I append to the
media or overwrite?
Both options are fine, But due to hard disk space limitations mostly people
choose the overwrite options (WITH INIT).
But ensure that old backup file is copied / moved to a remote hard disk or
Tape before overwriting.
Thanks
Hari
MCDBA
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1931e01c44ca6$7bc051a0$a501280a@.phx.gbl...
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
> What is the best option to take complete backup.
> Should I append to the media or overwrite?
> Thank you in advance,
> Linda
|||Linda,
Answers inline.
Linda wrote:
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
Is SQL Server Agent running? Do you get any errors? What does the
history for the job say?

> What is the best option to take complete backup.
> Should I append to the media or overwrite?
Depends. If backing up to disk I like to create a new file each time and
not overwrite the previous, or append. If going directly to tape you may
wish to append. What are you doing?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hari,
Thank you very much for your reply!
It works now.
-Linda

>--Original Message--
>Hi,
>Check "SQLServer Agent" service is running. If not start
the service and
>check if the schedule is working.
>What is the best option to take complete backup. Should
I append to the
>media or overwrite?
>Both options are fine, But due to hard disk space
limitations mostly people
>choose the overwrite options (WITH INIT).
>But ensure that old backup file is copied / moved to a
remote hard disk or
>Tape before overwriting.
>Thanks
>Hari
>MCDBA
>
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1931e01c44ca6$7bc051a0$a501280a@.phx.gbl...
the
>
>.
>
|||Mark,
Thank you very much for your reply! The agent wasn't up.
It works now.
-Linda
[vbcol=seagreen]
>--Original Message--
>Linda,
>Answers inline.
>
>Linda wrote:
the
>Is SQL Server Agent running? Do you get any errors? What
does the
>history for the job say?
>
>Depends. If backing up to disk I like to create a new
file each time and
>not overwrite the previous, or append. If going directly
to tape you may
>wish to append. What are you doing?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>.
>
|||Bojidar,
Thank you very much for your reply! The Agent wasn't
running. It works now.
-Linda

>--Original Message--
>Do you have Server Agent runing?
>Bojidar Alexnadrov
>
>.
>

backup not working

In Enterprise Manager, I created a schedule to backup the
database. But the database is not being backed up.
I am able to manually take backup of my database.
What is the best option to take complete backup.
Should I append to the media or overwrite?
Thank you in advance,
LindaDo you have Server Agent runing?
Bojidar Alexnadrov|||Hi,
Check "SQLServer Agent" service is running. If not start the service and
check if the schedule is working.
What is the best option to take complete backup. Should I append to the
media or overwrite?
Both options are fine, But due to hard disk space limitations mostly people
choose the overwrite options (WITH INIT).
But ensure that old backup file is copied / moved to a remote hard disk or
Tape before overwriting.
Thanks
Hari
MCDBA
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1931e01c44ca6$7bc051a0$a501280a@.phx
.gbl...
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
> What is the best option to take complete backup.
> Should I append to the media or overwrite?
> Thank you in advance,
> Linda|||Linda,
Answers inline.
Linda wrote:
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
Is SQL Server Agent running? Do you get any errors? What does the
history for the job say?

> What is the best option to take complete backup.
> Should I append to the media or overwrite?
Depends. If backing up to disk I like to create a new file each time and
not overwrite the previous, or append. If going directly to tape you may
wish to append. What are you doing?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html|||Hari,
Thank you very much for your reply!
It works now.
-Linda

>--Original Message--
>Hi,
>Check "SQLServer Agent" service is running. If not start
the service and
>check if the schedule is working.
>What is the best option to take complete backup. Should
I append to the
>media or overwrite?
>Both options are fine, But due to hard disk space
limitations mostly people
>choose the overwrite options (WITH INIT).
>But ensure that old backup file is copied / moved to a
remote hard disk or
>Tape before overwriting.
>Thanks
>Hari
>MCDBA
>
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1931e01c44ca6$7bc051a0$a501280a@.phx
.gbl...
the[vbcol=seagreen]
>
>.
>|||Mark,
Thank you very much for your reply! The agent wasn't up.
It works now.
-Linda

>--Original Message--
>Linda,
>Answers inline.
>
>Linda wrote:
the[vbcol=seagreen]
>Is SQL Server Agent running? Do you get any errors? What
does the
>history for the job say?
>
>Depends. If backing up to disk I like to create a new
file each time and
>not overwrite the previous, or append. If going directly
to tape you may
>wish to append. What are you doing?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>.
>|||Bojidar,
Thank you very much for your reply! The Agent wasn't
running. It works now.
-Linda

>--Original Message--
>Do you have Server Agent runing?
>Bojidar Alexnadrov
>
>.
>

Thursday, February 16, 2012

Backup log - will it truncate?

According to BOL
<--
BACKUP LOG
Specifies a backup of the transaction log only. The log is backed up
from the last successfully executed LOG backup to the current end of
the log. Once the log is backed up, the space may be truncated when no
longer required by replication or active transactions.
--
Does this mean I could do something else to truncate the log or is it
saying that the backup log command may truncate the log if it feels
like it?

I want ot get a log from a client site onto my server for analysis but
I want ot make absolute certain that my backing up the log on their
server won't truncate it there.Trevor

A backup of the transaction log will truncate the transaction log. All
completed transactions will be removed.

What are you planing to do with the transaction log? There is not a lot
you can do with out a matching database. As far as I am aware,
Lumigent's Log reader is the only product that can read the transaction
log.

Regards

John

Trevor Best wrote:
> According to BOL
> <--
> BACKUP LOG
> Specifies a backup of the transaction log only. The log is backed up
> from the last successfully executed LOG backup to the current end of
> the log. Once the log is backed up, the space may be truncated when no
> longer required by replication or active transactions.
> -->
> Does this mean I could do something else to truncate the log or is it
> saying that the backup log command may truncate the log if it feels
> like it?
> I want ot get a log from a client site onto my server for analysis but
> I want ot make absolute certain that my backing up the log on their
> server won't truncate it there.|||johnbandettini@.yahoo.co.uk wrote:
> Trevor
> A backup of the transaction log will truncate the transaction log. All
> completed transactions will be removed.
> What are you planing to do with the transaction log? There is not a lot
> you can do with out a matching database. As far as I am aware,
> Lumigent's Log reader is the only product that can read the transaction
> log.

Hi John, thanks for the reply.

I do intend to view the log, I'm using SQLLog Rescue from
www.red-gate.com. I can connect to their server over a VPN but it took
hours to read the log and got a general network error during the
process. What I want to do is restore the database on my server c/w
logs so I can view them here.

I know I can grab the data files and attach them on my end but I would
have to wait until tonight when people are off line to free up the
files.|||On 9 Jun 2006 02:35:01 -0700, Trevor Best wrote:

(snip)
>I want ot get a log from a client site onto my server for analysis but
>I want ot make absolute certain that my backing up the log on their
>server won't truncate it there.

Hi Trevor,

BACKUP LOG <databasename>
TO DISK = 'x:\y\z\logbackup.bak'
WITH COPY_ONLY;

--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:5hmi82pfl02iqnhelegm0v515dbdm1qpid@.4ax.com...
> On 9 Jun 2006 02:35:01 -0700, Trevor Best wrote:
> (snip)
> >I want ot get a log from a client site onto my server for analysis but
> >I want ot make absolute certain that my backing up the log on their
> >server won't truncate it there.
> Hi Trevor,
> BACKUP LOG <databasename>
> TO DISK = 'x:\y\z\logbackup.bak'
> WITH COPY_ONLY;

Is this a SQL 2005 specific command? I've never seen it before.

Very handy.

> --
> Hugo Kornelis, SQL Server MVP|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:5hmi82pfl02iqnhelegm0v515dbdm1qpid@.4ax.com...
>> BACKUP LOG <databasename>
>> TO DISK = 'x:\y\z\logbackup.bak'
>> WITH COPY_ONLY;
> Is this a SQL 2005 specific command? I've never seen it before.

Yes, that's a new addition (which I neither I had noticed until Hugo
posted about it). It appears to be a "NO_TRUNCATE light". That is,
NO_TRUNCATE also waives the requirement that the database should be
accessible etc and is mainly intended for emergency situations.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sat, 10 Jun 2006 12:51:23 +0000 (UTC), Erland Sommarskog wrote:

>Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
>> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
>> news:5hmi82pfl02iqnhelegm0v515dbdm1qpid@.4ax.com...
>>> BACKUP LOG <databasename>
>>> TO DISK = 'x:\y\z\logbackup.bak'
>>> WITH COPY_ONLY;
>>
>> Is this a SQL 2005 specific command? I've never seen it before.
>Yes, that's a new addition (which I neither I had noticed until Hugo
>posted about it). It appears to be a "NO_TRUNCATE light". That is,
>NO_TRUNCATE also waives the requirement that the database should be
>accessible etc and is mainly intended for emergency situations.

Hi Erland,

That's right. The major advantage of COPY_ONLY over NO_TRUNCATE is (IMO)
that this option also works on full and incremental backups. It's a
great way to quickly get a backup of a DB to do some tests or
trouble-shooting on without disrupting the backup schema.

--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:9juo821f71b7ah782onh5lbotre5qrgie1@.4ax.com...
> On Sat, 10 Jun 2006 12:51:23 +0000 (UTC), Erland Sommarskog wrote:
> >Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> >> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> >> news:5hmi82pfl02iqnhelegm0v515dbdm1qpid@.4ax.com...
> >>> BACKUP LOG <databasename>
> >>> TO DISK = 'x:\y\z\logbackup.bak'
> >>> WITH COPY_ONLY;
> >>
> >> Is this a SQL 2005 specific command? I've never seen it before.
> >Yes, that's a new addition (which I neither I had noticed until Hugo
> >posted about it). It appears to be a "NO_TRUNCATE light". That is,
> >NO_TRUNCATE also waives the requirement that the database should be
> >accessible etc and is mainly intended for emergency situations.
> Hi Erland,
> That's right. The major advantage of COPY_ONLY over NO_TRUNCATE is (IMO)
> that this option also works on full and incremental backups. It's a
> great way to quickly get a backup of a DB to do some tests or
> trouble-shooting on without disrupting the backup schema.

Yeah, that's why I was thinking it would be so useful.

I'll have to keep it in mind for when we upgrade to SQL 2005.

(Still have to have THAT particular debate with the finance folks who pay
the bills. :-)

> --
> Hugo Kornelis, SQL Server MVP

Backup Llast log - Primary crashed

HAs any one backed up the log when data files are damaged? I am
following KB article Q253817. It is not working. When I run the backup
command , it gives mdf not exists in sql server 2000. Any one has
success?If you show us the BACKUP LOG command and the exact error message (including
error number etc) we can
hopefully comment. Easiest s to execute these commands from Query Analyzer a
nd not try to drive the UI in EM.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"tram" <tram_e@.hotmail.com> wrote in message news:26ee1067.0404010502.2f64ff17@.posting.goog
le.com...
> HAs any one backed up the log when data files are damaged? I am
> following KB article Q253817. It is not working. When I run the backup
> command , it gives mdf not exists in sql server 2000. Any one has
> success?|||Thanks for your reply. The first scenario was master was intcact, lost
the datafile of DB. Then the backup log with no_truncate worked
fine.(though it giving errors with MSDE version).
The second scenario, master was intact. The datafile of DB lost. I've
created another db and dropped both daat and log of newly created db.
Copy the crashed DB log to new DB as per KB article except using the
same master. When sql server starts up, that is making the new DB
offline. When run the backup log with no_truncate, it gives error.
Could not open FCB for invalid file ID 0 in database 'test1'
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<#
#mDbq#FEHA.3540@.TK2MSFTNGP09.phx.gbl>...
> If you show us the BACKUP LOG command and the exact error message (includi
ng error number etc) we can
> hopefully comment. Easiest s to execute these commands from Query Analyzer
and not try to drive the UI in EM.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "tram" <tram_e@.hotmail.com> wrote in message news:26ee1067.0404010502.2f64
ff17@.posting.google.com...

Backup Llast log - Primary crashed

HAs any one backed up the log when data files are damaged? I am
following KB article Q253817. It is not working. When I run the backup
command , it gives mdf not exists in sql server 2000. Any one has
success?
If you show us the BACKUP LOG command and the exact error message (including error number etc) we can
hopefully comment. Easiest s to execute these commands from Query Analyzer and not try to drive the UI in EM.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"tram" <tram_e@.hotmail.com> wrote in message news:26ee1067.0404010502.2f64ff17@.posting.google.c om...
> HAs any one backed up the log when data files are damaged? I am
> following KB article Q253817. It is not working. When I run the backup
> command , it gives mdf not exists in sql server 2000. Any one has
> success?
|||Thanks for your reply. The first scenario was master was intcact, lost
the datafile of DB. Then the backup log with no_truncate worked
fine.(though it giving errors with MSDE version).
The second scenario, master was intact. The datafile of DB lost. I've
created another db and dropped both daat and log of newly created db.
Copy the crashed DB log to new DB as per KB article except using the
same master. When sql server starts up, that is making the new DB
offline. When run the backup log with no_truncate, it gives error.
Could not open FCB for invalid file ID 0 in database 'test1'
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<##mDbq#FEHA.3540@.TK2MSFTNGP09.phx.gbl>...
> If you show us the BACKUP LOG command and the exact error message (including error number etc) we can
> hopefully comment. Easiest s to execute these commands from Query Analyzer and not try to drive the UI in EM.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "tram" <tram_e@.hotmail.com> wrote in message news:26ee1067.0404010502.2f64ff17@.posting.google.c om...

backup jobs not runing on mssql7

Monday, February 13, 2012

backup jobs not runing on mssql7

Hi there,
I have a mssql-7 and 2/5 dbs get backed up nightly through
the scheduled backup jobs. 3 dbs keep failing at nights
and if I run the job manually. I can manually backup the
DBs through ALL TASKS..BACKUP DATABASE.
I've Checked:
SP-4
service running
sql agent running as admin
Job enabled
MDAC (latest)
jobs are 15mins apart
Any suggestions are appriciated.
ThanXHave you checked the logs and Agent history? What is the error that gets
produced?
--
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
> Hi there,
> I have a mssql-7 and 2/5 dbs get backed up nightly through
> the scheduled backup jobs. 3 dbs keep failing at nights
> and if I run the job manually. I can manually backup the
> DBs through ALL TASKS..BACKUP DATABASE.
> I've Checked:
> SP-4
> service running
> sql agent running as admin
> Job enabled
> MDAC (latest)
> jobs are 15mins apart
> Any suggestions are appriciated.
> ThanX
>|||Andrew,
I have cheched the agent log and its clean! and the server
log doesn't mention anything regarding the job failure.
ThanX
>--Original Message--
>Have you checked the logs and Agent history? What is the
error that gets
>produced?
>--
>Andrew J. Kelly SQL MVP
>
>"Emil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
>> Hi there,
>> I have a mssql-7 and 2/5 dbs get backed up nightly
through
>> the scheduled backup jobs. 3 dbs keep failing at nights
>> and if I run the job manually. I can manually backup the
>> DBs through ALL TASKS..BACKUP DATABASE.
>> I've Checked:
>> SP-4
>> service running
>> sql agent running as admin
>> Job enabled
>> MDAC (latest)
>> jobs are 15mins apart
>> Any suggestions are appriciated.
>> ThanX
>>
>
>.
>|||What about the Job History? Right click on thjob in EM and choose Show
History. Then check the checkbox labeled Show Details and see what that
says.
--
Andrew J. Kelly SQL MVP
"Emil" <anonymous@.discussions.microsoft.com> wrote in message
news:f29b01c43dad$56d3bc90$a601280a@.phx.gbl...
> Andrew,
> I have cheched the agent log and its clean! and the server
> log doesn't mention anything regarding the job failure.
> ThanX
> >--Original Message--
> >Have you checked the logs and Agent history? What is the
> error that gets
> >produced?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Emil" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:ed5901c43d33$cbb41660$a101280a@.phx.gbl...
> >> Hi there,
> >> I have a mssql-7 and 2/5 dbs get backed up nightly
> through
> >> the scheduled backup jobs. 3 dbs keep failing at nights
> >> and if I run the job manually. I can manually backup the
> >> DBs through ALL TASKS..BACKUP DATABASE.
> >>
> >> I've Checked:
> >> SP-4
> >> service running
> >> sql agent running as admin
> >> Job enabled
> >> MDAC (latest)
> >> jobs are 15mins apart
> >>
> >> Any suggestions are appriciated.
> >> ThanX
> >>
> >>
> >
> >
> >.
> >

Thursday, March 29, 2012

Backup Strategy

Monday, March 19, 2012

BackUp Question

Sunday, March 11, 2012

backup problem

Backup Problem

Backup Problem

Friday, February 24, 2012

backup not working

backup not working

backup not working

Thursday, February 16, 2012

Backup log - will it truncate?

Backup Llast log - Primary crashed

Backup Llast log - Primary crashed

backup jobs not runing on mssql7

Monday, February 13, 2012

backup jobs not runing on mssql7

backup jobs not runing on mssql7

sql backup files

Blog Archive

About Me