Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If and when my SQL server goes down and takes too long to get back up, can I restore my DB on another SQL server and run my software from there (I can point which SQL server to run from)?

Will all metadata and indexes and foreign keys be restored too?

Also my current SQL server is on our domain, the other one I have is on its own workgroup... Would it still be possible? Or would I need to join it to the domain first?

share|improve this question
add comment

3 Answers

Restoring a SQL Server database only requires admin rights to the destination SQL Server instance. It doesn't matter at all if the new or old machines were members of the domain.

share|improve this answer
add comment

If you are using Windows (AD) security currently, in your non-AD environment since those accounts aren't available, you could access the restored data in both environments with SQL Server accounts which you can set up in advance. SA is there, and you can create others.

Since it sounds like you are planning for disaster, I'd create a SQL Server account that is in the sysadmin role in advance. That way you can be sure it will be there in your non-AD environment after a restore. Here's a general ref.

I'm also assuming you are accessing the SQL data for consumption by an application, not just simply bringing the data online for ad hoc querying, so planning ahead for the non-AD environment by having a SQL account handy that works on both sides would be advisable.

share|improve this answer
    
Any account which is a member of the sysadmin fixed server role would be able to access the database. This includes local windows accounts on the destination server as well as domain accounts if the destination server is a member of the domain. –  mrdenny Apr 26 '12 at 6:09
    
Downvotes, really? Didn't the OP say clearly the destination was NOT in a domain? "the other one I have is on its own workgroup" Hence my advice. He could also use a local Windows account, this is true, however a local Windows account on a DIFFERENT host would be a different account, yes? So based on the OPs scenario, the simplest/safest step would be to use a SQL server account. With respect, it seems you missed the OPs question entirely. –  Eric Higgins Apr 26 '12 at 19:24
    
It all depends on how the destination SQL Server is setup. You stated "your only way to access the restored data would be with SQL Server accounts" but that isn't the case at all. I can setup a SQL Server not on the domain that doesn't support SQL Auth and still get at the data within the SQL Server database that I'm restoring no problem. –  mrdenny Apr 26 '12 at 21:13
    
My implication was that the (any) AD account previously relied upon wouldn't be available. I can clarify the answer. –  Eric Higgins Apr 26 '12 at 21:42
add comment

You can restore the database on another server, as long as the Disaster Recovery (DR) server has the same version of SQL Server. (Technically, you could restore it to a server with a more recent version of SQL Server, but the database files would be upgraded at that point and you wouldn't be able to go backwards when the old server gets fixed. (You should be able to go back to an earlier service pack, but not an earlier version. )

if the disk storage system looks different, you may need to specify new paths for the underlying MDF, NDF and LDF files. So, you should know how to change the location of those files (vis SSMS or TSQL, what ever you prefer).

All data, indexes, constraints, procedures, etc. will be restored. Things that will not be restored are SSIS packages, relevant SQL Agent jobs, linked servers.

The database will resemble what it looked like at the end of the last backup, assuming that you are just taking full backups. (If you are running with FULL recovery mode and are taking transaction log backups, things get more fussy, but I think that is a different question.)

A common problem is logins. In SQL Server, logins are tied to a server, users are tied to a database and the SID is the value that relates the two together. For AD logins, SQL uses the SID from the AD login, for SQL logins, SQL Server makes up a valid-looking SID and keeps track of that.

If you are using domain logins for your applications, it would be best to join the DR server to the domain. If not, may have to do join it in a hurry because your applications might be using those domain logins. You'd have to create SQL credentials during your DR restoration efforts and fix any connections strings that use a trusted login. This is likely to be error-prone and time-consuming.

If you are using SQL logins for your applications, be sure that you create those logins using the exact same passwords (of course) and the exact same SIDs. (sp_addlogin has a parameter that allows you specify a particular SID.) If the SIDs do not match, users may be able to connect to the server but may not be able to properly access the data. There is a ton of googlable stuff on matching up the SIDs between logins and databases after the fact; the easiest thing to do is make sure that they match before the fact. So, did up Microsoft's login reverse-engineering script, run it, and keep the output (which is a script that can create logins) somewhere safe. (If your primary server is down, you won't be able to run it then.) You can run that script on the DR server when you have a failure, you can run it immediately. Note that if logins can change their passwords, you will have to do something to sync them on the DR server.

It is a good idea to try this at least once before you have to do it for real. If you aren't used to doing this sort of thing, and often if you are, something will always pop up and you'll have to scramble at the last minute.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.