May 14, 2008 at 1:56 pm
to copy 1 db to another db on the same server. it is to be used for training purposes, instead of letting new users whack away at the live/current database. so i need to make a copy of the database and let the new train on our software using the test db.
currently i am doing this manually using the management console but would like to know if there is a TSQL way. Don't know a lot about dts but am needing the abilitiy to access each clients system log into different servers and copy select database to another.
May 14, 2008 at 1:58 pm
One of the simplest ways of doing this is to do a backup and a restore.
The T-SQL is pretty clear in books-online.
May 14, 2008 at 2:15 pm
Roy,
The quickest/easiest/simplest way to do it is to perform a SELECT INTO in a stored procedure that you can easily execute on the fly or execute it from the Scheduler (if it is performed on a regular basis.)
Running a SELECT INTO operation requires that the table you are selecting your data into may not exist - hence the IF exists() jazz. Your SELECT statement can be configured to pull any subset of data you desire. The "myWorkTable" table will have a schema identical to the same fields in the "myProdTable". If you truly want a full copy of the production table, then the SELECT portion can be simplified to a SELECT * INTO myWorkTable FROM myProdTable.
IF exists( SELECT * FROM sysobjects WHERE xtype='U' AND name = 'myWorkTable' )
BEGIN
DROP TABLE dbo.myWorkTable
END
Select
t1.date,
t1.stornum,
t1.register,
t1.cashier,
t1.transnum,
t1.price,
t1.qty,
t1.dept,
t1.transtotal,
t1.tndrtype1
INTO myWorkTable
FROM myProdTable AS t1
May 14, 2008 at 2:31 pm
1) I have written a program (delphi) that is using and adoconnection, command componenet.
2) Each users site has different server name/database name (nothing standardized on our part)
3) All table names are standardized (ie MASTERUSF is the same on every users system)
4) Each user can have different number of our tables (is AP, GL, PY modules or maybe just the GL module)
5) We currently use the Identity column on all tables
6) each table has a unique index
7) My program currently is processing 1 table at a time, (if it doesnt exist then i have to retrieve all the information to create a new one, then i use select {column names} into new db.table from old db.table.
8) as you might have guessed this takes up a lot of time to create the training database
this why i was curious to find a quicker/simpler way to create this training database. my program standardizes the training db name and sets our configuration file to use that db. currently when the users or our companies trainers set up these training databases the names are all over the place. So in a nutshell i am just looking for a way to get all the data from db1 to db2 (new, overwrite as needed)
May 15, 2008 at 5:39 am
Like I said, keeping it simple, doing a backup and a restore would be simple, easy, and probably end up the fastest solution.
The SELECT INTO option suggested could work, but it would mean you have to code the object drops, the SELECT INTO statements, and the creation of all missing indexes and statistics to get an accurate re-creation of your database.
You have that same type of problem in your application solution - the base table schema is the easy part, it is the re-creation of the indexes, statistics, views, procedures, functions, and permissions that will turn into a nightmare.
BACKUP DATABASE [MySource]
TO DISK = 'C:\Copy.BAK';
RESTORE DATABASE [MyDestination]
FROM DISK = 'C:\Copy.BAK'
WITH MOVE MySourceData TO 'C:\MyNewFile.mdf'
, MOVE 'MySourceLog TO 'C:\MyNewLog.ldf'
, REPLACE;
May 15, 2008 at 5:59 am
No doubt about it. The methode Michael Earl proposed is the simplest !
- Simple
- Straight forward
- complete
- no log overhead
- the space you need = the space of your prod db.
Best would even be putting it on a seperate instance or even server,
because then you would not hinder your production environment.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply