March 10, 2010 at 11:50 am
Hi all!
I was wondering if anyone could provide input or sources for the best way to go about this task. I am very new to SQL Server 2005.
I would like to copy a production DB from one server to another (probably from a physical to a virtual one) so that the user(s) can use this for testing before they try it out on the production DB. The copied over DB has to be identical to the production one.
I was thinking a backup/restore, but is there a better way? Is there a way to give the user the ability to copy the production DB over to the test DB/server?
Thanks in advance.
March 10, 2010 at 11:52 am
If you want everything to be identical, backup/restore should work just fine.
March 10, 2010 at 1:05 pm
There are other methods, but backup restore will likely be the fastest.
Once you restore the database, make sure you verify the logins work.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 10, 2010 at 2:53 pm
Is it necessary for me to copy over the .mdf and .ldf files from the source server to the destination server?
I cannot point to the original .mdf and .ldf files via "\\SourceServer\d$" giving me an error in regards to "'___.mdf' is on a network path that is not supported for database files."
March 10, 2010 at 2:57 pm
If you are doing a backup and restore, you would not need to copy the mdf and ldf file from the source server. You would use the backup file which will create the mdf and ldf on your destination server.
Here's a resource on the topic:
http://msdn.microsoft.com/en-us/library/ms177429.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 10, 2010 at 3:01 pm
chrisph (3/10/2010)
Is it necessary for me to copy over the .mdf and .ldf files from the source server to the destination server?I cannot point to the original .mdf and .ldf files via "\\SourceServer\d$" giving me an error in regards to "'___.mdf' is on a network path that is not supported for database files."
No, take backup of production database and copy back up file to test server and then restore, check BOL for Backup and Restore operation. You cannot copy mdf and ldf files if database is active, you have to bring database offline to to this (don't do this type of restore). You cannot point to prod environment from test environment because there should a firewall between them.
March 10, 2010 at 3:26 pm
The problem I was having was that the source DB files were located on a different drive letter (not the default location), which the destination server didn't have.
In the Restore Database Wizard under Options > "Restore the database files as:" I changed the file paths accordingly.
Thanks guys!
March 10, 2010 at 3:54 pm
Thanks, that makes sense.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 10, 2010 at 4:02 pm
chrisph (3/10/2010)
The problem I was having was that the source DB files were located on a different drive letter (not the default location), which the destination server didn't have.In the Restore Database Wizard under Options > "Restore the database files as:" I changed the file paths accordingly.
Thanks guys!
The is the GUI using the WITH MOVE statement. When restoring a DB to a different server and you want the files in a different location to the server from which the backup was taken, or the original location does not exist on the server being restored you need to specify WITH MOVE.
Gethyn Elliswww.gethynellis.com
March 11, 2010 at 8:01 am
Is there a way for the user to do this himself, without necessarily giving him sysadmin privileges to the server?
Would the Copy Database Wizard be better in this case?
March 11, 2010 at 10:49 am
chrisph (3/11/2010)
Is there a way for the user to do this himself, without necessarily giving him sysadmin privileges to the server?Would the Copy Database Wizard be better in this case?
The user would have to have adequate permissions in the source database as well as
GRANT CREATE ANY DATABASE TO Bob
in the target server.
To ensure the user is able to copy all procs, functions, tables, views - you might need to grant view all in the source database - which may be over-permission the user.
This, IMO, would be better if performed by the database department and thus can be controlled.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 4:30 pm
I agree on the DBA(s) handling this CirquedeSQLeil. The reason I ask is there might be a need that this be done weekly, I will have to learn more about SQL to be able to automate something like that. I'm guessing replication would work, but I haven't touched that, or a Copy Database schedule. Thoughts?
March 11, 2010 at 4:39 pm
I think an easier solution would be to backup the database and restore to the dev server via script and automated task. This can be done on a weekly or even nightly basis.
Via additional scripts, you would be able to ensure that the appropriate permissions are re-assigned each time.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 7:27 pm
chrisph (3/11/2010)
I agree on the DBA(s) handling this CirquedeSQLeil. The reason I ask is there might be a need that this be done weekly, I will have to learn more about SQL to be able to automate something like that. I'm guessing replication would work, but I haven't touched that, or a Copy Database schedule. Thoughts?
Refreshing Dev environment is one of the routine tasks of DBA's it is easy, Replication is resource intensive and recommended for object level only not database level, moreover you will have firewall between two environments. Even you don't have to copy back up files to Dev environment, you can restore dev environment from production file share.
March 12, 2010 at 3:00 am
The following works for me:
* Create a new database 'MyDB_SourceBackup'
* Copy all definitions (tables, sp's etc.) from MyDB to MyDB_SourceBackup (using SQLCompare)
* Restore a full backup from production to MyDB
* Copy all definitions from MyDB_SourceBackup to MyDB (using SQLCompare)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply