In this post, I will how to import data from Excel to MS SQL Server. The steps mentioned in this article are for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.
In order to restore database a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role else you will receive the below mentioned error message while trying to restore a database in SQL Server.
CREATE DATABASE permission denied in database ‘master’.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 262)
Sample TSQL Script to Create Login with DBCREATOR Server Role and DB_OWNER Database Role
DROP USER [BackupRestoreAdmin]
DROP LOGIN [BackupRestoreAdmin]
CREATE LOGIN BackupRestoreAdmin WITH PASSWORD=’[email protected]$$w0rd’
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
EXEC sp_addsrvrolemember ‘BackupRestoreAdmin’, ‘dbcreator’
EXEC sp_addrolemember ‘db_owner’,’BackupRestoreAdmin’
A Database Administrator or a user who is a member of DBCREATOR Server Role and DB_OWNER Database Role will be able to restore a SQL Server database from databases full back up using either :-
• Restore Database Using SQL Server Management Studio
• Restore Database Using TSQL Scripts
Take a look at both the above mentioned options to restore a SQL Server Database from a Full Database Backup in detail.
Restore a Full Database Backup Using SQL Server Management Studio
1. Open SQL Server Management Studio and connect to the appropriate instance of Microsoft SQL Server Database Engine in Object Explorer.
2. Right click Databases node and then select Restore Database… option from the drop down list as shown in the below snippet to open up Restore Database dialog box.
3. In General Page of Restore Database dialog box, select or type the name of a new or existing database for your restore operation. In Source for restore specify the source and location of backup sets to restore. Choose From Device radio button and then click the “…” button to specify backup file location.
4. In Specify Backup dialog box choose File as Backup Media and then click the Add button to choose the location of database backup file from which you want to restore the database. Click OK to return to Restore Database dialog box.
5. In Restore Database Dialog box select the checkbox under Restore as shown in the below snippet and then select Option Page from the left pane.
6. In Options Page of Restore Database dialog box select the checkbox next to Overwrite the existing database (WITH REPLACE) and choose the radio button next to Leave the database ready to use by rolling back uncommitted transactions. Additional transactional logs cannot be restored. Finally, click OK to start restoring the SQL Server Database.
7. To Generate TSQL Script for the database restore click Scripts and choose Script Action to your choice from the different options which are available.
Best SQL 2014 Hosting Recommendation
ASPHostPortal.com is Microsoft No #1 Recommended Windows and ASP.NET Spotlight Hosting Partner in United States. Microsoft presents this award to ASPHostPortal.com for ability to support the latest Microsoft and ASP.NET technology, such as: WebMatrix, WebDeploy, Visual Studio 2012, ASP.NET 4.5, ASP.NET MVC 4.0, Silverlight 5 and Visual Studio Lightswitch. You’ll use a flexible, powerful hosting control panel that will give you direct control over your web hosting account.