What is the best way to copy a DB?
-
Hello
in a project we are doing it was decided to hold a basic version of a DB on the MSSQL and every time the programm wants to create a new db to work with, it copies the template and includes it as a new DB on the MSSQL.
Can someone tell me what would be the best way to accomplish this? Can i just detach the db, copy the files (and rename the copies) and attach both of them again? Or do i have to do something else?
Thanks
Matt
-
I think the best way is to use a DTS package. When you create a new package, you can choose a task called "dataBase transfer task" (I don't know the english correct term because my Enterprise manager is in French).
You need to be Administrator to execute this task.
Kindest Regards,fabricej
-
Matt,
I assume you know about the model database, and it's not appropriate for your situation?
Well, just in case - from BOL...
System Databases and Data
model
The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
nope i didnt
can you point me to a good refernce in the iNet about these model database? My problem would be that we have at least 5 different templates so far. Does it work anyway by selecting one of these templates?
Thanks for the reply (too you both
)Matt
-
Okay - There's only 1 model database for each instance of sql server, so it sounds like it's not appropriate for you in this case. It was worth a try, anyway...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
You could just restore backups WITH MOVE.
You can script this task in TSQL using RESTORE, and in fact I don't see why you couldn't create a stored procedure to do it which would accept the location of the backup file, the name of the new DB, location of new db files. etc.
If your DBs may not be all on the same server, you will have problems with resyncing logins and users; not insurmountable though.
I assume this is not part of a production system, but for use by developers etc...creating new databases isn't the kind of thing I'd expect a user app to do.
If you really do need to do this as an unmanaged end-user-initiated process, you might be best off scripting out all objects and data from the DB and then executing the resulting script by whatever means the app will best support. E.g. save the scripts in a table column, have the app query the table to retrieve the batch as a string and execute it against the DB. You could even call osql to run sql batch files from a network location. I don't necessarily recommend doing so though.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply