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.
Subscribe to:
Posts (Atom)
Posts
