Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to import a sql server database ?


How to import a sql server database ?

Author
Message
spectra
spectra
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 170
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 ?
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3044 Visits: 3625
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’! **
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16378 Visits: 13199
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
SQL Surfer '66
SQL Surfer '66
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 328
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.
spectra
spectra
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 170
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 ?
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3044 Visits: 3625
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’! **
spectra
spectra
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 170
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 ?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16378 Visits: 13199
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3044 Visits: 3625
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’! **
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search