What's the difference between a regular backup and a copy only backup?

What's the difference between a regular backup and a copy only backup?

A copy only backup does not interupt the backup chain when you are doing multiple types of backups such as full, differential and log backups.
If the database is set to SIMPLE recovery it won't make a difference if you use the copy_only option or not.
If the database is set to FULL or BULK_LOGGED then if you do a copy_only backup for a full database backup this will not interupt the backup chain for your differentials. If you do a copy_only backup for a transaction log backup this does not interupt your backup chaing for your transaction log backups. Doing a copy_only for differential backups does not make a difference.
If your database is in SIMPLE and you are doing full and differential backups a copy_only backup does not interupt the next differential. A differential backup is a backup of all extents that have changed since the last full backup.

Create Backup File with Password protection

Create Backup File with Password protection

Sometime it is necessary to set password in backup file. If backup is password protected than we cannot restore the database or see details without knowing the password. Now to add password protection we have to add "With MediaPassword='DBPassword' " at the end of TSQL where 'DBPassword' is password we set for the backup. Now during restore we have to add this at the end also.


TSQL to backup database with password
Backup Database TEST To Disk ='C:\Temp\TEST.BAK' With MediaPassword='DBPassword'  
GO


Now we cannot restore this database or get details of database from this backup without knowing the password.
We will get the below error:



Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.

Now to restore the database run below command:

Restore Database TEST From Disk='C:\Temp\TEST.BAK' With MEDIAPASSWORD='DBPassword'  

To resore details of the backup run below command

Restore HeaderOnly From Disk='C:\Temp\TEST.BAK' With MEDIAPASSWORD='DBPassword' 

Truncate and Delete

Truncate and Delete

Generally Delete command removes the rows from a table based on some where condition. whereas Truncate removes all the rows from a table.

Truncate

• Truncate is faster and uses fewer system and transaction log resources than Delete. 
• You cannot use  Truncate Table on a table referenced by a FOREIGN KEY constraint.
• Truncate is a DDL Command.
• Truncate resets the identity of the table.
• Truncate removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column.
• Truncate cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
• Truncate removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

Delete

• Delete removes rows one at a time and records an entry in the transaction log for each deleted row.
• Delete does not reset the identity of the table.
• Delete is DML Command.
• Delete can be rolled back.
• Delete does not reset Identity property of the table.
• Delete can be used with or without a WHERE clause.
• Delete activates Triggers if defined on table.

Indexes that are not used

Indexes that are not used

One of the main task of a database administrator is proper handling of Indexes. He will have check for the right indexes from time to time. Below query checks the database and returns the unused indexes.

SELECT o.name AS object_name, i.name AS index_name
   , i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates
FROM sys.indexes i
JOIN sys.objects o ON  i.object_id = o.object_id
LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
          AND    i.index_id = u.index_id
          AND    u.database_id = DB_ID()
WHERE    o.type <> 'S'    
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY    (convert(decimal(19,4),ISNULL(u.user_seeks, 0)) + ISNULL(u.user_scans, 0) 
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc, o.name, i.name

The above query returns the indexes which are never used.

Shrink the Unshrinkable SQL Transaction Log

Shrink the Unshrinkable SQL Transaction Log

There are many reason for not shrinking of Transaction log. Our production database backup was failing for last several weeks, so the backup process could not clear out the transaction log. Our approx 1 GB database grow up to 37.5 GB.

The SQL Server GUI for shrinking the database rendered no effect, and even using the DBCC SHRINKFILE command was not working.

The key, as explained by Pinal Dave, is to run the SHRINKFILE command twice,with an explicit backup log truncation in between both runs. This code here will get you up and running:

DBCC SHRINKFILE("DemoData_Log", 1)

BACKUP LOG DemoData WITH TRUNCATE_ONLY

DBCC SHRINKFILE("DemoData_Log", 1)

Backup types in sql server

Backup types in sql server

In SQL Server 2008 or later following are the backup types:


Full backup
Differential backup
Partial backup
Differential partial backup
File backup
Differential file backups
Transaction Log Backups
Copy-Only Backups

ALTER Schema / Owner name

ALTER Schema / Owner name

Query to change the schema / Owner of database objects like 

EXEC sys.sp_changeobjectowner <Object Name>, <New Owner Name>

The Object Name(@objname) parameter should in the format "[owner].[object]". The New Owner Name(@newowner) should be valid name from sysUsers object

SELECT * FROM SysUsers

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

You must have seen below error while renaming or deleting database. 

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)" 

Above error normally occurs when the database is in Multi User mode where users are accessing your database or some objects are referring to your database. 

To resolve this error.
First set the database in Single user mode. 
ALTER DATABASE TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Perform the required action.

Again set the database in multi user mode.

ALTER DATABASE TEST SET MULTI_USER WITH ROLLBACK IMMEDIATE

Restart SQL Server from command prompt

Restart SQL Server from command prompt

Sometime time you may need to restart SQL server from command prompt. Below command is used to stop and start SQL Server: 

Net Stop mssql$YourSQLServerInstanceName

Net Start mssql$YourSQLServerInstanceName

database last accessed date time

database last accessed date time

Sometime you may required to know the last accessed date and time of database..
Below query returns the lass accessed date and time of SQL Database.


SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT 
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY MAX(LastAccessDate)

The biggest problem of above query is that all information will be lost when you restart SQL Server. So, this is not applicable for servers which frequently restarted.

Find database file size

Find database file size 

SELECT DB_NAME(dbid) AS DatabaseName,
Name AS LogicalFileName,
CASE WHEN GroupID=1 THEN 'Data' ELSE 'Log' END AS FileType,
FileName AS FilePath, size AS TotalPage,
(Size*8192E)/1048576 AS FileSizeInMB
FROM master..SysAltFiles

Changing the Default Database

Changing the Default Database

By default SQL Server loads master databse.
Sometimes it is required to change the default database in SQL Server. Use below query to chage the default database:

ALTER LOGIN [AbcL\Abc] with default_database =TEST