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

Hi,
I'm finalizing a backup strategy for a production SQL Server with several
databases. I intend to use the same setup for several new production SQL
Servers. Please let me know if there are any "holes" in this plan or if you
have any suggestions.
I've been advised not to use remote backups to shares but instead to backup
locally to disk. The reasons for this:
-performance of the backups
-integrity of the backups
-faster recovery from local backups
I'm going to be using SQL Server to backup locally to disk and then using
Veritas to backup the local backup files to a remote share and then to tape
for offsite storage.
I'll be using Database Maintanence Plans for Full and Transaction Log
backups based on the requirements for each database. The files will be
automatically removed over time (as part of the Maint plan) following the
file copies to the remote share. I may use custom backup scripts in the
future if I require additional functionality not exposed through the Maint
plans. The files for the last one or two backups for each database will be
retained locally.
I'll be adding a seperate drive to each machine to seperate the location of
the backups from the data to help maximize the recoverability of the data.
I could backup directly the array containing the data however a seperate
drive seems like a better option (?)
I'm considering adding two drives in a RAID 1 configuration for the local
backups. This would provide fault tolerance for the backups until the
backups can be copied to the remote share (nightly). Is this a common
practice? Adding two seperate drives in a RAID 1 configuration for each
production SQL Server will increase cost and I'm not sure it is required -
feedback?
Thanks.
JerryYou are taking some risk with this setup...Until the backup files are copied
OFF to another server you will be unable to restore...
I prefer to back up to a file share directly ( yes it is slower), but the
window of risk has been eliminated...
If you must put your backups on the same server, use a different disk
controller than the data disks, mirroring as you suggest will give you
redundancy on the hard drives, but will not account for entire server
failures...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:O29MjifZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm finalizing a backup strategy for a production SQL Server with several
> databases. I intend to use the same setup for several new production SQL
> Servers. Please let me know if there are any "holes" in this plan or if
> you have any suggestions.
> I've been advised not to use remote backups to shares but instead to
> backup locally to disk. The reasons for this:
> -performance of the backups
> -integrity of the backups
> -faster recovery from local backups
> I'm going to be using SQL Server to backup locally to disk and then using
> Veritas to backup the local backup files to a remote share and then to
> tape for offsite storage.
> I'll be using Database Maintanence Plans for Full and Transaction Log
> backups based on the requirements for each database. The files will be
> automatically removed over time (as part of the Maint plan) following the
> file copies to the remote share. I may use custom backup scripts in the
> future if I require additional functionality not exposed through the Maint
> plans. The files for the last one or two backups for each database will
> be retained locally.
> I'll be adding a seperate drive to each machine to seperate the location
> of the backups from the data to help maximize the recoverability of the
> data. I could backup directly the array containing the data however a
> seperate drive seems like a better option (?)
> I'm considering adding two drives in a RAID 1 configuration for the local
> backups. This would provide fault tolerance for the backups until the
> backups can be copied to the remote share (nightly). Is this a common
> practice? Adding two seperate drives in a RAID 1 configuration for each
> production SQL Server will increase cost and I'm not sure it is required -
> feedback?
> Thanks.
> Jerry
>|||In article <#QPCAqiZFHA.3220@.TK2MSFTNGP14.phx.gbl>,
wayne.nospam.snyder@.mariner-usa.com says...
> You are taking some risk with this setup...Until the backup files are copied
> OFF to another server you will be unable to restore...
> I prefer to back up to a file share directly ( yes it is slower), but the
> window of risk has been eliminated...
> If you must put your backups on the same server, use a different disk
> controller than the data disks, mirroring as you suggest will give you
> redundancy on the hard drives, but will not account for entire server
> failures...
The OP was talking about backups of SQL DB's not the server. I always
backup to a local drive (separate physical drive) and then backup to
tape. The backup to tape of a SQL server DB is slow and can present a
number of issues - the backup to disk is quick, gives an on-line
accessible backup, does not load the network, and can be copied to tape
quickly at any time.
In a production environment it's often a good idea to have a local
backup on the server in case you need to do a restore quickly. The
likelihood of a drive failure is slimmer than a network problem and the
performance is much higher.
--
--
spam999free@.rrohio.com
remove 999 in order to email me

Backup strategi

Hi all
I'm in the starting phase of creating a backup strategi for a new SQL Server box. It's a quite big installation with many databases and quite large databases (hundreds of Gbyte). I'm very famililiar with the SQL Server backup technology but I need some examples of strategies to "open up my mind". Does anybody have liks to examples for SQL Server backup strategies that will be very helpful. Maybe I don't have to reinvent the wheel..
Thanks!
FredrikHi,
Perform a full backup on the weekend and differential backups during the
week days , Hourly transaction log backups.
Have a look into the below link which contains the backup / restore
concepts with example:-
http://www.databasejournal.com/features/mssql/article.php/2232371
Thanks
Hari
MCDBA
"Fredrik" <anonymous@.discussions.microsoft.com> wrote in message
news:9322E3DE-20AB-422C-BAB5-0E66259B24FF@.microsoft.com...
> Hi all!
> I'm in the starting phase of creating a backup strategi for a new SQL
Server box. It's a quite big installation with many databases and quite
large databases (hundreds of Gbyte). I'm very famililiar with the SQL Server
backup technology but I need some examples of strategies to "open up my
mind". Does anybody have liks to examples for SQL Server backup strategies
that will be very helpful. Maybe I don't have to reinvent the wheel..
> Thanks!!
> Fredrik|||Fredrik..
We backup all databases using SQL LiteSpeed, which I highly recommend. Each backup job has three steps. After the DB is backed up locally to the SQL server in step 1, the second step is a CmdExec, which deletes the previous backup file located on a file server...
DEL /Q \\fileserver\SQLServers\SQLServerName\Database\
The third step copies the backup file from the SQL server to the file server..
COPY \\SQLServerName\E$\MSSQL\Backup\Database \\fileserver\SQLServers\SQLServerName\Database
We also do this for the hourly or 15 minute tran. log backups (depending on how much activity the DB has) that we do.
Tape backups are done locally on the file server every night.
Not overly complex, but I think it's a step in the right direction for covering ourselves in the case of a disaster
Let me know if you have any questions or suggestions. I'm always open to reviewing our current procedures
sean

Backup strategi

Hi all!
I'm in the starting phase of creating a backup strategi for a new SQL Server box. It's a quite big installation with many databases and quite large databases (hundreds of Gbyte). I'm very famililiar with the SQL Server backup technology but I need some ex
amples of strategies to "open up my mind". Does anybody have liks to examples for SQL Server backup strategies that will be very helpful. Maybe I don't have to reinvent the wheel..
Thanks!!
Fredrik
Hi,
Perform a full backup on the weekend and differential backups during the
week days , Hourly transaction log backups.
Have a look into the below link which contains the backup / restore
concepts with example:-
http://www.databasejournal.com/featu...le.php/2232371
Thanks
Hari
MCDBA
"Fredrik" <anonymous@.discussions.microsoft.com> wrote in message
news:9322E3DE-20AB-422C-BAB5-0E66259B24FF@.microsoft.com...
> Hi all!
> I'm in the starting phase of creating a backup strategi for a new SQL
Server box. It's a quite big installation with many databases and quite
large databases (hundreds of Gbyte). I'm very famililiar with the SQL Server
backup technology but I need some examples of strategies to "open up my
mind". Does anybody have liks to examples for SQL Server backup strategies
that will be very helpful. Maybe I don't have to reinvent the wheel..
> Thanks!!
> Fredrik
|||Thanks.
Acctually I was looking for solutions that are more complex . Not that my intention is to make a very complex solution but I really like to know what my options are.
I found one link that I thougt was interesting.
http://www.microsoft.com/technet/pro.../spltmirr.mspx
/Fredrik
|||Fredrik...
We backup all databases using SQL LiteSpeed, which I highly recommend. Each backup job has three steps. After the DB is backed up locally to the SQL server in step 1, the second step is a CmdExec, which deletes the previous backup file located on a file
server...
DEL /Q \\fileserver\SQLServers\SQLServerName\Database\*
The third step copies the backup file from the SQL server to the file server...
COPY \\SQLServerName\E$\MSSQL\Backup\Database \\fileserver\SQLServers\SQLServerName\Database
We also do this for the hourly or 15 minute tran. log backups (depending on how much activity the DB has) that we do.
Tape backups are done locally on the file server every night.
Not overly complex, but I think it's a step in the right direction for covering ourselves in the case of a disaster.
Let me know if you have any questions or suggestions. I'm always open to reviewing our current procedures.
sean
sql

