sql backup files: running

Showing posts with label running. 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

Backup Strategy

I am running SQL Server 2005 x64 Enterprise under Window Server 2003 x64 Enterprise. After reviewing many posts and suggestions in this forum, I am developing a backup strategy that should include keeping my transaction log file in a manageble size.

Please examine the following proposed backup schedule and let me know if this is considered a sound plan. The scripts below will write to disk and each night and then be backed up to tape.

*** TASK 1 ***

Backup transaction log

/* This script backs up the DSS database transaction log to disk, overwriting any
previous backup
*/

BACKUP LOG [DSS]

TO DISK = N'g:\mssql\backup\log\dss_log.bak'

WITH

INIT

, NAME = N'DSS-Transaction Log Backup'

*** TASK 2 ***

/* This script shrinks the DSS database transaction log file

BACKUP LOG [DSS] with truncate_only

dbcc shrinkfile(DSS_log)

**** TASK 3 ****

/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:\mssql\backup\database\DSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:\mssql\backup\database\DSS.bak'
WITH FILE = 1
GO

*** TASK 4 ***

Update statistics on the DSS database

(a) what you need to understand here is... if you want to restore the database in POINT IN Time (eg. just 5 min before the database corrupted), you need to keep the TL Backup Chain. Ie. you should not overwrite the TL Backup in any case. If you don't what Point Time restore , you don't need to keep the TL backup atall. Change the Recovery Model to Simple and take frequen Full Backup, it will control the grwoth of TL.

So your first step , Re-writing the TL Backup need to Re-looked as per the requirement.

(b) IF you want to keep the Datbase in Full recovery and you need to take the TL backup, you should not truncate & shrink the TL frequently. By doing this what will happen is, again your backup chain will fail. IF you want to shrink the TL, after shrinking the first step should be the full backup, all other old TL backups are invalid if you shrink the TL. So create a full backup script run after shrinking the TL if at all you want to shrik TL

(c) If your are keep in the full backup to any other place after backup up its ok.. otherwise, you are verifying after you overwrites the backup file, if the new backup taken is corrupted , then you will end up with no backup

Madhu

|||

Thank you for the clarification Madhu. Your comments have helped me to wrap my mind around how backup strategies work in SQL2005. A full database back up to disk is performed each night and then written to tape. In the event of a failure, we would only lose 24 hours which at this time is acceptable to the company. To provide better recovery I believe I will perform full backups several times throughout the day.

With this said, it would appear that a good strategy for us would be to change the recovery mode to simple, to manage the transaction log files size, and perform full database backups every 2 hours.

Thanks again!

Tuesday, March 27, 2012

Backup SQL server using msbackup

Guys, I want to be able to use MSBackup to create a backup of my Windows
2000 server with SQL 2000 server running. I need to be able to include the
SQL databases etc in this backup but obviously the files are open when the
SQL server is running and can not be backed up so I need to stop the service
before the backup and restart it after. Can anyone tell me how best to do
this? I do not want to have to install any other applications on this server
so I am hoping that the various Windows default programs will alow me to
achive this goal.
Thanks for your help with this.Gerry
For SQL Server you will better of to use BACKUP command of T-SQL
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
news:120bh7gpkhdsbc5@.corp.supernews.com...
> Guys, I want to be able to use MSBackup to create a backup of my Windows
> 2000 server with SQL 2000 server running. I need to be able to include the
> SQL databases etc in this backup but obviously the files are open when the
> SQL server is running and can not be backed up so I need to stop the
> service before the backup and restart it after. Can anyone tell me how
> best to do this? I do not want to have to install any other applications
> on this server so I am hoping that the various Windows default programs
> will alow me to achive this goal.
> Thanks for your help with this.
>|||Uri, thanks for the response but I do not know much about SQL so this is a
bit over my head, can you explain a bit more what you mean by that? I assume
you are referring to a SQL command and therefore I will have to create a
script of some sort... I want to use MSBackup to create a single source of
recovery for the entire server inclusive of the SQL databases. I was hoping
to create a schedule that would stop the SQL server, start the MSBackup,
then start the SQL server again after the backup is complete. Any
suggetsions on this? Maybe I could use batch files?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O1YrqnUPGHA.812@.TK2MSFTNGP10.phx.gbl...
> Gerry
> For SQL Server you will better of to use BACKUP command of T-SQL
>
> "Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
> news:120bh7gpkhdsbc5@.corp.supernews.com...
>> Guys, I want to be able to use MSBackup to create a backup of my Windows
>> 2000 server with SQL 2000 server running. I need to be able to include
>> the SQL databases etc in this backup but obviously the files are open
>> when the SQL server is running and can not be backed up so I need to stop
>> the service before the backup and restart it after. Can anyone tell me
>> how best to do this? I do not want to have to install any other
>> applications on this server so I am hoping that the various Windows
>> default programs will alow me to achive this goal.
>> Thanks for your help with this.
>|||MSBackup cannot back up a working SQL Server by itself. More importantly,
it cannot restore a working SQL Server by itself. It cannot get a
consistent picture of the multiple files that comprise a SQL database with
the system working. SQL includes a utility to create a transactionally
consistant self-describing backup that can be stored on one or more disk
files. Typical best practice is to back up the SQL Server to a disk (local
or network) and then back up those files to some external archive system
such as a tape library.
If you are responsible for restoring the SQL host sytem and database engine
in case of failure or disaster, I strongly suggest you read the section in
BOL (Books-On-Line) on Backuping Up and Restoring Databases (It's under
"Administering SQL Server" in the TOC). SQL backup and recovery is
different from ordinary file-system type restore. Expecting SQL to recover
just like a non-SQL system will lead to failure, disappointment, and
potential unemployment.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
news:120bk4crsfj5s9b@.corp.supernews.com...
> Uri, thanks for the response but I do not know much about SQL so this is a
> bit over my head, can you explain a bit more what you mean by that? I
> assume you are referring to a SQL command and therefore I will have to
> create a script of some sort... I want to use MSBackup to create a single
> source of recovery for the entire server inclusive of the SQL databases. I
> was hoping to create a schedule that would stop the SQL server, start the
> MSBackup, then start the SQL server again after the backup is complete.
> Any suggetsions on this? Maybe I could use batch files?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O1YrqnUPGHA.812@.TK2MSFTNGP10.phx.gbl...
>> Gerry
>> For SQL Server you will better of to use BACKUP command of T-SQL
>>
>> "Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
>> news:120bh7gpkhdsbc5@.corp.supernews.com...
>> Guys, I want to be able to use MSBackup to create a backup of my Windows
>> 2000 server with SQL 2000 server running. I need to be able to include
>> the SQL databases etc in this backup but obviously the files are open
>> when the SQL server is running and can not be backed up so I need to
>> stop the service before the backup and restart it after. Can anyone tell
>> me how best to do this? I do not want to have to install any other
>> applications on this server so I am hoping that the various Windows
>> default programs will alow me to achive this goal.
>> Thanks for your help with this.
>>
>|||Gerry Armstrong wrote:
> Guys, I want to be able to use MSBackup to create a backup of my Windows
> 2000 server with SQL 2000 server running. I need to be able to include the
> SQL databases etc in this backup but obviously the files are open when the
> SQL server is running and can not be backed up so I need to stop the service
> before the backup and restart it after. Can anyone tell me how best to do
> this? I do not want to have to install any other applications on this server
> so I am hoping that the various Windows default programs will alow me to
> achive this goal.
> Thanks for your help with this.
>
Hi Gerry
if you really need to backup using msbackup, and assuming that you've
got a default instance, run:
net stop sqlserveragent
net stop mssqlserver
to stop, and net start will get them back up and running again.|||Ok, judging from these responses I will be suggesting to the customer that
they purchase a backup product that will include the SQL agent. The main
issue here is that the customer is small and there is no-one onsight with
enough knowledge to be able to do anything technical so I am trying to find
the simplest most straightforward way of providing them with a backup that
will also be the easiest means of disaster recovery. I assumed that if the
SQL was not running and the server could access all the files that I would
be able to do a full backup and in the event of failure do a full restore
and the databases will work exactly as they were before the backup was
started but maybe my assumption is wrong like most assumptions.....
"Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
news:du4n3t$1gq$1@.news.freedom2surf.net...
> Gerry Armstrong wrote:
>> Guys, I want to be able to use MSBackup to create a backup of my Windows
>> 2000 server with SQL 2000 server running. I need to be able to include
>> the SQL databases etc in this backup but obviously the files are open
>> when the SQL server is running and can not be backed up so I need to stop
>> the service before the backup and restart it after. Can anyone tell me
>> how best to do this? I do not want to have to install any other
>> applications on this server so I am hoping that the various Windows
>> default programs will alow me to achive this goal.
>> Thanks for your help with this.
>
> Hi Gerry
> if you really need to backup using msbackup, and assuming that you've got
> a default instance, run:
> net stop sqlserveragent
> net stop mssqlserver
> to stop, and net start will get them back up and running again.|||In theory that should work. But I'd never trust that. Create SQL Server Agent jobs for your clients
(possibly using the Database Maintenance Wizard) which backup the database to files and then pick up
those files using ntbackup.
You can of course safeguard with *also* stopping SQL Server and grab the raw database file. Restore
from that is *likely* to work, and if it doesn't, you have the SQL server backups as a fall-back.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message news:120bs57po9qave9@.corp.supernews.com...
> Ok, judging from these responses I will be suggesting to the customer that they purchase a backup
> product that will include the SQL agent. The main issue here is that the customer is small and
> there is no-one onsight with enough knowledge to be able to do anything technical so I am trying
> to find the simplest most straightforward way of providing them with a backup that will also be
> the easiest means of disaster recovery. I assumed that if the SQL was not running and the server
> could access all the files that I would be able to do a full backup and in the event of failure do
> a full restore and the databases will work exactly as they were before the backup was started but
> maybe my assumption is wrong like most assumptions.....
> "Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
> news:du4n3t$1gq$1@.news.freedom2surf.net...
>> Gerry Armstrong wrote:
>> Guys, I want to be able to use MSBackup to create a backup of my Windows 2000 server with SQL
>> 2000 server running. I need to be able to include the SQL databases etc in this backup but
>> obviously the files are open when the SQL server is running and can not be backed up so I need
>> to stop the service before the backup and restart it after. Can anyone tell me how best to do
>> this? I do not want to have to install any other applications on this server so I am hoping that
>> the various Windows default programs will alow me to achive this goal.
>> Thanks for your help with this.
>>
>> Hi Gerry
>> if you really need to backup using msbackup, and assuming that you've got a default instance,
>> run:
>> net stop sqlserveragent
>> net stop mssqlserver
>> to stop, and net start will get them back up and running again.
>|||As Tibor said, I wouldn't trust a quiesced system to be restorable. I also
do not recommend purchasing a third-party SQL backup solution with a SQL
agent. I do recommend reading about the system you are giving advice on.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
news:120bs57po9qave9@.corp.supernews.com...
> Ok, judging from these responses I will be suggesting to the customer that
> they purchase a backup product that will include the SQL agent. The main
> issue here is that the customer is small and there is no-one onsight with
> enough knowledge to be able to do anything technical so I am trying to
> find the simplest most straightforward way of providing them with a backup
> that will also be the easiest means of disaster recovery. I assumed that
> if the SQL was not running and the server could access all the files that
> I would be able to do a full backup and in the event of failure do a full
> restore and the databases will work exactly as they were before the backup
> was started but maybe my assumption is wrong like most assumptions.....
> "Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
> news:du4n3t$1gq$1@.news.freedom2surf.net...
>> Gerry Armstrong wrote:
>> Guys, I want to be able to use MSBackup to create a backup of my Windows
>> 2000 server with SQL 2000 server running. I need to be able to include
>> the SQL databases etc in this backup but obviously the files are open
>> when the SQL server is running and can not be backed up so I need to
>> stop the service before the backup and restart it after. Can anyone tell
>> me how best to do this? I do not want to have to install any other
>> applications on this server so I am hoping that the various Windows
>> default programs will alow me to achive this goal.
>> Thanks for your help with this.
>>
>> Hi Gerry
>> if you really need to backup using msbackup, and assuming that you've got
>> a default instance, run:
>> net stop sqlserveragent
>> net stop mssqlserver
>> to stop, and net start will get them back up and running again.
>|||Ok Guys, I take all your suggestions under advisment... guess I got more
reading to do as if I don't have enough going on in my head as yet.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecr9qSWPGHA.2624@.TK2MSFTNGP12.phx.gbl...
> In theory that should work. But I'd never trust that. Create SQL Server
> Agent jobs for your clients (possibly using the Database Maintenance
> Wizard) which backup the database to files and then pick up those files
> using ntbackup.
> You can of course safeguard with *also* stopping SQL Server and grab the
> raw database file. Restore from that is *likely* to work, and if it
> doesn't, you have the SQL server backups as a fall-back.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
> news:120bs57po9qave9@.corp.supernews.com...
>> Ok, judging from these responses I will be suggesting to the customer
>> that they purchase a backup product that will include the SQL agent. The
>> main issue here is that the customer is small and there is no-one onsight
>> with enough knowledge to be able to do anything technical so I am trying
>> to find the simplest most straightforward way of providing them with a
>> backup that will also be the easiest means of disaster recovery. I
>> assumed that if the SQL was not running and the server could access all
>> the files that I would be able to do a full backup and in the event of
>> failure do a full restore and the databases will work exactly as they
>> were before the backup was started but maybe my assumption is wrong like
>> most assumptions.....
>> "Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
>> news:du4n3t$1gq$1@.news.freedom2surf.net...
>> Gerry Armstrong wrote:
>> Guys, I want to be able to use MSBackup to create a backup of my
>> Windows 2000 server with SQL 2000 server running. I need to be able to
>> include the SQL databases etc in this backup but obviously the files
>> are open when the SQL server is running and can not be backed up so I
>> need to stop the service before the backup and restart it after. Can
>> anyone tell me how best to do this? I do not want to have to install
>> any other applications on this server so I am hoping that the various
>> Windows default programs will alow me to achive this goal.
>> Thanks for your help with this.
>>
>> Hi Gerry
>> if you really need to backup using msbackup, and assuming that you've
>> got a default instance, run:
>> net stop sqlserveragent
>> net stop mssqlserver
>> to stop, and net start will get them back up and running again.
>>
>

Backup SQL server using msbackup

backup sql server online

Hi,
I new to sql database server administration. Currently I had a sql server
running on an win2000 server. Every night there is a full system backup, it
will backup all the hardisk partitions contents to tape. The sql server
services are not stop.
1.) By backuping up the sql server database online, will it cause any
corruption to the database?
2.) Is the database backup valid? If there is a media failure, can we used
the backup for recovery?
3.) How to apply the latest changes to the database if some changes was not
been backup?
4.) Any link/documents that I can refer to for the sql database server backup?
Thank You.This is not the recommended method of backing up SQL Server databases.
Because SQL Server is running during the backup the files will either
be 'open' and therefore not backed up properly or, if the backup
software is intelligent enough to be able to back these up, it still
doesn't give you recovery to a given point.
If you look at SQL Bokks Online you will find reference to Backups.
There are wizards that let you set up SQL Maintenance Plans. You may
need to also do regular Transaction Log Backups, dependent upon the the
Database Mode and what your recovery requirements are.
Regards
ALI
bin wrote:
> Hi,
> I new to sql database server administration. Currently I had a sql server
> running on an win2000 server. Every night there is a full system backup, it
> will backup all the hardisk partitions contents to tape. The sql server
> services are not stop.
> 1.) By backuping up the sql server database online, will it cause any
> corruption to the database?
> 2.) Is the database backup valid? If there is a media failure, can we used
> the backup for recovery?
> 3.) How to apply the latest changes to the database if some changes was not
> been backup?
> 4.) Any link/documents that I can refer to for the sql database server backup?
> Thank You.|||No. File system backups aren't really good for SQL Server databases. SQL
Server provides a native and online way to backup the databases, using the
BACKUP command. Databases can be restored from these backup files (created
by BACKUP command), using the RESTORE command.
See SQL Server 2000 Books Online for more information on how Backup and
Restore work. Also check out the database manintenance wizard in SQL Server
Enterprise Manager.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bin" <bin@.discussions.microsoft.com> wrote in message
news:572878EE-7CF2-487F-B720-AB9F54635B9F@.microsoft.com...
Hi,
I new to sql database server administration. Currently I had a sql server
running on an win2000 server. Every night there is a full system backup, it
will backup all the hardisk partitions contents to tape. The sql server
services are not stop.
1.) By backuping up the sql server database online, will it cause any
corruption to the database?
2.) Is the database backup valid? If there is a media failure, can we used
the backup for recovery?
3.) How to apply the latest changes to the database if some changes was not
been backup?
4.) Any link/documents that I can refer to for the sql database server
backup?
Thank You.|||Hi,
So if the sql server services are shutdown before the backup. It should
be ok right?
"Narayana Vyas Kondreddi" wrote:
> No. File system backups aren't really good for SQL Server databases. SQL
> Server provides a native and online way to backup the databases, using the
> BACKUP command. Databases can be restored from these backup files (created
> by BACKUP command), using the RESTORE command.
> See SQL Server 2000 Books Online for more information on how Backup and
> Restore work. Also check out the database manintenance wizard in SQL Server
> Enterprise Manager.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "bin" <bin@.discussions.microsoft.com> wrote in message
> news:572878EE-7CF2-487F-B720-AB9F54635B9F@.microsoft.com...
> Hi,
> I new to sql database server administration. Currently I had a sql server
> running on an win2000 server. Every night there is a full system backup, it
> will backup all the hardisk partitions contents to tape. The sql server
> services are not stop.
> 1.) By backuping up the sql server database online, will it cause any
> corruption to the database?
> 2.) Is the database backup valid? If there is a media failure, can we used
> the backup for recovery?
> 3.) How to apply the latest changes to the database if some changes was not
> been backup?
> 4.) Any link/documents that I can refer to for the sql database server
> backup?
> Thank You.
>
>|||Well, if the SQL Server Services are stopped and then the O/S level
backup is carried out then this wil give you usable SQL database files
for potential recovery but this is still not the 'correct' way of going
about this!
Use SQL's native BACKUP to backup the database(s) then run the O/S
level backups against the files created by the BACKUP.|||Probably, if you are willing to shutdown the SQL Server while the files are
backed up. But there's no guarantee that a file that wasn't properly
detached using sp_detach_db, can be attached to SQL Server using
sp_attach_db. As someone else also said, it is not a recommended practice.
Use Backup Restore instead.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bin" <bin@.discussions.microsoft.com> wrote in message
news:E4D44FD0-96E2-4296-B628-88F9C26A5634@.microsoft.com...
Hi,
So if the sql server services are shutdown before the backup. It should
be ok right?
"Narayana Vyas Kondreddi" wrote:
> No. File system backups aren't really good for SQL Server databases. SQL
> Server provides a native and online way to backup the databases, using the
> BACKUP command. Databases can be restored from these backup files (created
> by BACKUP command), using the RESTORE command.
> See SQL Server 2000 Books Online for more information on how Backup and
> Restore work. Also check out the database manintenance wizard in SQL
Server
> Enterprise Manager.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "bin" <bin@.discussions.microsoft.com> wrote in message
> news:572878EE-7CF2-487F-B720-AB9F54635B9F@.microsoft.com...
> Hi,
> I new to sql database server administration. Currently I had a sql
server
> running on an win2000 server. Every night there is a full system backup,
it
> will backup all the hardisk partitions contents to tape. The sql server
> services are not stop.
> 1.) By backuping up the sql server database online, will it cause any
> corruption to the database?
> 2.) Is the database backup valid? If there is a media failure, can we used
> the backup for recovery?
> 3.) How to apply the latest changes to the database if some changes was
not
> been backup?
> 4.) Any link/documents that I can refer to for the sql database server
> backup?
> Thank You.
>
>sql

Backup SQL Database

The menu item "Backup SQL Database" does not appear as a
choice under Database Utilities when running an Access
2003 Project connected to an MSDE 2000 database on the
local machine. Why?
hi,
JBMoore wrote:
> The menu item "Backup SQL Database" does not appear as a
> choice under Database Utilities when running an Access
> 2003 Project connected to an MSDE 2000 database on the
> local machine. Why?
I've no particulary skill with Access, but the menu item is available to me
even when connected to a particular database with "public" role membership
only, as I thought that under circumstances of being member of
db_backupoperator database role at least was the reason of the menu item not
being shown... but is not the case.. ok the backup failed for insufficient
privileges :D:D
perhaps you've better post the question on Office NG...
sorry
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Sunday, March 25, 2012

Backup SQL 2005 - Restore In SQL 2000

Hi,
I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
properties) and need to restore it back to another machine running SQL 2000
SP4. I am getting errors and cannot find a way to do it? Can any one help
please?
Thanks
Mike
One way to do that is running DTS Packages to transfer the data
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:987F2970-3600-4207-ACCB-E397C7F2AE6D@.microsoft.com...
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from
> the
> properties) and need to restore it back to another machine running SQL
> 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike
|||sonicm wrote:
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
> properties) and need to restore it back to another machine running SQL 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike
You cannot restore backwards across versions like this... You'll have
to migrate the data and schema using import/export, DTS, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql

Backup SQL 2005 - Restore In SQL 2000

Hi,
I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
properties) and need to restore it back to another machine running SQL 2000
SP4. I am getting errors and cannot find a way to do it? Can any one help
please?
Thanks
MikeOne way to do that is running DTS Packages to transfer the data
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:987F2970-3600-4207-ACCB-E397C7F2AE6D@.microsoft.com...
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from
> the
> properties) and need to restore it back to another machine running SQL
> 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike|||sonicm wrote:
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from t
he
> properties) and need to restore it back to another machine running SQL 200
0
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike
You cannot restore backwards across versions like this... You'll have
to migrate the data and schema using import/export, DTS, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup SQL 2005 - Restore In SQL 2000

Hi,
I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
properties) and need to restore it back to another machine running SQL 2000
SP4. I am getting errors and cannot find a way to do it? Can any one help
please?
Thanks
MikeOne way to do that is running DTS Packages to transfer the data
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:987F2970-3600-4207-ACCB-E397C7F2AE6D@.microsoft.com...
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from
> the
> properties) and need to restore it back to another machine running SQL
> 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike|||sonicm wrote:
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
> properties) and need to restore it back to another machine running SQL 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike
You cannot restore backwards across versions like this... You'll have
to migrate the data and schema using import/export, DTS, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

backup speed SQL 2000

I'm running a manually written nightly backup script on all dbs on a
server. it's a dev server FYI.
the script does the following for all databases:
dbcc checkdb
dbcc dbreindex (on all relevant tables) -- this is done once a week
only
backup transaction with no_log
backup database
now... the duration for this is around 2 hours, given the number &
size of the databases. however, on the night I do the dbcc dbreindex,
the duration drops to 5 minutes. i've added the dbcc dbreindex to run
on other nights, and yes it drops down to 5 minutes each time. next
time it runs without, back up to 2 hours.....
why?
if any other info is required, i will supply.
Specify an output file and check for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1179959856.718711.314480@.x35g2000prf.googlegr oups.com...
> I'm running a manually written nightly backup script on all dbs on a
> server. it's a dev server FYI.
> the script does the following for all databases:
> dbcc checkdb
> dbcc dbreindex (on all relevant tables) -- this is done once a week
> only
> backup transaction with no_log
> backup database
> now... the duration for this is around 2 hours, given the number &
> size of the databases. however, on the night I do the dbcc dbreindex,
> the duration drops to 5 minutes. i've added the dbcc dbreindex to run
> on other nights, and yes it drops down to 5 minutes each time. next
> time it runs without, back up to 2 hours.....
> why?
> if any other info is required, i will supply.
>

backup speed SQL 2000

I'm running a manually written nightly backup script on all dbs on a
server. it's a dev server FYI.
the script does the following for all databases:
dbcc checkdb
dbcc dbreindex (on all relevant tables) -- this is done once a week
only
backup transaction with no_log
backup database
now... the duration for this is around 2 hours, given the number &
size of the databases. however, on the night I do the dbcc dbreindex,
the duration drops to 5 minutes. i've added the dbcc dbreindex to run
on other nights, and yes it drops down to 5 minutes each time. next
time it runs without, back up to 2 hours.....
why?
if any other info is required, i will supply.Specify an output file and check for error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1179959856.718711.314480@.x35g2000prf.googlegroups.com...
> I'm running a manually written nightly backup script on all dbs on a
> server. it's a dev server FYI.
> the script does the following for all databases:
> dbcc checkdb
> dbcc dbreindex (on all relevant tables) -- this is done once a week
> only
> backup transaction with no_log
> backup database
> now... the duration for this is around 2 hours, given the number &
> size of the databases. however, on the night I do the dbcc dbreindex,
> the duration drops to 5 minutes. i've added the dbcc dbreindex to run
> on other nights, and yes it drops down to 5 minutes each time. next
> time it runs without, back up to 2 hours.....
> why?
> if any other info is required, i will supply.
>sql

backup speed SQL 2000

I'm running a manually written nightly backup script on all dbs on a
server. it's a dev server FYI.
the script does the following for all databases:
dbcc checkdb
dbcc dbreindex (on all relevant tables) -- this is done once a week
only
backup transaction with no_log
backup database
now... the duration for this is around 2 hours, given the number &
size of the databases. however, on the night I do the dbcc dbreindex,
the duration drops to 5 minutes. i've added the dbcc dbreindex to run
on other nights, and yes it drops down to 5 minutes each time. next
time it runs without, back up to 2 hours.....
why?
if any other info is required, i will supply.Specify an output file and check for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1179959856.718711.314480@.x35g2000prf.googlegroups.com...
> I'm running a manually written nightly backup script on all dbs on a
> server. it's a dev server FYI.
> the script does the following for all databases:
> dbcc checkdb
> dbcc dbreindex (on all relevant tables) -- this is done once a week
> only
> backup transaction with no_log
> backup database
> now... the duration for this is around 2 hours, given the number &
> size of the databases. however, on the night I do the dbcc dbreindex,
> the duration drops to 5 minutes. i've added the dbcc dbreindex to run
> on other nights, and yes it drops down to 5 minutes each time. next
> time it runs without, back up to 2 hours.....
> why?
> if any other info is required, i will supply.
>

Backup solution - question about transaction log

I've just inherited (i.e., our sys admin / DBA left the company) a fairly small SQL Server that's running 7 production databases. Most are quite small, but there are two which are about 40gb each. Traffic is quite low - ~30-40 users at one time doing your basic SELECT / UPDATE / INSERT stuff.

Anyway, I was going through some of the backups jobs and noticed that the transaction logs for each database were absolutely huge (in some cases bigger than the DB itself) which led me to think the log wasn't getting truncated.

The T-SQL being run in each case was

Currently, the transaction log for 6 DBs is backed up 3 times a day (and the 7th, "mission critical" DB is backed up every 15 minutes) with the following T-SQL:

BACKUP LOG <database> to <device> WITH NOINIT, NOFORMAT, NOSKIP, NOUNLOAD

5 of the 7 databases get a full backup twice a day, with the 2 larger ones getting a differential, with e.g.,

BACKUP DATABASE <database> TO <device> WITH NOINIT , NOUNLOAD , NAME = N'db', NOSKIP , STATS = 10, DESCRIPTION = N'db', NOFORMAT , MEDIANAME = N'db'DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='db'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='db')
RESTORE VERIFYONLY FROM <device> WITH FILE = @.i

This is then backed up to tape each night.

Looking through the documentation, those WITH commands are largely the default settings so I'm not sure why they're specified explicitly.

If I issue a

BACKUP LOG <database> to <device> WITH INIT, SKIP

then the log does get truncated. However, could someone explain the implications of that for me? As I understand it, INIT will overwrite any existing sets in the device, but considering that it will always backup anything that hasn't been committed then should it be a problem?

Alternatively could someone perhaps explain why the log wasn't getting truncated? It is my understanding that this should happen every time a full backup is completed... which is twice a day. Or does the Transaction log need to be manually shrunk every now and then?

Also, I understand the DECLARE... part in the last part of that DB backup SQL, but is it at all necessary?

Finally, does this backup strategy seem viable? Any thoughts and comments are appreciated!
Matt

By default in the backup database command if you do not specify anything it will append ,eg...

backup database xxxx to disk='F:\test\xxxx.bak' and check the bak file size

now again reissue the same command,

backup database xxxx to disk='F:\test\xxxx.bak' and once again check the bak file size.......it will be double the size

next time issue the same command using WITH INIT option,

backup database xxxx to disk='F:\test\xxxx.bak' WITH INIT now see the bak file size it will be the same size as in 1st case.........if you do not specify anything SQL server will use the default With NOINIT option..........so you need to specify them explicitly if you needed to overwrite........

the log gets truncated if you specify the command,

backup log dbname with truncate_only.........this command will be depreciated in future versions........once you issue this comand, all the committed transactions will be rolled forward and written to data file and uncommitted trans will be rolled back.......

since you have a mission critical db i suggest you go for Log shipping or Database mirroring in sql 2005.........instead of relying on your db backups.........if you have sufficient disk space go for full backup and if you feel the db is important and if you want to have db consistency you can have tran log backups and differential backups else not required........

|||Am I right about when the transaction log should get truncated - i.e., on a full database backup?|||

You are right, that the transaction log should be truncated after a full backup. However, note that the contents of the log will be truncated and the physical file will maintain its size. Therefore, you may see a large transaction log file although it won't be full. In order to reduce the size of the file, you'll need to run some form of SHRINKFILE operation.

HTH!

Backup Slow Throughput

Hi Team,

I have a SQL 2000 instance with 46 Databases (all databases put together will be 15 GB in size). I am running a SQL Backup using a third party software.

My full backup of the SQL instance which backs up 15 GB of data finishes within 30-45 minutes. But, my differential backup of the same instance which backs up only 150 -250 MB of data takes 12 hours to backup.

I found a knowledge article from MS Support site which says, differntial backup would take more time than full in few scenarios.

http://support.microsoft.com/default.aspx?scid=kb;en-us;196658

But the above document is for SQL 7.0. Will it be the same for SQL 2000 and 2005 too? If yes, can you please tell if I can increase the speed of differential backup in my environment. Should I modify any SQL Parameters?

Please let me know your thoughts on this..

Thanks
Santhosh

This really doesn't make sense.

The codepath for differential backups is the same as for fulls. The only difference is that in differential backups, we optionally skip writing some pages to the backup stream. The test to see if we should skip a page is very fast. Generally, worst case for differential backups is that they take the same time as a full backup.

Is there perhaps some other backup activity going on at the same time which could be causing lock contention?

If not, you can try starting SQL with traceflags 3605 and 3004 and sending the log to us (kevin.farlee@.microsoft.com). Those flags will cause more verbose logging about the backup process.

|||

"starting SQL with traceflags 3605 and 3004" - Can you be more specific on how to start SQL with trace flags.

I have not done this before. Also , it would be great if you could say what are we going to achieve by starting SQL with traceflags 3605 and 3004 .

Thank you,

Santhosh

|||

You can enable the trace flags with the command:

DBCC TRACEON(3004, 3605, -1)

What this does is to cause SQL to log much more detailed information about the backup process in the SQL log.

This information will help us to determine what is happening.

After collecting this information, you can use the command

DBCC TRACEOFF(3004, 3605, -1)

to disable the trace flags and go back to normal logging.

Thursday, March 22, 2012

Backup Server

I have just finished my install of SQL2K on Win2003. I
want to make a complete backup of the server. Do I back
with the SQL services running, or do I stop them?Do your normal NT backup, then back up all of the sql databases using SQL
Server Backup...
Some folks ( for the initial setup) will bring down sql and do the NT
backup...
"Chris" <cj.hunter@.excite.com> wrote in message
news:19e3901c3876b$8dca6d50$a601280a@.phx.gbl...
> I have just finished my install of SQL2K on Win2003. I
> want to make a complete backup of the server. Do I back
> with the SQL services running, or do I stop them?

Backup serious appending problem

We are running Small Business Server 2003 and using a SQL
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
LauraYou have to tell us how the backups are done. Are they happening from a
Maintenance plan job or plain BACKUP commands? You can find this by looking
at the job step.
Anyway, you have to use the WITH INIT option for the BACKUP command, if you
don't want to append.
Also, you should think about moving the database backups to a different
machine, to be able to recover from a failure on the SQL Server box.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"laura" <lauras@.microtechsystems.com> wrote in message
news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
We are running Small Business Server 2003 and using a SQL
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
Laura|||Thank you much for the reply..
As I said I do not work with SQL except to troubleshoot
when there is a problem that no one else is trying to
solve, but with your suggestion I did find out 1. What a
job step is
2. Where to look for this information under the
Management ->SQL Server Agent -> Jobs (at least I think I
am looking in the right place..
3. Now I will try to find why there are 4 Backup jobs
listed in this category..
Any other info you can send would be appreciated..
Again, Thank you
Laura
>--Original Message--
>You have to tell us how the backups are done. Are they
happening from a
>Maintenance plan job or plain BACKUP commands? You can
find this by looking
>at the job step.
>Anyway, you have to use the WITH INIT option for the
BACKUP command, if you
>don't want to append.
>Also, you should think about moving the database backups
to a different
>machine, to be able to recover from a failure on the SQL
Server box.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"laura" <lauras@.microtechsystems.com> wrote in message
>news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
>We are running Small Business Server 2003 and using a SQL
>database .. At some point in the setup of this, another
>person apparently setup from 1 to 3 backup jobs .. the
>problem is that there have been 3 backup jobs running
>nightly and that 2 of the 3 are appending to the previous
>backup.. this resulted in our hard drive filling up almost
>to the crash point before we realized there was a
>problem .. I don't know where to look for this ..
>To recover space on the server we had deleted all of the
>backups which are in the c:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP folder...
>I don't work with SQL much and this has become my problem
>to fix .. any help would be appreciated
>Thank you
>Laura
>
>.
>sql

Backup serious appending problem

We are running Small Business Server 2003 and using a SQL
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
Laura
You have to tell us how the backups are done. Are they happening from a
Maintenance plan job or plain BACKUP commands? You can find this by looking
at the job step.
Anyway, you have to use the WITH INIT option for the BACKUP command, if you
don't want to append.
Also, you should think about moving the database backups to a different
machine, to be able to recover from a failure on the SQL Server box.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"laura" <lauras@.microtechsystems.com> wrote in message
news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
We are running Small Business Server 2003 and using a SQL
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
Laura
|||Thank you much for the reply..
As I said I do not work with SQL except to troubleshoot
when there is a problem that no one else is trying to
solve, but with your suggestion I did find out 1. What a
job step is
2. Where to look for this information under the
Management ->SQL Server Agent -> Jobs (at least I think I
am looking in the right place..
3. Now I will try to find why there are 4 Backup jobs
listed in this category..
Any other info you can send would be appreciated..
Again, Thank you
Laura
>--Original Message--
>You have to tell us how the backups are done. Are they
happening from a
>Maintenance plan job or plain BACKUP commands? You can
find this by looking
>at the job step.
>Anyway, you have to use the WITH INIT option for the
BACKUP command, if you
>don't want to append.
>Also, you should think about moving the database backups
to a different
>machine, to be able to recover from a failure on the SQL
Server box.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"laura" <lauras@.microtechsystems.com> wrote in message
>news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
>We are running Small Business Server 2003 and using a SQL
>database .. At some point in the setup of this, another
>person apparently setup from 1 to 3 backup jobs .. the
>problem is that there have been 3 backup jobs running
>nightly and that 2 of the 3 are appending to the previous
>backup.. this resulted in our hard drive filling up almost
>to the crash point before we realized there was a
>problem .. I don't know where to look for this ..
>To recover space on the server we had deleted all of the
>backups which are in the c:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP folder...
>I don't work with SQL much and this has become my problem
>to fix .. any help would be appreciated
>Thank you
>Laura
>
>.
>

Tuesday, March 20, 2012

Backup Restore question

Hi,
SQL Server 2000 SP3 running on Windows 2000 server
Current Backups:
Daily full backup at 10 PM done by Veritas Netbackup
New Backups that will be setup:
Daily Full backup at 3 AM in the morning
Transaction Log backups every hour.
Now my question is, during a restore from the full backup done at 3 AM how
would the restore from transaction logs be affected by the full backup taking
place at 10 PM?
Thanks in advance.You cannot do a restore to a database that is having a backup done at the
same time - and why would you?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
Hi,
SQL Server 2000 SP3 running on Windows 2000 server
Current Backups:
Daily full backup at 10 PM done by Veritas Netbackup
New Backups that will be setup:
Daily Full backup at 3 AM in the morning
Transaction Log backups every hour.
Now my question is, during a restore from the full backup done at 3 AM how
would the restore from transaction logs be affected by the full backup
taking
place at 10 PM?
Thanks in advance.|||Also, you will have to the use the previous full backup if you want to
restore the 10 p.m. transaction log backup
Ash
"sharman" wrote:
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM how
> would the restore from transaction logs be affected by the full backup taking
> place at 10 PM?
> Thanks in advance.
>|||Sorry for not making myself clear. I want to know if I start the new full
backup and the new hourly transaction log backup would I be able to restore
at 11PM with just the 3AM full backup and then restoring the transaction logs
in sequence until 11 PM and just IGNORING the 10 PM full backup.
The reason I ask is because many times during the testing to restore from
the 10 PM backups (that is done by the third party software), I get an error
message and I do not trust that full backup. Therefore I want to set up these
new backups through Enterprise Manager( I have done it this way earlier and I
find them very trustworthy)
Thanks.
"Tom Moreau" wrote:
> You cannot do a restore to a database that is having a backup done at the
> same time - and why would you?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM how
> would the restore from transaction logs be affected by the full backup
> taking
> place at 10 PM?
> Thanks in advance.
>
>|||Yes, you don't have to use the most recent full backup. You can use a
previous full backup and then all of the logs taken after that point in
time.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F39EAE37-615B-4AF7-BA67-13C56F34AB0B@.microsoft.com...
Sorry for not making myself clear. I want to know if I start the new full
backup and the new hourly transaction log backup would I be able to restore
at 11PM with just the 3AM full backup and then restoring the transaction
logs
in sequence until 11 PM and just IGNORING the 10 PM full backup.
The reason I ask is because many times during the testing to restore from
the 10 PM backups (that is done by the third party software), I get an error
message and I do not trust that full backup. Therefore I want to set up
these
new backups through Enterprise Manager( I have done it this way earlier and
I
find them very trustworthy)
Thanks.
"Tom Moreau" wrote:
> You cannot do a restore to a database that is having a backup done at the
> same time - and why would you?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM
> how
> would the restore from transaction logs be affected by the full backup
> taking
> place at 10 PM?
> Thanks in advance.
>
>

Backup reporting false success

I am experiencing the same behavior as below (see snip)on
my SQL Server 7.0
running on NT (lastest service packs and patched on
both.) I too have witnessed the BAK file created and
then
deleted. I do not have the maintance plan configured to
deleting old .BAK files. I do receive this error message
when I create a backup from the "backup" option (not the
database maintenance plan.) This is the message I get:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
percent backed up. [SQLSTATE 01000] (Message 3211)
ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 30 percent
backed up. [SQLSTATE 01000] (Message 3211). The step
failed.
NOTE: I am backing up to the server harddrive(NO NETWORK
INVOLVED!!!)with 20 gigs free. This database is only
199mb in size. I suspect the "general network error" is
bogus.
Please help!
snip from other person with same issue
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>>Server
>>2000 SE SP3A+hotfix. The server has 4 user databases >>
(the
largest being around 500 megs). 3 of the databases
backup
>>fine but 1 database will not take a full backup or a
transaction log backup from the maintenance plan. The
job
runs and succeeds but the file is not where it should be
(nor anywhere else).
The development server with the same configuration does
not have this same problem, and it takes about 7 seconds
to backup the database in question.
I have monitored the directory that the file should be
created in while the job is running, and the file does
create and stays there for the 5 to 7 seconds that the
job
is running and then it dissappears.
I have also used File Monitor from sysinternals and
monitored the system while a backup is being run and
sqlservr.exe and sqlmaint.exe are the only things that
touch the .BAK file. Sqlservr.exe reads and writes to
the .BAK file numerous times and the last thing that
happens is sqlservr.exe opens the .BAK file, then Deletes
the .BAK file, and then closes the .BAK file. No other
applications besides sqlservr.exe and sqlmaint.exe touch
the .BAK file. The index numbers in File Monitor do not
skip any numbers.
Here are the lines from sysinternals File Monitor where
the backup is deleted:
1646 6:32:56 PM sqlservr.exe:3688 OPEN
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS Options: Open Access: All
1647 6:32:56 PM sqlservr.exe:3688 DELETE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
1648 6:32:56 PM sqlservr.exe:3688 CLOSE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
The error message in the errorlog is:
BACKUP failed to complete the command BACKUP LOG [t_prod]
TO DISK = N'E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
Disk space is not an issue as there is over 60 gigs free
and all the databases together barely total a 1 gig.
Backups from a script that run through a job (without the
maint plan wizard) succeed about 75% of the time but
about
25% of the time the job fails with the same errors as the
maint plan fails with. I'm still trying to get File
Monitor running at the time a backup from a script fails,
but haven't been luckly enough to catch it at the right
time yet.
Any information or suggestions to resolve this problem
are
greatly appreaciated as I've been trying to figure this
out for too long. If there is any other information that
would be useful to post, please let me know.
Thanks,
Trent.
.Just a thought... Do you have disk compression turned on?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kathy" <rnurse@.educ.state.ak.us> wrote in message news:799c01c3b458$0ebdd730$a601280a@.phx.gbl...
> I am experiencing the same behavior as below (see snip)on
> my SQL Server 7.0
> running on NT (lastest service packs and patched on
> both.) I too have witnessed the BAK file created and
> then
> deleted. I do not have the maintance plan configured to
> deleting old .BAK files. I do receive this error message
> when I create a backup from the "backup" option (not the
> database maintenance plan.) This is the message I get:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
> percent backed up. [SQLSTATE 01000] (Message 3211)
> ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
> 258) General network error. Check your network
> documentation. [SQLSTATE 08S01] (Error 11) 30 percent
> backed up. [SQLSTATE 01000] (Message 3211). The step
> failed.
> NOTE: I am backing up to the server harddrive(NO NETWORK
> INVOLVED!!!)with 20 gigs free. This database is only
> 199mb in size. I suspect the "general network error" is
> bogus.
> Please help!
> snip from other person with same issue
> >>I have a server with Windows 2000 SP4+hotfixes, SQL
> >>Server
> >>2000 SE SP3A+hotfix. The server has 4 user databases >>
> (the
> largest being around 500 megs). 3 of the databases
> backup
> >>fine but 1 database will not take a full backup or a
> transaction log backup from the maintenance plan. The
> job
> runs and succeeds but the file is not where it should be
> (nor anywhere else).
> The development server with the same configuration does
> not have this same problem, and it takes about 7 seconds
> to backup the database in question.
> I have monitored the directory that the file should be
> created in while the job is running, and the file does
> create and stays there for the 5 to 7 seconds that the
> job
> is running and then it dissappears.
> I have also used File Monitor from sysinternals and
> monitored the system while a backup is being run and
> sqlservr.exe and sqlmaint.exe are the only things that
> touch the .BAK file. Sqlservr.exe reads and writes to
> the .BAK file numerous times and the last thing that
> happens is sqlservr.exe opens the .BAK file, then Deletes
> the .BAK file, and then closes the .BAK file. No other
> applications besides sqlservr.exe and sqlmaint.exe touch
> the .BAK file. The index numbers in File Monitor do not
> skip any numbers.
> Here are the lines from sysinternals File Monitor where
> the backup is deleted:
> 1646 6:32:56 PM sqlservr.exe:3688 OPEN
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS Options: Open Access: All
> 1647 6:32:56 PM sqlservr.exe:3688 DELETE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
> 1648 6:32:56 PM sqlservr.exe:3688 CLOSE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>
> The error message in the errorlog is:
> BACKUP failed to complete the command BACKUP LOG [t_prod]
> TO DISK = N'E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
> Disk space is not an issue as there is over 60 gigs free
> and all the databases together barely total a 1 gig.
> Backups from a script that run through a job (without the
> maint plan wizard) succeed about 75% of the time but
> about
> 25% of the time the job fails with the same errors as the
> maint plan fails with. I'm still trying to get File
> Monitor running at the time a backup from a script fails,
> but haven't been luckly enough to catch it at the right
> time yet.
> Any information or suggestions to resolve this problem
> are
> greatly appreaciated as I've been trying to figure this
> out for too long. If there is any other information that
> would be useful to post, please let me know.
> Thanks,
> Trent.
> .
>|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I should give some explanation to the phenomenon you described in the snip.
The backup process in the SQL Server is an automatic process, which means
when backup failed to complete, all the backup process will rollback. This
is why the customer you snipped from used File Monitor and found that "does
create and stays there for the 5 to 7 seconds that the job is running and
then it disappears." As he mentioned later "Backups from a script that run
through a job (without the maintenance plan wizard) succeed about 75% of
the time but about 25% of the time the job fails with the same errors as
the maintenance plan fails with".
For the error message you mentioned, I need more information for analysis.
First, is this error message encountered for all the databases you have, or
just one database when using "Backup" option?
Second, besides using the "backup" option, do you meet the same error
message when using T-SQL or job for back up? You can try the T-SQL code
like this:
use master
exec sp_addumpdevice 'disk','northwindback', 'c:\northwind_back.bak'
backup database northwind to northwindback
go
restore database northwind from northwindback
go
If you do not encounter this error, then please provide information for
every step when using "backup" option to check if anywhere in this process
might involve the network device
If you meet the error message again, please collect information from
SQLDIAG, NT application event logs, system event logs and dbcc checkdb out
put and post here for our further analysis.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for responding!!! :-)
The first point you made really made sense to me. I knew
transactions rolled back but never thought about a backup
job rolling back. I did as you suggested. I created a
backup device and backup a database (and restored it)
using T-SQL code. Of course this worked, because it is
not a job scheduled using the backup wizard interface. I
can always manually backup a database by right clicking,
choicing "All Task" and "Backup Database." But as soon
as I ran it as a scheduled job it will failed, or SQL
would reported it as suceeding with no BAK file.
So, I decided to create a backup job manually. It
worked!!! Then I scheduled it through the job interfaced
and it ran as scheduled.
To clarify this to you:
Backup Job FAILS when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Right click on the new backup device (otg_backupdevice)
and select "Backup a Database"
The SQL Server backup wizard window appears, select the
database (otg) to backup, add the backup device
(otg_backupdevice) and schedule the backup to occur once
a day at 12:00 A.M.
Then ran the job from the jobs window
The job fails.
Backup Job Succeeds when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Go to the job window
Right-click and select new job
Name the job - OTG backup to device
Click on the "Steps" tab and click on "New Step"
Select OTG as the database
In the command window I enter "backup database otg to
otg_backupdevice
Click on the "Schedule" tab and schedule it to backup at
10:00 am
The job suceeds
I suspect there is a problem with the backup wizard'
Thank you for your help. I still wonder why the backups
will not work through maintenance plans or through the
backup wizard.
Kathy
>--Original Message--
>Hi Kathy,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>I should give some explanation to the phenomenon you
described in the snip.
>The backup process in the SQL Server is an automatic
process, which means
>when backup failed to complete, all the backup process
will rollback. This
>is why the customer you snipped from used File Monitor
and found that "does
>create and stays there for the 5 to 7 seconds that the
job is running and
>then it disappears." As he mentioned later "Backups from
a script that run
>through a job (without the maintenance plan wizard)
succeed about 75% of
>the time but about 25% of the time the job fails with
the same errors as
>the maintenance plan fails with".
>For the error message you mentioned, I need more
information for analysis.
>First, is this error message encountered for all the
databases you have, or
>just one database when using "Backup" option?
>Second, besides using the "backup" option, do you meet
the same error
>message when using T-SQL or job for back up? You can try
the T-SQL code
>like this:
>use master
>exec
sp_addumpdevice 'disk','northwindback', 'c:\northwind_back
.bak'
>backup database northwind to northwindback
>go
>restore database northwind from northwindback
>go
>If you do not encounter this error, then please provide
information for
>every step when using "backup" option to check if
anywhere in this process
>might involve the network device
>If you meet the error message again, please collect
information from
>SQLDIAG, NT application event logs, system event logs
and dbcc checkdb out
>put and post here for our further analysis.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||I tested restoring a database and the system reports the
database is restore sucessfully but it does not. The
sysdatabase does not show an entry for the restored
database. When I close Enterprise Manager and reopen the
database is gone. The mdf file is still in the data
directory. What is wrong with my sql server.
I can restore the database from the same file on my SQL
2000 server but it fail on every one of the SQL 7.0
servers. The production 7.0 SQL server and the 7.0 SQL
server on my laptop computer.
Kathy
>--Original Message--
>Hi Kathy,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>I should give some explanation to the phenomenon you
described in the snip.
>The backup process in the SQL Server is an automatic
process, which means
>when backup failed to complete, all the backup process
will rollback. This
>is why the customer you snipped from used File Monitor
and found that "does
>create and stays there for the 5 to 7 seconds that the
job is running and
>then it disappears." As he mentioned later "Backups from
a script that run
>through a job (without the maintenance plan wizard)
succeed about 75% of
>the time but about 25% of the time the job fails with
the same errors as
>the maintenance plan fails with".
>For the error message you mentioned, I need more
information for analysis.
>First, is this error message encountered for all the
databases you have, or
>just one database when using "Backup" option?
>Second, besides using the "backup" option, do you meet
the same error
>message when using T-SQL or job for back up? You can try
the T-SQL code
>like this:
>use master
>exec
sp_addumpdevice 'disk','northwindback', 'c:\northwind_back
.bak'
>backup database northwind to northwindback
>go
>restore database northwind from northwindback
>go
>If you do not encounter this error, then please provide
information for
>every step when using "backup" option to check if
anywhere in this process
>might involve the network device
>If you meet the error message again, please collect
information from
>SQLDIAG, NT application event logs, system event logs
and dbcc checkdb out
>put and post here for our further analysis.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Thank you for your detailed information and it is helpful for us to
analysis.
As you said, when execute the TSQL and JOB schedule, the backup process
will complete fine while when using backup option, it failed, right? If I
misunderstood the information you provided, please feel free to let me know.
As using the "backup" option, because it add many options when carried out
the backup, so please carry out it step by step as I mentioned below:
1) Right clicking, choose "All Task" and "Backup Database."
2) This is very important step! Make sure in the destination, there should
be only one device (When you add the device, there could be some other
devices in it), remove other devices from the destination. If not, you will
encounter the errors.
3) Click "OK" after you assigned the schedule time (You can also run it
immediately by click "ok" to test if this will work first)
Try the above steps and pay attention to the second one, only one
destination could be appeared in the "destination".
I hope this will solve the problem and if there is any problem, feel free
to post message here and I am ready to help! By the way, happy
thanks-giving day to you and your family!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Thursday, March 29, 2012

Backup Strategy

Backup Strategy

Tuesday, March 27, 2012

Backup SQL server using msbackup

Backup SQL server using msbackup

Backup SQL server using msbackup

backup sql server online

Backup SQL Database

Sunday, March 25, 2012

Backup SQL 2005 - Restore In SQL 2000

Backup SQL 2005 - Restore In SQL 2000

Backup SQL 2005 - Restore In SQL 2000

backup speed SQL 2000

backup speed SQL 2000

backup speed SQL 2000

Backup solution - question about transaction log

Backup Slow Throughput

Thursday, March 22, 2012

Backup Server

Backup serious appending problem

Backup serious appending problem

Tuesday, March 20, 2012

Backup Restore question

Backup reporting false success

sql backup files

Blog Archive

About Me