| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2005-01-10 : 16:39:58
|
| I'd be surprised if this hasn't been asked here, but I can't figure out the procedure for restoring a SQL server from backup files, and my searches here either timeout or return nothing useful. I create nightly backups (.BAK files) of every database (6 user db's, master, model, msdb; tempdb is never used, so I don't back it up), and I want to restore the files to a test server to make sure I know what I'm doing if the production server goes down. Can anybody point me to the procedure I must follow?thanks in advance, Alex |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 17:04:01
|
| Take a look at RESTORE DATABASE in SQL Server Books Online. You might need to use the WITH MOVE option if the path information isn't the same.Tara |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-01-10 : 17:07:37
|
| I've been looking at that, but I don't know what order to do them in. For example, do I do master first, or not at all? If yes, msdb and model next? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 17:09:59
|
| I never restore the system databases except to the system where it was backed up. You should test the user databases. And it doesn't matter the order for those.Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-01-10 : 17:12:34
|
| But if I only restore the user databases, won't I be missing logins (master.syslogins), among other things? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 17:18:55
|
| Yep. Those can be transferred via other means. On the production server, you would just restore the system databases. But for a test server, you can transfer logins via sp_help_revlogin which you can find by searching the forums or grab it from the MSDN site. For DTS packages, you can do a save as to move them. There are ways to do the other things as well.Now if the two servers are identical meaning drives are exactly the same, then you can do the system database restores. But that's only if they are the same.Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-01-10 : 17:53:11
|
| Isn't sp_help_revlogin only useful if the production server is available? Maybe I shouldn't have called it a "test" server, but rather a "restore" server to replace the production server in case all the RAID drives happen to get wiped out or blown up or something. Can I not restore the system databases in any way if the drives are not the same? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 17:55:20
|
| If the drives are not the same, then you can not restore the master database. Yes sp_help_revlogin requires you to get the users beforehand from the source. On servers where we use SQL authentication, we run it each night.Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-01-10 : 17:58:28
|
| Do you mean partition size or path or both? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 17:59:54
|
| Path information.Tara |
|
|
|
|
disaster recovery