Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to import a sql server database ? Expand / Collapse
Author
Message
Posted Thursday, September 12, 2013 9:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 27, 2014 5:36 AM
Points: 103, Visits: 83
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 ?



Post #1494428
Posted Friday, September 13, 2013 12:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 2,304, Visits: 2,767
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


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1494454
Posted Friday, September 13, 2013 12:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 13,741, Visits: 10,713
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.

Copy-Only Backups

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.

Database Detach and Attach




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1494465
Posted Friday, September 13, 2013 3:29 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, January 31, 2014 2:56 AM
Points: 483, Visits: 256
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.
Post #1494515
Posted Friday, September 13, 2013 5:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 27, 2014 5:36 AM
Points: 103, Visits: 83
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 ?
Post #1494535
Posted Friday, September 13, 2013 5:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 2,304, Visits: 2,767
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.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1494543
Posted Friday, September 13, 2013 5:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 27, 2014 5:36 AM
Points: 103, Visits: 83
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 ?
Post #1494544
Posted Friday, September 13, 2013 5:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 13,741, Visits: 10,713
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 issues

Is it necessary step ? or I may skip it ?


Sure you can skip it. If you like to have useless users in your database.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1494551
Posted Friday, September 13, 2013 7:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 2,304, Visits: 2,767
spectra (9/13/2013)
[quote][b]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.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1494583
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse