Copy databases

Hello all,
I have a unique situation on my hands, and I need a bit of advice. I
currently use transaction-log based backups on my SQL server through
Symantec BackupExec for data protection / disaster recovery purposes.
However, I need a copy of my SQL data available for my development and
support people.
What I need is a way to copy the data from my production server that
does NOT truncate the transaction log, but does not require that my
support people restore from a full backup file, and then apply log
backup files to get a "current" database. As of now, I have written
a .Net app that allows them to restore files created with the "BACKUP
DATABASE" command, but clearly that will not work going forward.
Is there a good way to copy databases from one server to another
server in this situation? I don't necessarily care about syncing the
user IDs on my test machine, because end users have no visibility into
the testing area. If this process works, I would also like to use it
when updating training databases (copies of prod databases kept on the
same SQL instance).
Any help here is GREATLY appreciated!
Tom:
Have you considered some form of replication: either transactional or
snapshot?
HTH
Paul
"tom@.drdabbles.us" wrote:

> Hello all,
> I have a unique situation on my hands, and I need a bit of advice. I
> currently use transaction-log based backups on my SQL server through
> Symantec BackupExec for data protection / disaster recovery purposes.
> However, I need a copy of my SQL data available for my development and
> support people.
> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log, but does not require that my
> support people restore from a full backup file, and then apply log
> backup files to get a "current" database. As of now, I have written
> a .Net app that allows them to restore files created with the "BACKUP
> DATABASE" command, but clearly that will not work going forward.
> Is there a good way to copy databases from one server to another
> server in this situation? I don't necessarily care about syncing the
> user IDs on my test machine, because end users have no visibility into
> the testing area. If this process works, I would also like to use it
> when updating training databases (copies of prod databases kept on the
> same SQL instance).
> Any help here is GREATLY appreciated!
>
|||Replication would not be appropriate, since we want a point-in-time
copy of the data and not a constantly refreshed copy.
|||Tom wrote:
> Replication would not be appropriate, since we want a point-in-time
> copy of the data and not a constantly refreshed copy.
Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
<database> WITH COPY_ONLY.
|||> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log
BACKUP DATABASE. The only type of backup that truncates the log is BACKUP LOG.
I agree with Jeffrey that you check out COPY_ONLY. This isn't needed in your situation, but it makes
the intent of that backup a bit more clear. (COPY_ONLY for BACKUP DATABASE will not affect
differential backups - has no effect on log backups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<tom@.drdabbles.us> wrote in message
news:fd8f3a92-c082-4e21-b366-0e5079265554@.b1g2000hsg.googlegroups.com...
> Hello all,
> I have a unique situation on my hands, and I need a bit of advice. I
> currently use transaction-log based backups on my SQL server through
> Symantec BackupExec for data protection / disaster recovery purposes.
> However, I need a copy of my SQL data available for my development and
> support people.
> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log, but does not require that my
> support people restore from a full backup file, and then apply log
> backup files to get a "current" database. As of now, I have written
> a .Net app that allows them to restore files created with the "BACKUP
> DATABASE" command, but clearly that will not work going forward.
> Is there a good way to copy databases from one server to another
> server in this situation? I don't necessarily care about syncing the
> user IDs on my test machine, because end users have no visibility into
> the testing area. If this process works, I would also like to use it
> when updating training databases (copies of prod databases kept on the
> same SQL instance).
> Any help here is GREATLY appreciated!
|||Isnt there a copy db task in SSIS?
MC
"Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Tom wrote:
> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
|||Watch out so Erland don't see this! ;-)
I haven't used it, but I do know that among others, Erland have issues with this. It can run in two
modes where one does detach and attach (the only mode available for 2000).
The other mode does some object scripting and then transfer the data. I believe this is where most
of the problems are.
SSIS also have some options to transfer server level objects, like logins, jobs etc. This can be
fine, but unfortunately for logins the SID isn't the same on dest server...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:8900F35F-E714-4D73-902B-2104E8F950A8@.microsoft.com...
> Isnt there a copy db task in SSIS?
>
> MC
>
> "Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
> news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
>
|||Ahhh okay. It actually worked for me, but it seems i was lucky. Anyway,
Erland DONT read this ;)
MC
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uED7OdtmIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Watch out so Erland don't see this! ;-)
> I haven't used it, but I do know that among others, Erland have issues
> with this. It can run in two modes where one does detach and attach (the
> only mode available for 2000).
> The other mode does some object scripting and then transfer the data. I
> believe this is where most of the problems are.
> SSIS also have some options to transfer server level objects, like logins,
> jobs etc. This can be fine, but unfortunately for logins the SID isn't the
> same on dest server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:8900F35F-E714-4D73-902B-2104E8F950A8@.microsoft.com...
>
|||On Apr 10, 1:08Xam, Jeffrey Williams <jeff.williams3...@.verizon.ent>
wrote:
> Tom wrote:
> Are you on SQL Server 2005? XIf so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
Jeffrey,
This was EXACTLY what I needed. I somehow missed the section in the
docs on MSDN that gave the description of this option. As it turns
out, this actually is important. While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out. This is also true of log-
based backups using BACKUP from within T-SQL code.
Thanks again!!
|||<<While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out.>>
What a weird design. BE cripples the product and usability, for no gain. Glad that COPY_ONLY solves
this for you, though!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom" <tom@.drdabbles.us> wrote in message
news:d8bee064-e931-49f5-a3d8-e329f96e6c04@.u69g2000hse.googlegroups.com...
On Apr 10, 1:08 am, Jeffrey Williams <jeff.williams3...@.verizon.ent>
wrote:
> Tom wrote:
> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
Jeffrey,
This was EXACTLY what I needed. I somehow missed the section in the
docs on MSDN that gave the description of this option. As it turns
out, this actually is important. While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out. This is also true of log-
based backups using BACKUP from within T-SQL code.
Thanks again!!