Backup failed in "Loading" status. How to restart or recover?

I asked this question before but never received a decent answer. When a restore fails, what are the steps to re-start the restore? This should be a very common issue and for the life of me I have been unable to find it in BOL or the MS site! Please let me in on the secret!!

Thanks

You should be able to just re-issue the RESTORE command to complete the restore.

If you want to try to recovery the database, you can issue RESTORE <dbname> WITH RECOVERY, but if you failed in the middle of a restore it will not let you bring up a knonwn inconsistent database.

|||

Hi!

I tried to issue the "Restore With Recovery" but that failed as well. Dropping the database seems to be the only way to reliably correct this situation. I don't have a big objection to this, but shouldn't this be in BOL and prominently covered in the Backup and Recovery doc? It would save us all a lot of time!

Thanks,

Michael

|||If the statement WITH RECOVERY does not work, I would not rely on the recovered backup anymore and start from scratch as you did.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

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 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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>

Backup Database with code

Hello there
On beggining of any month i:
1. Backup the database,
2. Restore it as diffrent name
3. Run some actions that change the database
So far i have store procedure for the the changes only
Is there a way to build code/script that do the backup action and the
restore as diffrent name action?
..
roy@.atidsm.co.il
: 03-5611606
' 050-7709399Roy
> Is there a way to build code/script that do the backup action and the
> restore as diffrent name action?
If I understand you correctly , you need to write a stored procedure that
does backup of the database and the restore the database with a differnet
name. Am I right?
CREATE PROC myProc_Backup
AS
DECLARE @.FileName AS VARCHAR(255)
DECLARE @.Date AS VARCHAR(20)
SELECT @.Date =SELECT CONVERT(VARCHAR(50),GETDATE(),112)
SELECT @.FileName = 'N:\MyFolder\DatabaseName' + @.Date+'.bak'
BACKUP DATABASE DatabaseName
TO DISK = @.FileName
CREATE PROC myProc_Restore
AS
DECLARE @.FileName AS VARCHAR(255)
DECLARE @.Date AS VARCHAR(20)
SELECT @.Date =SELECT CONVERT(VARCHAR(50),GETDATE(),112)
SELECT @.FileName = 'N:\MyFolder\DatabaseName' + @.Date+'.bak'
RESTORE DATABASE DatabaseName_New FROM DISK= @.FileNameWITH RECOVERY,
MOVE 'DataBase_Data' TO 'N:\Program Files\Microsoft SQL
Server\MSSQL\Data\DataBase_data.mdf',
MOVE 'DataBase_Log' TO 'N:\Program Files\Microsoft SQL
Server\MSSQL\Data\DataBase__log.ldf'
Note: Create a job with two steps (backup/restore) and schedule it. Please,
make sure that you cannot restore operation while users perform some
activities at the database, it must be in SINGLE USER mode and before
RESTORE you need to drop an old database. Look , I don't need your business
requirements ,sothe logic might be changed.
If you want the SP to accept some parameters as Backup's Path or something
like this you can rewrite it
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:e$ZpbwUsFHA.2520@.TK2MSFTNGP10.phx.gbl...
> Hello there
> On beggining of any month i:
> 1. Backup the database,
> 2. Restore it as diffrent name
> 3. Run some actions that change the database
> So far i have store procedure for the the changes only
> Is there a way to build code/script that do the backup action and the
> restore as diffrent name action?
>
> --
>
> ..
> roy@.atidsm.co.il
> : 03-5611606
> ' 050-7709399
>

Backup Database 2000 and restore the data to SQL 7.0

Hi
Is there a way whereby I can restore data set backup from SQL 2000 onto a
SQL 7.0 server? Please advise asap. Thanks a lot
Regards
Christina
No. Use some of the export/import methods instead. Like DTS, BCP etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:9AB9F395-3F87-4326-9992-5EFAC07E1D49@.microsoft.com...
> Hi
> Is there a way whereby I can restore data set backup from SQL 2000 onto a
> SQL 7.0 server? Please advise asap. Thanks a lot
> Regards
> Christina

Backup Database 2000 and restore the data to SQL 7.0

Hi
Is there a way whereby I can restore data set backup from SQL 2000 onto a
SQL 7.0 server? Please advise asap. Thanks a lot
Regards
ChristinaNo. Use some of the export/import methods instead. Like DTS, BCP etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:9AB9F395-3F87-4326-9992-5EFAC07E1D49@.microsoft.com...
> Hi
> Is there a way whereby I can restore data set backup from SQL 2000 onto a
> SQL 7.0 server? Please advise asap. Thanks a lot
> Regards
> Christina

Backup Database 2000 and restore the data to SQL 7.0

Hi
Is there a way whereby I can restore data set backup from SQL 2000 onto a
SQL 7.0 server? Please advise asap. Thanks a lot
Regards
ChristinaNo. Use some of the export/import methods instead. Like DTS, BCP etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:9AB9F395-3F87-4326-9992-5EFAC07E1D49@.microsoft.com...
> Hi
> Is there a way whereby I can restore data set backup from SQL 2000 onto a
> SQL 7.0 server? Please advise asap. Thanks a lot
> Regards
> Christina

Backup database

Hi,
Is there a way to backup database every week and the restore a particular week's database.
Thanks
Kiran
Of course. have you looked at Backup and Restore T-SQL commands and topics
about Scheduled Jobs in Books OnLine?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Backup database

Hi,
Is there a way to backup database every week and the restore a particular we
ek's database.
Thanks
KiranOf course. have you looked at Backup and Restore T-SQL commands and topics
about Scheduled Jobs in Books OnLine?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

backup and restoring

I used backup and restore to upgrade a database from sql 2000 to sql 2005. Is it necessary to create the mdf and ldf on the new server at the time of restore under options or should I copy them to the new server in the data folder? I am new and not quite sure what the log files hold.
Thanksif you have created a backup of a database, you don't need anything but the .bak file.

when you restore from a bak file, the data and log files will be created automatically by the server.|||Edit: You don't need to create them, the restore does.

How backup works:
When SQL Server backs up a database, it backs up the data file first. During the backup of the data file, no changes are written to the data file, only to the log. When the bacup of the data file is complete, the changes written to the log file is backed up. In other words, backup of a SQL Server restores to the point of time when the backup finished, not started. Furthermore, your log file will be used to keep track of transactions afer the backup have restored, so you will need the file.|||Ok. If I don't specify in options at the time of restore, I can't find where the the mdf and ldf files were created. And are these new mdf and ldf files or do they hold the same data as the database before they were created on the new server.
thanks|||this will tell you where they are:

exec sp_helpfile

backup and restoring

How to back up SQL 2000 and restore it ti SQL 7 vice versa?
thank youYou can take a backup from 7 and restore it to 2000 with no problems =(update the optimiser stats afterwards though). But a 2k backup cannot =be restored to 7. Easiset way for that is via DTS.
Mike John
"sitreks" <anonymous@.discussions.microsoft.com> wrote in message =news:9554F949-A533-41FC-8AC2-82449CC50F81@.microsoft.com...
> How to back up SQL 2000 and restore it ti SQL 7 vice versa?
> thank you

Backup and Restore with STOPAT

Hi
I want to implement the following simple scenario as a test prior the real
implementation to see if these scenario is plausible. I want to test the
STOPAT option of the RESTORE LOG command.
1. First I want to full backup the database (the "Recovery model"
option is set to "Full") using the following code:
BACKUP DATABASE atsTables
TO DISK = 'c:\temp\20060318_1236_atsTables.dat'
WITH
DESCRIPTION = 'atsTables Backup1'
2. After backup I will insert a new row in one table of the db with the
following command (clock time: 12:37:xx):
INSERT INTO Classes(Title)
VALUES('Test1')
3. After at least one minute from the first insert I will insert
another row to the db (now the time is: 12:38:xx)
INSERT INTO Classes(Title)
VALUES('Test2')
4. At 12:39:xx I will backup the Transaction protocol with the
following command:
BACKUP LOG atsTables
TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
5. Now I will restore the db:
RESTORE DATABASE atsTables
FROM DISK = 'c:\temp\20060318_1236_atsTables.dat'
WITH
NORECOVERY
, REPLACE
6. I will now restore the transaction protocol and stop at 12:37:59
DECLARE @.dtm as DateTime
SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
RESTORE LOG atsTABLES
FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
WITH
Recovery
, STOPAT = @.dtm
7. I expect to have now in the "Classes" table only the "Test1" class
"Title".
USE atsTables
SELECT *
FROM Classes
8. But I get the error that the DB cannot be opened because it is still
restoring
9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG
command again as before:
DECLARE @.dtm as DateTime
SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
RESTORE LOG atsTABLES
FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
WITH
Recovery
, STOPAT = @.dtm
10. I select again the Classes table:
USE atsTables
SELECT *
FROM Classes
11. Now it works but I get both classes ("Test1" and "Test2") instead of
only the "Test1" class title
The question is:
Why is the STOPAT option of the RESTORE LOG not working as I expected? Is my
scenario wrong? Can someone help me further?
Any answer can help me a lot.
Thanks, George.Hi
Read this article
http://www.karaszi.com/SQLServer/in...veral_times.asp
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:e68eWErSGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I want to implement the following simple scenario as a test prior the real
> implementation to see if these scenario is plausible. I want to test the
> STOPAT option of the RESTORE LOG command.
>
> 1. First I want to full backup the database (the "Recovery model"
> option is set to "Full") using the following code:
>
> BACKUP DATABASE atsTables
> TO DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> DESCRIPTION = 'atsTables Backup1'
>
> 2. After backup I will insert a new row in one table of the db with
> the following command (clock time: 12:37:xx):
>
> INSERT INTO Classes(Title)
> VALUES('Test1')
>
> 3. After at least one minute from the first insert I will insert
> another row to the db (now the time is: 12:38:xx)
>
> INSERT INTO Classes(Title)
> VALUES('Test2')
>
> 4. At 12:39:xx I will backup the Transaction protocol with the
> following command:
>
> BACKUP LOG atsTables
> TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
>
> 5. Now I will restore the db:
>
> RESTORE DATABASE atsTables
> FROM DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> NORECOVERY
> , REPLACE
>
> 6. I will now restore the transaction protocol and stop at 12:37:59
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 7. I expect to have now in the "Classes" table only the "Test1" class
> "Title".
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 8. But I get the error that the DB cannot be opened because it is
> still restoring
> 9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG
> command again as before:
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 10. I select again the Classes table:
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 11. Now it works but I get both classes ("Test1" and "Test2") instead of
> only the "Test1" class title
>
> The question is:
> Why is the STOPAT option of the RESTORE LOG not working as I expected? Is
> my scenario wrong? Can someone help me further?
>
> Any answer can help me a lot.
> Thanks, George.
>|||Hi, George
You should verify that the @.dtm variable contains the expected value. I
guess that the conversion of the varchar value to a datetime resulted
in a different value than expected. You should specify the dates in a
language neutral format, for example '20060318 12:37:59' or
'2006-03-18T12:37:59'. See the following article for more informations:
http://www.karaszi.com/SQLServer/info_datetime.asp
Razvan|||After reading the article everithing is clear now and works.
Thanks for help.
George.
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:e68eWErSGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I want to implement the following simple scenario as a test prior the real
> implementation to see if these scenario is plausible. I want to test the
> STOPAT option of the RESTORE LOG command.
>
> 1. First I want to full backup the database (the "Recovery model"
> option is set to "Full") using the following code:
>
> BACKUP DATABASE atsTables
> TO DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> DESCRIPTION = 'atsTables Backup1'
>
> 2. After backup I will insert a new row in one table of the db with
> the following command (clock time: 12:37:xx):
>
> INSERT INTO Classes(Title)
> VALUES('Test1')
>
> 3. After at least one minute from the first insert I will insert
> another row to the db (now the time is: 12:38:xx)
>
> INSERT INTO Classes(Title)
> VALUES('Test2')
>
> 4. At 12:39:xx I will backup the Transaction protocol with the
> following command:
>
> BACKUP LOG atsTables
> TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
>
> 5. Now I will restore the db:
>
> RESTORE DATABASE atsTables
> FROM DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> NORECOVERY
> , REPLACE
>
> 6. I will now restore the transaction protocol and stop at 12:37:59
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 7. I expect to have now in the "Classes" table only the "Test1" class
> "Title".
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 8. But I get the error that the DB cannot be opened because it is
> still restoring
> 9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG
> command again as before:
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 10. I select again the Classes table:
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 11. Now it works but I get both classes ("Test1" and "Test2") instead of
> only the "Test1" class title
>
> The question is:
> Why is the STOPAT option of the RESTORE LOG not working as I expected? Is
> my scenario wrong? Can someone help me further?
>
> Any answer can help me a lot.
> Thanks, George.
>

Backup and Restore using VBScript (ActiveX)

We are trying to setup a central job server that will perform the backups, restores, and other automated tasks for all of our SQL servers. The goal is that we have one place to check the status of all of our jobs. I have a couple of problems, though.

The first is that, to backup a database on a remote server, we are using VBScript as an ActiveX Script type of step. The script uses SQLDMO. The jobs mostly run fine, but there seems to be a problem with concurrent jobs running against the same server. For instance, I have 4 backup jobs that I kick off at 7:00PM. One of them (not always the same one) will fail with:

Error Code: 0 Error Source= Microsoft SQL-DMO Error Description: [SQL-DMO]The application is busy. Error on Line 11. The step failed.

The databses are all quite small (master, msdb, and a couple of utility dbs we have). Are there threading issues in using SQL-DMO such that I can't run jobs concurrently? The remote server is SQL2000, so I don't believe I can use SMO. My script is at the end of this post.

My second problem is trying to remotely kick off a restore. Currently, when using the local scheduler, we use the RESTORE DATABASE command right after an ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE command. This command is necessary to kill any processes currently using the DB so that a restore will succeed. In DMO I can't find anyway to duplicate this functionality. Any ideas?

Thanks

Steve

-- Backup Script --

Dim sServer, sDatabase, sPath, sDumpFile, oSQLServer, oBackup

sServer = "PRDSQL01"
sDatabase = "master"
sPath = "E:\MSSQL\DUMP\"
sDumpFile = "master.dmp"

Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oBackup = CreateObject("SQLDMO.BackUp")

oSQLServer.loginsecure = true
oSQLServer.Connect sServer

oBackup.Database = sDatabase
oBackup.Files = sPath & sDumpFile
oBackUp.BackupSetDescription = "Backup - " & sDatabase
oBackup.Initialize = "TRUE"
oBackup.SQLBackup oSQLServer

oSQLServer.Disconnect

Set sServer = nothing
Set sDatabase = nothing
Set sPath = nothing
Set sDumpFile = nothing
Set oSQLServer = nothing
Set oBackup = nothing

Hi Stev

Vimal , here ,

sir its helpful to take backup ,please tell me script for restore the taken dump file.

Vimal

Backup and Restore using VBScript (ActiveX)

We are trying to setup a central job server that will perform the backups, restores, and other automated tasks for all of our SQL servers. The goal is that we have one place to check the status of all of our jobs. I have a couple of problems, though.

The first is that, to backup a database on a remote server, we are using VBScript as an ActiveX Script type of step. The script uses SQLDMO. The jobs mostly run fine, but there seems to be a problem with concurrent jobs running against the same server. For instance, I have 4 backup jobs that I kick off at 7:00PM. One of them (not always the same one) will fail with:

Error Code: 0 Error Source= Microsoft SQL-DMO Error Description: [SQL-DMO]The application is busy. Error on Line 11. The step failed.

The databses are all quite small (master, msdb, and a couple of utility dbs we have). Are there threading issues in using SQL-DMO such that I can't run jobs concurrently? The remote server is SQL2000, so I don't believe I can use SMO. My script is at the end of this post.

My second problem is trying to remotely kick off a restore. Currently, when using the local scheduler, we use the RESTORE DATABASE command right after an ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE command. This command is necessary to kill any processes currently using the DB so that a restore will succeed. In DMO I can't find anyway to duplicate this functionality. Any ideas?

Thanks

Steve

-- Backup Script --

Dim sServer, sDatabase, sPath, sDumpFile, oSQLServer, oBackup

sServer = "PRDSQL01"
sDatabase = "master"
sPath = "E:\MSSQL\DUMP\"
sDumpFile = "master.dmp"

Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oBackup = CreateObject("SQLDMO.BackUp")

oSQLServer.loginsecure = true
oSQLServer.Connect sServer

oBackup.Database = sDatabase
oBackup.Files = sPath & sDumpFile
oBackUp.BackupSetDescription = "Backup - " & sDatabase
oBackup.Initialize = "TRUE"
oBackup.SQLBackup oSQLServer

oSQLServer.Disconnect

Set sServer = nothing
Set sDatabase = nothing
Set sPath = nothing
Set sDumpFile = nothing
Set oSQLServer = nothing
Set oBackup = nothing

Hi Stev

Vimal , here ,

sir its helpful to take backup ,please tell me script for restore the taken dump file.

Vimal

Backup and restore using ssis

Hi,

I am trying to take the backup of a database, and restore it over a diff database (with same schema).

i am new to SSIS can you guys give me some pointers.

also, i would like to do a daily update of data from one table in a db to a table in another db with same schema; only the rows with latest edit time must exist, i have a column in both tables which records the last editing time of a row....

Help....

Regards

Mave

From a 50,000 foot level. If you have a time stamp value, you could store the last timestamp value processed in a table some where. When the package runs it could grab the current max timestamp value, then get all the records with a time stamp value greater than the last time stamp value and less than or equal to the current max. For each record you would perform an update or insert as appropriate (note that deletes will need to be handled another way) and finally store the current max as the last max.

For deletes you could create a table of id's and timestamps. On the table you're auditing place a trigger that just inserts the ID of the deleted records. Grab the records deleted since last run and delete them in the destination table like we did above for the inserts and updates and then save the last deleted timestamp.

Hope this helps.

Backup and Restore using SQL Server with Merge Replication

We have a project where we are replicating filtered data sets to SQL Express
clients. We are thinking of a back and restore approach that uses SQL
Express. The a SQL Express Instance would receive all data thru replication
then that data set would be backed up. We are pursuing the approach because
of distance and location of the central replication server and logistics of
backing it up. Or managers have requested Microsoftâ's documented or
undocumented view on this approach. If there is any online documentation
that outlines a process like this or a document that illustrates that this
can be done and is a feasible back and restore approach, please post back.
Thanks in advance.
--
Alexander L. Wykel
MIT ProfessionalsHi Alexander,
I am not sure if I totally understand your meaning. Based on your
description, I understand that you have a project which can replicate
filtered data sets to your SQL Express clients and that you would like to
know of a backup and restore approach for your SQL Express clients so that
they can backup the data set after they receive all data through
replication function in your project. So your question here is that you
would like to know a method for backing up your data set in your SQL
Express client, right?
If I have misunderstood, please let me know.
Unfortunately there is no backup method at partial data set level in SQL
Server 2005, but only at file level or database level. Regarding this, you
can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
At SQL Server side, I think that you may consider the following two ways:
1. Use incremental database backup for your SQL Express client.
2. Create those tables on a dedicated file and then only backup your
database on that file.
Of course if your client database is not large, you may directly perform a
full database backup. This depends on your database size and performance
requirement.
You may refer to the following articles:
Copying Databases with Backup and Restore
http://msdn2.microsoft.com/en-us/library/ms190436.aspx
Backing Up and Restoring Databases in SQL Server
http://technet.microsoft.com/en-us/library/ms187048.aspx
Strategies for Backing Up and Restoring Merge Replication
http://technet.microsoft.com/en-us/library/ms152497.aspx
Backing Up and Restoring Replicated Databases
http://technet.microsoft.com/en-us/library/ms151152.aspx
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Sorry Charles,
My outline of the project details left al lot of unresolved issues for you
to have to piece together. The process is simple, for now forget about the
individual clients whom are receiving filtered data sets. What our primary
aim is to replicate the entire data set from the publisher to a local
Subscriber SQL Express instance. Our Corporate network is distributed and
our disaster recovery center is located off site (which we intend on
implementing via hot spare), so we would like to have local copy of the
entire data set (the data that is really important to our organization, to
use to rebuild the server while itâ's down and the hot spare is in use) not
the replication and publisher subscriber information, that we would have to
rebuild. If worst comes to worst we can rebuild that from the main
replicated data set which will be managed locally.
I guess the critical path question is can SQL Express server in this
capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
an SQL Express Subscriber Instance? I have heard of Replication Database
Mirroring techniques:
â'The Publisher and Distributor must be Microsoft SQL Server 2005.
Subscribers can be SQL Server 2005 or a previous versionâ'
â'Replication supports mirroring the publication database for merge
replication and for transactional replication with read-only Subscribers or
queued updating Subscribers. Immediate updating Subscribers, Oracle
Publishers, Publishers in a peer-to-peer topology, and republishing are not
supported.â'
SQL Server 2005 Books On-line (September 2007)
Replication and Database Mirroring
- http://msdn2.microsoft.com/en-us/library/ms151799.aspx
Not sure if this means we can use this technology. From what Iâ'm reading it
looks like it does, could you clarify it for me please?
--
Alexander L. Wykel
MIT Professionals
"Charles Wang[MSFT]" wrote:
> Hi Alexander,
> I am not sure if I totally understand your meaning. Based on your
> description, I understand that you have a project which can replicate
> filtered data sets to your SQL Express clients and that you would like to
> know of a backup and restore approach for your SQL Express clients so that
> they can backup the data set after they receive all data through
> replication function in your project. So your question here is that you
> would like to know a method for backing up your data set in your SQL
> Express client, right?
> If I have misunderstood, please let me know.
> Unfortunately there is no backup method at partial data set level in SQL
> Server 2005, but only at file level or database level. Regarding this, you
> can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> At SQL Server side, I think that you may consider the following two ways:
> 1. Use incremental database backup for your SQL Express client.
> 2. Create those tables on a dedicated file and then only backup your
> database on that file.
> Of course if your client database is not large, you may directly perform a
> full database backup. This depends on your database size and performance
> requirement.
> You may refer to the following articles:
> Copying Databases with Backup and Restore
> http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> Backing Up and Restoring Databases in SQL Server
> http://technet.microsoft.com/en-us/library/ms187048.aspx
> Strategies for Backing Up and Restoring Merge Replication
> http://technet.microsoft.com/en-us/library/ms152497.aspx
> Backing Up and Restoring Replicated Databases
> http://technet.microsoft.com/en-us/library/ms151152.aspx
> If you have any other questions or concerns, please feel free to let me
> know. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> =========================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================>
>
>
>
>
>
>|||I have one minor correction, the site we intend to replicate the full data
set is off site (SQL Express Subscriber). This allows us to incrementally
send data as it arrives as apposed to sending large backup distributions.
Then the main back up would then proceed from the off site location. We
intend to back up the MDF file as apposed to creating a backup, then if the
main location goes down we send the attach the MDF and rebuild the Publisher
Data Set from the full copy of the Subscriber MDF data set.
--
Alexander L. Wykel
MIT Professionals
"Alexander Wykel" wrote:
> Sorry Charles,
> My outline of the project details left al lot of unresolved issues for you
> to have to piece together. The process is simple, for now forget about the
> individual clients whom are receiving filtered data sets. What our primary
> aim is to replicate the entire data set from the publisher to a local
> Subscriber SQL Express instance. Our Corporate network is distributed and
> our disaster recovery center is located off site (which we intend on
> implementing via hot spare), so we would like to have local copy of the
> entire data set (the data that is really important to our organization, to
> use to rebuild the server while itâ's down and the hot spare is in use) not
> the replication and publisher subscriber information, that we would have to
> rebuild. If worst comes to worst we can rebuild that from the main
> replicated data set which will be managed locally.
> I guess the critical path question is can SQL Express server in this
> capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
> an SQL Express Subscriber Instance? I have heard of Replication Database
> Mirroring techniques:
> â'The Publisher and Distributor must be Microsoft SQL Server 2005.
> Subscribers can be SQL Server 2005 or a previous versionâ'
> â'Replication supports mirroring the publication database for merge
> replication and for transactional replication with read-only Subscribers or
> queued updating Subscribers. Immediate updating Subscribers, Oracle
> Publishers, Publishers in a peer-to-peer topology, and republishing are not
> supported.â'
> SQL Server 2005 Books On-line (September 2007)
> Replication and Database Mirroring
> - http://msdn2.microsoft.com/en-us/library/ms151799.aspx
> Not sure if this means we can use this technology. From what Iâ'm reading it
> looks like it does, could you clarify it for me please?
> --
> Alexander L. Wykel
> MIT Professionals
>
> "Charles Wang[MSFT]" wrote:
> > Hi Alexander,
> > I am not sure if I totally understand your meaning. Based on your
> > description, I understand that you have a project which can replicate
> > filtered data sets to your SQL Express clients and that you would like to
> > know of a backup and restore approach for your SQL Express clients so that
> > they can backup the data set after they receive all data through
> > replication function in your project. So your question here is that you
> > would like to know a method for backing up your data set in your SQL
> > Express client, right?
> > If I have misunderstood, please let me know.
> >
> > Unfortunately there is no backup method at partial data set level in SQL
> > Server 2005, but only at file level or database level. Regarding this, you
> > can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> >
> > At SQL Server side, I think that you may consider the following two ways:
> > 1. Use incremental database backup for your SQL Express client.
> > 2. Create those tables on a dedicated file and then only backup your
> > database on that file.
> >
> > Of course if your client database is not large, you may directly perform a
> > full database backup. This depends on your database size and performance
> > requirement.
> >
> > You may refer to the following articles:
> > Copying Databases with Backup and Restore
> > http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> >
> > Backing Up and Restoring Databases in SQL Server
> > http://technet.microsoft.com/en-us/library/ms187048.aspx
> >
> > Strategies for Backing Up and Restoring Merge Replication
> > http://technet.microsoft.com/en-us/library/ms152497.aspx
> >
> > Backing Up and Restoring Replicated Databases
> > http://technet.microsoft.com/en-us/library/ms151152.aspx
> >
> > If you have any other questions or concerns, please feel free to let me
> > know. Have a nice day!
> >
> > Best regards,
> > Charles Wang
> > Microsoft Online Community Support
> > =========================================================> > Delighting our customers is our #1 priority. We welcome your
> > comments and suggestions about how we can improve the
> > support we provide to you. Please feel free to let my manager
> > know what you think of the level of service provided. You can
> > send feedback directly to my manager at: msdnmg@.microsoft.com.
> > =========================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > =========================================================> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||Hi Alexander,
Thanks for your response.
Indeed Database Mirroring can provide high availability (automatic
failover) at database level, however it does not support SQL Server Express
Edition. Only SQL Server 2005 Standard/Enterprise Edition can support this
feature.
For SQL Server Express Edition, you can use SQL Server replication to
replicate data from your publisher to your subscriber. However do you
really need merge replication? If the data change on your subscriber need
not be merged to your publisher, I recommend that you use Transactional
replication so that you can win the best performance among the existing
three types of SQL Server replication.
Though transactional replication has very high performance for replicating
data, actually it is also not a real time replication as well as other type
of replication, so if you want to use detach/attach methods to recover your
database on your publisher, there might be still at a risk of a little part
data loss. Since the data is very critical to your organization, I
recommend that you may consider the following solutions for your publisher
server: SQL Server failover cluster, or Database Mirroring, or Replication
together with Database Mirroring, or Replication together with failover
cluster.
With SQL Server failover cluster or Database Mirroring for your publisher
servers (two or more nodes), when the primary publisher is down, the
secondary publisher will automatically failover to be the primary
publisher. By using replication together with the failover function, it
brings you an additional level of security safeguard. You can distribute
your data to a different location. If some emergent situations happen, you
can recover your publisher by using this data, though there might be a
little data loss.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Backup and Restore using SQL Server with Merge Replication

We have a project where we are replicating filtered data sets to SQL Express
clients. We are thinking of a back and restore approach that uses SQL
Express. The a SQL Express Instance would receive all data thru replication
then that data set would be backed up. We are pursuing the approach because
of distance and location of the central replication server and logistics of
backing it up. Or managers have requested Microsoft’s documented or
undocumented view on this approach. If there is any online documentation
that outlines a process like this or a document that illustrates that this
can be done and is a feasible back and restore approach, please post back.
Thanks in advance.
Alexander L. Wykel
MIT Professionals
Hi Alexander,
I am not sure if I totally understand your meaning. Based on your
description, I understand that you have a project which can replicate
filtered data sets to your SQL Express clients and that you would like to
know of a backup and restore approach for your SQL Express clients so that
they can backup the data set after they receive all data through
replication function in your project. So your question here is that you
would like to know a method for backing up your data set in your SQL
Express client, right?
If I have misunderstood, please let me know.
Unfortunately there is no backup method at partial data set level in SQL
Server 2005, but only at file level or database level. Regarding this, you
can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
At SQL Server side, I think that you may consider the following two ways:
1. Use incremental database backup for your SQL Express client.
2. Create those tables on a dedicated file and then only backup your
database on that file.
Of course if your client database is not large, you may directly perform a
full database backup. This depends on your database size and performance
requirement.
You may refer to the following articles:
Copying Databases with Backup and Restore
http://msdn2.microsoft.com/en-us/library/ms190436.aspx
Backing Up and Restoring Databases in SQL Server
http://technet.microsoft.com/en-us/library/ms187048.aspx
Strategies for Backing Up and Restoring Merge Replication
http://technet.microsoft.com/en-us/library/ms152497.aspx
Backing Up and Restoring Replicated Databases
http://technet.microsoft.com/en-us/library/ms151152.aspx
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
|||Sorry Charles,
My outline of the project details left al lot of unresolved issues for you
to have to piece together. The process is simple, for now forget about the
individual clients whom are receiving filtered data sets. What our primary
aim is to replicate the entire data set from the publisher to a local
Subscriber SQL Express instance. Our Corporate network is distributed and
our disaster recovery center is located off site (which we intend on
implementing via hot spare), so we would like to have local copy of the
entire data set (the data that is really important to our organization, to
use to rebuild the server while it’s down and the hot spare is in use) not
the replication and publisher subscriber information, that we would have to
rebuild. If worst comes to worst we can rebuild that from the main
replicated data set which will be managed locally.
I guess the critical path question is can SQL Express server in this
capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
an SQL Express Subscriber Instance? I have heard of Replication Database
Mirroring techniques:
“The Publisher and Distributor must be Microsoft SQL Server 2005.
Subscribers can be SQL Server 2005 or a previous version”
“Replication supports mirroring the publication database for merge
replication and for transactional replication with read-only Subscribers or
queued updating Subscribers. Immediate updating Subscribers, Oracle
Publishers, Publishers in a peer-to-peer topology, and republishing are not
supported.”
SQL Server 2005 Books On-line (September 2007)
Replication and Database Mirroring
- http://msdn2.microsoft.com/en-us/library/ms151799.aspx
Not sure if this means we can use this technology. From what I’m reading it
looks like it does, could you clarify it for me please?
Alexander L. Wykel
MIT Professionals
"Charles Wang[MSFT]" wrote:

> Hi Alexander,
> I am not sure if I totally understand your meaning. Based on your
> description, I understand that you have a project which can replicate
> filtered data sets to your SQL Express clients and that you would like to
> know of a backup and restore approach for your SQL Express clients so that
> they can backup the data set after they receive all data through
> replication function in your project. So your question here is that you
> would like to know a method for backing up your data set in your SQL
> Express client, right?
> If I have misunderstood, please let me know.
> Unfortunately there is no backup method at partial data set level in SQL
> Server 2005, but only at file level or database level. Regarding this, you
> can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> At SQL Server side, I think that you may consider the following two ways:
> 1. Use incremental database backup for your SQL Express client.
> 2. Create those tables on a dedicated file and then only backup your
> database on that file.
> Of course if your client database is not large, you may directly perform a
> full database backup. This depends on your database size and performance
> requirement.
> You may refer to the following articles:
> Copying Databases with Backup and Restore
> http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> Backing Up and Restoring Databases in SQL Server
> http://technet.microsoft.com/en-us/library/ms187048.aspx
> Strategies for Backing Up and Restoring Merge Replication
> http://technet.microsoft.com/en-us/library/ms152497.aspx
> Backing Up and Restoring Replicated Databases
> http://technet.microsoft.com/en-us/library/ms151152.aspx
> If you have any other questions or concerns, please feel free to let me
> know. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== =======
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> ================================================== =======
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== =======
>
>
>
>
>
>
>
|||I have one minor correction, the site we intend to replicate the full data
set is off site (SQL Express Subscriber). This allows us to incrementally
send data as it arrives as apposed to sending large backup distributions.
Then the main back up would then proceed from the off site location. We
intend to back up the MDF file as apposed to creating a backup, then if the
main location goes down we send the attach the MDF and rebuild the Publisher
Data Set from the full copy of the Subscriber MDF data set.
Alexander L. Wykel
MIT Professionals
"Alexander Wykel" wrote:
[vbcol=seagreen]
> Sorry Charles,
> My outline of the project details left al lot of unresolved issues for you
> to have to piece together. The process is simple, for now forget about the
> individual clients whom are receiving filtered data sets. What our primary
> aim is to replicate the entire data set from the publisher to a local
> Subscriber SQL Express instance. Our Corporate network is distributed and
> our disaster recovery center is located off site (which we intend on
> implementing via hot spare), so we would like to have local copy of the
> entire data set (the data that is really important to our organization, to
> use to rebuild the server while it’s down and the hot spare is in use) not
> the replication and publisher subscriber information, that we would have to
> rebuild. If worst comes to worst we can rebuild that from the main
> replicated data set which will be managed locally.
> I guess the critical path question is can SQL Express server in this
> capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
> an SQL Express Subscriber Instance? I have heard of Replication Database
> Mirroring techniques:
> “The Publisher and Distributor must be Microsoft SQL Server 2005.
> Subscribers can be SQL Server 2005 or a previous version”
> “Replication supports mirroring the publication database for merge
> replication and for transactional replication with read-only Subscribers or
> queued updating Subscribers. Immediate updating Subscribers, Oracle
> Publishers, Publishers in a peer-to-peer topology, and republishing are not
> supported.”
> SQL Server 2005 Books On-line (September 2007)
> Replication and Database Mirroring
> - http://msdn2.microsoft.com/en-us/library/ms151799.aspx
> Not sure if this means we can use this technology. From what I’m reading it
> looks like it does, could you clarify it for me please?
> --
> Alexander L. Wykel
> MIT Professionals
>
> "Charles Wang[MSFT]" wrote:
|||Hi Alexander,
Thanks for your response.
Indeed Database Mirroring can provide high availability (automatic
failover) at database level, however it does not support SQL Server Express
Edition. Only SQL Server 2005 Standard/Enterprise Edition can support this
feature.
For SQL Server Express Edition, you can use SQL Server replication to
replicate data from your publisher to your subscriber. However do you
really need merge replication? If the data change on your subscriber need
not be merged to your publisher, I recommend that you use Transactional
replication so that you can win the best performance among the existing
three types of SQL Server replication.
Though transactional replication has very high performance for replicating
data, actually it is also not a real time replication as well as other type
of replication, so if you want to use detach/attach methods to recover your
database on your publisher, there might be still at a risk of a little part
data loss. Since the data is very critical to your organization, I
recommend that you may consider the following solutions for your publisher
server: SQL Server failover cluster, or Database Mirroring, or Replication
together with Database Mirroring, or Replication together with failover
cluster.
With SQL Server failover cluster or Database Mirroring for your publisher
servers (two or more nodes), when the primary publisher is down, the
secondary publisher will automatically failover to be the primary
publisher. By using replication together with the failover function, it
brings you an additional level of security safeguard. You can distribute
your data to a different location. If some emergent situations happen, you
can recover your publisher by using this data, though there might be a
little data loss.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Backup and Restore Urgent!

Hi All,
Sql server 7.0

i have a db ABC and want to restore it as ABCbak, I am taking the backup of the db ABC and restoring it on ABCbak, i am also seeing that the physical names are different but logical names remains the same. Will this cause any kind of problem to db ABC.

Waiting for reply.

TIA
AdilThe logical names of the files should not present a problem.|||yeah... no problem... if you're super anal and it really bothers you... you can change them after the restore... can't think of the commands offhand though, there's threads on here that have them in them and I'm sure it would be in books online

Backup and Restore Urgent!

Hi All,
Sql server 7.0
i have a db ABC and want to restore it as ABCbak, I am
taking the backup of the db ABC and restoring it on
ABCbak, i am also seeing that the physical names are
different but logical names remains the same. Will this
cause any kind of problem to db ABC.
Waiting for reply.
TIA
AdilYou should be able to do this using the "with replace" phrasing to allow SQL
to overwrite the other database. You will probably also have to use "with
move" to ensure the physical files do not try to go to the same place as the
existing ones. The logical names staying the same is not a problem but can
be changed with ALTER DATABASE after the restore is complete if you so
desire. Check out BOL for the exact syntax.
Christian Smith
"haseeb" <anonymous@.discussions.microsoft.com> wrote in message
news:11cf901c3f647$cca656b0$a101280a@.phx
.gbl...
> Hi All,
> Sql server 7.0
> i have a db ABC and want to restore it as ABCbak, I am
> taking the backup of the db ABC and restoring it on
> ABCbak, i am also seeing that the physical names are
> different but logical names remains the same. Will this
> cause any kind of problem to db ABC.
>
> Waiting for reply.
> TIA
> Adil

Backup and Restore Urgent!

Hi All,
Sql server 7.0
i have a db ABC and want to restore it as ABCbak, I am
taking the backup of the db ABC and restoring it on
ABCbak, i am also seeing that the physical names are
different but logical names remains the same. Will this
cause any kind of problem to db ABC.
Waiting for reply.
TIA
AdilYou should be able to do this using the "with replace" phrasing to allow SQL
to overwrite the other database. You will probably also have to use "with
move" to ensure the physical files do not try to go to the same place as the
existing ones. The logical names staying the same is not a problem but can
be changed with ALTER DATABASE after the restore is complete if you so
desire. Check out BOL for the exact syntax.
Christian Smith
"haseeb" <anonymous@.discussions.microsoft.com> wrote in message
news:11cf901c3f647$cca656b0$a101280a@.phx.gbl...
> Hi All,
> Sql server 7.0
> i have a db ABC and want to restore it as ABCbak, I am
> taking the backup of the db ABC and restoring it on
> ABCbak, i am also seeing that the physical names are
> different but logical names remains the same. Will this
> cause any kind of problem to db ABC.
>
> Waiting for reply.
> TIA
> Adil