backup databases in SQL Server 2005

Hello,
i've done some little stored procedures to backup an restore databases. If
you see some error or things that coul be better, any help would be
appreciated.
Greetings
Carlos
Why WITH ENCRYPTION?
"Carlos" <ca@.msn.es> wrote in message
news:u8hiC$gvHHA.3588@.TK2MSFTNGP06.phx.gbl...
> Hello,
> i've done some little stored procedures to backup an restore databases. If
> you see some error or things that coul be better, any help would be
> appreciated.
> Greetings
>
>
>
|||Hi Uri,
I dont want that other people can see the content of the procedure, this is
the only reason :-)
"Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
news:%23dgDnNhvHHA.3556@.TK2MSFTNGP02.phx.gbl...
> Carlos
> Why WITH ENCRYPTION?
>
> "Carlos" <ca@.msn.es> wrote in message
> news:u8hiC$gvHHA.3588@.TK2MSFTNGP06.phx.gbl...
>
|||Carlos
It is not safety for 100 percent.
create procedure hello
with encryption
as
print 'hello world'
--Now somebody opens QA and issue the following
alter procedure hello
as
print 'bye bye'
"Carlos" <ca@.msn.es> wrote in message
news:O9wCXYhvHHA.2352@.TK2MSFTNGP03.phx.gbl...
> Hi Uri,
> I dont want that other people can see the content of the procedure, this
> is the only reason :-)
>
> "Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
> news:%23dgDnNhvHHA.3556@.TK2MSFTNGP02.phx.gbl...
>
|||Hi Uri,
Databases and users are created trough a script that does:
-create the database
-create a user whith the procedure sp_addlogin
-DENY VIEW ANY DATABASE to this user
-DENY ALTER ANY LOGIN TO this user
-execute the procedure sp_changedbowner to this user and db created.
The backups/restores procedures are in "master" database; i try to alter
then with a normal user and I cant (this is ok!).
About the backups/restores procedures, do you think that are ok? I pretend
to do a weekly backup and a diary differential backup that will be stored in
the server during a week; after that i'll move it to a backup server and i
delete it.
Thanks for your help!
"Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
news:%23NjazfhvHHA.3500@.TK2MSFTNGP05.phx.gbl...
> Carlos
> It is not safety for 100 percent.
> create procedure hello
> with encryption
> as
> print 'hello world'
>
> --Now somebody opens QA and issue the following
> alter procedure hello
> as
> print 'bye bye'
>
> "Carlos" <ca@.msn.es> wrote in message
> news:O9wCXYhvHHA.2352@.TK2MSFTNGP03.phx.gbl...
>
|||Hi
It looks OK as it suits to your business requirements. Have you looked at
WITH NOINIT option to keep more than one file for example for DIFF backups
"Carlos" <ca@.msn.es> wrote in message
news:ugtpQphvHHA.3500@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> Databases and users are created trough a script that does:
> -create the database
> -create a user whith the procedure sp_addlogin
> -DENY VIEW ANY DATABASE to this user
> -DENY ALTER ANY LOGIN TO this user
> -execute the procedure sp_changedbowner to this user and db created.
> The backups/restores procedures are in "master" database; i try to alter
> then with a normal user and I cant (this is ok!).
> About the backups/restores procedures, do you think that are ok? I pretend
> to do a weekly backup and a diary differential backup that will be stored
> in the server during a week; after that i'll move it to a backup server
> and i delete it.
> Thanks for your help!
>
> "Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
> news:%23NjazfhvHHA.3500@.TK2MSFTNGP05.phx.gbl...
>
|||Carlos
I'd keep a full/diff/log backup for each day. So in case of restore I easily
know what backup to be taken.
There is no need (in my opinion) to keep hundred of files within one single
file.
"Carlos" <ca@.msn.es> wrote in message
news:OT5xh1hvHHA.4796@.TK2MSFTNGP04.phx.gbl...
> Hi Uri,
> i think "WITH NOINIT" is the default value, isnt it? If i dont specify
> this option, will keep the diff backups?
> All backups files, full and differential will be in separate files, and i
> must be able to restore the database to any day, if i have backup of
> course ;-)
>
> "Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
> news:evynOthvHHA.4612@.TK2MSFTNGP03.phx.gbl...
>
|||Yes Uri, i agree with you. I need a file for each full and diff backup
(i.e., a new file for each day), not a file that contains all the backups
So, will you keep the procedures as they are?
"Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
news:OwSaj8hvHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Carlos
> I'd keep a full/diff/log backup for each day. So in case of restore I
> easily know what backup to be taken.
> There is no need (in my opinion) to keep hundred of files within one
> single file.
>
>
> "Carlos" <ca@.msn.es> wrote in message
> news:OT5xh1hvHHA.4796@.TK2MSFTNGP04.phx.gbl...
>

