Copying the database

I realize that SQL Server is a big database and a big subject so perhaps I
should preface my question by saying the following. I am using SQL Server
2000 simply because Microsoft Visual Studio .net has so much market-share.
VS.net and SQL Server are a good match. I see that SQL Server is a quality
product, but frankly, except for the reason mentioned, it is overkill for
what we are doing. Prior to using SQL Server I was using Adaptive Server
Anywhere. The reason I mention this is that it will really help if I can
keep things as simple as possible where SQL Server is concerend. I am
running it as a back-end to a Windows Client/Server application in VS.net on
a local area network. My largest table is only about 75, 000 records. It
is a simple database by SQL Server standards.
That said, I need to develope a scheme for getting a copy of the database at
the Client's location and copying it (about weekly) to my office location.
The client's database is kept on a Windows 2000 Server. The database at my
office is on a stand-alone single machine. I have reviewed the material
extensively in a knowledgebase article titled "How to move databases between
computers that are running SQL Server" (314546). I think I understand most
of it but have a few questions relative to Detaching and Attaching, Logins
and Passwords, and Diagrams.
First - Detaching and attaching. When I first started copying databases
between the two locations I had SQL Server installed on a local machine at
my office, but also on a local machine at the clients. At that point in
time, it was not installed on their server. I did not know I needed to
detach and re-attach. So, I just copied the .mdf and .ldf files from
machine to machine. Funny thing is, it seems to work. That is, on the
destination machine I created a database called "Corp" and then simply
overwrote Corp.mdf and Corp.ldf with the files from the source computer. In
this case, both the source and destination machines had SQL Server installed
in exactly the same way with the same directories. This may account for why
it worked. I don't know. If in my case it is not really necessary to
detach and re-attach it would be nice not to have to do so. Just one less
step to have to worry about. Perhaps someone could clarify.
Second - Diagrams. the article implies that Diagrams do not get copied but
it appears to me they do. Did I mis-understand? On my target machine I do
see what appears to be the same diagram I had on the source machine.
Third - Logins and Passwords. 99% of the time I will be making a copy of
the database at the clients and coping it to my office, not the other way
around. I understand that Logins and passwords do not get copied. The way
the application is setup now all machines access the database in
Authentication mode. Is there anything to be aware of at my office with
regard to still being able to access the database? I read quite a bit about
orphaned users in the Knowledgebase articles but it is not clear to me how
much of a problem, if any, this might be to me. I don't know if I have
explained this well but perhaps somebody could give it a shot.
Sorry to be so wordy but don't know how else to ask what I need to know."Woody Splawn" <woody@.splawns.com> wrote in message
news:ewPrp4EtDHA.3496@.TK2MSFTNGP11.phx.gbl...
> I realize that SQL Server is a big database and a big subject so perhaps I
> should preface my question by saying the following. I am using SQL Server
> 2000 simply because Microsoft Visual Studio .net has so much market-share.
> VS.net and SQL Server are a good match. I see that SQL Server is a
quality
> product, but frankly, except for the reason mentioned, it is overkill for
> what we are doing. Prior to using SQL Server I was using Adaptive Server
> Anywhere. The reason I mention this is that it will really help if I can
> keep things as simple as possible where SQL Server is concerend. I am
> running it as a back-end to a Windows Client/Server application in VS.net
on
> a local area network. My largest table is only about 75, 000 records. It
> is a simple database by SQL Server standards.
> That said, I need to develope a scheme for getting a copy of the database
at
> the Client's location and copying it (about weekly) to my office location.
> The client's database is kept on a Windows 2000 Server. The database at
my
> office is on a stand-alone single machine. I have reviewed the material
> extensively in a knowledgebase article titled "How to move databases
between
> computers that are running SQL Server" (314546). I think I understand
most
> of it but have a few questions relative to Detaching and Attaching, Logins
> and Passwords, and Diagrams.
> First - Detaching and attaching. When I first started copying databases
> between the two locations I had SQL Server installed on a local machine at
> my office, but also on a local machine at the clients. At that point in
> time, it was not installed on their server. I did not know I needed to
> detach and re-attach. So, I just copied the .mdf and .ldf files from
> machine to machine. Funny thing is, it seems to work. That is, on the
> destination machine I created a database called "Corp" and then simply
> overwrote Corp.mdf and Corp.ldf with the files from the source computer.
In
> this case, both the source and destination machines had SQL Server
installed
> in exactly the same way with the same directories. This may account for
why
> it worked. I don't know. If in my case it is not really necessary to
> detach and re-attach it would be nice not to have to do so. Just one less
> step to have to worry about. Perhaps someone could clarify.
You're really beyond Detatching and Attaching. To make this work right,
you're better off using backups. Set up a maintence plan for the database
to back it up nightly (which you should do anyway). Set retention period
and file naming convention, etc. Just dump the backups to disk right on the
server.
Then you can instruct your customer to move move the backups off-server
nightly. For instance they can just do a flat-file backup of your DB server
nightly, and they will pick up the full backups.
Anyway, for getting the database back to your office, you can just look in
the backup folder, and pick the latest backup file and beam it back home.
Once there use RESTORE DATBASE XXX WITH MOVE to restore the client's
database onto your server.
> Second - Diagrams. the article implies that Diagrams do not get copied
but
> it appears to me they do. Did I mis-understand? On my target machine I
do
> see what appears to be the same diagram I had on the source machine.
Diagrams are stored in the msdb database. They will not be included.
> Third - Logins and Passwords. 99% of the time I will be making a copy of
> the database at the clients and coping it to my office, not the other way
> around. I understand that Logins and passwords do not get copied. The
way
> the application is setup now all machines access the database in
> Authentication mode. Is there anything to be aware of at my office with
> regard to still being able to access the database? I read quite a bit
about
> orphaned users in the Knowledgebase articles but it is not clear to me how
> much of a problem, if any, this might be to me. I don't know if I have
> explained this well but perhaps somebody could give it a shot.
As SA of your database, you will be DBO of the restored database. In other
words, don't worry about it.
David|||Thanks for responding.
> You're really beyond Detatching and Attaching. To make this work right,
> you're better off using backups.
If you think this is what I should do I am open to it. Having to go through
the restore seems slightly more cumbersome but maybe I'm being picky and am
just not use to it. If you don't mind, briefly, what is the advantage of
using backup and restore over detach and re-attach?
>Set up a maintence plan for the database
> to back it up nightly (which you should do anyway).
The applicaiton is not in production yet but it is my plan to do as you've
suggested once it is.
> Set retention period
> and file naming convention, etc. Just dump the backups to disk right on
the
> server.
I'm not sure what you mean when you mean when you talk about setting the
retention period and naming convention. However, I suppose what it boils
down to is to create a .bak file somewhere on the server and be sure it gets
backed up nightly. The backup tapes are moved off-site daily.
> Diagrams are stored in the msdb database. They will not be included.
Bare with me a minute. I am still new to this. I have been creating my
relationships between the tables by use of the Diagrams. It's easy to draw
lines there etc. If the diagram is gone that doesn't mean the link or
relationship between the tables is gone, does it? I suppose not. Further,
I suppose I should get in the habit of creating the relationship from within
design mode of the tables themselves. Y/N?
> As SA of your database, you will be DBO of the restored database. In
other
> words, don't worry about it.
That's nice to hear.
Thank You again.|||David:
Could I ask another question. When you restore a database and there is
already a database of the same name in the target directory of the target
machine, I suppose you must delete or remove that database before doing the
restore. Y/N? It will not just overwrite the database Y/N?|||"Woody Splawn" <woody@.splawns.com> wrote in message
news:eBI6WBItDHA.3144@.tk2msftngp13.phx.gbl...
> David:
> Could I ask another question. When you restore a database and there is
> already a database of the same name in the target directory of the target
> machine, I suppose you must delete or remove that database before doing
the
> restore. Y/N? It will not just overwrite the database Y/N?
>
This is the reason you want to use BACKUP and RESTORE intead of attach and
detach.
When you restore a dump, you extract the data files from the dump and put
them somewhere on your server. RESTORE gives you the option of renaming the
data files and either restoring over an existing database, or restoring as a
new database.
A database is made up of data files and log files. For a small database,
perhaps just one data file and one log file. Each of the files has both a
logical name and a physical name. So lets walk through what you would do.
Say your database is called MyApp.
ps
About the diagrams. The layout of the diagrams is stored in MSDB, but the
relationships are in the application database. The diagram is easilly
recreated, it's perfectly fine to use the diagram to design tables and
relationships.
Anyway below are samples of everything you will need. BACKUP and RESTORE
can be complicated, but only a DBA really needs to know most of it.
Here's everything a developer really needs to know:
David
--to create a database
create database MyApp
/*
The CREATE DATABASE process is allocating 0.63 MB on disk 'MyApp'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'MyApp_log'.
*/
--to dump the entire databse to disk
backup database myApp to disk='c:\MyApp.bak'
/*
Processed 80 pages for database 'myApp', file 'MyApp' on file 1.
Processed 1 pages for database 'myApp', file 'MyApp_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.240 seconds (2.734
MB/sec).
*/
--to get a list of the logical files in the dump:
--(if you always use the same 2 logical files, you can skip this step
restore filelistonly from disk='c:\MyApp.bak'
/*
LogicalName,PhysicalName,Type,FileGroupName,Size,MaxSize
MyApp,d:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp.mdf,D,PRIMARY,655360,35184372080640
MyApp_log,d:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp_log.LDF,L,,516096,35184372080640
(2 row(s) affected)
*/
--then to restore the dump to a new database called MyApp_customer1
restore database MyApp_customer1 from disk ='c:\MyApp.bak'
with
move 'MyApp' to 'd:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp_customer1.mdf',
move 'MyApp_log' to 'd:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp_log_customer1.ldf'
/*
Processed 80 pages for database 'MyApp_customer1', file 'MyApp' on file 1.
Processed 1 pages for database 'MyApp_customer1', file 'MyApp_log' on file
1.
RESTORE DATABASE successfully processed 81 pages in 0.118 seconds (5.562
MB/sec).
*/
--to restore the dump and overwrite the MyApp_customer1 database
restore database MyApp_customer1 from disk ='c:\MyApp.bak'
with
replace,
move 'MyApp' to 'd:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp_customer1.mdf',
move 'MyApp_log' to 'd:\Program Files\Microsoft SQL
Server\MSSQL\data\MyApp_log_customer1.ldf'
/*
Processed 80 pages for database 'MyApp_customer1', file 'MyApp' on file 1.
Processed 1 pages for database 'MyApp_customer1', file 'MyApp_log' on file
1.
RESTORE DATABASE successfully processed 81 pages in 0.118 seconds (5.562
MB/sec).
*/|||Hi Woody,
Thanks for using MSDN newsgroup. It's my pleasure to assist you with this issue.
As we know, detach/attach has the different mechanism from backup/restore. Detach/attach is
logical copying the database. The database files (.mdf, .ndf and .ldf) are not physically
moved and the server just references the attached database.
As you can see now, if the original database (or files) is damaged or corrupted, the attached
database will no longer be used until we use the backuped database to perform a restore
operation. Additionally, backup/restore database can also shrink the transaction logs and
prevent some volume limits for performance benefits.
=========For diagrams, their information is stored in the system table "dtproperties". If you use
backup/restore of detach/attach method, the diagrams will certainly not be lost as SQL Server
can get the needed information in that table.
However, if you try using DTS (describes in KB 314546 third method to move user database)
to move the database, the information of the diagram will be lost as well as some description
information for the table column. To work around this issue, you should perform a statement
"select * from dtproperties" to transfer all the information in the dtproperties table.
For more information, please reference the following article:
320125 HOW TO: Move a Database Diagram
http://support.microsoft.com/?id=320125
=========For the last question about restore database if it already exists, it's no need to delete it before
you restore back the database. When you arrive at the Restore Database Dialog Box, you
can navigate to the Option pan. There are three check boxes and radio options allowing you
to decide what restore mode you'd like to perform.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Copying Row Data within the same table

I have the following table:

Table name: RR

columns:

Subject (varchar (35), Null)

Topic (varchar (35), Null)

RD (text, null)

RR (text, null)

Picture (varchar (50), Null)

Video (varchar (50), Null)

RRID (int, Not Null)

TSTAMP (datetime, Null)

RRCount (int, Not Null)

This table stores common information used in resolving technical problems based on Subject and Topic. However, I've now created a Subject/Topic where I want to copy all the data that corresponds to another Subject/topic.

Example:

There are 35 rows that correspond to Subject = 'Publisher01' and Topic = 'Subcategory03'. I want to create 35 new rows that contain the same RD and RR data, but have Subject = 'Publisher02' and Topic = 'Subcategory07'. Highest current RRID = 5008

I cannot figure out how to write that query. I apologize in advance for the fact that this is, no doubt, a seriously beginner question.

Hi,

would be nice to have some DDL on hand to see your additional table information and some expected results, but anyway:

if you just want to copy these rows (I don′t know what you mean by RRID ?!) the easiest insert statement is:

INSERT INTO RR
(...collist....,Subject,Topic )
SELECT
...collist...,'Publisher02','Subcategory07'
FROM RR
WHERE = 'Publisher01' and
Topic = 'Subcategory03'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Copy schema and data from sqls2k to sqls2k5express

Hello,
I am a newbie on this subject.
How do I move a database in SQL Server 2000 to SQL Server 2005 Express?
How do I manage SQL Server 2005 Express? Can that be done in VS 2005
Professional.
Thank you,
dbuchananHi
Backup and restore or DB attach and detach.
You can use Microsoft SQL Server Management Studio Express - Community
Technology Preview (CTP) November 2005 to manage the SQL Express instance.
http://www.microsoft.com/downloads/...&displaylang=en
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
news:1134435181.280719.206900@.g49g2000cwa.googlegroups.com...
> Hello,
> I am a newbie on this subject.
> How do I move a database in SQL Server 2000 to SQL Server 2005 Express?
> How do I manage SQL Server 2005 Express? Can that be done in VS 2005
> Professional.
> Thank you,
> dbuchanan
>|||Mike,
My previous question I asked for my target machine.
I have MSDN Professional subscription and I have SQL Server 2005
installed, but I don't know how to get started with 2005
1.) Where are the databases. I selected the option to install all
sample databases.
2.) Where also is the equivalent to Enterprise Manager, Query Analyser
for 2005 and the other services. I did a complete install!
Under SQL Configuration Manager / SQL Server 2005 Services I can see a
list of everything I would be interested in finding. I can see the
state, start mode, etc. of these services but where to I get access to
them'''?
How do I get started with SQL Server 2005? Does it have to be made into
such a mystery'
Thank you,
dbuchanan|||The new weapon of choice is the SQL Server Management Studio (SSMS). And whe
n
I say 'choice' one really has no other choice. :)
But you might start with the SQL Server Cofiguration Manager and the SQL
Server Surface Area Configuration to enable access to the server, or better
yet - Books Online, What's new.
ML
http://milambda.blogspot.com/|||ML,
1.) I did a full install. Does SQL Server Management Studio install
with SQL Server 2005? I cannot find it.
2.) Where is Books Online? The Icon that looks like it from SQL Server
Configuration Manager is really MMC help and a search of "What's New"
says nothing about SQL Server 2005.
The Help from SQL Server 2005 Serface Area Configuration is SQL Server
Setup help and a search of "What's New" is whats net for setup.
For SQL Server 2005 from the start menu there is no shortcut to Books
Online like there is for SQL Server 2000.
Where is Books Online?
Why is SQL Server 2005 services so hard to find?
dbuchanan|||Are you using SQL 2005 Express? Well, there are no visual tools available in
the SQL Express set up. SQL Server Management Studio Express is available as
a separate download. The same goes for Books Online.
Go to http://www.microsoft.com/downloads
Help (Books Online) is also avaliable on-line:
http://msdn2.microsoft.com/default.aspx
ML
http://milambda.blogspot.com/|||ML,
No I am not using SQL 2005 Express.
I have done a *complete* install of *SQL Server 2005 Developer
Edition*. I am a MSDN *Professional* Subscriber.
Where is Books Online? Were are the SQL Server Management Studio?
dbuchanan|||
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Backup and restore or DB attach and detach.
>
I have tried to do this. SQL2005 Express does not support these featurs for
SQL 2000 databases. If you are sucessful please let us all know! I would
like to distribute a SQL2000 database to clients running SQL2005 express.
After much research I am still trying to get the database into SQL2005
Express. Any help would be very much appreciated!|||> If you are sucessful please let us all know!
I detached a SQL 2000 database using sp_detach_db and successfully attached
the files to a SQL 2005 with CREATE DATABASE ... FOR ATTACH:
CREATE DATABASE MyDatabase
ON(
NAME='MyDatabase',
FILENAME='C:\DataFiles\MyDatabase_Data.mdf')
LOG ON(
NAME='MyDatabase_Log',
FILENAME='C:\LogFiles\MyDatabase_Log.ldf')
FOR ATTACH
Hope this helps.
Dan Guzman
SQL Server MVP
"HVG" <HVG@.discussions.microsoft.com> wrote in message
news:D5F8E749-D268-4B4D-870E-7B7089195D82@.microsoft.com...
>
> "Mike Epprecht (SQL MVP)" wrote:
>
> I have tried to do this. SQL2005 Express does not support these featurs
> for
> SQL 2000 databases. If you are sucessful please let us all know! I would
> like to distribute a SQL2000 database to clients running SQL2005 express.
> After much research I am still trying to get the database into SQL2005
> Express. Any help would be very much appreciated!