Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mob_obrienm
Starting Member
6 Posts |
 Posted - 2007-10-22 : 16:31:47
|
| Hi,Hoping someone can point me in the right direction. I have several Production SQL server 2005 databases that need to be copied to a different (test) SQL server 2005 database server. What is the best way to copy the existing structures over to the test environment.I'm new to sql server, so any assistance would be greatly appreciated!Thanks |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-23 : 02:31:54
|
| Can you just take a Backup off Production and Restore on TEST? Or are there some other requirements (such as removing, or obfuscating, the data?)Do you need to automate this, or is it an "on-demand" manual operation?Kristen |
 |
|
|
mob_obrienm
Starting Member
6 Posts |
Posted - 2007-10-28 : 23:51:39
|
| Hello,I apologize, I thought I had replied but it must not have went through. Thank you both for responding. I will check the link above. It is a fresh install of sql server 2005 on the TEST server and currently only has the system databases. This should only be on an on-demand basis, no need for automated replication (until the requirements change :)). I have good full backups of the PROD databases....just wasn't sure if i could restore onto TEST without having and of the data structures in the TEST environment. I tried to restore with a backup from PROD to TEST but received this error:TITLE: Microsoft SQL Server Management Studio------------------------------Restore failed for Server 'DEDVDM01'. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476------------------------------ADDITIONAL INFORMATION:System.Data.SqlClient.SqlError: Directory lookup for the file "F:\MSSQL2005\MSSQL.1\MSSQL\DATA\DATAMART.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476------------------------------BUTTONS:OK------------------------------Still digging into it a little more. I'm sure I'm probably making this more difficult. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-29 : 05:00:56
|
| "wasn't sure if i could restore onto TEST without having and of the data structures in the TEST environmen"A backup contains all database structure, permissions, stored procedures and data.It does not contain the jobs that appertain to the database, and the database users are associated with server logins - which will have different IDs between two server (these "orphan" User IDs can be re-synchronised with the Server Logins easily enough, and obvious restore on SAME server has no problem)By default RESTORE will attempt to restore to the original folders the backup was made from. Quite problably the TEST server has different drives / folders, and you may want to restore to a different locationFor details on the restore syntax, and how to interrogate a backup file to see what folder information it contains, etc, see:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example,Fix+Orphaned+Users,Script+User+Logins+after+a+Restore,Scripting+users,RestoreKristen |
|
|
|
mob_obrienm
Starting Member
6 Posts |
Posted - 2007-10-30 : 15:35:45
|
| Kristen,Thanks so much, the scripts did the trick. I am unfamiliar with sql server db maintenance (obviously), but know sql fairly well--mostly through Oracle. i was trying to do the backup/restore through the gui in sql server 2005 (ie right-click backup on source, right-click restore on target), but didn't seem to be working out too well for me. Again, many thanks...this seems to have taken care of it. |
|
|
|
|
|
|
|
|
How to copy db from one database server to another