Copy SQL Server permissions with ease‏

	SQL permissions extractor is Idera’s new, FREE tool that extracts and copies permissions from objects in SQL Server. Copying logins from one server to another or duplicating a login on the same server can be time consuming and a little painful.SQL permissions extractor makes it fast and easy -
	1. Select any SQL Server  2. Select a login  3. Generate a permissions script  4. Edit and apply changes to any selected target
	All from an easy-on-the-eyes graphical interface. Don a pair of dark sunglasses and refer to yourself as “The Extractinator.” The possibilities are endless. GET IT FREE!
	Once you’ve extractinated it, dig into it.SQL secure shows you who has access to what, and how permissions are granted. Identify vulnerabilities and harden security across your SQL Servers. Give it a free go to see how easy managing permissions can be.

FREE, must-have tools for SQL Server DBAs‏

	Learn how to optimize your SQL Server with a set of handy (and free) tools that are already a part of SQL Server. In this white paper, Michael K. Campbell, SQL consultant and contributing editor for SQL Server Magazine, shares some of the tools and tricks of the trade to help SQL Server DBAs detect, analyze, and correct performance problems, sometimes before they even occur. Read Performance Essentials: Must-have tools for SQL Server DBAs. These free tips can help streamline your workload. And when you’re ready to take things to the next level, try the performance-enhancing power of SQL diagnostic manager. Try it FREE for 14 days and enjoy: 24x7 Monitoring on hundreds of key metrics Identifying worst-performing or frequently run queries Monitoring and fixing issues from any web-enabled device

Restore Web applicaton using SQL server tools in SharePoint 2010

There are some limitations to restore the whole web applicartion using SQL restore but we can use DB associated with Web Application using SQL tools

To restore databases associated with a Web application by using SQL Server tools

1. The account must be a member of the sysadmin fixed server role.
2. All stored procedures must be finished before a restore if any procedure is running stop SharePoint Timer service and wait for several minutes for any currently running stored procedures to finish.
3. Do not restart the service until after you restore the databases.
4. Now we are all set open SQL management studio and expand Database server
5. Select the DB you want to restore > Right Click > Tasks > Restore > Database.
6. In the Restore Database dialog box, specify all required information
7. Open Options.
8. In the Restore options section, select only Overwrite the existing database
9. Recovery state section specify details as per your requirements
10. Click OK

How to restore content database using SQL server in SharePoint 2010 ?

1. If the Windows SharePoint Services Timer service is running, stop the service and wait for several minutes for any currently running stored procedures to finish.
2. Do not restart the service until after you restore the content databases.
3. Open SQL Server Management Studio
4. Connect to the database server.
5. Right-click the Database that you want to restore > Tasks > Restore > Database.
6. In the Restore Database dialog box, specify the destination and the source, and then select the backup set or sets that you want to restore.
7. In the Select a page pane, click Options.
8. In the Restore options section, select only Overwrite the existing database.
9. In the Recovery state section:
• If you have included all the transaction logs that you must restore, select RECOVER WITH RECOVERY.
• If you must restore additional transaction logs, select RECOVER WITH NORECOVERY.
• The third option, RECOVER WITH STANDBY is not used in this scenario.
10. Click OK to complete the recovery operation.
11. Repeat steps 4 through 10 for each database that you are restoring.
12. Start the Windows SharePoint Services Timer service.

Ref: http://technet.microsoft.com/en-us/library/ee748604#section3

SharePoint creates SQL Databases

When we install SharePoint we have the databases created in SQL, SQL is the backbone of the SharePoint server, whenever you upload any data or any document to the site its actually uploaded to the SQL database associated with that application, the documents gets stored in SQL and they have only the reference link

Let’s look at the databases created in SQL when you install SharePoint and configure stuff like search My Sites and SSP, also when you make your very first application
Installing SharePoint on your server

When you install SharePoint on your farm, irrespective of the type of installation you choose that’s Basic or Advanced (I mean using SSEE or Full SQL version) you get two databases created on your specified SQL instance they are:

Sharepoint_Config : This is the configuration Database for the site this database holds information on farm configuration, patches and updates applied to the server, versions of sites and databases, users, custom templates, Features, etc to be specific it contains 24 tables and each tables has its own significance

1> dbo.antivirusvendors – contains 3 columns
Vendor Id
Vendor Product
Vendor Version
As you would guess it will be talking about the Antivirus properties applied on the server obviously this table has to be present on the Config database as Antivirus is a portal level application and not an application level

2> dbo.Binaries - Contains 2 columns
Object ID
File Image
I am not really sure what this table is doing here its maybe used for some customization references

1> dbo.classes- contains 3 columns
Id –
BaseClassId-
FullName
It contains the classes used to create the SharePoint dll
So the next time you face an error of a particular class not working you can check the entry here

2> dbo.customtemplates – contains 6 columns
ID
Title
Description
LCID
Content
Template ID
This table would contain the information of the custom templates applied to the site, when you run the stsadm –o addtemplate command the attributes are added to the site as well as the dbo.customtemplates
just comparing these columns with the attributes of stsadm –o addtemplate, they are filename, title, description,
so we can troubleshoot issues with the custom templates not getting updated or the custom templates not being applied to the site from these

3> dbo.databases – contains 15 columns
DatabaseID
VirtualserverID
Name
UserID
Password
SiteCount
SiteCountWarning
SiteCountLimit
DatabaseServiceID
LastModified
LastModifiedUser
LastModifiedServer
Status
Version
Properties
As seen its used to get all the details of the databases used in the SharePoint site, It also contains the warnings and limitations of the site, status their version and properties

4> dbo.dependencies – contains 2 columns
ObjectID
DependentID
Generally a list of object ID and Dependent ID mapped together

5> dbo.EmailenabledLists- contains 5 columns
Alias
SiteID
WebID
ListID
Deleted
Whenever you create an email enabled document library you can see the attributes being listed here, we configure this library on a particular site collection and have to provide ‘Alias’ of the library while creating the library, the ‘SiteId’ of the site holinding the email enabled list, Web ID of that site, ListID of the List, and status as deleted or not

6> dbo.globals- Contains 14 columns
GlobalID
SchemaVersion
UseHostHeader
UseNTAuthfordatabase
smtpserviceID
Mailcodepage
FromAddress
Replytoaddress
LastModified
LastModifiedUser
LastModifiedServer
Status
Version
Properties
These all are the global settings for the site which can be done via several links on the central administration page like Web application general settings, AAM etc

7> dbo.InstalledWebpartPackages- contains 5 columns
WPPID
url
IISInstance
ServerID
Globalinstall
It talks about the third party web parts added to the site or the custom webpart solution deployed to the server, can be a good medium to troubleshoot the issues with Out of the Box web part solutions

8> dbo.LastUpdate – contains 2 columns
Value
Version
This is an interesting thingy to look at cause it contains the information of the last update applied to the server, so In case you feel that something happened to your server because of the automatic update applied or you want to check on the last update you can check this table

9> dbo.Objects- contains 7 columns
ID
ClassID
ParentIDName
Status
Version
Properties
Contains information of the objects

10> dbo.pendingDistributionList- contains 5 columns
SiteID
WebID
GroupName
ModifiedBY
Version
Contains list of pending DL’s and some information

11> dbo.Servers- contains 9 columns
ServerID
Name
Address
LastModified
LastModifiedUser
LastModifiedServer
Status
Contains information of the servers in the farm and there details

12> dbo.services- contains 10 columns
ServiceId
ClassId
ServerID
Instance
LastModified
LastModifiedUser
LastModifiedServer
Status
Version
Properties
Contains information of the services

13> dbo.SiteCounts- contains 2 columns
DatabaseID
NumSites
It contains the count of sites in each Database

14> dbo.SiteMap- contains 9 columns
ID
ApplicationID
DatabaseID
Path
Pairing
Status
Version
Redirecturl
HostheaderIssiteName
This also gives information about the sites

15> dbo.sites- contains 9 columns
SiteId
DatabaseID
URLpath
LastModified
LastModifiedUser
LastModifiedServer
Status
Version
Properties
This one is supposed to contain the number of sites and its attributes with respect to each application

16> dbo.Timerlocks- contains 9 columns
JobID
LockedByServerName
LockedTime
This contains all the jobs Locked by the timer job

17> dbo.TImerRunningJobs - contains 10 columns
ServiceID
VirtualServerID
JobID
JobTitle
ServerName
Status
StartTime
CurrentStatus
TargetCount
CurrentTargetPageDone
Shows time and status of all the timer jobs

18> dboTimerTargetInstances- contains 2 columns
JobID
TargetInstanceID
Will contain Timer Target Instances and there list with the Job ID

19> dbo.tombstones- - contains 2 columns
ID
Version

20> dbo.Versions - contains 10 columns
VersionID
Version
ID
Username
Timestamp
Finaltimestamp
Mode
ModeStack
Updates
Notes
Contains previous versions of the site and the version History, also account that changed it the newer version (by applying updates)

21> dbo.Virtualservers- contains 12 columns
Virtualserver ID
Name
smtpserviceID
Mailcodepage
Fromaddress
Replytoaddress
LastModified
LastModifiedUser
LastModifiedServer
Status
Version
Properties
Shows the entries and attributes of virtual servers added to the farm

22> dbo.webpartpackages- contains 3 columns
WPPID
Title
Content
Similar to dbo.InstalledWebpartPackages contains information of the OOB or custom web part packages


Admin_Content : This database is created when you complete running the PS Config wizard for SharePoint, this wizard launches the Central administration application, actually in background the wizard creates an application for the Central admin page

The central admin database is names as admin_content database, this is by default and you cannot rename or edit it.

It is just like any other normal content database, but it has the Central Admin site, which is the most important site.

How to back up a User Profile Service application using SQL Server in SharePoint 2010 ?

• Open SQL Server Management Studio
• Connect to the database server.
• Expand Databases.
• Right-click the database that you want to back up, point to Tasks
• Then click Back Up.
• In the Back up Database dialog box,
• In the Source area, select the backup type that you want to perform
• User Profile Service application always requires a full backup, so select Full backup
• In the Backup component area, click Database.
• Provide the name to the backup
• We can set an expiration date to make this back up available till desired date.
• By default its 0, means no expiration date
• Provide the path where you want to store this back up under Destination
• That’s it we are done with user profile backup

A very good article available @ http://technet.microsoft.com/en-us/library/gg576965

Step by step SharePoint migration

Steps to migrate one moss server to other server
1. Take back up of all databases from SQL management studio (in .bak format)
2. You can also copy the .mdf and .ldf files of the content databases
3. Must remember the versions of the databases.
4. Copy the backed up files to some safe location
5. Create a new MOSS server
5.1 Install .NET 3.0 or later version
5.2 Install SQL Server 2005 or later version with SQL management studio
5.3 Install MOSS
6. Do not run PSconfig after installation of MOSS
7. Before that we have to copy the backed up databases files to the new server
8. Restored the databases form the .bak files to the newly installed SQL server
9. You can attach .mdf files to the new databases.
10. After copying the backed up data to the newly created MOSS machine run PSConfig wizard
11. In PSConfig wizard if you want the same customization as it was in the previous one then connect the MOSS to the existing farm and in the existing farm select the old configuration database.
12. Wait till the PSConfig wizard will be completed.
13. Open the Central admin page and check whether you get the same customization or not.

Now how to up your previous sites back in to the new MOSS machine.
1. Create a new site in new MOSS machine
2. Go to the content databases option in Application management of the central admin page.
3. Dropped the existing database , now your site comes down
4. Open the command prompt and type the following command to attach the content DB of the previous machine

Stsadm –o addcontentdb –url -databasename

5. The database which you want to attach must be restored in the SQL server