copying database backup file

This is a pretty basic question, but I've generated a backup file for one of my SQL 2000 databases, and I need to copy the backup file to another computer so I can burn it on CD. Whenever I try to copy the file, I get an error message saying that the file is in use by another process and can't be copied. I have tried detaching the database, shutting down Enterprise Manager, stopping the MSSQL service, but all to no avail. There must be a way to do this, but I can't get it to work. Does anyone have any ideas?This shouldn't happen for the backup file, unless you are accessing the backup somehow. What is the filename you backuped up to? have you tried stopping SQL Server, then copying? Since I don't want to assume anything, I'll add this: make sure you are copying the backup file, not the mdb or ldf file.|||If you used Enterprise Manager, you may want to make sure you remove the backupfile from the SQLserver Backup options in All Tasks. But it is intriguing that the file is still in use.|||I tried all of the suggestions posted, but the only thing that ended up working was to rename the backup. The original backup had the same name as the database, but once I made another backup with a different name, I was able to copy the file. Thanks again for the help!

Copy table record as new record

Hi
I want to create a copy of an existing record in the same table, except with
a different primary key value of course.
This is pretty easy if you specify each in turn like:
INSERT INTO TableA
(
Col1
Col2
...
)
SELECT Col1, Col2, ...
FROM TableA
WHERE PK = @.RecordtoCopy
However, in my situation the database structure is evolving. Therefore, if
the fields in the TableA, above, are changed then the associated SQL script
is no longer valid.
Is there some way of doing the equivalent of:
INSERT INTO TableA
SELECT * FROM TableA
WHERE PK = @.RecordtoCopy
but leaving out the primary key field, which is an identity field.
That is, I don't want to have to specify the column names each time. I
guess I could use 'sycolumns' to construct a query or is there an easier way
?
What I've shown here is a very simplistic query. Mine are much more
complex, and I want to copy all fields from several records in tables formin
g
an hierarchical structure.
Cheers
TimHi Tim
I dont imagine a situation where you need to keep duplicate copy in the same
table.
Can you explain why you want to do that so to as provide some thought on tha
t.
Regards
R.D
"Tim M" wrote:

> Hi
> I want to create a copy of an existing record in the same table, except wi
th
> a different primary key value of course.
> This is pretty easy if you specify each in turn like:
> INSERT INTO TableA
> (
> Col1
> Col2
> ...
> )
> SELECT Col1, Col2, ...
> FROM TableA
> WHERE PK = @.RecordtoCopy
> However, in my situation the database structure is evolving. Therefore, i
f
> the fields in the TableA, above, are changed then the associated SQL scrip
t
> is no longer valid.
> Is there some way of doing the equivalent of:
> INSERT INTO TableA
> SELECT * FROM TableA
> WHERE PK = @.RecordtoCopy
> but leaving out the primary key field, which is an identity field.
> That is, I don't want to have to specify the column names each time. I
> guess I could use 'sycolumns' to construct a query or is there an easier w
ay?
> What I've shown here is a very simplistic query. Mine are much more
> complex, and I want to copy all fields from several records in tables form
ing
> an hierarchical structure.
> Cheers
> Tim
>|||This is for a cost estimating system where you want to create a new version
of an existing estimate. This would happen when you want to be on a new job
,
which is very similar to one that was previously costed and you want to use
the old one as a template for a new one.
I think I've solved it anyway. The code below is a function that creates an
insert clause as a string.
CREATE FUNCTION dbo.fn_CreateInsert (@.@.Table varchar(50), @.@.PKey
varchar(20)) RETURNS varchar(1000) AS
BEGIN
DECLARE @.InsertClause varchar(1000),
@.@.TableName varchar(255),
@.ColName varchar(255),
@.ColsCursor CURSOR
SET @.ColsCursor = CURSOR FAST_FORWARD
FOR
SELECT sysobjects.name AS TableName, syscolumns.name AS ColumnName
FROM syscolumns
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
INNER JOIN (sysobjects INNER JOIN sysusers ON sysobjects.uid =
sysusers.uid)
ON syscolumns.id = sysobjects.id
WHERE sysobjects.name = @.@.Table
OPEN @.ColsCursor
FETCH NEXT FROM @.ColsCursor
INTO @.@.TableName, @.ColName
SET @.InsertClause = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.ColName NOT IN (@.@.PKey, 'DateCreated', 'DateModified')
BEGIN
IF @.InsertClause = ''
SET @.InsertClause = '(' + @.ColName
ELSE
SET @.InsertClause = @.InsertClause + ', ' + @.ColName
END
FETCH NEXT FROM @.ColsCursor
INTO @.@.TableName,@.ColName
END
CLOSE @.ColsCursor
DEALLOCATE @.ColsCursor
SET @.InsertClause = 'INSERT INTO ' + @.@.Table + ' ' + @.InsertClause + ')'
RETURN @.InsertClause
END
GO
"R.D" wrote:
> Hi Tim
> I dont imagine a situation where you need to keep duplicate copy in the sa
me
> table.
> Can you explain why you want to do that so to as provide some thought on t
hat.
> Regards
> R.D
> "Tim M" wrote:
>|||Tim, try,
-- DDL & sample data
create table dbo.t1
(
keycol int not null identity primary key,
col1 int,
col2 int,
col3 int
);
insert into dbo.t1 values(1, 2, 3);
go
-- usp_copyt1row proc
create proc usp_copyt1row
@.keyval as int
as
declare @.col_list as nvarchar(1000), @.sql as nvarchar(4000);
set @.col_list = N'';
select @.col_list = @.col_list + ',[' + column_name + N']'
from information_schema.columns
where table_schema = N'dbo'
and table_name = N't1'
and column_name <> N'keycol';
set @.col_list = right(@.col_list, len(@.col_list) - 1);
set @.sql =
N'insert into dbo.t1('
+ @.col_list + N') select '
+ @.col_list + N' from dbo.t1 where keycol = @.keyval'
exec sp_executesql
@.sql, N'@.keyval as int', @.keyval;
go
-- test
exec usp_copyt1row 1;
exec usp_copyt1row 2;
select * from dbo.t1;
-- Output
keycol col1 col2 col3
-- -- -- --
1 1 2 3
2 1 2 3
3 1 2 3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Tim M" wrote:

> Hi
> I want to create a copy of an existing record in the same table, except wi
th
> a different primary key value of course.
> This is pretty easy if you specify each in turn like:
> INSERT INTO TableA
> (
> Col1
> Col2
> ...
> )
> SELECT Col1, Col2, ...
> FROM TableA
> WHERE PK = @.RecordtoCopy
> However, in my situation the database structure is evolving. Therefore, i
f
> the fields in the TableA, above, are changed then the associated SQL scrip
t
> is no longer valid.
> Is there some way of doing the equivalent of:
> INSERT INTO TableA
> SELECT * FROM TableA
> WHERE PK = @.RecordtoCopy
> but leaving out the primary key field, which is an identity field.
> That is, I don't want to have to specify the column names each time. I
> guess I could use 'sycolumns' to construct a query or is there an easier w
ay?
> What I've shown here is a very simplistic query. Mine are much more
> complex, and I want to copy all fields from several records in tables form
ing
> an hierarchical structure.
> Cheers
> Tim
>|||Thanks for that.
"Itzik Ben-Gan" wrote:
> Tim, try,
> -- DDL & sample data
> create table dbo.t1
> (
> keycol int not null identity primary key,
> col1 int,
> col2 int,
> col3 int
> );
> insert into dbo.t1 values(1, 2, 3);
> go
> -- usp_copyt1row proc
> create proc usp_copyt1row
> @.keyval as int
> as
> declare @.col_list as nvarchar(1000), @.sql as nvarchar(4000);
> set @.col_list = N'';
> select @.col_list = @.col_list + ',[' + column_name + N']'
> from information_schema.columns
> where table_schema = N'dbo'
> and table_name = N't1'
> and column_name <> N'keycol';
> set @.col_list = right(@.col_list, len(@.col_list) - 1);
> set @.sql =
> N'insert into dbo.t1('
> + @.col_list + N') select '
> + @.col_list + N' from dbo.t1 where keycol = @.keyval'
> exec sp_executesql
> @.sql, N'@.keyval as int', @.keyval;
> go
> -- test
> exec usp_copyt1row 1;
> exec usp_copyt1row 2;
> select * from dbo.t1;
> -- Output
> keycol col1 col2 col3
> -- -- -- --
> 1 1 2 3
> 2 1 2 3
> 3 1 2 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Tim M" wrote:
>