Solved

Restore SQL Database

Posted on 2014-07-24
10
157 Views
Last Modified: 2014-08-06
Can we backup a SQL2008R2 standard database and restore it into SQL 2008R2 Enterprise ? Is there anything that I need to be aware of ?

Tks
0
Comment
Question by:AXISHK
  • 5
  • 4
10 Comments
 
LVL 8

Expert Comment

by:Wilder_Admin
ID: 40216144
This should work with a sql dump because here you really dump data there are no informations out of which database systems they come from.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40216173
Restoring to a higher edition of the same version will work fine.

You may need to run a "fix logins" script if your database has an domain users, but you would have to do that anyway if moving to another server, so isn't unique to your scenario.
0
 

Author Comment

by:AXISHK
ID: 40238382
"fix logins" ... I am not aware of this issue. Can you give me more information about this, Tks
0
 

Author Comment

by:AXISHK
ID: 40238421
EXEC sp_change_users_login 'Report', the following are listed.

UserName            UserSID
bakerpreport            0x9DF2F5CD331FF14AB4B345B33A731C8B
erpreport3            0x0F42C6E03696B649BE9D8E20307EDCED
SalesReportingAX2009      0xBC60008F737DE748975E795E4F9D5B13
puradmin            0xE26A5AC1075F7B42A20D641C2FACCA87
reportuser            0x9E9DCE427AD01543A5E81A90F996F4C3

Afterwards, I issue "EXEC sp_change_users_login 'Auto_Fix', 'bakerpreport' ". The following message is logged, Tks

"Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'."
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40238457
If you're using "Auto_Fix" then you need to supply a password. Auto_Fix will create a matching login if one doesn't already exist - which, depending on your security policy, you may or may not want it doing.

You might be better using "Update_One" option instead to ensure you are only mapping existing logins.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:AXISHK
ID: 40238468
so, should I issue,

use database_name
go
EXEC sp_change_users_login 'Update_One', 'bakerpreport', 'bakerpreport'
go
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40238470
I would.
0
 

Author Comment

by:AXISHK
ID: 40238561
It returns with the message below. However, EXEC sp_change_users_login 'Report' show the user "bakerpreport". However, I can't see the name under MS SQL Server Management Studio -> Security -> Logins. What does it mean  ? Tks

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Login name 'bakerpreport' is absent or invalid.
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 40238567
It means there isn't a login name that corresponds to the user name.

You need to decide if you need those users or not. If you do then you'll need to create the login and map the user to it - or you could go back to using "Auto_Fix" but you will need to provide a password to use to the login.

Personally I would script the logins manually, and then re-run sp_change_users_login with the "Update_One" option.

Alternatively, if you don't need the database users, you can just drop them.
0
 

Author Closing Comment

by:AXISHK
ID: 40245342
Tks
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql query to Stored Procedure 6 39
SQL Transaction logs 8 28
Pivot not using aggregate yield error 3 42
SQL Query 3 49
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now