| Author |
Topic |
|
kiwikencox
Starting Member
11 Posts |
 Posted - 2005-09-14 : 19:12:32
|
| Hi all,I need to be able to take a copy of a PROD database on one server and restore it to a DEV server automatically.AT my last job the DBA's created a SQL Job that we could run using the DEV management console to restore a database to a DEV server from .bak files on the PROD server. This is the kind of thing i'm after.Can anyone give me an example of the code/script i should use for the SQL Job to get the .bak files from the PROD server & restore to DEV server.Cheers,Ken |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-14 : 19:30:11
|
| Here's a start:EXEC master.dbo.xp_cmdshell 'xcopy \\ProdServer\e$\MSSQL\BACKUP\SomeFile.BAK E:\MSSQL\BACKUP\'RESTORE DevDbFROM DISK = 'E:\MSSQL\BACKUP\SomeFile.BAK'WITH REPLACE, MOVE 'DBName_Data' TO 'E:\MSSQL\DATA\DBName_Data.MDF',MOVE 'DBName_Log' TO 'E:\MSSQL\DATA\DBName_Log.LDF'Tara |
 |
|
|
kiwikencox
Starting Member
11 Posts |
Posted - 2005-09-14 : 20:07:37
|
| Thanks Tara, i'll give that a go.K:-) |
|
|
|
khautinh
Starting Member
10 Posts |
Posted - 2005-09-19 : 16:52:58
|
| Please help.My network guy screwed up my SQL 2000 server. My questions:Can I restore backup DB(s) included Master, MODEL and MSDB to new installed SQL 2000 server?I have all the backup files for all db(s). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-19 : 16:54:22
|
| Yes. Please see the topic in SQL Server Books Online that describes how to restore the master database. The rest of them can be done the normal way.Tara |
|
|
|
khautinh
Starting Member
10 Posts |
Posted - 2005-09-19 : 18:00:09
|
| I tried to restore the master DB, but an error message which returned some thing like the old master backup file is different version with the new master file. Please urgen help! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-19 : 18:19:58
|
| Well you need to be on the same version in order to do the restore. So if your original server has sp2 and your new one has sp3, then you need your new one to have sp2.Tara |
|
|
|
khautinh
Starting Member
10 Posts |
Posted - 2005-09-19 : 18:53:09
|
| here is the error message"The backup of the system database on the device cannot be restored becuase it was created by a different version of the server that this serverRestore database is terminating abnormally "I have SQL 2000 server without sp3 yet. I will try. Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-19 : 22:34:05
|
| I can see this biting me at some time in the future! So it begs the question:Would this example work?I have a backup of master from an SP2 server.I restore it as a user database on an SP3 machine, then back it up again.Then I restore the new backup over the master database on an SP3 installationKristen |
|
|
|
khautinh
Starting Member
10 Posts |
Posted - 2005-09-20 : 12:12:30
|
| Nothing is working now for me. Windows 2000 server with the latest sp and SQL 2000 server with the latest sp but the problem is still there.BUT I can restore the system DB(s) by the different name. My question isCan I see my DTS jobs? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-20 : 12:36:47
|
| You can only see your DTS packages and jobs if you restore the msdb database.I don't think that'll work Kristen. Can you even restore master as a user database? I've never done it, but I'm guessing it wouldn't work.Tara |
|
|
|
khautinh
Starting Member
10 Posts |
Posted - 2005-09-20 : 13:30:25
|
| Believe it or not I have a solution. What I did was 1. Restore the system DB(s) to my local server by different name.2. Shut down my local SQL 2000 server, included the services.3. Rename the current MSDB and MODEL of my local server to something.4. Rename the restored DB(s) from step 1 above to MSDB and MODEL5. Restart the SQL 2000 server and its services then the magic happens. Now I can see all my DTS jobs and schedulesThanks for all your helps!Happy with SQL again! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-20 : 13:33:53
|
That knocks spots of my idea of restoring it to yet-another-server. </ThumpsForehead!>Restore it to the actual server, as a user database, so it is gets updated to the current version, and then "toggle filenames" for want of a more impressive technical term!"Can you even restore master as a user database?"Well ... its just a database, isn't it?  Kristen |
|
|
|
|
restore database to diff server from .bak files Q?