September 12, 2013 at 9:03 pm
I have installed SQL Server 2008 - R2 version in my local machine.
I want to import database from another remote SQL Server 2008 database inside a LAN.
How to do this ? Can you please tell me the steps ?
Also , do I have to create user and give permission in my database after import ? or users and permission also be copied from remote database to my database ?
September 13, 2013 at 12:26 am
1. Script all applicable logins from the source (use sp_help_revlogin: http://support.microsoft.com/kb/918992) and use this script to create the logins on the target instance.
2. Make a backup on the source and restore this on the new instance. Because you have created all logins in step 1 all users in the database will be automatically linked to the logins.
3. Check for orphaned users (exec sp_change_users_login 'report') and fix any issues
September 13, 2013 at 12:47 am
If you want a copy of the remote database, use a backup as HanShi mentioned. Use a copy only backup though to not break the backup chain.
If you want to move the remote database to your new instance, you can detach it, move it to the new server and attach it to the new SQL Server instance.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 13, 2013 at 3:29 am
HanShi (9/13/2013)
3. Check for orphaned users (exec sp_change_users_login 'report') and fix any issues
Thanks. Very useful. I'm right in the middle of cleaning up old user accounts after migration to a new server.
September 13, 2013 at 5:01 am
I find it hard to understand....this looks a bit difficult process that other databases like mysql and oracle.
I am stuck because of the following reasons
Could you please tell
1. Script all applicable logins from the source (use sp_help_revlogin: http://support.microsoft.com/kb/918992) and use this script to create the logins on the target instance.
Do you want me to run this EXEC sp_help_revlogin in source machine ?
2. Make a backup on the source and restore this on the new instance.
How to make a backup ? this part is not clear.
3. Check for orphaned users (exec sp_change_users_login 'report') and fix any issues
Is it necessary step ? or I may skip it ?
September 13, 2013 at 5:23 am
spectra (9/13/2013)
I find it hard to understand....this looks a bit difficult process that other databases like mysql and oracle.I am stuck because of the following reasons
Could you please tell
1. Script all applicable logins from the source (use sp_help_revlogin: http://support.microsoft.com/kb/918992) and use this script to create the logins on the target instance.
Do you want me to run this EXEC sp_help_revlogin in source machine ?
Yes, you need to create a script for all logins on the source instance. Next step is to execute the generated script on the target instance so the logins are created on the target. (see "Method 3" in the Microsoft URL).
Another option is to create a script of all permissions on the source instance. Idera ha a free tool for this http://www.idera.com/productssolutions/freetools/sqlpermissionsextractor. You can execute the generated script on the target to create all logins.
2. Make a backup on the source and restore this on the new instance.
How to make a backup ? this part is not clear.
Right click the database, select "tasks", select option "backup...". Select all required options (look at both tabs), fill in the variables, etc. and click on [ok] (see http://technet.microsoft.com/en-us/library/ms187510.aspx)
3. Check for orphaned users (exec sp_change_users_login 'report') and fix any issues
Is it necessary step ? or I may skip it ?
You can skip it, but it will give you an overview of users inside the database that are not connected to a login and therefor cannot be used. You have to see if these users need access to the database or not.
September 13, 2013 at 5:34 am
Yes, you need to create a script for all logins on the source instance. Next step is to execute the generated script on the target instance so the logins are created on the target. (see "Method 3" in the Microsoft URL).
Another option is to create a script of all permissions on the source instance. Idera ha a free tool for this http://www.idera.com/productssolutions/freetools/sqlpermissionsextractor. You can execute the generated script on the target to create all logins.
Thanks ...bit comfortable now. I'll prefer GUI based approach....
I just want to extract user/permission from source machine and then want to run the same in my target machine through GUI way
Can I use idera for this ? if not please tell me a tool which could do this work in a GUI way.
Right click the database, select "tasks", select option "backup...". Select all required options (look at both tabs), fill in the variables, etc. and click on [ok] (see http://technet.microsoft.com/en-us/library/ms187510.aspx)
I just checked this .....looks easy to me. Please tell if there is any important selection which i may need to remember here.
also, how do I restore this backed file later on ? I see a restore menu is Microsoft Management Studio GUI.....Can I use that to restore ?
September 13, 2013 at 5:59 am
spectra (9/13/2013)
2. Make a backup on the source and restore this on the new instance.
How to make a backup ? this part is not clear.
The article I linked to about copy only backup has script examples...
spectra (9/13/2013)
3. Check for orphaned users (exec sp_change_users_login 'report') and fix any issuesIs it necessary step ? or I may skip it ?
Sure you can skip it. If you like to have useless users in your database.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 13, 2013 at 7:41 am
spectra (9/13/2013)
I just want to extract user/permission from source machine and then want to run the same in my target machine through GUI way
Can I use idera for this ? if not please tell me a tool which could do this work in a GUI way.
The SSMS has rather good build-in scripting options. Just select an object, right click and select "script object as...". Also if you have defined actions (like backup or restore) in the GUI you can generate a script just before hitting the [ok] button.
To move logins and permissions in a GUI way is afaik not possible. I heard about the Idera tool by other posters. I don't have hands-on experience with this tool. I suggest you download it and try it on a test environment.
I just checked this .....looks easy to me. Please tell if there is any important selection which i may need to remember here.
also, how do I restore this backed file later on ? I see a restore menu is Microsoft Management Studio GUI.....Can I use that to restore ?
There are no 'real' important selections for backup, the link I gave you describes all options.
Restoring with the GUI is perhaps a bit less intuitive. Near the bottom of the Microsoft link you can go to the URL describing the restore process.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply