Copying of database

I tried to write a batch script which copies a database by making full
backup, then copying the backup file to another location and restoring it.
The backup schedule of the my database is this:
full backup on sunday at 23 h stored in file1
differential backup every day except sunday at 23h stored in file2
Now i'm not sure what will happen with my differential scheduled backups
when i delete the temporary backup file that is made only for copying during
the w. It's stored in another file (let's call it file3). I see that SQL
Server remembers every full backup, is there a way to tell it not to write
to backup history tables?
I'm thinking of another ways of copying database. One is to detach the
database or to stop the server but i think it's not good. Another way is DTS
package. Can you tell me if it can do the trick? Is there another way?
Thank you in advance
Georgi PeshterskiWays of copying a database:
1. Certainly backup and restore is an option, but not the first I would take
.
2. Detach, copy and attach also works, but it is kludgy.
3. Snapshot replication works well if you desire a snapshot to copy at any
one time (ie, the update is the entire database and instant update of data i
s
not important).
4. Transaction replication works well if you need real time updates
5. DTS works, as well, but can end up with unnecessary locking on the
original database while you are migrating. You have some control over this,
of course, but it gets more advanced.
I would aim for replication or DTS long before using a backup/restore or
detach/copy/attach scenario.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"George Peshterski" wrote:

> I tried to write a batch script which copies a database by making full
> backup, then copying the backup file to another location and restoring it.
> The backup schedule of the my database is this:
> full backup on sunday at 23 h stored in file1
> differential backup every day except sunday at 23h stored in file2
> Now i'm not sure what will happen with my differential scheduled backup
s
> when i delete the temporary backup file that is made only for copying duri
ng
> the w. It's stored in another file (let's call it file3). I see that SQ
L
> Server remembers every full backup, is there a way to tell it not to write
> to backup history tables?
> I'm thinking of another ways of copying database. One is to detach the
> database or to stop the server but i think it's not good. Another way is D
TS
> package. Can you tell me if it can do the trick? Is there another way?
> Thank you in advance
> Georgi Peshterski
>
>|||Thank you, i'm working to do it with DTS
"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM> wrote
in message news:F34C25E5-AD13-4255-B1E0-38CBD3481C5F@.microsoft.com...
> Ways of copying a database:
> 1. Certainly backup and restore is an option, but not the first I would
take.
> 2. Detach, copy and attach also works, but it is kludgy.
> 3. Snapshot replication works well if you desire a snapshot to copy at any
> one time (ie, the update is the entire database and instant update of data
is
> not important).
> 4. Transaction replication works well if you need real time updates
> 5. DTS works, as well, but can end up with unnecessary locking on the
> original database while you are migrating. You have some control over
this,
> of course, but it gets more advanced.
> I would aim for replication or DTS long before using a backup/restore or
> detach/copy/attach scenario.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************
> "George Peshterski" wrote:
>
full
it.
backups
during
SQL
write
the
DTS

copying data folder to another instance

How do I copy the data from one instance of an MSDE to another? What I need
to do is write the structure, data, etc on a CD so I can copy this back on
my server instance at home and work on it.
Thanks for the information.
Brad
Attach/Detach or Backup/Restore
"Brad" <ballison@.ukcdogs.com> wrote in message
news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> How do I copy the data from one instance of an MSDE to another? What I
need
> to do is write the structure, data, etc on a CD so I can copy this back on
> my server instance at home and work on it.
> Thanks for the information.
> Brad
>
|||Norman,
I primarily progam and I really am not a DB Admin. I understand the
concepts of Attach/Detach but I have never practically used it. How would I
get into the database to do that? Is the Backup/Restore the Backup that is
used through Windows?
I know I can get into it with OSQL, right? When I do try this opening a
command window, typing OSQL -U sa, then the sa password, I get the following
error:
[Shared Memory]SQL Server does not exist or access denied
[Shared Memory]ConnectionOpen (Connect())
Thanks for the information.
Brad
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Attach/Detach or Backup/Restore
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> need
>
|||Norman,
In doing some research I changed OSQL -U to OSQL -E -S <server\instance> and
it worked.
I am now using BACKUP and RESTORE.
Thanks for the information.
Brad
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Attach/Detach or Backup/Restore
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> need
>
|||Now I am getting an error message when I try to RESTORE. It looks as if
RESTORE is using the InstanceName from the Backup and I am trying to retore
to a different MSDE instance name on a different server. It gives me error
messages.
Did I do something wrong with the restore statement?
"Brad" <ballison@.ukcdogs.com> wrote in message
news:OTXJuiEEFHA.464@.TK2MSFTNGP15.phx.gbl...
> Norman,
> In doing some research I changed OSQL -U to OSQL -E -S <server\instance>
> and it worked.
> I am now using BACKUP and RESTORE.
> Thanks for the information.
> Brad
>
> "Norman Yuan" <NotReal@.NotReal.not> wrote in message
> news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
|||Posting the error message would help, but you're probably trying to restore
a database whose physical location does not exists on the destination
computer. Look at the WITH MOVE qualifier of the RESTORE command. This will
allow you to the logical database files to a different physical location
than where they originated from. Another solution is to make the location
for the database files the same on all systems.
Jim
"Brad" <ballison@.ukcdogs.com> wrote in message
news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
> Now I am getting an error message when I try to RESTORE. It looks as if
> RESTORE is using the InstanceName from the Backup and I am trying to
> retore to a different MSDE instance name on a different server. It gives
> me error messages.
> Did I do something wrong with the restore statement?
>
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OTXJuiEEFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||Jim,
Thanks for the information. This is the error message that I get:
File 'CLASSICSQL' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL.mdf'. Use WITH MOVE to identify a valid
location for the file.
The syntax I am using is "RESTORE DATABASE ced FROM DISK =
'D:\MSDEBack\CES.bak' "
This is when I get the error. What is the syntax using the WITH MOVE? or
where would I find this information?
Thanks,
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uOfJD%23FEFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Posting the error message would help, but you're probably trying to
restore
> a database whose physical location does not exists on the destination
> computer. Look at the WITH MOVE qualifier of the RESTORE command. This
will[vbcol=seagreen]
> allow you to the logical database files to a different physical location
> than where they originated from. Another solution is to make the location
> for the database files the same on all systems.
> Jim
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
gives[vbcol=seagreen]
<server\instance>[vbcol=seagreen]
I[vbcol=seagreen]
back
>
|||Jim,
Got it. Using osql for the first time and making sure there are not any
syntax errors is a pain.
Thanks again,
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uOfJD%23FEFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Posting the error message would help, but you're probably trying to
restore
> a database whose physical location does not exists on the destination
> computer. Look at the WITH MOVE qualifier of the RESTORE command. This
will[vbcol=seagreen]
> allow you to the logical database files to a different physical location
> than where they originated from. Another solution is to make the location
> for the database files the same on all systems.
> Jim
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
gives[vbcol=seagreen]
<server\instance>[vbcol=seagreen]
I[vbcol=seagreen]
back
>
|||hi,
AllcompPC wrote:
> Jim,
> Thanks for the information. This is the error message that I get:
> File 'CLASSICSQL' cannot be restored to 'C:\Program Files\Microsoft
> SQL Server\MSSQL$UKCSQL\Data\CLASSICSQL.mdf'. Use WITH MOVE to
> identify a valid location for the file.
> The syntax I am using is "RESTORE DATABASE ced FROM DISK =
> 'D:\MSDEBack\CES.bak' "
> This is when I get the error. What is the syntax using the WITH
> MOVE? or where would I find this information?
>
RESTORE DATABASE ced
FROM DISK = 'D:\MSDEBack\CES.bak'
WITH
MOVE Logical_File_Name_for_Data to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL.Mdf' ,
MOVE Logical_File_Name_for_Log to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL_Log.Ldf'
or the alike, specifying the logical file names and the destination physical
position you want the files to be restored to..
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

copy tuples from table tsql?

Hi all,
I'm trying to write a TSQL procedure that takes as an argument a
tablename in a database and then it copies the first 100 tuples of the
given table in the same table (it actually duplicates them). The table
doesn't have a standard schema and the query should be able to deal with
different kinds of schemas.
Any help on this?
Thanks in advance,
PeterWhere to start! Why would you ever want to copy a row* in the same table?
That's undesirable and unnecessary in SQL. Every table should have a key
that prevents duplicate rows. Maybe you mean you want to copy rows to
another table? But then you go on to say you want to copy the "first 100
tuples". Tables in SQL have no inherent logical order. What do you mean by
"first 100"?
Finally, parameterizing table names is generally a very bad idea in a
production system. Sometimes DBAs need to do that for admin tasks and the
way to do it is to use dynamic SQL. The following article has the gory
details and all the caveats that go with dynamic code:
http://www.sommarskog.se/dynamic_sql.html
I do recommend you study some relational database theory and practice and
then reconsider your requirements.
[ * In relational theory tuples by definition are not duplicated in a
relation. Rows are not tuples and your use of that term is incorrect. ]
David Portas
SQL Server MVP
--
"pnp" <pnpNOSPAM@.softlab.ntua.gr> wrote in message
news:OcHFgFUxFHA.2232@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I'm trying to write a TSQL procedure that takes as an argument a tablename
> in a database and then it copies the first 100 tuples of the given table
> in the same table (it actually duplicates them). The table doesn't have a
> standard schema and the query should be able to deal with different kinds
> of schemas.
> Any help on this?
> Thanks in advance,
> Peter

Copy table from ODBC to SQL database

Hi all,

I'm trying to copy a table from an odbc db to an sqlserver2005 db. SSIS will not do this for me and says I must write a script. I'm not experienced enough in sqlserver to do this and was unable to find a example of this by searching the web. Does anyone know of any websites with an explanation of how to do this?

thanks for any help.

You can use Import/Export Wizard to copy your data to SQL Server db.

http://www.databasejournal.com/features/mssql/article.php/3580216
http://msdn2.microsoft.com/en-us/library/ms141209.aspx|||

That does not really help as the question was not abou how to start the process but how to get part this point, I am having the same problem and am not very good with SQL queries.

Thanks

|||

Have you seen the following thread?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379235&SiteID=1

There is a lot more messages on the topic in this forum; try to search for them.

Thanks.

Copy table from ODBC to SQL database

Hi all,

I'm trying to copy a table from an odbc db to an sqlserver2005 db. SSIS will not do this for me and says I must write a script. I'm not experienced enough in sqlserver to do this and was unable to find a example of this by searching the web. Does anyone know of any websites with an explanation of how to do this?

thanks for any help.

You can use Import/Export Wizard to copy your data to SQL Server db.

http://www.databasejournal.com/features/mssql/article.php/3580216
http://msdn2.microsoft.com/en-us/library/ms141209.aspx|||

That does not really help as the question was not abou how to start the process but how to get part this point, I am having the same problem and am not very good with SQL queries.

Thanks

|||

Have you seen the following thread?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379235&SiteID=1

There is a lot more messages on the topic in this forum; try to search for them.

Thanks.

Copy Selected column data from table to another during Upgrade of App

Hi,
I need to write a script that will be called during the database upgrade of my application. This is part of reorg of the tables. The script has to get data for say 4 columns from table A and insert it into another table B. Table B has identity insert column and remaining 4 columns matching the ones to be copied. The data is dependent on user database, hence number of records needs to be copied might be different. Also the columns can have null values.

I tried using bcp Command as follows..
bcp "select colA,colB,colC,colD from A" queryout "c:\temp\A.dat" -t"\t" -r"\n" -c

I'm able to get the dat file, but not the format file. Can anyone tell me how to get it using query file with -c option. Also if there is better option to copy data, kindly let me know.

This is very critical. Appreciate your help.

Thanks,
Ramya.Why not copy the data into a local temporary (or permanent) table?

I presume (perhaps incorrectly) that you want to retain this data to be inserted back into the modified table (or another table) later during the upgrade process.

Regards,

hmscott|||That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..

Thanks,
Ramya.|||Create TableTemp (
ID int IDENTITY(1,1),
ColumnA varchar(10),
ColumnB varchar(10),
ColumnC varchar(10),
ColumnD varchar(10)
)
GO

INSERT INTO TableTemp (ColumnA, ColumnB, ColumnC, ColumnD)
SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM
MySourceTable
GO

ALTER TABLE MySourceTable DROP COLUMN ColumnA
GO

This will leave a permanent copy of the data from MySourceTable in TempTable.

Regards,

hmscott

That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..

Thanks,
Ramya.