| Author |
Topic |
|
Tritim
Starting Member
17 Posts |
 Posted - 2008-05-09 : 12:19:06
|
| Hello all,I need to copy a database from our production server to my local development machine.This was easy in SQL 2000, but I'm stumped for SQL 2005. In order to use Management Studio's Database/Tasks/Copy Database feature, I must have SysAdmin rights on not only my local machine, but also on the production server machine. Because there are other important databases on that machine, they won't give me sa rights on the entire production machine.Someone suggested a two step process:1. I first script the database2. Then I use Import/Export Wizard to copy the data.For scripting, I see that in Management Studio, I can use either Database/Script As, or, I can use Database/Tasks/Generate Scripts. Which is more appropriate? They seem to be very different.Copying a database seems like a common need. How do other members here do it?Thanks.Any thoughts or ideas will be appreciated.Tritim |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-09 : 12:29:04
|
| Ask your DBA to backup and restore in your dev server and give necessary rightYou don't need Sysadmin rights to backup database. You just need db_backupoperator role to backup database. Its good to have DBA handle these stuff (fro prod to dev environment) |
 |
|
|
Tritim
Starting Member
17 Posts |
Posted - 2008-05-09 : 12:36:16
|
| Thanks sodeep.I'm hoping to find a way of doing it from my end so I don't have to make a request from the dba everytime I need a copy. However, I'll start thinking about handling it as you suggest. That may be the only practical way. Thanks.I'm still curious about how others script or copy a database.Tritim |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-10 : 12:25:30
|
| You still need proper permission to do that even with script. |
|
|
|
Tritim
Starting Member
17 Posts |
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-06-20 : 16:39:40
|
| here is a deal. Only you need to ask to PROD DBA to copy backup file in Dev server and you should be good to go. Or else PROD DBA can keep PROD .bak file in any other location(In That location you should be having access) and again you can copy to your local Dev box start your restoration process.ManojMCP, MCTS |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-20 : 22:37:58
|
| Mdubey,This has already been addressed before. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-06-21 : 06:57:06
|
| You can use the Copy Database element in SSIS - I use this all the time for various jobs.Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
Tritim
Starting Member
17 Posts |
Posted - 2008-06-21 : 13:37:07
|
| Jack,I hadn't thought about SSIS for this, but that might be a good way for us to go. I'm going to look into that.Thanks.Tritim |
|
|
|
Tritim
Starting Member
17 Posts |
Posted - 2008-06-21 : 14:04:53
|
| Now, unfortunately, it looks like SSIS isn't viable because we're using VS 2008, which doesn't have SSIS ability for SQL 2005. Instead they plan to give it SSIS ability for SQL 2008.Bad news.Tritim |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-21 : 16:12:20
|
| You can use sql2k5's bids to create ssis package. |
|
|
|
|
Copy a database in SQL Server 2005