Backup a Database with a number as name

I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?
Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>

Backup & Restore Problem

Dear All,
I've problem when restoring differential & log backup.
How I can restore differential backup and log backup?
Note : I backup sql server data to files.
Thanks
First restore last full backup of your database. Specifiy (in EM) 'able to
receive additional tran logs'
Then restore last differential backup of your database
then restore sequentially transaction logs from the last diff backup to the
point you need.
MC
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:eqh$nek%23FHA.272@.tk2msftngp13.phx.gbl...
> Dear All,
> I've problem when restoring differential & log backup.
> How I can restore differential backup and log backup?
> Note : I backup sql server data to files.
> Thanks
|||First you need full backup.
Regards

Backup

Hi,
I've received this message when trying to backup the database to a tape.
'"he file on device '\\.\Tape0' is not a valid Microsoft Tape Format backup
set. BACKUP DATABASE is terminating adnormally."
What is the cause & how can it be solve? Please help. Thanks
Say
Are you using the tapes for anything other than backing up
from SQL Server to tape? You can get this error when the
tapes have backups other than SQL Server backups on them.
If you are storing other windows backups on the tape, you
would need to do native SQL backups to disk and then backup
these backup files on disk to tape.
-Sue
On Mon, 18 Apr 2005 00:55:01 -0700, Say
<Say@.discussions.microsoft.com> wrote:

>Hi,
> I've received this message when trying to backup the database to a tape.
>'"he file on device '\\.\Tape0' is not a valid Microsoft Tape Format backup
>set. BACKUP DATABASE is terminating adnormally."
> What is the cause & how can it be solve? Please help. Thanks
>Say

Backup

I've recently taken over our comapnies SQL servers. I'm trying to go through
the way my predecessor had things done. I came across two questions
regarding the maintenance plans he had set up to schedule backups through the
enterprise manager.
1) Can you backup all databses in one plan both system and user provided
they are of similar type simple or full
2) When I scheduled the backups how far apart should the BAK and TRN backups
be apart.
They currently have weird scheduled times ex. 11:00 PM for BAK and TRN 1:00
Daily, 1:00 AM for BAK Monday through Saturday, TRN not backed up, BAK 2:00
AM Monday through Saturday, TRN 12:00 AM Monday through Saturday.
Also when these backups run do they interfere with accessing the Database.
First, when in process, a backup will have a small but noticeable impact
upon server performance. (It is relative to CPU/Memory, etc.) Under normal
circumstances, user rarely notice.
Backup plans should be carefully considered based upon need: How much data
is new or changed, and how long can you afford to be offline in case of a
system failure and need to restore.
Look in SQL Server Books Online for the topic "Backing Up and Restoring
Databases'. That will get you started down the path of better understanding
how to balance need vs. capability.
System databases: Usually these are not subject to much daily change
activity, and don't require frequent backups. Master is critical and must be
backed up immediately after any changes to database schema, users,
permissions, etc. Never bother with backing up TempDb. MSDB needs to be
backed up when you create or change any scheduled jobs, add 'operators', or
'alerts'. (Maintenance Plans are scheduled 'jobs'.)
User databases: Again, judge the frequency of new and changed data, and
balance with the risk of loss. In some situations, such as databases used
for reporting, the data is relatively static and doesn't require regular
backups. Other needs include high frequency input with little room for
potential loss. A common schedule for high frequency online databases would
be weekly (or nightly) FULL backups, with hourly Transaction Log backups.
After you do some reading and gather your business requirements, you will be
better prepared to make a schedule that works best for your organization.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:A4C1032D-BE35-4F85-A863-EA2DDF856CF6@.microsoft.com...
> I've recently taken over our comapnies SQL servers. I'm trying to go
> through
> the way my predecessor had things done. I came across two questions
> regarding the maintenance plans he had set up to schedule backups through
> the
> enterprise manager.
> 1) Can you backup all databses in one plan both system and user provided
> they are of similar type simple or full
> 2) When I scheduled the backups how far apart should the BAK and TRN
> backups
> be apart.
> They currently have weird scheduled times ex. 11:00 PM for BAK and TRN
> 1:00
> Daily, 1:00 AM for BAK Monday through Saturday, TRN not backed up, BAK
> 2:00
> AM Monday through Saturday, TRN 12:00 AM Monday through Saturday.
> Also when these backups run do they interfere with accessing the Database.
|||What I usually do is have a Plan for all the simple db and one for all the
full backups.
We've established its exceptable to just do One backup at a point in time
lets say 3:00 Am every night. How soon after the BAK backup should I have the
trn backup run?
"Tibor Karaszi" wrote:

> I think Arnie covered most, but just to add one little thing:
>
> You can definitely have the same plan for the db backup part. But not for the log backup part, since
> you cannot do log backup of the master database. You can do log backup of msdb, but 2000 has this
> weird behavior of setting msdb to simple each time Agent starts (which you can handle if you do wish
> to log backup msdb).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Schneider" <georgedschneider@.news.postalias> wrote in message
> news:A4C1032D-BE35-4F85-A863-EA2DDF856CF6@.microsoft.com...
>
>
|||I guess I'm having a little trouble understanding the purpose of the
transaction log backups. A typically backup schedule for our databse would
be a full at 1:00 AM and the trans backup at 2:00 AM. Would exactly is the
function of trans backups?
"Tibor Karaszi" wrote:

> I usually consider the frequency for log backups instead. Every 10 minutes? Every hour? This is of
> course not my decision, it is the system owner's decision. Say you only want to do one log backup
> each day (to enable point in time restore, for instance), then it doesn't matter much when you do
> it...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Schneider" <georgedschneider@.news.postalias> wrote in message
> news:F9E2C858-B650-4588-B1BF-6D88D81D50D9@.microsoft.com...
>
|||George Schneider wrote:
> I guess I'm having a little trouble understanding the purpose of the
> transaction log backups. A typically backup schedule for our databse would
> be a full at 1:00 AM and the trans backup at 2:00 AM. Would exactly is the
> function of trans backups?
>
Think of the transaction log as a sort of journal. In that journal is a
record of every change that is made to your database. When you run a
full backup, you're saving a "snapshot" of the database as it exists at
that point in time, at 1:00am. For the next 24 hours, changes are being
made to your database. The transaction log is recording those changes.
If you suffer a drive failure, or some other catastrophic loss, you
likely are going to have to restore from the last full backup, losing
those 24 hours of changes.
If you're taking regular backups of the transaction log, you can restore
the last full backup, THEN restore, or "playback", the entries in your
transaction log, thus reproducing the changes that were made to your
database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||typically what I've done on other servers is have the db backup run at 3:00
Am and the tyransaction backup at 3:15 Am. Its exceptable for our purposes
to backup at one point in time. I use enterpsrise manager through a
maintenance plan to create a disk based backup and then backup the backup
directory to tape. If we had a failure all I would need to restore would be
the db backup and then the transaction log backup which would contain all the
changes since the full backup or is it since the last transaction backup?
Does this sound right?
"Tracy McKibben" wrote:

> George Schneider wrote:
> Think of the transaction log as a sort of journal. In that journal is a
> record of every change that is made to your database. When you run a
> full backup, you're saving a "snapshot" of the database as it exists at
> that point in time, at 1:00am. For the next 24 hours, changes are being
> made to your database. The transaction log is recording those changes.
> If you suffer a drive failure, or some other catastrophic loss, you
> likely are going to have to restore from the last full backup, losing
> those 24 hours of changes.
> If you're taking regular backups of the transaction log, you can restore
> the last full backup, THEN restore, or "playback", the entries in your
> transaction log, thus reproducing the changes that were made to your
> database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:65BEB0C9-1499-4243-AAB0-580DFEAD3C9E@.microsoft.com...
> typically what I've done on other servers is have the db backup run at
> 3:00
> Am and the tyransaction backup at 3:15 Am. Its exceptable for our
> purposes
> to backup at one point in time. I use enterpsrise manager through a
> maintenance plan to create a disk based backup and then backup the backup
> directory to tape. If we had a failure all I would need to restore would
> be
> the db backup and then the transaction log backup which would contain all
> the
> changes since the full backup or is it since the last transaction backup?
> Does this sound right?
>
Not exactly. You really want to be doing transaction backups throughout the
day.
i.e. schedule transaction backups to run say every 15 minutes throughout the
day.
If you completely lose your server, you can restore the DB to the state it
was 15 minutes previously by restoring the full backup (with NORECOVERY
explicitely used) and then each subsequent transaction backup (also with
NORECOVERY).
At the very end you can either restore with recovery and have a functional
database, or if possibly apply the "tail of the log" from the now dead
(assuming you could at least get the log) server.
In this way it's possible in some cases to recover a database to the moment
of failure.