By using this option you can move a Database to new directory, location or drive. You can use either T-SQL or SQL Server Management Studio (SSMS) to backup and restore the database. When using this option you will be creating a new database at destination server and you can perform this action during online.
To backup the database use below script
BACKUP DATABASE TestDB TO DISK = 'C:\Backup\TestDB.bak'
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Copy the Database to the Destination server and if the destination server has same driver and folder structure then you can restore the database using below script.
RESTORE DATABASE TestDB from DISK = 'C:\Backup\TestDB.bak'
WITH RECOVERY,STATS=1
GO
If you want to move the database to new folder or new drive then use “WITH MOVE” with restore command.
RESTORE DATABASE TestDB
FROM DISK='C:\Backup\TestDB.bak'
WITH MOVE 'TestDB' TO 'E:\SQLData\TestDB_Data.mdf',
MOVE 'TestDB_Log' TO 'E:\SQLLog\TestDB_Log.ldf'
You must know the logical filenames of the database in order to use WITH MOVE clause, if you don’t know then use below command to find
RESTORE FILELISTONLY
FROM DISK = 'C:\Backup\TestDB.bak'
GO
Backup and Restore the database using SQL Server Management Studio(SSMS)
You can perform the same using SQL Server Management Studio, follow the below steps
Backup the database using SSMS
1. Select Database that you want to backup, Right click and choose Tasks and Backup option
2. Provide the Backup Set Name (For ex:- TestDB_FullBackup.bak)
3. Select the Destination for Backup to as “Disk” or Tape
4. Select the file or backup device for the backup destination.
5. Click OK
Restore the Database using SSMS
1. Right client on Databases, Select Restore Database
2. Choose “From Device” and Specify the Backup media and its location of your restore
3. Select the backup sets to restore
4. Select or type the name of a new or existing database for your restore option
5. Go To “Options” tab , Modify the path under “Restore As” if you want to MOVE data and log files in different folder or drive
6. Select “Recovery State” as RESTORE WITH NO RECOVERY, If you want to apply additional logs ( Otherwise the default option will be RESTORE WITH RECOVERY)
7. Click on OK
Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com
Wednesday, December 11, 2013
How to move SQLServer Database
For a DBA, it is one of the common activities to move the database to new folder or location or drive.
We have couple of options available to move the database in SQL Server.
1. Backup and Restore the database by using WITH MOVE option
2. Detach and Attach database using SQLServer Management Studio
3. Detach and Attach database using Transact-SQL
If you choose any option, I always recommend running DBCC CHECKDB after you move the database to check the database is structurally consistent.
Backup and Restore the database by using WITH MOVE option
Using Backup and Restore the database option you will be creating a new database at destination server and you can perform this action during online. If the database that you are moving is a production database then I advise to stop the application and take the backup.
When you use WITH MOVE option, the destination folder or driver must be available
Do not keep Backups on the same device where database is sitting. In case of device failure you should be able to restore the database using backup. Also keeping on separate drive enhances the I/O performance for both backups and database use.
Detach and Attach database using Server Management Studio or T-SQL
When you use Detach and attach database you are moving the database instead of copying and you must perform this task during offline only.
When you detach a database, it removes the data and transaction files from the instance. You can attach these files to any other SQL Server instance
You CANNOT detach a database if any of the following is true
1. The database is replicated and published – Before you detach you must disable publishing by running sp_replicationdboption or remove replication by running sp_remove dbreplication.
2. Database snapshot exists– Before you detach you must drop all database snapshots
3. Database is being mirrored – Before you detach remove the Database Mirroring
4. A suspect Database – Before you detach you must put it into emergency mode
5. System Database – You cannot detach a System Database from Instance
We have couple of options available to move the database in SQL Server.
1. Backup and Restore the database by using WITH MOVE option
2. Detach and Attach database using SQLServer Management Studio
3. Detach and Attach database using Transact-SQL
If you choose any option, I always recommend running DBCC CHECKDB after you move the database to check the database is structurally consistent.
Backup and Restore the database by using WITH MOVE option
Using Backup and Restore the database option you will be creating a new database at destination server and you can perform this action during online. If the database that you are moving is a production database then I advise to stop the application and take the backup.
When you use WITH MOVE option, the destination folder or driver must be available
Do not keep Backups on the same device where database is sitting. In case of device failure you should be able to restore the database using backup. Also keeping on separate drive enhances the I/O performance for both backups and database use.
Detach and Attach database using Server Management Studio or T-SQL
When you use Detach and attach database you are moving the database instead of copying and you must perform this task during offline only.
When you detach a database, it removes the data and transaction files from the instance. You can attach these files to any other SQL Server instance
You CANNOT detach a database if any of the following is true
1. The database is replicated and published – Before you detach you must disable publishing by running sp_replicationdboption or remove replication by running sp_remove dbreplication.
2. Database snapshot exists– Before you detach you must drop all database snapshots
3. Database is being mirrored – Before you detach remove the Database Mirroring
4. A suspect Database – Before you detach you must put it into emergency mode
5. System Database – You cannot detach a System Database from Instance
When you attach a database, all f MDF and NDF files must be
available.
Make sure that no data files or file groups are in read-only
mode. If primary data file being attached is ready only then database engine
assumes that database is read-only.
Regards,
Satishbabu Gunukula
Thursday, November 21, 2013
Stop successful backup logging info in SQL Server Error Log
The SQL Server error log contains systems events and user defined events have information, user will use this log to troubleshoot problems related to SQLServer.
SQL Server error log will have information about
2013-11-18 18:15:08.08 Backup Database backed up. Database: EXP, creation date(time): 2008/07/09(16:46:27), pages dumped: 3732, first LSN: 10550:280:300, last LSN: 10550:408:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\\SQLServer_Backup\Full_Backup\EXP\EXP_backup_2013_11_18_181504_1347670.bak'}). This is an informational message only. No user action is required.
2013-11-18 18:45:06.40 Backup Log was backed up. Database: EXP, creation date(time): 2010/12/14(14:30:59), first LSN: 73542:11608:1, last LSN: 73542:11650:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\SQLServer_Backup\Log_Backup\EXP\EXP_backup_2013_11_18_184503_3906746.trn'}). This is an informational message only. No user action is required.
You can recycle the SQLServer error log to decrease the ERRORLOG size using below link, but it will not solve your problem completely.
Create New Error Log or Recycle Error log in SQLServer
http://www.sqlserver-expert.com/2012/06/create-new-error-log-or-recycle-error.html
It is really difficult to read the ERRORLOG due to this backup information, and we can stop logging successful backup information by using one of the below solution
1. DBCC TRACEON
Run below command to turn off/stop the successful backup entries.
DBCC TRACEON (3226,-1)
If you restart the SQL Server the changes will be gone. If you want to make these changes permanent then you need to specify the flag at start-up parameter.
2. SQL Server startup parameters
You can add trace flag “-T3226” to SQLServer startup parameters to suppress successful backup information
You need to restart SQL Server in order to affect the changes.
Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com
SQL Server error log will have information about
- Backup and restore operations
- batch command info
- processes info
- automatic recovery message
- SQLServer stop and start info
- kernel messages
- Server –level error messages ...etc
2013-11-18 18:15:08.08 Backup Database backed up. Database: EXP, creation date(time): 2008/07/09(16:46:27), pages dumped: 3732, first LSN: 10550:280:300, last LSN: 10550:408:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\\SQLServer_Backup\Full_Backup\EXP\EXP_backup_2013_11_18_181504_1347670.bak'}). This is an informational message only. No user action is required.
2013-11-18 18:45:06.40 Backup Log was backed up. Database: EXP, creation date(time): 2010/12/14(14:30:59), first LSN: 73542:11608:1, last LSN: 73542:11650:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\SQLServer_Backup\Log_Backup\EXP\EXP_backup_2013_11_18_184503_3906746.trn'}). This is an informational message only. No user action is required.
You can recycle the SQLServer error log to decrease the ERRORLOG size using below link, but it will not solve your problem completely.
Create New Error Log or Recycle Error log in SQLServer
http://www.sqlserver-expert.com/2012/06/create-new-error-log-or-recycle-error.html
It is really difficult to read the ERRORLOG due to this backup information, and we can stop logging successful backup information by using one of the below solution
1. DBCC TRACEON
Run below command to turn off/stop the successful backup entries.
DBCC TRACEON (3226,-1)
If you restart the SQL Server the changes will be gone. If you want to make these changes permanent then you need to specify the flag at start-up parameter.
2. SQL Server startup parameters
You can add trace flag “-T3226” to SQLServer startup parameters to suppress successful backup information
- SQL Server Configuration Manager, Select SQL Server Services
- Right click on SQL Server Instance service go to properties and select "startup parameters" tab
- Enter “-T3226” value in “Specify a startup parameter” and click Add
- Click on Apply.
You need to restart SQL Server in order to affect the changes.
Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com
Install SQL Server Reporting Services (SSRS)
SQL Server reporting services (SSRS) is a report generating software system, it has full range of ready to use tools and services to create, deploy and manage reports for your organization.
The reporting service is not cluster-aware, must be installed locally. But scale-out deployment provides a way to share a database. If one report server goes down the other reporting servers will continue to work for your reports.
User can install Reporting Services in Native or SharePoint mode.
1. Native – User can use as stand-alone application with default configuration
2. SharePoint - User can integrate SSRS with SharePoint
In SQL Server 2012, reporting services introduces Power view – an interactive data exploration, visualization and presentation experience and provides drag and drop and ad hoc reporting for your business needs
SQL Server 2012 reporting services Installation Steps:
1. Launch SQLServer Installation Center à Go to Installation tab
2. Click on New SQL Server Stand-alone installation or add features to an existing installation
3. Select Installation Type : Perform a new installation of SQLServer 2012
4. Product Key: Enter the product Key
5. License Terms : Click on check boxes to accept the license terms
6. Setup Role: Select one of the below feature based upon your requirement
· SQL Server Feature Installation
· SQL Server PowerPivot for SharePoint
· All Features with Defaults
7. Feature Selection : Reporting Services – Native
8. Installation Rules: You should not see any warning/failures
9. Instance Configuration: Choose the name that you want
10. Disk space requirements: Review available and free space
11. Server configuration: Select Service account
12. Reporting Services Configuration: Install only
13. Error Reporting : Check the box if you want send the error reports to Microsoft
14. Installation and Configuration Rules: You should not see any warning/failures
15. Ready to Install: Click on “Install Button”
16. Installation Progress: You will see that installation progress info
17. Complete
Before you configure SSRS You need to have a SQLServer instance on a computer where you will host Report server or you can use remote SQL Server instance.
Downloads
You can download Microsoft SQL Server 2012 Report Builder using below link
http://www.microsoft.com/en-us/download/details.aspx?id=29072
You can download Microsoft SQL Server 2008 Reporting Service Report Builder 2.0 using below link
http://www.microsoft.com/en-us/download/details.aspx?id=24085
You can download Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies
http://www.microsoft.com/en-us/download/details.aspx?id=1848
SQL Server 2000 Reporting Services Service Pack 2 (SP2)
http://www.microsoft.com/en-us/download/details.aspx?id=20497
Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com
The reporting service is not cluster-aware, must be installed locally. But scale-out deployment provides a way to share a database. If one report server goes down the other reporting servers will continue to work for your reports.
User can install Reporting Services in Native or SharePoint mode.
1. Native – User can use as stand-alone application with default configuration
2. SharePoint - User can integrate SSRS with SharePoint
In SQL Server 2012, reporting services introduces Power view – an interactive data exploration, visualization and presentation experience and provides drag and drop and ad hoc reporting for your business needs
SQL Server 2012 reporting services Installation Steps:
1. Launch SQLServer Installation Center à Go to Installation tab
2. Click on New SQL Server Stand-alone installation or add features to an existing installation
3. Select Installation Type : Perform a new installation of SQLServer 2012
4. Product Key: Enter the product Key
5. License Terms : Click on check boxes to accept the license terms
6. Setup Role: Select one of the below feature based upon your requirement
· SQL Server Feature Installation
· SQL Server PowerPivot for SharePoint
· All Features with Defaults
7. Feature Selection : Reporting Services – Native
8. Installation Rules: You should not see any warning/failures
9. Instance Configuration: Choose the name that you want
10. Disk space requirements: Review available and free space
11. Server configuration: Select Service account
12. Reporting Services Configuration: Install only
13. Error Reporting : Check the box if you want send the error reports to Microsoft
14. Installation and Configuration Rules: You should not see any warning/failures
15. Ready to Install: Click on “Install Button”
16. Installation Progress: You will see that installation progress info
17. Complete
Before you configure SSRS You need to have a SQLServer instance on a computer where you will host Report server or you can use remote SQL Server instance.
Downloads
You can download Microsoft SQL Server 2012 Report Builder using below link
http://www.microsoft.com/en-us/download/details.aspx?id=29072
You can download Microsoft SQL Server 2008 Reporting Service Report Builder 2.0 using below link
http://www.microsoft.com/en-us/download/details.aspx?id=24085
You can download Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies
http://www.microsoft.com/en-us/download/details.aspx?id=1848
SQL Server 2000 Reporting Services Service Pack 2 (SP2)
http://www.microsoft.com/en-us/download/details.aspx?id=20497
Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com
Wednesday, November 6, 2013
Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube
Hold a chance to Win FREE copy of “Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube”, just by LIKING and commenting!
For the contest we have 4 e-Book copies of Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube, to be given away to 4 lucky winners.
To Win your copy of this book, all you need to do is LIKE the Facebook page https://www.facebook.com/SQLServerExpert and come up with a Comment highlighting the reason "why you would like to win this book”.
The contest is valid for 10 days, and is open to everyone. Winners will be selected on the basis of their comment posted and all rights reserved.
About the book:
Microsoft SQL Server Analytical Services, or SSAS, is an analytical processing OLAP tool which allows users to analyze business data quickly and easily.
In this book you will get started with SQL Server cube development. You will start by learning how to install SQL Server 2012 and create a development environment. You will then finish by diving into the development and design of dimension and cubes in SQL Server.
This book starts by jumping into the new and important features of Microsoft SQL Server 2012. Afterwards we will run through the installation of SQL Server then how to check the validation of SQL Server and security consideration during and after installation.
After you have SQL Server running, you will get started with SQL Server cube development, first by creating a development environment, then by developing and designing dimensions and cubes in SQL Server.
Moving on to administration and user management of SQL Server 2012 you will then build your own SQL Server development environment and create your own dimensions and cubes. You will then finish with a journey through further tutorials and guidance on continuing and progressing with SSAS cube development.
Friday, October 11, 2013
Change or remove the Central Administration Web site port number in SharePoint
During the SharePoint Foundation Installation, the Central Administration Web application is established on a PORT that you have assigned or randomly assigned port if you don’t select.
You can change this Web site port number or delete the port number complete from Central Administration Web application Server.
Follow the steps if you want to change the port number 80, means when you launch you Web application you no need to specify the port number for ex:- http://oracleracexpert.com
1. Open “SharePoint 2013 Management Shell” as Administrator
2. Run below command
PS C:\Users\svc-sharepoint> stsadm -o setadminport -port 80
You should receive the message “Operation completed successfully”
3. Reset IIS using below command
C :\> iisreset
4. Now you should be able to launch your Central Administration Web application without port number i.e http://Application_Server_Name
Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com
Remove SharePoint Central Administration Web Application
If you deleting a
Application server or Central Administration Web application from Server Farm
in SharePoint 2013 follow the below simple steps.
Removing the Central Admin
Web application is very similar as
Configure SharePoint Central Administration Web Application
1. Start “SharePoint Products Configuration Wizard” on the
server where you want to Delete the Central Administration
Select the option
·
Do not disconnect from this server farm – Choose
this option, If you want to delete only Central Administration Web application
from server farm
·
Disconnect from this server farm- Choose this
option, if you want to disconnect this server from farm
2. Select “Yes, I
want to remove the web site from this machine” , Click Next
3. In this screen you
should be able to see the Database Server Configuration Settings, Click Next
4. It will remove the Central Administration information
from registry and also removes the web application .You should see “Configuration Successful” message once the
configuration Wizard removed the Central Administration Web application, click Finish.
Regards,Satishbabu Gunukula
http://www.sqlserver-expert.com
Wednesday, August 21, 2013
Error While enabling NetFx3 during SQLServer 2012 Installation
Users may receive below error during SQLServer 2012 installation.
Error while enabling Windows Feature :NetFx3, Error Code : -2146498298 , Please try enabling Wndows feature : NetFx2 from Windows management tools and then run setup again. For more information on how to enable windows freature, see http://go.microsoft.com/fwlink/?linkid=227143
Users will see this error if they don’t install/enable ".Net Framework 3.5" before installing SQLServer 2012.
Follow below steps to enable .Net Framework 3.5 on Windows Server 8 or Windows Server 2012
1. Server Manager --> Manage --> Click “Add Roles and Features" --> Next
2. On “Installation Type”, Select “Role-based or feature-based installation” -->Next
3. On “Server Selection”, Select the server on which you want to install from the list --> Next
4. On “Server Roles”, Select new roles if you need
5. On “Features” Page, select “.Net Framework 3.5 Features” --> Next--> Install
You can use above steps to add any new roles/Features in Windows Server 8 or Windows Server 2012 environment.
You can also add Roles and Features using command line
C:\>dism /online /enable-feature /featurename:NetFx3 /source:E:\sources\sxs
Where E drive is the windows media mount.
Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com
Wednesday, May 1, 2013
Error: 26 - Error Locating Server/ Instance Specified in SQLServer
Recently I have experienced the following error message when connecting to SQLServer
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 26 – Error Locating Server/ Instance Specified) (Microsoft SQL Server)
Cause 1: If SQLServer is not allowing remote connections.
Possible solution1:
1. Microsoft Management Studio, Right Click Database Server and go to properties
2. On Security Page, under Server Authentication select “SQL Server and Windows Authentication mode” and click OK
3. Restart SQLServer Service
In case of SQLServer 2005, follow below steps to enable remote connections
1. Click on Startup, go to Program files and Select Microsoft SQL Server 2005--> Configuration tools--> SQL Server Surface Area Configuration
2. Click on “Surface Area Configuration for Services and Connections --> SQL Server Browser--> Select “Automatic” for Startup type and Apply
Cause 2: TCP Port 1433 or UDP Port 1434 ports are not in Firewall exceptions
Possible solution2: Add exception to allow port TCP Port 1433 and UDP Port 1434 or Turn off Firewall rules
Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 26 – Error Locating Server/ Instance Specified) (Microsoft SQL Server)
Cause 1: If SQLServer is not allowing remote connections.
Possible solution1:
1. Microsoft Management Studio, Right Click Database Server and go to properties
2. On Security Page, under Server Authentication select “SQL Server and Windows Authentication mode” and click OK
3. Restart SQLServer Service
In case of SQLServer 2005, follow below steps to enable remote connections
1. Click on Startup, go to Program files and Select Microsoft SQL Server 2005--> Configuration tools--> SQL Server Surface Area Configuration
2. Click on “Surface Area Configuration for Services and Connections --> SQL Server Browser--> Select “Automatic” for Startup type and Apply
Cause 2: TCP Port 1433 or UDP Port 1434 ports are not in Firewall exceptions
Possible solution2: Add exception to allow port TCP Port 1433 and UDP Port 1434 or Turn off Firewall rules
Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com
Subscribe to:
Posts (Atom)