September 29, 2008 at 8:52 am
I need a way of creating a copy (read and write capable) of a database from either a script or a stored proc.
Any suggestions? Is there a way of using the db copy wizard and saving the script being the scenes??
September 29, 2008 at 9:00 am
You can create a script of your databse by using Object Explorer-Right click on DB --> tasks --> Generate Scripts
September 29, 2008 at 9:05 am
If you need data and structure, one of the most common practices is to do a database backup (or use a backup from your DR plan) and restore it to another server or different database name. This can be easily scripted in a stored procedure.
September 29, 2008 at 9:50 am
Yes I do need data as well as structure. Since the db is 20Gb I was rather hoping to avoid the backup to file, copy file (to another server), restore file route.
:blink:
September 29, 2008 at 9:55 am
FNS (9/29/2008)
Yes I do need data as well as structure. Since the db is 20Gb I was rather hoping to avoid the backup to file, copy file (to another server), restore file route.:blink:
You can try import/export wizard to move table structure and also data. For other database objects like views, procs, function you can script them and execute it on target database.
SQL DBA.
September 29, 2008 at 9:56 am
FNS (9/29/2008)
Yes I do need data as well as structure. Since the db is 20Gb I was rather hoping to avoid the backup to file, copy file (to another server), restore file route.:blink:
why?
Backup/restore will be more efficent than copying data via another means such as SSIS
September 29, 2008 at 9:59 am
The import/export wizard is probably going to be less efficient than a backup / restore approach.
Depending on your recovery plan, you probably do not need to do a backup - you should already have one. One good approach I have used in the past is to simply always copy backup files to my development server and have a manual process that restores the necessary files on demand.
September 29, 2008 at 10:04 am
i find the quickest method and the easiest (unless the DB must be online 27-7) is to detach the DB (10 secs), copy the mdf and ldf files to another location (preferably other server - around 45 mins depending on network speed) and then just re-attach the db on the new instance.
September 29, 2008 at 10:23 am
Thanks for all your suggestions.
I just wanted to make sure I hadn't missed a simple method for doing what seems to be basic task. Since the live db must be on-line 24x7 I'll get the backup files copied over night.
The stored proc will then restore the latest (probably) backup file from the local disk which should take about 30 mins or so.
September 29, 2008 at 10:31 am
There was an article in the newsletter today that may be of use to you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply