January 16, 2007 at 1:39 pm
I'm trying to script Backup and Restore operations to 'Copy' and rename databases for new use. I'd like to be able to omit the log and full text from this procedure, any ideas?
Thanks in advance for any assitance!
January 17, 2007 at 11:46 am
Hi Rob,
Plz try the below steps:
1. Script out your original database
2. Create a new database with new name using that script. This will ensure that all objects are present under correct schema in the new database.
3. Change the new database to simple recovery mode.
4. Fire the SSIS to load the data for all tables.
Regards
Utsab Chattopadhyay
January 17, 2007 at 4:01 pm
For small db it should be fine where as for large dbs it will be pain and take long time
Backup restore is the best and simple way move the data when you need all data.
Why you don't want to use backup/restore?
MohammedU
Microsoft SQL Server MVP
January 17, 2007 at 5:21 pm
Rob,
See "How to restore a database with a new name" under "RESTORE DATABASE" in BooksOnLine for the T-SQL commands needed to restore a database with a new name and new file names. You have to restore both the data file and log file. Full-text catalogs aren't backed up.
Greg
Greg
January 17, 2007 at 5:32 pm
Hey all, thanks so much for the thoughts.
I actually have been using backup and restore which worked but in SQL 2005 the FullText catalog is baked into the process. I wanted to omit the log files (and fulltext) 'cause they're huge (sometimes 3GB or larger) and the new DB doesn't need to have any 'history' of its parent DB...only its data. I eventually got around the issue by doing a full backup and 'renaming' the fulltext catalog on restore, then dropping it and creating a new one
I didn't really explain myself well in the initial post and am still leaving out some details here. I'm going to post some of my code ahen I get a chance, hopefully tonight.
Thanks again for all the help
January 18, 2007 at 12:38 pm
Why don't use try to back just data file up and restore it in the target server?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply