Copying instead of backing up SQL 2005 db

I just found out that our backup software (MS DPM) is conflicting with the
SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
Is there a way to copy a database? I'd like to add this to maintenance jobs
as a t-sql script, or sql server agent job.
Thanks in advance...
The BACKUP command has an option named COPY_ONLY that allows to perform
database backups without affecting the normal sequence of full, differential
and transaction log backups.
See more details on COPY_ONLY on BACKUP on BOL.
Hope this helps,
Ben Nevarez
"Artunc" wrote:

> I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
> Is there a way to copy a database? I'd like to add this to maintenance jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...
|||My question would be why are you trying to back it up twice then? How
exactly is it conflicting?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Artunc" <Artunc@.discussions.microsoft.com> wrote in message
news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace
> jobs.
> Is there a way to copy a database? I'd like to add this to maintenance
> jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...
|||No other reson than having a second set of backup for importand dbs.
This is the error I am getting in DPM when backup maintenance job and DPM
backup job run for the same databases:
"DPM tried to do a SQL log backup, either as part of a backup job or a
recovery to latest point in time job. The SQL log backup job has detected a
discontinuity in the SQL log chain for SQL Server 2005 database database
SQLSVR\DB1 since the last backup. All incremental backup jobs will fail until
an express full backup runs. (ID 30140 Details: Internal error code:
0x80990D11)"
"Andrew J. Kelly" wrote:

> My question would be why are you trying to back it up twice then? How
> exactly is it conflicting?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Artunc" <Artunc@.discussions.microsoft.com> wrote in message
> news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>

Copying instead of backing up SQL 2005 db

I just found out that our backup software (MS DPM) is conflicting with the
SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
Is there a way to copy a database? I'd like to add this to maintenance jobs
as a t-sql script, or sql server agent job.
Thanks in advance...The BACKUP command has an option named COPY_ONLY that allows to perform
database backups without affecting the normal sequence of full, differential
and transaction log backups.
See more details on COPY_ONLY on BACKUP on BOL.
Hope this helps,
Ben Nevarez
"Artunc" wrote:
> I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
> Is there a way to copy a database? I'd like to add this to maintenance jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...|||My question would be why are you trying to back it up twice then? How
exactly is it conflicting?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Artunc" <Artunc@.discussions.microsoft.com> wrote in message
news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace
> jobs.
> Is there a way to copy a database? I'd like to add this to maintenance
> jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...|||No other reson than having a second set of backup for importand dbs.
This is the error I am getting in DPM when backup maintenance job and DPM
backup job run for the same databases:
"DPM tried to do a SQL log backup, either as part of a backup job or a
recovery to latest point in time job. The SQL log backup job has detected a
discontinuity in the SQL log chain for SQL Server 2005 database database
SQLSVR\DB1 since the last backup. All incremental backup jobs will fail until
an express full backup runs. (ID 30140 Details: Internal error code:
0x80990D11)"
"Andrew J. Kelly" wrote:
> My question would be why are you trying to back it up twice then? How
> exactly is it conflicting?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Artunc" <Artunc@.discussions.microsoft.com> wrote in message
> news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
> >I just found out that our backup software (MS DPM) is conflicting with the
> > SQL Maintenance jobs. We can't use back up database task in maintenace
> > jobs.
> >
> > Is there a way to copy a database? I'd like to add this to maintenance
> > jobs
> > as a t-sql script, or sql server agent job.
> >
> > Thanks in advance...
>|||You should decide which backup method is more important to you. Let this do both database and log
backups. Let the other do only database backup, which will not disrupt the log backup chain (for the
other backup method).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Artunc" <Artunc@.discussions.microsoft.com> wrote in message
news:B5F12DC7-47DE-41E4-B8CD-2F9158EEC288@.microsoft.com...
> No other reson than having a second set of backup for importand dbs.
> This is the error I am getting in DPM when backup maintenance job and DPM
> backup job run for the same databases:
> "DPM tried to do a SQL log backup, either as part of a backup job or a
> recovery to latest point in time job. The SQL log backup job has detected a
> discontinuity in the SQL log chain for SQL Server 2005 database database
> SQLSVR\DB1 since the last backup. All incremental backup jobs will fail until
> an express full backup runs. (ID 30140 Details: Internal error code:
> 0x80990D11)"
>
> "Andrew J. Kelly" wrote:
>> My question would be why are you trying to back it up twice then? How
>> exactly is it conflicting?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Artunc" <Artunc@.discussions.microsoft.com> wrote in message
>> news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>> >I just found out that our backup software (MS DPM) is conflicting with the
>> > SQL Maintenance jobs. We can't use back up database task in maintenace
>> > jobs.
>> >
>> > Is there a way to copy a database? I'd like to add this to maintenance
>> > jobs
>> > as a t-sql script, or sql server agent job.
>> >
>> > Thanks in advance...
>>

Copy Table

I have a DB which will have many tables that are the same only named
differently. Instead of recreating each one is there a way to "Copy Table" ?
Thanks in advanceIt is possible to "copy" a table via a SELECT statement
Lets say you have a table called foo and you want to copy it to a table =named bar.
This will copy the structure and data:
SELECT * INTO bar FROM foo
This will copy the structure:
SELECT * INTO bar FROM foo WHERE 0=3D1
Note: you have to have the select into/bulkcopy dboption set to 'on' for =this method to work.
-- Keith
"Jim Campau" <Jim_Campau@.bausch.com> wrote in message =news:uA1kPus1DHA.1760@.TK2MSFTNGP10.phx.gbl...
> I have a DB which will have many tables that are the same only named
> differently. Instead of recreating each one is there a way to "Copy =Table" ?
> > Thanks in advance
> >|||Hi,
Use the command
select * into newtablename from tablename
The above command copy the table along with data
select * into newtablename from tablename where 1=2
The above command copy the table structure only.
Thanks
Hari
MCDBA
"Jim Campau" <Jim_Campau@.bausch.com> wrote in message
news:uA1kPus1DHA.1760@.TK2MSFTNGP10.phx.gbl...
> I have a DB which will have many tables that are the same only named
> differently. Instead of recreating each one is there a way to "Copy Table"
?
> Thanks in advance
>

Copy Table

I have a DB which will have many tables that are the same only named
differently. Instead of recreating each one is there a way to "Copy Table" ?
Thanks in advanceIt is possible to "copy" a table via a SELECT statement
Lets say you have a table called foo and you want to copy it to a table =
named bar.
This will copy the structure and data:
SELECT * INTO bar FROM foo
This will copy the structure:
SELECT * INTO bar FROM foo WHERE 0=3D1
Note: you have to have the select into/bulkcopy dboption set to 'on' for =
this method to work.
--=20
Keith
"Jim Campau" <Jim_Campau@.bausch.com> wrote in message =
news:uA1kPus1DHA.1760@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I have a DB which will have many tables that are the same only named
> differently. Instead of recreating each one is there a way to "Copy =

---

Table" ?

quote:

---

>=20
> Thanks in advance
>=20
>

---

|||Hi,
Use the command
select * into newtablename from tablename
The above command copy the table along with data
select * into newtablename from tablename where 1=2
The above command copy the table structure only.
Thanks
Hari
MCDBA
"Jim Campau" <Jim_Campau@.bausch.com> wrote in message
news:uA1kPus1DHA.1760@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I have a DB which will have many tables that are the same only named
> differently. Instead of recreating each one is there a way to "Copy Table"

---

?

quote:

---

> Thanks in advance
>

---

Copy rows from multiple tables into one table (in another database)

My boss has been using linked tables (read only) from Access to copy data
from our accounting system to his pc. Instead of using Access I would like
to copy rows from different tables into a new table in a different database.
I want to use a different database for security reasons because the new
database will be accessed by the web server.
What is the best way to simulate Access' linked tables and can the
resolution be realtime or would I need to run a query once a day to have the
most current info in my new table?You can either use DTS or Replication depends on how your boss want to see
the data as exactly current or could be sometime delay.
Perayu
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> My boss has been using linked tables (read only) from Access to copy data
> from our accounting system to his pc. Instead of using Access I would
> like to copy rows from different tables into a new table in a different
> database. I want to use a different database for security reasons because
> the new database will be accessed by the web server.
> What is the best way to simulate Access' linked tables and can the
> resolution be realtime or would I need to run a query once a day to have
> the most current info in my new table?
>
>|||It looks like I should use SQL triggers to update information in new table
when changes have been made in the accounting database. What is the best
way to export data rows from different tables to one new table and does
anyone have an example of how to create a trigger that watches many tables,
but only inserts into one table?
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> My boss has been using linked tables (read only) from Access to copy data
> from our accounting system to his pc. Instead of using Access I would
> like to copy rows from different tables into a new table in a different
> database. I want to use a different database for security reasons because
> the new database will be accessed by the web server.
> What is the best way to simulate Access' linked tables and can the
> resolution be realtime or would I need to run a query once a day to have
> the most current info in my new table?
>
>|||Sometime delayed is fine with us. I'll research those two topics, but which
is less complicated seeing as I'm not a SQL guru.
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:O%23lvOifvFHA.3100@.TK2MSFTNGP12.phx.gbl...
> It looks like I should use SQL triggers to update information in new table
> when changes have been made in the accounting database. What is the best
> way to export data rows from different tables to one new table and does
> anyone have an example of how to create a trigger that watches many
> tables, but only inserts into one table?
> "JohnS" <JohnSPAM@.hotmail.com> wrote in message
> news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||In my opinion, DTS is a easier one. Sometime, Replication is hard to
maintain whenever you try to change the related data structure.
Perayu
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:Oi4bWnfvFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Sometime delayed is fine with us. I'll research those two topics, but
> which is less complicated seeing as I'm not a SQL guru.
> "JohnS" <JohnSPAM@.hotmail.com> wrote in message
> news:O%23lvOifvFHA.3100@.TK2MSFTNGP12.phx.gbl...
>|||The functional equivalent to Access's linked tables is to set up a Linked
Server in SQL Server. A linked server can be any OLEDB data source. Any
tables in the remote system would be available through this technique.
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> My boss has been using linked tables (read only) from Access to copy data
> from our accounting system to his pc. Instead of using Access I would
> like to copy rows from different tables into a new table in a different
> database. I want to use a different database for security reasons because
> the new database will be accessed by the web server.
> What is the best way to simulate Access' linked tables and can the
> resolution be realtime or would I need to run a query once a day to have
> the most current info in my new table?
>
>|||I tried DTS but didn't find anyway to choose which rows in a table I want.
It seemed to be the whole table or nothing at all. The other problem was
data from the accounting system would wipe out the target destination data
and I want them to merge.
Can I choose certain rows from different tables and merge them into one
table with Linked Server?
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:uFybfFhvFHA.2504@.TK2MSFTNGP15.phx.gbl...
> The functional equivalent to Access's linked tables is to set up a Linked
> Server in SQL Server. A linked server can be any OLEDB data source. Any
> tables in the remote system would be available through this technique.
> "JohnS" <JohnSPAM@.hotmail.com> wrote in message
> news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||Well, DTS and linked servers are kind of "apples and oranges" topics.
In DTS, you can use regular SQL statements to define which columns and rows
you want to retrieve from your source connection, and you can define where
that data gets placed in your destination connection. DTS is a fairly large
topic with many sub-topics. I would encourage you to thouroughly review the
topic in SQL Server Books Online (BOL).
As for linked servers, all I was pointing out was that you can set up a
linked server in SQL Server in a somewhat similar fashion to the way you set
up linked tables in MS Access. Again, this is a moderately involved topic.
For more information, see the topic "Accessing External Data" in BOL.
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:eXBFQPivFHA.904@.tk2msftngp13.phx.gbl...
>I tried DTS but didn't find anyway to choose which rows in a table I want.
>It seemed to be the whole table or nothing at all. The other problem was
>data from the accounting system would wipe out the target destination data
>and I want them to merge.
> Can I choose certain rows from different tables and merge them into one
> table with Linked Server?
>
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:uFybfFhvFHA.2504@.TK2MSFTNGP15.phx.gbl...
>

Copy row in trigger instead of update?

I think this is a tough one, at least with my limited knowlege. Here
is what I am trying to do.
Background:
I want to create an audit trail for edits made to a database. I want
to do this by catching the update in a SQL trigger and turning it into
an insert with a time stamp and cancel the update.
So any row you change really does not change, it is copied to a new
row in the same table with a time stamp.
Copying the row is normally easy as in:
Insert into mytable select * from mytable where id= '17'
This will not work though, The problem with this is that id is an
Identity field and you cannot specify the value of an identity. So the
first trick is to basically do a select * (except id). I figured this
out here is the code:
_________________________________________________________________
declare @.tb sysname, @.col sysname, @.identid sysname
select @.tb='testupdate',
@.col='id'
declare @.sql varchar(2000)
select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
from information_schema.columns
where column_name!=@.col
and table_name=@.tb
select @.sql='insert into ' + @.tb + ' ' +
substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where
id=17'
print @.sql
exec(@.sql)
The problem with this is that it copies every row in the table. I
need to copy just the changed row. The above code does not include the
timestamp part here is that part, and separate section of code that
will be in the same trigger.
UPDATE testupdate
SET testupdate.updatedate = GETDATE()
FROM testupdate INNER JOIN Inserted ON testupdate.id = Inserted.id
This little bit of code effectively creates a time stamp on every
edited row and obviously get the id of the changed row. Now if I could
capture that id and use it in the first bit of code I think I would
have it.
I tried to do something like this
CREATE TRIGGER testtrig
ON testupdate
FOR UPDATE
AS
declare @.tb sysname, @.col sysname, @.myid sysname
select @.tb='testupdate',
@.col='id',
@.myid = @.@.identity
declare @.sql varchar(2000)
select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
from information_schema.columns
where column_name!=@.col
and table_name=@.tb
select @.sql='insert into ' + @.tb + ' ' +
substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where id='
+ @.myid
print @.sql
exec(@.sql)
UPDATE testupdate
SET testupdate.updatedate = GETDATE()
FROM testupdate INNER JOIN Inserted ON testupdate.id = Inserted.idThere are several things you might consider.
If you can, add a column to the table with a default value of getdate, so
all inserts are covered.
Then read about instead of triggers... They are new to SQL 2000, the normal
trigger fires AFTER the action, which is causing you a problem. An instead
of trigger fires INSTEAD OF the update ( for instance). You still get the
inserted ,deleted tables. The ACTUAL update only occurs IF you include an
update in your trigger... you could write something like
create trigger mytrig on mytable instead of update
as
insert into mytable select * from inserted
The original update would NOT occur, and instead you would have inserted the
NEW value of the row. The NEW row would have a new ID of course... you'd
have to mess with that...perhaps have a second integer column which
contains the original ID..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"OrlandoRocks" <parkJunkie@.orlandorocks.com> wrote in message
news:55879c92.0409120419.8418a63@.posting.google.com...
> I think this is a tough one, at least with my limited knowlege. Here
> is what I am trying to do.
> Background:
> I want to create an audit trail for edits made to a database. I want
> to do this by catching the update in a SQL trigger and turning it into
> an insert with a time stamp and cancel the update.
> So any row you change really does not change, it is copied to a new
> row in the same table with a time stamp.
> Copying the row is normally easy as in:
> Insert into mytable select * from mytable where id= '17'
> This will not work though, The problem with this is that id is an
> Identity field and you cannot specify the value of an identity. So the
> first trick is to basically do a select * (except id). I figured this
> out here is the code:
> _________________________________________________________________
> declare @.tb sysname, @.col sysname, @.identid sysname
> select @.tb='testupdate',
> @.col='id'
> declare @.sql varchar(2000)
> select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
> from information_schema.columns
> where column_name!=@.col
> and table_name=@.tb
> select @.sql='insert into ' + @.tb + ' ' +
> substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where
> id=17'
> print @.sql
> exec(@.sql)
>
> The problem with this is that it copies every row in the table. I
> need to copy just the changed row. The above code does not include the
> timestamp part here is that part, and separate section of code that
> will be in the same trigger.
> UPDATE testupdate
> SET testupdate.updatedate = GETDATE()
> FROM testupdate INNER JOIN Inserted ON testupdate.id => Inserted.id
>
> This little bit of code effectively creates a time stamp on every
> edited row and obviously get the id of the changed row. Now if I could
> capture that id and use it in the first bit of code I think I would
> have it.
> I tried to do something like this
>
> CREATE TRIGGER testtrig
> ON testupdate
> FOR UPDATE
> AS
> declare @.tb sysname, @.col sysname, @.myid sysname
> select @.tb='testupdate',
> @.col='id',
> @.myid = @.@.identity
> declare @.sql varchar(2000)
> select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
> from information_schema.columns
> where column_name!=@.col
> and table_name=@.tb
> select @.sql='insert into ' + @.tb + ' ' +
> substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where id='
> + @.myid
> print @.sql
> exec(@.sql)
> UPDATE testupdate
> SET testupdate.updatedate = GETDATE()
> FROM testupdate INNER JOIN Inserted ON testupdate.id => Inserted.id