| Author |
Topic |
|
malachi151
Posting Yak Master
152 Posts |
 Posted - 2007-10-10 : 21:12:07
|
| When I use the Copy Database Wizard to create a SQL job it doesn't work. I get an error in the log file that says something like "Source database is not set to an instance". (I forget exactly, its been a while and it happens at work)I've tried everything I can think of. The source and destination are on the same server, and the SQL account is a local admin on the machine.Any ideas?Thanks |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-10 : 22:31:13
|
| Why create job with copy db wizard at first place? |
 |
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2007-10-11 : 08:17:21
|
| Well its there, it should work. It's certainly easier than scripting it, but I suspect that whatever the problem is with creating the job via the wizard will also be a problem when scripting the job. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-11 : 23:28:50
|
| If you like to copy job between servers, better to use transfer job task in ssis. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 01:31:33
|
| I really wish MS hadn't made the copy database wizard available. It's so much easier to just use backup/restore and it works!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2007-10-12 : 09:26:17
|
| I'm copying a database on the same server, not between servers. The purpose of this is for a test environment. The production database is to be copied to a test database nightly, then the scripts from the development database are run against the test database. The copy needs to overwrite the existing database every time. It's easy to do all of this as a scheduled job, and it seemed like the Copy Database Wizard was supposed to be the easy way to create the copy database portion of this job.However, of, course, I can't get the copy database job, forget the wizard at this point, to work. Even if I just create a job myself I get the same error. I'll post the exact error when I get to work. |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2007-10-12 : 11:50:20
|
| I tried doing this again at work and it wouldn't even creat the package, so I don't know what the issue is with that now.I'm now using the backup and restore method, but really, it seems like in theory the copy database method should be better. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 11:52:53
|
quote:
Originally posted by malachi151
I'm now using the backup and restore method, but really, it seems like in theory the copy database method should be better.
It's pointless to use the copy database wizard when backup/restore is so easy and does exactly what you want. You can get the wizard to work, but it'll take a bit of work.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2007-10-12 : 12:42:30
|
| I suppose, although I don't really like the fact that I now have to create a backup file just to copy the database. The copy method works without creating an additional extra copy of the database.I'm not even certain if this step will create problems with our backup schedule if I want to run this process on a schedules nightly basis.If the Copy Database Wizards worked I think it would be easier to use than doing a backup and restore.It seems to me that the Copy Database Wizard, or the job that it creates, simply has bugs. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 13:14:03
|
| Creating an additional full backup will not interfere with any of your other processes. You could just use an existing full backup though.As you have seen already, the copy database wizard is simply not easy to get to work, so why bother? Of all of the people that I know who post here, everyone uses backup/restore method.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-12 : 13:18:41
|
CDW uses about 18 different technologies to do (or try to do, anyway) its job.backup/restore is tried and true.
elsasoft.org |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 13:21:42
|
"I'm not even certain if this step will create problems with our backup schedule if I want to run this process on a schedules nightly basis."The effects of taking an "unexpected" Full backup are important if you have Differential Backups. Subsequent Differential Backups can only be restored if the preceding Full Backup is available.So if you take a full backup, copy to the TEST server, and then delete the backup file that would upset a Differential recovery  but if you don't take differential backups there are no other problems I can think of - Disk Space aside.Can you not just use an existing Full backup file? If you are going to do this each night then schedule it after the backup is made, and use that file perhaps?I don't know how big the Copy Wizard transfer is [in terms of bandwidth] but I imagine it does a "Insert row by row" type approach, which will be much larger, in terms of bandwidth, than the file size of a database Backup file. Backup / Restore has some advantages for getting a database to TEST server:It will restore IDENTICALLY to the Source database. So if the Statistics are shot, and indexes badly fragmented [on Live] you will get that reproduced exactly on Test.Whereas the Copy Wizard will be different. In fact probably worse in the reverse case:Assuming Production is all fully optimised, statistics updated, indexes rebuild/defragged. The "Insert these one by one" approach which [I believe] Copy Database does will create indexes which are shot to hell!! and thus the Testing may have terrible performance, where the Production site is actually fine!Kristen |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-12 : 15:22:27
|
CDW has two methods to copy a database. one uses detach/copy data and log files/attach. The other uses the SMO Transfer class, which transfers data using SSIS. Regardless of which method you choose, it always creates an agent job on the destination server to kick off the work. So if agent is not running on the destination, CDW won't work at all.
elsasoft.org |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2007-10-12 : 15:38:05
|
| Yes, we do do differential nightly backups, so thanks for bringing that point up.Bandwidth isn't an issue because both databases are on the same server.I've tried both the Detach/Attach method and the SSIS method, they both fail with the same error.Previously the package was being created, but now it is no longer being created, so there is some other problem I would have to address before getting back to the root issue. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 15:40:16
|
| I would avoid the detach/attach method, since that method takes down the source database which is production in your case. This is why backup/restore should be the only method for copying of a production database.If you decide to create a new backup for this process, then I'd put it into the same directory as your other backups so that you don't interfere with the recovery process when using differential backups.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 17:55:40
|
| "Yes, we do do differential nightly backups, so thanks for bringing that point up."We have an SProc for a Full Backup. It puts the Full Backup in the appropriate folder, and makes sure that it is added to a list so it is deleted once it has become "stale".EVERY process (on pain-of-death!) that needs to make a backup has to use this one process.Thus we don't get caught with our trousers down when we have to make a restore.if a Developer wants a "quickie" backup "just in case" then that use the one-same-Sproc.It has saved our Bacon numerous times ...Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-13 : 00:04:21
|
| What CDW does is detach db, copy db files over then attach them. By the way, I still didn't get what 'use the Copy Database Wizard to create a SQL job' means. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-13 : 00:15:40
|
I think what he means is that CDW actually creates an agent job on the destination to do the attach. It's ridiculous, I know, but that's what it does. I think the idea behind this is so you can schedule your copying of databases. Inside the agent job, CDW creates a single step that executes a DTS package that actually does the detach/attach. It's unholy.EDIT: bad grammar. 
elsasoft.org |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-13 : 10:14:38
|
| "What CDW does is detach db, copy db files over then attach them"Sounds pretty handy. Its copying any "empty space" in the database files, which may cost a bit of bandwidth, but other than that it should be as good as a Backup, Copy, Restore [i.e. preserve index fragmentation, or "lack of", so that testing on the copy is equivalent to same test on the original.And I imagine it would help trying to get a database up to an ISP that doesn;t allow any files to be FTP'd up, which in turn makes the restore route difficult!Kristen |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-13 : 17:22:21
|
very unlikely that CDW could be used with an ISP. the ISP would have to allow you to create agent jobs on their server.
elsasoft.org |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2007-10-13 : 18:28:41
|
| To get a database to an ISP I've always created the database with a script and then used replication to populate the data. Not sure if there is a better way, but that's what I do. |
|
|
|
Next Page
|
Copy Database Wizard