Backup strategi

Hi all!
I'm in the starting phase of creating a backup strategi for a new SQL Server
box. It's a quite big installation with many databases and quite large data
bases (hundreds of Gbyte). I'm very famililiar with the SQL Server backup te
chnology but I need some ex
amples of strategies to "open up my mind". Does anybody have liks to example
s for SQL Server backup strategies that will be very helpful. Maybe I don't
have to reinvent the wheel..
Thanks!!
FredrikHi,
Perform a full backup on the weekend and differential backups during the
week days , Hourly transaction log backups.
Have a look into the below link which contains the backup / restore
concepts with example:-
http://www.databasejournal.com/feat...cle.php/2232371
Thanks
Hari
MCDBA
"Fredrik" <anonymous@.discussions.microsoft.com> wrote in message
news:9322E3DE-20AB-422C-BAB5-0E66259B24FF@.microsoft.com...
> Hi all!
> I'm in the starting phase of creating a backup strategi for a new SQL
Server box. It's a quite big installation with many databases and quite
large databases (hundreds of Gbyte). I'm very famililiar with the SQL Server
backup technology but I need some examples of strategies to "open up my
mind". Does anybody have liks to examples for SQL Server backup strategies
that will be very helpful. Maybe I don't have to reinvent the wheel..
> Thanks!!
> Fredrik|||Thanks.
Acctually I was looking for solutions that are more complex . Not that my in
tention is to make a very complex solution but I really like to know what my
options are.
I found one link that I thougt was interesting.
http://www.microsoft.com/technet/pr...n/spltmirr.mspx
/Fredrik|||Fredrik...
We backup all databases using SQL LiteSpeed, which I highly recommend. Each
backup job has three steps. After the DB is backed up locally to the SQL s
erver in step 1, the second step is a CmdExec, which deletes the previous ba
ckup file located on a file
server...
DEL /Q \\fileserver\SQLServers\SQLServerName\Da
tabase\*
The third step copies the backup file from the SQL server to the file server
..
COPY \\SQLServerName\E$\MSSQL\Backup\Database
\\fileserver\SQLServers\SQLSer
verName\Database
We also do this for the hourly or 15 minute tran. log backups (depending on
how much activity the DB has) that we do.
Tape backups are done locally on the file server every night.
Not overly complex, but I think it's a step in the right direction for cover
ing ourselves in the case of a disaster.
Let me know if you have any questions or suggestions. I'm always open to re
viewing our current procedures.
sean

Backup SQL using MSDE Writer

How do I backup my SQL Databases using MSDE Writers?

I do not see MSDE Writers under NT Backup.

Thanks

Santhosh

You also don't see any of the other writers. They generally aren't directly used by a backup user.

The backup app may use them behind the scenes. The important thing is whether the backup app correctly backs up your SQL database. If you back up a volume containing a SQL database, the MSDE writer should be invoked behind the scenes automatically as part of the VSS snapshot done by NTBackup.

Backup SQL Server Failures

I get the following errors when I use a maintenance plan to backup all of my databases on one of our servers. Note: When I run them manually they work as long as I put them in a different place on the same drive. The Everone group has full access on the NTFS drive that this is writing to so it can't be a rights issue. Also some of my databases are backed up while others fail. The account that I'm using to back these up is an administrator of the machine, I'm using the same account when I automate them through a maintenance plan that I do for doing them manually. The E:\ drive (where these are trying to be backed up) is Raid 5 with 30 gb free and 30gb used. These backups would only take up about 5gb of space (if they would work).

Please help!!!

BACKUP failed to complete the command BACKUP LOG [WebLogs] TO DISK = N'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731. TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Internal I/O request 0x2E0CB728: Op: Write, pBuffer: 0x17D20000, Size: 983040, Position: 328286720, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x13914200, OffsetHigh: 0x0, m_buf: 0x17D20000, m_len: 983040, m_actualBytes: 0, m_errcode: 2, BackupFile: E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.TR N

BackupMedium::ReportIoError: write failure on backup device 'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.T RN'. Operating system error 2(The system cannot find the file specified.).Try to redefine the Maint.plan and also try using BACKUP LOG statement individually for this database.|||Thanks for your reply.

I've redefined my Maintenance Plan several times, as you could imagine, but to no avail.

Also, I've ran the BACKUP LOG statement on all of my databases in the place of the maintenance plan and received the same error.|||What is the SP[service pack] level on the SQL Server & OS?|||Windows 2000 Server SP3

SQL Server 2000 SP3 (Standard Edition not Enterprise)|||Then how about RECOVERY MODEL on these DBs.|||All of the user databases on this server have the Recovery Model option set to FULL.|||Looks like issues with NTFS permissions, check whether the SQL Services have necessary privileges and also under REGISTRY keys for these accounts.|||Since this is an internal machine and not our Internet SQL Server, the SQL Server Services (Main, Agent, etc.) all run under an account that is an administrator of the machine.

Security rights for both logical drives on the machine have the Everyone group having all rights locally. (The network shares only allow administrators in)|||As you'd mentioned the disk is 60GB, whereas 30Gb is free and another 30 is used.

Could you point out exact size of databases and free space available on the disk. It looks like when the Maint.plan is running typically the error refers to Free space on the server.

For a test try to redesing maint.plan for each database and see the results.|||The drive has 21.0 GB used. It has 38.7 GB free.

There are 16 databases to be backed up. The error happens randomly upon the 3 largest databases.

Email database = 263.88 mb (data and log)
ESP database = 316.25 mb (data and log)
WebLogs database = 5,069.76 gb (data and log)

(note 5 datafiles in the WebLogs and one log file... low amount of transactions and the database and the log is shrunk periodically throughout the day. We backup and truncate the log after a main import process is executed each day.)

I've ran the backup by hand using BACKUP DATABASE and it seldom works on these databases. Sometimes it will actually complete, others it doesn't. Also, the WebLogs database never completes and it always seems to fail at 24 percent (stats = 1 on the backup database command).

The backup log doesn't work at all on these databases whether I run it by hand or through the maintenance plan.|||Sorry for wasting your time.

I finally became fed up and went throught the security check points and actually went to the physical machine and looked in the event viewer of windows.

There I found that one of the Raid disks was going bad. That explains a lot.

Thanks for all of your help.

You had a lot of great ideas. If I had the ability to "dial in" to the machine, I would have spotted the problem much earlier.

I'm betting you agree that a bad disk could cause all of the quirky backup errors?|||Dont' be sorry.... Glad to hear your resolution and efforts, keep it up.
No second thought for these failures, but anytime if you've them again then follow the listings above.

Good luck.

backup SQL server databases

Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.
You can start a job using sp_start_job. But jobs are executed asynchronously. This means that you
cannot reliably "wait" until end of execution and then report back the error. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegr oups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>

backup SQL server databases

Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.You can start a job using sp_start_job. But jobs are executed asynchronously. This means that you
cannot reliably "wait" until end of execution and then report back the error. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegroups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>

BACKUP SQL SERVER DATABASES

I have setup a backup device that backs up a databases daily but i want to
overwrite the data weekly right now its has two months of data in the device.
How do i set it to overwrite
Use the INIT option of the BACKUP command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lyle" <lyle@.discussions.microsoft.com> wrote in message
news:683AAFF2-4F8C-44F3-A195-F85A9881C047@.microsoft.com...
> I have setup a backup device that backs up a databases daily but i want to
> overwrite the data weekly right now its has two months of data in the device.
> How do i set it to overwrite

backup SQL server databases

Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.You can start a job using sp_start_job. But jobs are executed asynchronously
. This means that you
cannot reliably "wait" until end of execution and then report back the error
. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that
using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegroups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>

backup sql server database in hosting environment?

I am new to database.

I have a website in a hosting environment and it uses sql server 2005 databases. I want to have a complete backup of my database in addition to the hosting company's routine backup. I want to write a program to automatically backup everything every night. And of couse have the ability to restored the whole database if necessary.

How should I do it? Are there any tools exist that I can use? What is the best way to deal with backup/restore in hosting environment?

Thanks!

Why not just schedule the backup task to be execueted every night? In Enterprise Manager, right click a database->choose Backup->check the Schedule checkbox in the General tab->Customize the schedule as you like.sql

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 slowing down systemcan it run at lower priority?

We have 200 databases (totalling about 20GB), and counting. I use
maintenance plans for backup--seems very convenient, and I don't have to
change anything when we add another DB.
Every night we do disk-to-disk backup at 9:30 (which runs screamingly
fast--only 10 minutes--because we backup to another disk), and then a
separate disk-to-tape backup occurs.
The problem: With the increased workload leading up to Christmas, the users
are saying the system slows down too much during those 10 minutes. They are
trying to use the system during live phone calls, and the caller can get
impatient--so they end up writing things on paper, which has to be entered
into the DB later. (Lots of extra work because somebody else has to replay
the tape of the call to do this.)
My main question is: Is there a way I can tell the backup to run at a lower
CPU priority?
I don't think we can backup directly to tape (thus reducing the load),
because other backup software needs to use the same tape after the SQL
backup is done. I suppose I could backup to the SAME disk, which would
almost certainly get rid of the CPU contention, but then there would be disk
contention instead, which may not be any improvement (it could even be
worse). And I'd rather not move away from using the maintenance plans
because of all the automatic stuff they do for me, like logging, deletion of
old files on my desired schedule, etc. Moving the backup later into the
night is problematic because (1) other backups need to happen afterward, and
some of them run most of the night--and sometimes the verify phase continues
well into the next day; and (2) nightly maintenance jobs and DB loads also
run later in the night.I presume this backup that is causing slowdown is a FULL backup ?
Can you do it out of hours?
Do you use LOG backups or DIFFERENTIAL backups?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Warren Odom" <no_spam@.no_spam.com> wrote in message
news:uvP%23vlOwDHA.3436@.tk2msftngp13.phx.gbl...
> We have 200 databases (totalling about 20GB), and counting. I use
> maintenance plans for backup--seems very convenient, and I don't have to
> change anything when we add another DB.
> Every night we do disk-to-disk backup at 9:30 (which runs screamingly
> fast--only 10 minutes--because we backup to another disk), and then a
> separate disk-to-tape backup occurs.
> The problem: With the increased workload leading up to Christmas, the
users
> are saying the system slows down too much during those 10 minutes. They
are
> trying to use the system during live phone calls, and the caller can get
> impatient--so they end up writing things on paper, which has to be entered
> into the DB later. (Lots of extra work because somebody else has to replay
> the tape of the call to do this.)
> My main question is: Is there a way I can tell the backup to run at a
lower
> CPU priority?
> I don't think we can backup directly to tape (thus reducing the load),
> because other backup software needs to use the same tape after the SQL
> backup is done. I suppose I could backup to the SAME disk, which would
> almost certainly get rid of the CPU contention, but then there would be
disk
> contention instead, which may not be any improvement (it could even be
> worse). And I'd rather not move away from using the maintenance plans
> because of all the automatic stuff they do for me, like logging, deletion
of
> old files on my desired schedule, etc. Moving the backup later into the
> night is problematic because (1) other backups need to happen afterward,
and
> some of them run most of the night--and sometimes the verify phase
continues
> well into the next day; and (2) nightly maintenance jobs and DB loads also
> run later in the night.
>
>|||"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eim467OwDHA.2000@.TK2MSFTNGP11.phx.gbl...
> I presume this backup that is causing slowdown is a FULL backup ?
> Can you do it out of hours?
> Do you use LOG backups or DIFFERENTIAL backups?
Yes it's a full backup. We do log backups every half hour throughout the
day.
And my original post gave a lot of details why it would be a problem to
schedule the backup any later in the evening. (Although I did neglect to
mention this server is used 24/7.)
So can it be run at a lower priority or not?
-- Warren|||Backup isn't so much CPU intensive as it is disk I/O. So
if you're doing your disk backups to disks on the same
disk plane, you will see I/O issues, which affect SQL
Server. If you're backing up all 200 at once, you may
want to stagger them a bit ... that would certainly cause
contention.
Backing up to the same disk would be worse for I/O.
How are your disks set up?|||You have been offered some good suggestions already, I only want to answer
your concrete question:
> So can it be run at a lower priority or not?
No.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Warren Odom" <no_spam@.no_spam.com> wrote in message
news:uQfcAbPwDHA.3196@.TK2MSFTNGP11.phx.gbl...
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eim467OwDHA.2000@.TK2MSFTNGP11.phx.gbl...
> > I presume this backup that is causing slowdown is a FULL backup ?
> > Can you do it out of hours?
> > Do you use LOG backups or DIFFERENTIAL backups?
> Yes it's a full backup. We do log backups every half hour throughout the
> day.
> And my original post gave a lot of details why it would be a problem to
> schedule the backup any later in the evening. (Although I did neglect to
> mention this server is used 24/7.)
> So can it be run at a lower priority or not?
> -- Warren
>

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.

backup should continue even after failure

I have created a stored procedure to backup databases,
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.
Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>

backup should continue even after failure

I have created a stored procedure to backup databases,
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>

backup should continue even after failure

I have created a stored procedure to backup databases,
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>sql

Backup script

Hi,

I'm currently working on a backup script for my MSSQL databases. I have managed to make a working backup command but I have one problem.
I need every new backup file to have a uniqe filename, for example backup_UNIQE.bak. How can I make a variable that can do this?

Alexander

Here's the basis of one that can be used which uses the date as a unique identifier:

Code Snippet

declare @.filename nvarchar(2000)

set @.filename = 'c:\yourbackupdirectory\dbname_bk_' + convert(varchar,getdate(),112) + '.bak'

backup database northwind
to disk = @.filename

HTH!

|||

Hi,

Can you stick a date on the end of it? See below and then pass the filename into you script somehow.

declare @.datet varchar(10), @.filename varchar(20)

set @.datet = convert(varchar(10),getdate(),112)

set @.filename = 'backup'+@.datet+'.bak'

print @.filename

will give you

backup20070824.bak

You could always add the time to it :

set @.datet = replace(convert(varchar(8),getdate(),114),':', '')

Any help to you?

Cheers

Matt

|||Thank you both! This worked like a charm!

Matt Tolhurst:

Can you please explain this one: set @.datet = replace(convert(varchar(8),getdate(),114),':', '') ?

I'm not TSQL coder yet, and I would appricitate it very much!

- Alexander
|||

Alexander Davidsen wrote:

Can you please explain this one: set @.datet = replace(convert(varchar(8),getdate(),114),':', '') ?

Hi,

That bit is simply taking the time part of the getdate function and removing the colons, as you don't really want to put colons in the file name.

declare @.datet2 datetime, @.datet varchar(10), @.filename varchar(20)

set @.datet2 = getdate()

print @.datet2 --

Aug 24 2007 11:07AM

set @.datet = convert(varchar(10),@.datet2,112)

print @.datet

20070824

here is the time part:

set @.datet = convert(varchar(8),@.datet2,114)

print @.datet

11:07:44

set @.datet = replace(convert(varchar(8),@.datet2,114),':', '')

print @.datet

110744

Any clearer?

Matt

|||Thanks Matt

This made it alot clearer!

Have a nice weekend!

- Alexander

Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign it
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than one
week?
I thank you for your help...
Regards,
Jamil
I would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil
|||The following scripts will backup the database and name the backup along with
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL FOR
SELECT Catalog_Name FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD , NAME =
@.Name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil
|||http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
This allows you to specify how long you want the backup files to be retained.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s_...estBackup.html
http://www.mindsdoor.net/SQLAdmin/s_...estBackup.html
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil