Take copy_only backups

 How to take copy only backup for all backups

declare @OutputDir varchar(255)='<path>'--Give the path

DECLARE @name VARCHAR(50) -- database name

    DECLARE @path VARCHAR(256) -- path for backup files

    DECLARE @fileName VARCHAR(256) -- filename for backup

    DECLARE @fileDate VARCHAR(20) -- used for file name

    SET @path = @OutputDir

    --SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)

       SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

    PRINT 'Starting Backups'

    DECLARE db_cursor CURSOR FOR

        SELECT name FROM MASTER.dbo.sysdatabases

            --WHERE name like ('')--Change the db’s according to your requirements

                     --NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')

 

        OPEN db_cursor

            FETCH NEXT FROM db_cursor INTO @name

            WHILE @@FETCH_STATUS = 0 BEGIN

                SET @fileName = @path + @name + '_'+'0000'+'_' + @fileDate + '.BAK'

                    PRINT 'Starting Backup For ' + @name

                    BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT, copy_only, compression

                                  print @name + 'backup complete'

                FETCH NEXT FROM db_cursor INTO @name

            END

        CLOSE db_cursor

    DEALLOCATE db_cursor

    PRINT 'Backups Finished'

Database Restoration error User does not have permission to perform this action

User does not have permission to perform this action

When we trying to restore a database we got below error.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'xxxxxxx' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: User does not have permission to perform this action. (Microsoft.SqlServer.SmoExtended)

Cause:

No active connection on that database even though got same error finally we identified the reason is one of the orphaned replication job

Fix:

We removed that orphaned replication job from SQL agent, after that we are able to complete the restoration.

Kill Script

Kill Script:

DECLARE @cmdKill VARCHAR(50)

DECLARE killCursor CURSOR FOR

SELECT 'KILL ' + Convert(VARCHAR(5), p.spid)

FROM master.dbo.sysprocesses AS p

WHERE db_name(p.dbid) like 'Your_DBname%'

OPEN killCursor

FETCH killCursor INTO @cmdKill

WHILE 0 = @@fetch_status

BEGIN

EXECUTE (@cmdKill)

FETCH killCursor INTO @cmdKill

END

CLOSE killCursor

DEALLOCATE killCursor

Installing AdventureWorks2014 Sample Database

Installing AdventureWorks Sample Database

Now the SQL Server and the management studio are ready for using. For training purpose, we need a database with some sample data. It will be very helpful to have a sample database while learning SQL Server. For this purpose, Microsoft has introduced the AdventureWorks Sample Database. Currently, AdventureWorks sample database is available in CodePlex as open source.

Downloading & Extracting AdventureWorks Sample Database

1. You can download the sample databases for SQL Server 2014 from CodePlex. There you can see all the different types of sample databases available for download.
2. From the list, download the Adventure Works 2014 Full Database Backup.zip below the Recommended Download Section. This is the sample database useful for learning SQL Server basics.

https://msftdbprodsamples.codeplex.com/releases/view/125550

1. Once downloaded, unzip the file to extract the sample database backup file named AdventureWorks2014.bak.
2. Place the backup file (AdventureWorks2014.bak) under the default SQL Server 2014 backup folder.
   • On 64 bit operating system, the default backup folder will be like C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\.
   • On 32 bit OS, use C:\Program Files (x86)\Microsoft SQL Server\…..
3. Now, follow one of the below methods to restore this backup file to the SQL Server.

Installing Or Restoring The AdventureWorks Sample Database

A. Restore Sample Database Using SQL Scripts

1. Login to the SQL Server Management Studio (SSMS).
2. Open a new SQL Query Editor window
3. Copy the below code and paste it into the query editor window

   1 2 3 4 5 6 7 8 9

   USE master RESTORE DATABASE AdventureWorks2014 FROM disk = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak' WITH MOVE 'AdventureWorks2014_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.mdf', MOVE 'AdventureWorks2014_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.ldf', REPLACE

4. Click the Execute icon in the toolbar. You will see the status message as in the below picture once the database is successfully restored.
5. You can see the restored sample database in the Object Explorer under Databases folder.

B. Restore Sample Database Using SSMS GUI

1. Login to the SQL Server Management Studio (SSMS).
2. In the Object Explorer, right-click  the Database folder and select Restore Database...
3. In the Restore Database screen, choose Devices and click the ellipsis button to launch the backup device selection screen. In the device selection window, press Add button to launch the file dialog. The file dialog will open the default SQL Server backup location. As we have already placed the backup file in that location, just select the backup file in the file dialog and press OK. Again press OK in the device selection window.
4. Now the restore database window is filled with the details of the database to be restored from the backup.
5. Press the OK button in restore database window. The sample database backup is restored as a new database AdventureWorks2014. You can see the restored sample database in the Object Explorerunder Databases folder.

In my future article on SQL Server Basics series, I’ll be using this sample database for training.

How to Eliminate all successful backups messages in SQL server error log

We have got a one request in my organization To Eliminate the all successful backup's messages only from SQL server error log,use below trace flag.
-T3226

Normally Sql server error log contain all the information about how the sql server is running, what is happening and occurring on your database, its normally the first place you look at when you have any issue of the database. Keeping it small and as useful as it can be helps a lot when it comes to troubleshooting, since you don’t have to scan through hundreds of lines of useless information and locate the issue you might be having. Keeping it recycle on a regular interval is a good practice and should be set as default for easier maintenance.

If you have many databases within a single instance and/or have frequent backup mean that you will have all the successful backup entries written to SQL error log and makes the file grow large (and fast). If you do not have any scripts or monitoring that requires the successful backup entry in error log, it would be recommended to turn on trace flag 3226 to suppress all successful backups entries. It will, however, still write out the error entry if backup is unsuccessful, and all successful entry are still logged in msdb database.

Manually How to implement:

To manually turn on this trace flag globally, you can use the below code:

DBCC TRACEON (3226, -1)

To turn this trace off, you can just below query execute:

DBCC TRACEOFF (3226)

To see the what traces are enable globally use Trace status command:

DBCC TRACESTATUS(-1)

As above setup is a manual setup, it will not effect after service restart, to keep this setting effective after restart please use through startup parameter option.

Globally how to enable trace flag:

Step-1: Open sql server configuration manager through run command or startup programs

Step-2: After sql server configuration manager open select the sql server service in left pane

Step-3: In right pane select and right on MSSQLSERVER service

Step-4: click on ok properties sql server process

Step-5: Go to the advanced startup parameter tab

Step-6: Enter and add the below trace flag

-T 3226

Step-7: Apply and Click on OK, ok

Step-8: Restart the sql services.

Step-9: After restart connect to instance and open new query analyzer run below command to verify the trace flag whether enable or not.

DBCC TRACESTATUS(-1)

Or

use master

go

exec xp_readerrorlog 0, 1, 'lock'

Screen shots for above steps:

Lab test:

Let try to run this and see what happens, we are testing manual setup method mentioned above.

BACKUP DATABASE DBA_test TO DISK = 'D:\DBA_test.bak'

GO

DBCC TRACEON (3226, -1)

BACKUP DATABASE DBA_test TO DISK = 'D:\DBA_test.bak'

GO

DBCC TRACEOFF (3226, -1)

BACKUP DATABASE DBA_test TO DISK = 'D:\DBA_test.bak'

GO

--wanted make the backup fail

DBCC TRACEON (3226, -1)

BACKUP DATABASE DBA_test TO DISK = 'D:\MSSQL\DBA_test.bak'

GO

DBCC TRACEOFF (3226, -1)

From the code above, we will firstly backup the test database, enable the trace flag and perform the backup again, turn the trace flag off and perform the backup the third time. After its done, we will turn on the trace flag and wanted perform a failure backup. What we are expecting is that we should see the backup entry from the first try, nothing from the second and an entry for the third. The reason for the last part is to ensure that even if we have the trace flag on, we will still get failure backup entry in error log. Let check the result:

The above result in screen shot match's what we are expecting. we can conclude is that if you are not depending on the successful backup entry in SQL error log, you can enable this trace flag to minimize the number of entries in it.

T-SQL Script, It returns all Backups history information:

Using this script, you can find different types of information like Backup media, Type of Backup, Taken Duration.

 BOL

T-SQL Script, It returns all Backups history information:

SELECT

       bs.server_name AS ServerName,

       bs.database_name AS DatabaseName

       ,CASE bs.type

              WHEN 'D' THEN 'Full'

              WHEN 'I' THEN 'Differential'

              WHEN 'L' THEN 'Transaction Log'

       END AS BackupType

       ,CAST(DATEDIFF

              (SECOND,bs.backup_start_date,bs.backup_finish_date)

              AS VARCHAR(4)) + ' ' + 'Seconds' AS TotalTimeTaken

       ,bs.backup_start_date AS BackupStartDate

       ,CAST(bs.first_lsn AS VARCHAR(50)) AS FirstLSN

       ,CAST(bs.last_lsn AS VARCHAR(50)) AS LastLSN

       ,bmf.physical_device_name AS PhysicalDeviceName

       ,CAST(CAST(bs.backup_size / 1000000 AS INT) AS VARCHAR(14))

              + ' ' + 'MB' AS BackupSize

       ,bs.recovery_model AS RecoveryModel

FROM msdb.dbo.backupset AS bs

INNER JOIN msdb.dbo.backupmediafamily AS bmf

       ON bs.media_set_id = bmf.media_set_id

WHERE bs.database_name = 'master'

ORDER BY

       backup_start_date DESC

       ,backup_finish_date