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
begin 666 restorebackupdb_differential.txt
M+RH-"BHJ"45J96UP;&\Z(')E<W1O<F5B86-K=7!D8E]D:69F97)E;G1I86P@.
M)W1E<W0Q)RPG0SI<8F%C:W5P<UQB86-K=7!<=&5S=#%"86-K=7 N8F%K)RPG
M0SI<8F%C:W5P<UQB86-K=7!<=&5S=#%"86-K=7 N9&8Q)PT**B\-"D-214%4
M12!P<F]C961U<F4@.<F5S=&]R96)A8VMU<&1B7V1I9F9E<F5N=&EA; T*0$YO
M;6)R94)A<V4@.=F%R8VAA<B@.U,"DL#0I 4G5T84)A<V5#;VUP;&5T;R!V87)C
M:&%R*#(U-2DL#0I 4G5T84)A<V5$:69E<F5N8VEA;"!V87)C:&%R*#(U-2D-
M"E=)5$@.@.14Y#4EE05$E/3@.T*87,-"D)%1TE.#0H-"E)%4U1/4D4@.1$%404)!
M4T4@.0$YO;6)R94)A<V4-"D923TT@.1$E32R ]($!2=71A0F%S94-O;7!L971O
M( T*5TE42"!.3U)%0T]615)9+" -"B @.(%)%4$Q!0T4[( T*#0I215-43U)%
M($1!5$%"05-%($!.;VUB<F5"87-E#0I&4D]-($1)4TL@./2! 4G5T84)A<V5$
6:69E<F5N8VEA;#L-"@.T*14Y$#0H-"@.``
`
end
begin 666 backupdb_full.txt
M+RH-"BHJ"45J96UP;&\Z('!R;V-E9'5R92!B86-K=7!D8E]F=6QL("=T97-T
M,2<L)T,Z7&)A8VMU<'-<8F%C:W5P7'1E<W0Q0F%C:W5P+F)A:R<-"BHO#0I#
M4D5!5$4@.<')O8V5D=7)E(&)A8VMU<&1B7V9U;&P-"D!.;VUB<F5"87-E('9A
M<F-H87(H-3 I+ T*0%)U=&%"87-E('9A<F-H87(H,C4U*0T*5TE42"!%3D-2
M65!424].#0IA<PT*0D5'24X-"@.T*#0I"04-+55 @.1$%404)!4T4@.0$YO;6)R
H94)A<V4-"E1/($1)4TL@./2! 4G5T84)A<V4[#0I%3D0-"D=/#0H-"@.``
`
end
begin 666 backupdb_differential.txt
M+RH-"BHJ"45J96UP;&\Z(&)A8VMU<&1B7V1I9F9E<F5N=&EA;" G=&5S=#$G
M+"=#.EQB86-K=7!S7&)A8VMU<%QT97-T,4)A8VMU<"YD9C$G#0HJ+PT*0U)%
M051%('!R;V-E9'5R92!B86-K=7!D8E]D:69F97)E;G1I86P-"D!.;VUB<F5"
M87-E('9A<F-H87(H-3 I+ T*0%)U=&%"87-E('9A<F-H87(H,C4U*0T*5TE4
M2"!%3D-265!424].#0IA<PT*0D5'24X-"@.T*0D%#2U50($1!5$%"05-%($!.
M;VUB<F5"87-E#0I43R!$25-+(#T@.0%)U=&%"87-E( T*5TE42"!$249&15)%
73E1)04P[#0H-"@.T*14Y$#0I'3PT*#0H`
`
end
begin 666 restorebackupdb_full.txt
M+RH-"BHJ"45J96UP;&\Z(')E<W1O<F5B86-K=7!D8E]F=6QL("=T97-T,2<L
M)T,Z7&)A8VMU<'-<8F%C:W5P7'1E<W0Q0F%C:W5P+F)A:R<-"BHO#0I#4D5!
M5$4@.<')O8V5D=7)E(')E<W1O<F5B86-K=7!D8E]F=6QL#0I 3F]M8G)E0F%S
M92!V87)C:&%R*#4P*2P-"D!2=71A('9A<F-H87(H,C4U*0T*5TE42"!%3D-2
M65!424].#0IA<PT*0D5'24X-"@.E215-43U)%($1!5$%"05-%($!.;VUB<F5"
L87-E($923TT@.1$E32R ]($!2=71A(%=)5$@.@.<F5P;&%C93L-"D5.1 T*1T\`
`
endCarlos
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...
>> 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
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...
>> 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,
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 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...
>> 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
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
>> 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...
>> 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 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...
> 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
>> 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...
>> 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
>>
>>
>>
>>
>>
>>
>|||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...
>> 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
>> 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...
>> 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
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||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...
>> 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...
>> 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
>> 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...
>>> 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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>