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.