SQL Server Database Backup and Restore -- for windows Batch scripts

--Backup batch file script

SQLCMD -S SQLInstance1 -E -i "c:\test\Backupquery.sql"

--Backupquery.sql script
--Get Date format to YYYYMMDDHHMM
Declare @BkpFiledate varchar(255)
set @BkpFiledate=CONVERT(VARCHAR(20), GETDATE(), 112) +CONVERT(VARCHAR(20), DATEPART(hour, GETDATE()))+CONVERT(VARCHAR(20), DATEPART(minute, GETDATE()), 112)

--print 'BkpFiledate'+@BkpFiledate
DECLARE @BkpFilePath NVARCHAR(255), @BkpFileName NVARCHAR(255), @BkpFullPath NVARCHAR(255)

SET @BkpFilePath = 'C:\Test\'

print '['+convert(varchar(25),Getdate())+'] '+'Start BACKUP DATABASE TestDB ';
SET @BkpFileName = 'Backup\TestDB_backup_'+@BkpFiledate+'.BAK'
SET @BkpFullPath = @bkpFilePath + @bkpFileName
BACKUP DATABASE TESTDB TO DISK = @BkpFullPath WITH INIT, STATS=10
print '['+convert(varchar(25),Getdate())+'] '+'End BACKUP DATABASE TestDB ';
-------------------------------------------------------------------------------------

--Restore batch file script
set bkpLocation=C:\Test\Backup
set Data1Location=C:\Test\Data1
set Log1Location=C:\Test\LOG1

set DBName=TestDB2
REM Finds the FOR loop for recent backup file on %BackupLocation%\%DBName%\ location
FOR /F %%I IN ('DIR "%BkpLocation%\%DBName%\*.BAK" /B /O:D') DO set RecentBackupFile=%%I
SQLCMD -S SQLInstance2 -E -d master -Q "RESTORE DATABASE %DBName% FROM DISK=N'%BkpLocation%\%DBName%\%RecentBackupFile%' WITH FILE = 1,
MOVE N'%DBName%_Data' TO N'%Data1Location%\%DBName%.mdf',
MOVE N'%DBName%_Log' TO N'%Log1Location%\%DBName%_1.ldf',
REPLACE,STATS=10"

Backcp & Restore minimum Permissions

1.Backup Permissions
  --User/Batch id should have db_backupoperator role or SysAdmin role
2.Restore Permissions
 -- User/Batch id should have dbcreator role  or SysAdmin
 -- Or If the user/Batch id Administror privileges on windows server, then can restore databases without   
     'SysAdmin' or 'dbcreator' roles.

SSIS Package Permissions 2005 and 2008

http://benchmarkitconsulting.com/colin-stasiuk/2009/05/01/running-ssis-packages-security/

Microsoft SQL Server Constraints

Good article:
http://odetocode.com/articles/79.aspx

FOR loop for Batch script

--For loop for btach scripts
For /f "tokens=*" %%a in ('dir /b D:\temp\Scripts\*.SQL') DO (

  echo %%a
 )

How to Downgrade SQL Server Database from higher version to lower version

http://www.mytechmantra.com/LearnSQLServer/Downgrade_SQL_Server_Database_P1.html

http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/32a67469-0636-4ded-923f-2e257f7e68e1/


Options:
1.Create a blank data base in Sql 2005 and import data from sql 2008 to 2005 using import wizard.
2.Generate all script from sql 2008, and seperate primary key and foriegn key scripts from the generated script file.
3.Run that script in your Sql 2005 database.(To set PK and FK in 2005)

MAXRECURSION Option error

How can we avoid the error
“Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion. ”

if cte goes above 100 levels, then what need to do?

Ans: Set the MAXRECURSION hint of CTE query to any nonnegative value between 0 and 32767 as below:
SELECT EmployeeID, ManagerID, Title
FROM cteEmployee
OPTION (MAXRECURSION 200);

When 0 is specified, no limit is applied. If this option is not specified, the default limit is 100.

Single user multi user mode issue

http://www.sqlservercentral.com/Forums/Topic672411-149-1.aspx

use master
go
declare @kill_spid varchar(20)
-- Find spid of user connection to database test
select @kill_spid= max(spid) from master.dbo.sysprocesses
where dbid in ( select dbid from sysdatabases where name = 'test' )

select [Connection to Kill] = @kill_spid

-- Kill connection to db test
exec ('kill '+@kill_spid )
go
-- set DB test ofline
alter database [test] set offline with rollback immediate
go
-- Bring DB test online in multi user mode
alter database [test] set online, multi_user with rollback immediate
go
use [test]
select Current_DB = db_name()
go

LogOn Trigger

USE master
GO

drop trigger trgRestrictUser2

CREATE TRIGGER trgRestrictUser3
ON ALL SERVER WITH EXECUTE AS 'Test'
FOR LOGON
AS
BEGIN
IF (ORIGINAL_LOGIN()= 'Test' AND
-- APP_NAME() = 'Microsoft SQL Server Management Studio - Query')
(select getdate())>='2013-05-12 16:10:22.097')
ROLLBACK;
END;

select getdate()
SELECT CURRENT_TIMESTAMP
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]


http://sqlandme.com/2011/07/13/sql-server-login-auditing-using-logon-triggers/

To find the list of table names on database

select name,type from sysobjects where type='U'
and name not like '%OracleGG%' and name not like '%sys%'
and name not in ('MSpeer_conflictdetectionconfigrequest',
'MSpeer_conflictdetectionconfigresponse',
'MSpeer_lsns',
'MSpeer_originatorid_history',
'MSpeer_request',
'MSpeer_response',
'MSpeer_topologyrequest',
'MSpeer_topologyresponse',
'MSpub_identity_range',
'dtproperties',
'captured_columns',
'change_tables',
'change_tables',
'ddl_history',
'index_columns',
'lsn_time_mapping')
order by name


SELECT * FROM information_schema.Tables where TABLE_TYPE='BASE TABLE' and
table_schema not in ('cdc') and Table_name not like '%sys%'
and table_name not in ('MSpeer_conflictdetectionconfigrequest',
'MSpeer_conflictdetectionconfigresponse',
'MSpeer_lsns',
'MSpeer_originatorid_history',
'MSpeer_request',
'MSpeer_response',
'MSpeer_topologyrequest',
'MSpeer_topologyresponse',
'MSpub_identity_range',
'dtproperties'
)
order by table_name

exec sp_msforeachtable 'print ''?'''

dbcc loginfo

Find out the Virtual machine or physical machine of the server

1.Using Services
2.1 Go to start ---->run type  Services.msc open ----> then look for "VMWare services"  running or not.
     If it VMWare server then it will show up "VMware Tools Service" ----> it should  "started"
    --VMware Tools Service

3.Using Windows command prompt: Is the Physical server or VMware Server:
Run the systeminfo command @ command prompt see the system menufacturer n system model details. There you can find the virtual and physical machine information.

4.Using SQL Server: Run below
declare @Virtualname varchar(255)
declare @Virtual varchar(255)
create table #OSinfo(OSinfo varchar (255))
insert into #OSinfo
exec master..xp_cmdshell 'systeminfo'
set @Virtualname =(select ltrim(SUBSTRING(OSinfo,CHARINDEX(' ',OSinfo),LEN(OSinfo)))
from #OSinfo
where OSinfo like '%system%' and OSinfo like '%manufacture%')

if (@Virtualname not like '%virtual%')
begin
set @Virtual='Not Virtual'
set @Virtualname='Not Virtual'
end
else
begin
set @Virtual='Virtual'
end
print @Virtualname
print @Virtual

Run the below and look for  System Model:
select * from #OSinfo    -- example: System Model: VMware Virtual Platform
Drop table #OSinfo

5. If it SQL Server 2008 R2:
SELECT @@SERVERNAME AS SRVName,virtual_machine_type FROM sys.dm_os_sys_info
--The virtual_machine_type attribute is a new addition to this DMV as of SQL 2008 R2.
  There are three possible values: 0,1, or 2.  The value of 0 means that the machine is physical.
  Any other value means that it is a virtual machine.  You can read more about that from MSDN.

CLR Enable SQL Server 2005

EXEC sp_CONFIGURE 'show advanced options' , '1';
GO
RECONFIGURE;
GO
EXEC sp_CONFIGURE 'clr enabled' , '1'
GO
RECONFIGURE;
GO

Memory Configuration Changes Restart

http://social.msdn.microsoft.com/Forums/eu/sqldatabaseengine/thread/3e00fcbe-8b10-4775-a9c9-9d656ff4790d


The min server memory and max server memory options are advanced options. If you are using the sp_configure system stored procedure to change these settings, you can change them only when show advanced options is set to 1. These settings take effect immediately without a server restart.

Lock Pages Memory

http://msdn.microsoft.com/en-us/library/ms190730(v=sql.90).aspx


Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 5845, Level 16, State 1, Line 2

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

--http://msdn.microsoft.com/en-us/library/ms190730.aspx
--http://www.sqlcoffee.com/Tuning05.htm


To enable the lock pages in memory option
1.On the Start menu, click Run. In the Open box, type gpedit.msc.


The Group Policy dialog box opens.

2.On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

3.Expand Security Settings, and then expand Local Policies.

4.Select the User Rights Assignment folder.

The policies will be displayed in the details pane.

5.In the pane, double-click Lock pages in memory.

6.In the Local Security Policy Setting dialog box, click Add.

7.In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

8.Restart the sql server engine.If not working then reboot the server.


"This setting is not compatable with coputer runnning windows 2000 "

XL to SQL database data load

i found another sol for exporting and importing to/from excel
i hope that is useful and it work

SQL Server Management Studio (SQL Server 2005)
1. In SQL Server Management Studio, expand Server Objects in Object Explorer.
2. Right-click Linked Servers, and then click New linked server.
3. In the left pane, select the General page, and then follow these steps:
a. In the first text box, type any name for the linked server.
b. Select the Other data source option.
c. In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.
d. In the Product name box, type Excel for the name of the OLE DB data source.
e. In the Data source box, type the full path and file name of the Excel file.
f. In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.
g. Click OK to create the new linked server.


Import:-
CREATE PROCEDURE Import_Data
AS
BEGIN
Insert Customer
SELECT * FROM OPENQUERY(EXCELIMPPRT, ‘SELECT * FROM [Sheet1$]‘)
END

to Export:
Create PROCEDURE Export_Data
AS
BEGIN
INSERT INTO OPENROWSET
(‘Microsoft.Jet.OLEDB.4.0',’Excel 8.0;Database=C:\\sales.xls;’,'SELECT Customer_Key, First_Name,Middle_Name,Last_Name,Birthday, Marital_Status, Gender,Email_address, English_Education, Spanish_Education, Phone,Date_Frist_Purchase,Geography_Key FROM [Sheet1$]‘)
SELECT Customer_Key, First_Name,Middle_Name,Last_Name,Birthday, Marital_Status, Gender,Email_address, English_Education, Spanish_Education, Phone,Date_Frist_Purchase,Geography_Key from Customer
END

Generate Insert Statements

http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/

1.DATABASE NAME: Right Click
2.TASKS: GENERATE SCRIPTS
3.Under Table/View Options: Set SCRIPT DATA = TRUE   --It is on SQL 2008



Right click on the database name in the object explorer.
Select Tasks
Select Generate Scripts...
Then select source database
In the Chose Script Options database screen,  scroll down for Table/View Options
Set Script Data option to True
Then in the following screen select Tables
Select tables you wish to script
Select output type



The really low cost solution though is to use this stored procedure:
http://vyaskn.tripod.com/code.htm#inserts


http://noprobs.wordpress.com/2008/07/20/generating-insert-statements-for-a-table-in-sql-server/



http://agilebi.com/mcole/2011/04/04/how-to-generate-insert-scripts-for-existing-data/


SELECT 'INSERT INTO dbo.MyTable (Eid,First_Name) VALUES (' + Eid + ',' + First_Name + ')' AS InsertStatement from [DB_NAME].[dbo].[Table_Name]

SELECT 'INSERT INTO dbo.MyTable (Eid,First_Name)
  VALUES (' + CAST(Eid as int) + ',' + First_Name + ')' AS InsertStatement from Test

SELECT 'INSERT INTO dbo.MyTable (id,Name)
  VALUES ('+ CAST(id as varchar(20)) + ',' + Name + ')' AS InsertStatement from Test;

Data load methods -- SQL Server

1.SSIS pacakge
2.XL doc
3.BCP
4.Import/Export wizard
5.insert/select option


BCP:
http://sqlfool.com/2008/12/bcp-basics/

Database growth if it is only primary file group with multiple data file disks

Database growth if it is only primary file group with multiple data file disks

SOme how the database created long back and as given below configuration/files/filegroup/space.

SQL Server 2005
mdf  --200GB ( on primary) - DISK1 --(Currently mdf file full size of data is 150GB)
ndf1 --100GB ( on primary) --DISK2 --(Currently mdf file full size of data is 20 GB)
ndf2 --100GB ( on Primary) --DISK3 --(Currently mdf file full size of data is 20 GB)
ldf  --100GB (not applicable) --DISK4((Currently mdf file full size of data is 50 GB)

NOte: ndf1 and ndf2 should be separate filegroup(Secondary) for better I/O. It was not happened.

Question: The database mdf,ndf1,ndf2 files creatd on same filesgroup "PRIMARY".It has only one filegroup. MDF fle size 150 GB now, If mdf file size full(200GB), then will it automatically go to ndf1 then ndf2.The DB growth is not restricted to anyone of them.

How the database will grow ?
mdf-->ndf1--->ndf2 data growth?
Will it be round robin ?  --I think it will not, because seconday filegroup does not exist.


I read below but did not clear to me.
--http://msdn.microsoft.com/en-us/library/ms189563(v=sql.90).aspx


It is worth to read these articles:

•http://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/rebalancing-data-files-filegroup-140068
•http://sqlskills.com/blogs/jonathan/post/Looking-at-multiple-data-files-and-proportional-fill-with-Extended-Events.aspx

UDL sql server connection to test the connectivity

SQL Server connection Test:

1.SQLCMD/OSQL
2.SQL Server management studio
3.Any SQL Client tool
4.Create file with .UDL extention

fixing a suspect database

Creating, detaching, re-attaching, and fixing a suspect database
--http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/

SQLCMD Error output if using Batch script file

SQLCMD -S ServerName -E -b -i "D:\Test.sql"

if errorlevel 1 (
     Echo Error in scripts
     goto outerror1
 )

:outerror1
ECHO Batch job not ran successfully
EXIT 1

SP_ stored procedure script

sp_helptext 'sp_helpdb'

USE master
EXEC sp_helptext 'sp_MSdbuserpriv'



--It will give the sp_helpdb SP inside script.


sp_tables_rowset

Clearing Your DNS Cache

Clearing Windows® XP, 2000, or Vista®

Windows® XP, 2000, or Vista®


Your DNS Cache
1.Open the Start menu.
2.Go to Run.
?If you do not see the Run command in Vista, search for "run" in the Search bar.
3.In the Run text box, type: ipconfig /flushdns
4.Press Enter or Return, and your cache will be flushed.

Automatically remove agent history

SSMS vs SQLAgent: "Automatically remove agent history" bugs


Through SSMS, the SQL Agent has a checkbox named "Automatically remove agent history." Once clicked, the user can set the number of days, weeks, etc.

There is not anything "automatic" about this feature.

From SQL Server 2005, it's issuing a one-time sp_purge_jobhistory @oldest_date, generating @oldest_date from a DateAdd(-1 * what you specified, getdate)

From SQL Server 2000, it returns the error "@oldest_date is not a parameter for procedure sp_purge_jobhistory."





https://connect.microsoft.com/SQLServer/feedback/details/172026/ssms-vs-sqlagent-automatically-remove-agent-history-bugs

SA password change

http://msdn.microsoft.com/en-us/library/dd207004.aspx

http://blog.sqlauthority.com/2009/08/04/sql-server-forgot-the-password-of-username-sa/
http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx


or

http://www.richnetapps.com/forgot_or_lost_sql_server_sa_password_1/

Here’s a neat thing to remember if you’re using MSSQL:
To change the “sa” password, login to your computer using an administrator account. Open your SQL Server and log in using Windows Authentication. Open a new query window and type in the following command:
sp_password @new = 'new_password', @loginame = 'sa'
After that go to section Security –> Logins –> double click “sa” or choose properties. Go to “Status” and check to see if “Login:” is enabled. If not, enable it.


or


osql -E -S .\SQLEXPRESS exec sp_password @new='changeme', @loginame='sa'
go
alter login sa enable
go
exit


Create new user with Sysadmi in Single user mode:
-------------------------------------------------
EXEC sp_addsrvrolemember ‘Domain\User_Name’, ‘sysadmin’;
GO

SQL Server Max Memory

http://msdn.microsoft.com/en-us/library/ms143685(v=sql.105).aspx

Permissions - Instance Security Audit Documentation

http://www.sqlservercentral.com/Forums/Topic1251262-146-1.aspx

Users Roles

--You can use the following query to determine which users are assigned to database roles.

SELECT

p.name, p.type_desc, pp.name, pp.type_desc, pp.is_fixed_role

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

Fill Factor

http://ask.sqlservercentral.com/questions/95085/about-fill-factor.html

Cloning User Permissions

http://vyaskn.tripod.com/scripting_permissions_in_sql_server_2005.htm
http://vyaskn.tripod.com/code/scripting_permissions_in_sql_server_2005.txt

SET NOCOUNT ON


DECLARE @OldUser sysname, @NewUser sysname


SET @OldUser = 'HRUser'
SET @NewUser = 'PersonnelAdmin'


SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'


SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'


SELECT 'EXEC sp_addrolemember @rolename =' 
 + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC


SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
 + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) 
 + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
 + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
 + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS usr
 ON perm.grantee_principal_id = usr.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC


SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
 + SPACE(1) + perm.permission_name + SPACE(1)
 + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
 + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.database_principals AS usr
 ON perm.grantee_principal_id = usr.principal_id
WHERE usr.name = @OldUser
AND perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC

Slow - Running Queries

--Slow-Running Queries

--http://msdn.microsoft.com/en-us/library/ms177500(v=sql.105).aspx

1.PERFMON : The Windows System Monitor can be used to monitor the performance of SQL Server and non-SQL Server related components
2.Profiler: Use SQL Server Profiler to help identify the slow query or queries
            Use the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views to find similar queries that collectively consume a large number of resources
3.Showplan: Analyze query performance by producing a Showplan, which can be a text, XML, or
   graphical representation of the query execution plan that the query optimizer generates. You can produce a Showplan using Transact-SQL SET options, SQL Server Management Studio, or SQL Server Profiler.


Example: This example shows how many logical and physical reads are used by SQL Server as it processes the statements.

SET STATISTICS IO ON;
GO
SELECT * FROM dbo.Table_Name
GO
SET STATISTICS IO OFF;


4. Using Statistics to Improve Query Performance

Example:

SELECT name AS "Name",
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO

--The following example sets AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS to ON
USE AdventureWorks2008R2;
GO
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_UPDATE_STATISTICS ON;
GO





Queries or updates that take longer than expected to execute can be caused by a variety of reasons. Slow-running queries can be caused by performance problems related to your network or the computer where SQL Server is running. Slow-running queries can also be caused by problems with your physical database design.

There are a number of common reasons for slow-running queries and updates:

Slow network communication.

Inadequate memory in the server computer, or not enough memory available for SQL Server.

Lack of useful statistics

Lack of useful indexes.

Lack of useful indexed views.

Lack of useful data striping.

Lack of useful partitioning.

Indexed Views

--http://blog.sqlauthority.com/2009/09/24/sql-server-interesting-observation-index-on-index-view-used-in-similar-query/
--http://msdn.microsoft.com/en-us/library/dd171921.aspx

Indexed Views
In SQL Server, a view that has a unique clustered index is referred to as an indexed view.

Most expensive queries on SQL Server

--find out the most expensive queries on SQL Server Box.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Database Backup and Restore SQL Scripts

Database backup steps:

--Full backup
BACKUP DATABASE [DB_NAME] TO  DISK = N'D:\Backup\Test_DB.bak'  WITH FORMAT, STATS = 10
GO
--TransactionLog backup
BACKUP LOg [DB_NAME] TO  DISK = N'D:\Backup\logs\Test_DB.trn' WITH FORMAT, STATS = 10
GO


Restore database steps:

Good article
http://blog.sqlauthority.com/2009/07/14/sql-server-backup-timeline-and-understanding-of-database-restore-process-in-full-recovery-model/

--Display logical names
RESTORE FILELISTONLY  FROM DISK = D:\MSSQL2K5\DBANME_backup.bak'

--To find the logical and physical file names if the DB exis ton instance
SELECT name, filename FROM SYSFILES

--set database in single user mode
ALTER DATABASE RELOAD_DEV SET SINGLE_USER WITH  ROLLBACK IMMEDIATE

-- check online users for that DB
sp_who2

-- STATS = 10:  will display the 10% database restore completed.It could be 10,20...any number.



**************************** Restore ************************************

*****--With Recovery  means you can not restore/add Tlog backup next.
When RECOVERY keyword is specified, the SQL Server brings back the database online and will not accept any further log backups.


*****--with NORecovery means you can restore/add Tlog backup next.  --FullBackup then Tlog backup
**** Restore Tlog you can use wiht "Recovery"



Restore database TestDB2 from disk='D:\temp\Restoretest\TestDB_2142013133.bk' with
move'TestDB' to 'D:\temp\Restoretest\TestDB2.mdf',
move 'TestDB_log' to 'D:\temp\Restoretest\TestDB_Log.ldf',
Norecovery,stats=10


Restore log TestDB2 from disk='D:\temp\Restoretest\TestDB__log_2142013134.trn' with
move'TestDB' to 'D:\temp\Restoretest\TestDB2.mdf',
move 'TestDB_log' to 'D:\temp\Restoretest\TestDB_Log.ldf',
recovery,stats=10

****************************Restore end ***********************************

--http://www.mssqltips.com/sqlservertip/1075/copyonly-backups-with-sql-server-2005/
Restore database with copyonly option.


--If database is in restoring mode then run below,if you used Restore script as NORECOVER mode
   RESTORE DATABASE Test_DB  RECOVERY;
Select an option for the Recovery state box. This box determines the state of the database after the restore operation
• RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.
• RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.
--TRAN Log backups restore:
   NOTE: Restore the full backup with NORECOVERY option and then restore the transaction log backup.

--5. To make database to multiuser mode
    ALTER DATABASE RELOAD_DEV SET MULTI_USER
    GO
--6.Change the database owner to 'sa'

Backup error Auto Close

Backup failed for Server

"The log for database 'MYTestDB' is not available. Check the event log for related error messages. Resolve any errors and restart the database. (Microsoft SQL Server, Error: 9001)


Possibilities:
1. Someone deleted the log file
2. Disk holding the .ldf file went bad
3. Other hardware (disk) malfunction


Do this:
A. Backup all databases
B. Hard boot server
C. If problem occurs again, have HW diagnostics performed.


Sol: In mycase after re-starting the SQL Server Instance, i was able to take database backup.


--Auto Close option off
"When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.
 When set to OFF, the database remains open after the last user exits."



What is AutoClose and why is it Bad?
AutoClose is a database option or setting – set on a database by database basis (meaning that it can’t be controlled at the server level).

According to Books Online:

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

When set to OFF, the database remains open after the last user exits.


--To check the auto close on or off.
select name,is_auto_close_on from master.sys.databases where is_auto_close_on=1;

--To change auto close option
Alter database DB_Name set auto_close off
Go




2. Open SSMS and run the following command new query window.
   Alter database DB_Name set offline with rollback immediate
   go
 
3. Open new query window and run the following script.
   Alter database DB_Name set online
   go
 
4. Once the databases are online run checkdb.

5. Run the following query to set the Auto close off.

   Alter database DB_Name set Auto_close off
   go
6. close ssms and log off from the server

Restore DB from recent backup file Automatic

--http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/
--Auto generate SQL Server restore script from backup files in a directory


DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'Test_DB_NAME'
SET @backupPath = 'D:\Backup\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
SELECT @lastFullBackup= MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
print @lastFullBackup

SQL Server error 9001: The log for the database is not available

Event ID: 9001

The log for database 'database name' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

http://serverfault.com/questions/238394/diagnosing-microsoft-sql-server-error-9001-the-log-for-the-database-is-not-avai

MS KL: ---> Event ID:      9001
http://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=SQL+Server&ProdVer=2000.80.760.0&EvtID=9001&EvtSrc=MSSQLServer&LCID=1033

Database snapshot cannot be created

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.

Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 9001, Level 21, State 1, Line 1
The log for database 'DB_NAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

ORA--00604 and ORA--0094

ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "from$_subquery$_006"."PARENT": invalid identifier

Changes
Applied PSU2 Patch 9162498 on top of the 10.2.0.5 Agent.

Cause
The problem is caused by the fact that the associated query used to gather the metric "GRANT ANY/SELECT
PRIVILEGES " only works on 10g or 11g databases and it fails on 9i.
There's an internal bug not visible that corresponds to the issue :
BUG 9169817 UPGRFUNC:METRIC COLLECTION RECEIVED FOR 9I DATABASE

Solution
Note: Solutions are listed in order of strength of recommendation

https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?_afrLoop=1632723052892222&type=DOCUMENT&id=1065410.1&displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=gyfjvkxqn_271

Insert_BulkCopy Options

--http://manuals.sybase.com/onlinebooks/group-as/asg1250e/svrtsg/@Generic__BookTextView/40676;pt=40842


Error: Unable to Set the BulkCopy Option to disable logging

Turn On select into/bulkcopy (all versions)
Use the sp_dboption stored procedure to set the select into/ bulkcopy option to true:

1--To display into/bulkcopy option.
sp_dboption DB_NAME, "select into/bulkcopy"

2--To enable the TRUE/FALSE option for into/bulkcopy option
sp_dboption DB_NAME, "select into/bulkcopy", TRUE

3.--
use database_name
go

4.--
checkpoint
go

Once a minimally logged operation such as "fast" bulk copy runs in the database, you are not allowed to dump the transaction log to a device, because unlogged changes are not recoverable. Instead, you should do a dump database as soon as possible to restore recoverability and allow transaction dumps to devices again.



http://www.cisco.com/en/US/products/sw/custcosw/ps1001/products_tech_note09186a0080094997.shtml
http://www.mssqltips.com/sqlservertutorial/5/sql-server-bulklogged-recovery-model/

Memory consumption by each db

--works for 2005 and 2008
--for memory consumption by each db

select isnull(db_name(database_id),'Total memory used') as Databasename,
convert(numeric(8,2),count(page_id)/128.0) as MB
from sys.dm_os_buffer_descriptors with (nolock)
where database_id !=32767
group by database_id
with rollup
order by count(page_id) desc
got this for memory consumption by each db

VMWare issue --The log for database 'XYZ' is not available.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133004


What have this lesson learned me?

1) Don't use VMware 3.5 and definately not during snapshots and backups.
2) Rethink if virutal machine is the right choice for a terabyte datawarehouse, even if data files are on SAN.

Moving DTS packages from one server to other server

http://www.sqlservercentral.com/Forums/Topic697546-146-1.aspx

INSERT INTO msdb.dbo.sysdtspackages
SELECT *
FROM OPENDATASOURCE('SQLNCLI','Data Source=ServerName;Integrated Security=SSPI').msdb.dbo.sysdtspackages


--------------------------------------------------------------------------------

Error message while DBCC Checkdb

DBCC Checkdb

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/194c1c3b-3c6d-450b-a033-ceab67480433

" stack dump error "

Warning: Fatal error 211 occurred at Aug 21 2012  3:09PM. Note the error and time, and contact your system administrator.
A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 21)

Sol: Database ldf file location moved to different location then re-started the SQL Server Instance.The DB went to "Recovery Pending" state.Then we repaired with emergency mode with data lose. This resolveed after copying the ldf files to new location.

Identity column OFF and ON

http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/

CREATE TABLE dbo.Tmp_example1
(
eid INT NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_example1 ON
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_example1 OFF
GO
DROP TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT


If you run into the following error message:
An explicit value for the identity column in table ‘’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
It can mean two things.

One you’ve not enabled identity insert on your table, meaning SQL Server will not let you insert into the Identity column.
This can be rectified with the following statement:
SET IDENTITY_INSERT table_name ON
And then turn it off again when done
SET IDENTITY_INSERT table_name OFF

However it can also mean that you are using for example INSERT INTO, in which cause the message tells you to specify the column names. This means using the following syntax:

INSERT INTO target_able_name (column_name1, column_name2…. column_nameN)
SELECT
YOUR_SELECT_LIST_WHICH_MATCHES_COLUMN_LIST
FROM source_table_name

SSAS port number change

--to change the analysis service ports for named instances,
is there anyother way..I did try to change in ini file..thats not working


-- remeber...there is some right click on the SSAS or instnace then give the port numbers


http://support.microsoft.com/kb/2466860
http://deangrant.wordpress.com/2011/11/30/change-the-port-number-of-ssas-instance/

To get a count of objects for all the databases on a Host -- Table_Count of rows

Table_Count of rows
--To get a count of objects for all the databases on a Host

sp_msforeachdb ‘select ”?” as DatabaseName, count(*) as CountObjects from ?.sys.objects’


  To get a count of rows from all the tables in a database
  sp_msforeachtable ‘select ”?”, Count(*) from ?’