June 3, 2015 at 5:13 pm
I tried to use the option copy database but it is failing and I use the option text file but I didn't see the text file is generated after the operation failed.
I am using same server (instance) for both source and destination
June 3, 2015 at 8:40 pm
When I need to copy a database, I always use the backup/restore approach. Either take a full backup or use the most recent full backup you have, whichever is appropriate. Then restore from that file to the destination database, using the move parameters to specify your new filenames. You can restore to the same SQL version or 2 versions newer, but not to a previous version. I find it to be a very efficient and reliable approach.
June 4, 2015 at 7:35 am
Ed Wagner (6/3/2015)
... using the move parameters to specify your new filenames.
I personally don't have any experience with the Copy feature but I wanted to endorse Ed's approach and put emphasis on the MOVE parameter. I remember the sinking feeling I got when I first tried to Backup/Restore to a newly created database (in an effort to "copy" it) and I didn't use the MOVE parameter. We're a single server environment so I have production DB's and test DB's (the "copies") on the same server (I know, I should take the time to create a new instance....one of these days ;-)) ... well without the MOVE parameter I found that SQL Server was just going to overwrite my production MDF and LDF files despite the fact I thought it was restoring to the test DB. Fortunately, since the production files were in use, it wasn't allowed but that's when I realized just how close I came to disaster (perhaps if I had tried this on a Saturday it would have let me ... :sick:).
Anyway, use the MOVE parameter if you go the backup/restore route.
June 4, 2015 at 11:33 am
An example of using the restore command with moving the data files is:
RESTORE DATABASE [NewName] FROM DISK = N'R:\path_to_backup\backup_filename.bak'
WITH FILE = 1,
MOVE N'OldName_data' TO N'D:\Data\NewName.mdf',
MOVE N'OldName_log' TO N'L:\Log\NewName.ldf',
NOUNLOAD, STATS = 10;
I like to keep the logical filenames named well, so I'll also do something like this to update them after the new database is restored:
ALTER DATABASE [NewName] MODIFY FILE (NAME = N'OldName_data', NEWNAME = N'NewName_data');
ALTER DATABASE [NewName] MODIFY FILE (NAME = N'OldName_log', NEWNAME = N'NewName_log');
June 8, 2015 at 12:50 pm
Thanks for posting. I've never had the need to change the logical names (thus the reason I've never looked it up) except for on the rare occasion that I'm restoring a Test DB backup to another Test DB environment (to test the test) and it's always a moment of confusion when I see the original Production DB name there. I think I'll add this to the bottom of my script.
Curious, I've never used the STATS command. Is it useful?
June 8, 2015 at 2:14 pm
When you say the copy option is failing are you talking about copying the mdf/ldf files or using a BACKUP DATABASE command with the COPY_ONLY option? I use the latter all the time when moving data from production to test/dev environments as I do not have to worry about it messing with the backup chain.
Stats is extremely useful if you backup using scripts/command line. It allows for tracking progress of the backup/restore. If you have ever seen the little green circle in SSMS showing the progress of your backup/restore it is doing the same thing.
Joie Andrew
"Since 1982"
June 9, 2015 at 9:12 am
thisisfutile (6/8/2015)
Thanks for posting. I've never had the need to change the logical names (thus the reason I've never looked it up) except for on the rare occasion that I'm restoring a Test DB backup to another Test DB environment (to test the test) and it's always a moment of confusion when I see the original Production DB name there. I think I'll add this to the bottom of my script.Curious, I've never used the STATS command. Is it useful?
Definitely when restoring a DB, typically in smaller or change-uncontrolled environments, management will get nervous about restoration times, the 'Stats = 1' clause will give you a dummy to shove in their mouths...
🙂
June 10, 2015 at 7:52 am
The text file is relative to the server, not your machine. So C:\Output.txt will be on the server's C:, not yours.
I have found that the copy database feature generally works OK for simple databases.
By simple, I mean nested functions and user-defined table types are problematic; and a couple of other restrictions.
The dependency checker in Management Studio is awesome. The one used in the copy database tool, not so much.
And, if you have declared referential integrity -- you do, don't you? -- then you can't really run copy database while the db is in use, as you'll almost certainly encounter a foreign key violation.
I've made a roll-your-own version of copy database in Powershell and SMO, which allows fine control over the order of operations. Still can't be run during operating hours, but otherwise works fine.
June 10, 2015 at 10:53 am
Joie Andrew (6/8/2015)
Stats is extremely useful if you backup using scripts/command line. It allows for tracking progress of the backup/restore. If you have ever seen the little green circle in SSMS showing the progress of your backup/restore it is doing the same thing.
JaybeeSQL (6/9/2015)
Definitely when restoring a DB, typically in smaller or change-uncontrolled environments, management will get nervous about restoration times, the 'Stats = 1' clause will give you a dummy to shove in their mouths...🙂
Thank you, both, for your response. I'll use it next time I Restore.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply