Can I backup SQl server 2005 databases with Shadow Copies (VSS)

Good Day,
I would like to create shadow copies of our SQL databases as a way of
providing temporary point in time backups. I was thinking of doing the
following:-
1) Hidden share of the DB folders (mdf/ldf).
2) Apply NTFS as precaution.
3) Store shadow copies on external drive.
4) Create copies every 30/60 minutes.
Would this cause a problem with the SQL server 2005? Would a restoration be
as simple as restoring the mdf & ldf files? It is a live server and I would
not want to cause any probs so thought I would look/check around. I have
googled but can only find pages with people using VSS programatically.
Kind Regards,
Paul Johnson.
--
<a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
target="_blank"><img border = "0"
src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help from
Paul Johnson!" /></a>"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:e7f$JL8nIHA.2292@.TK2MSFTNGP03.phx.gbl...
> Good Day,
> I would like to create shadow copies of our SQL databases as a way of
> providing temporary point in time backups. I was thinking of doing the
> following:-
> 1) Hidden share of the DB folders (mdf/ldf).
> 2) Apply NTFS as precaution.
> 3) Store shadow copies on external drive.
> 4) Create copies every 30/60 minutes.
> Would this cause a problem with the SQL server 2005? Would a restoration
> be as simple as restoring the mdf & ldf files? It is a live server and I
> would not want to cause any probs so thought I would look/check around. I
> have googled but can only find pages with people using VSS
> programatically.
> Kind Regards,
> Paul Johnson.
>
> --
> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
> target="_blank"><img border = "0"
> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help from
> Paul Johnson!" /></a>
Hi Paul
As I understand it, you would need to implement your backup solution using
the VSS API or an application that uses it. NTBackup seems to only be able
to cope with databases that are using SIMPLE recovery mode.
Without backing up the logs, you will not have any ability to rollforward
your transactions, therefore your solution is going to incorporate some data
loss.
John|||I have not looked into shadow copy with SQL Server closely to be able to
comment on your proposed approach intelligently. But one thing I do know is
that your proposed approach is not a common one. So unless you are doing a
POC or just exploring, I'd suggest you don't go there since there are other
much more 'mature' approach to meeting your requirements.
Linchi
"Paul Johnson" wrote:
> Good Day,
> I would like to create shadow copies of our SQL databases as a way of
> providing temporary point in time backups. I was thinking of doing the
> following:-
> 1) Hidden share of the DB folders (mdf/ldf).
> 2) Apply NTFS as precaution.
> 3) Store shadow copies on external drive.
> 4) Create copies every 30/60 minutes.
> Would this cause a problem with the SQL server 2005? Would a restoration be
> as simple as restoring the mdf & ldf files? It is a live server and I would
> not want to cause any probs so thought I would look/check around. I have
> googled but can only find pages with people using VSS programatically.
> Kind Regards,
> Paul Johnson.
>
> --
> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
> target="_blank"><img border = "0"
> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help from
> Paul Johnson!" /></a>
>
>|||Good Day Linchi,
Thank you for your reply.
I was hoping to avoid using a backup program to do this Shadow Copies offers
exactly what I want and it works in the background. I guess I will have to
use our regular backup software.
Thanks once again.
Kind Regards,
Paul Johnson.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:99C5AB60-74B5-4166-A943-C86288A92F1C@.microsoft.com...
>I have not looked into shadow copy with SQL Server closely to be able to
> comment on your proposed approach intelligently. But one thing I do know
> is
> that your proposed approach is not a common one. So unless you are doing a
> POC or just exploring, I'd suggest you don't go there since there are
> other
> much more 'mature' approach to meeting your requirements.
> Linchi
> "Paul Johnson" wrote:
>> Good Day,
>> I would like to create shadow copies of our SQL databases as a way of
>> providing temporary point in time backups. I was thinking of doing the
>> following:-
>> 1) Hidden share of the DB folders (mdf/ldf).
>> 2) Apply NTFS as precaution.
>> 3) Store shadow copies on external drive.
>> 4) Create copies every 30/60 minutes.
>> Would this cause a problem with the SQL server 2005? Would a restoration
>> be
>> as simple as restoring the mdf & ldf files? It is a live server and I
>> would
>> not want to cause any probs so thought I would look/check around. I have
>> googled but can only find pages with people using VSS programatically.
>> Kind Regards,
>> Paul Johnson.
>>
>> --
>> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
>> target="_blank"><img border = "0"
>> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help
>> from
>> Paul Johnson!" /></a>
>>|||Thank you for your reply.
I was hoping to avoid using a backup program to do this. Shadow Copies
offers exactly what I want and it works in the background. I guess I will
have to use our regular backup software or just get SQL server to create
backups to the drive on a schedule.
Thanks once again.
Kind Regards,
Paul Johnson.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u3Ktnr8nIHA.1768@.TK2MSFTNGP03.phx.gbl...
> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
> news:e7f$JL8nIHA.2292@.TK2MSFTNGP03.phx.gbl...
>> Good Day,
>> I would like to create shadow copies of our SQL databases as a way of
>> providing temporary point in time backups. I was thinking of doing the
>> following:-
>> 1) Hidden share of the DB folders (mdf/ldf).
>> 2) Apply NTFS as precaution.
>> 3) Store shadow copies on external drive.
>> 4) Create copies every 30/60 minutes.
>> Would this cause a problem with the SQL server 2005? Would a restoration
>> be as simple as restoring the mdf & ldf files? It is a live server and I
>> would not want to cause any probs so thought I would look/check around. I
>> have googled but can only find pages with people using VSS
>> programatically.
>> Kind Regards,
>> Paul Johnson.
>>
>> --
>> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
>> target="_blank"><img border = "0"
>> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help
>> from Paul Johnson!" /></a>
> Hi Paul
> As I understand it, you would need to implement your backup solution using
> the VSS API or an application that uses it. NTBackup seems to only be able
> to cope with databases that are using SIMPLE recovery mode.
> Without backing up the logs, you will not have any ability to rollforward
> your transactions, therefore your solution is going to incorporate some
> data loss.
> John
>|||"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:e7f$JL8nIHA.2292@.TK2MSFTNGP03.phx.gbl...
> Good Day,
> I would like to create shadow copies of our SQL databases as a way of
> providing temporary point in time backups. I was thinking of doing the
> following:-
Why not use normal SQL Server backups?
Or if you have Enterprise you can also take snapshots.
> 1) Hidden share of the DB folders (mdf/ldf).
> 2) Apply NTFS as precaution.
> 3) Store shadow copies on external drive.
> 4) Create copies every 30/60 minutes.
> Would this cause a problem with the SQL server 2005? Would a restoration
> be as simple as restoring the mdf & ldf files? It is a live server and I
> would not want to cause any probs so thought I would look/check around. I
> have googled but can only find pages with people using VSS
> programatically.
> Kind Regards,
> Paul Johnson.
>
> --
> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
> target="_blank"><img border = "0"
> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help from
> Paul Johnson!" /></a>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:%23z0$3F%23nIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Good Day Linchi,
> Thank you for your reply.
> I was hoping to avoid using a backup program to do this Shadow Copies
> offers exactly what I want and it works in the background. I guess I will
> have to use our regular backup software.
Unless your regular backup software is SQL Server aware, I would not go down
this route.
> Thanks once again.
> Kind Regards,
> Paul Johnson.
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:99C5AB60-74B5-4166-A943-C86288A92F1C@.microsoft.com...
>>I have not looked into shadow copy with SQL Server closely to be able to
>> comment on your proposed approach intelligently. But one thing I do know
>> is
>> that your proposed approach is not a common one. So unless you are doing
>> a
>> POC or just exploring, I'd suggest you don't go there since there are
>> other
>> much more 'mature' approach to meeting your requirements.
>> Linchi
>> "Paul Johnson" wrote:
>> Good Day,
>> I would like to create shadow copies of our SQL databases as a way of
>> providing temporary point in time backups. I was thinking of doing the
>> following:-
>> 1) Hidden share of the DB folders (mdf/ldf).
>> 2) Apply NTFS as precaution.
>> 3) Store shadow copies on external drive.
>> 4) Create copies every 30/60 minutes.
>> Would this cause a problem with the SQL server 2005? Would a restoration
>> be
>> as simple as restoring the mdf & ldf files? It is a live server and I
>> would
>> not want to cause any probs so thought I would look/check around. I have
>> googled but can only find pages with people using VSS programatically.
>> Kind Regards,
>> Paul Johnson.
>>
>> --
>> <a href="http://links.10026.com/?link=http://www.crossloop.com/PaulJohnson?type=email"
>> target="_blank"><img border = "0"
>> src="http://pics.10026.com/?src=http://www.crossloop.com/images/badge-sm-gray.jpg" alt="Get help
>> from
>> Paul Johnson!" /></a>
>>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html