Category: SQL-Advanced

SQL Server 2012 – Get Backup History of the database

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-Server 2012, Varinder Sandhu

The msdb database is system database used by SQL Server Agent for scheduling alerts and jobs. msdb.dbo.backupset – Contain the information concerning the most-granular details of the backup process msdb.dbo.backupmediafamily – Contains the metadata for the physical backup files as they relate to backup sets Using the above mentioned tables, we will find the backup history of database Script: SELECT s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Backup_Size, CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(4))

» Read more
, , ,

Schedule Automatic Backup in SQL Server 2012 – Part2

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-Server 2012, Varinder Sandhu

In the previous post, we have scheduled automatic backup in SQL Server 2012 – Part1. As we have scheduled daily backup of database and backup file will be generated every day. It is a good practice that to delete backup files after a certain period of time as per requirement. Now we will add the Maintenance Clean Up Task along with the Back Up Database Task. Configure Clean Up Task From the Maintenance Plan Tasks tool box pane in the left

» Read more
, , , ,

Schedule Automatic Backup in SQL Server 2012 – Part1

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-Server 2012, Varinder Sandhu

You know it is very important to back up your SQL server database backup on the regular basis. So in this post, we will see how to schedule automatic backup in SQL server 2012. We will go step by step to schedule so that we all can understand each and every step clearly. Before scheduling automatic backup we have make sure the following two points: User should have the SYSADMIN privileges to schedule automatic backup. SQL Server Agent service should

» Read more
, , ,

SQL Server – Authentication Mode – Windows Mode and Mixed Mode

SQL, SQL-Advanced, SQL-Tips and Tricks, Varinder Sandhu

During the installation of SQL Server you can choose the authentication mode for Database Instance. SQL Server support the two authentication mode as Windows authentication mode: Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication.  Even if you choose Windows Authentication mode during SQL Server installation “sa” user is created but disable for SQL Server Authentication. But later we you can use “sa” user by enabling for SQL Server Authentication .Any window user can configure as system administrator

» Read more
, , , ,

Best way to fix SQL error 8942

Guest Post, SQL, SQL-Advanced, SQL-Tips and Tricks

Structured query language is applied to manage any kind of data extracted from relational database. But during these specific operations several errors can come up into the field. Amongst those, Error 8942 is much common in unexpected occurrence. Though Sql server is much reliable, effective and faster environmental platform, it creates a result like DBCC errors. The main reason is nothing but the overlapping of multiple slots. Here the offsets are greater than previous slots for any exceptional causes. So

» Read more
, ,

SQL Server – How to add column dynamically in where clause

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

In this post, we will learn with example how to add the column dynamically in where clause. Example: For demo we have table as shown in the snapshot Basically we want to execute following script (i.e. Script: 1) but column name (i.e. FIRST_NAME) added dynamically in where clause. Script: 1 SELECT * FROM EMPLOYEE WHERE FIRST_NAME = 'VARINDER' Created a table and insert the column name value (i.e. FIRST_NAME) as shown in the snapshot Now we will execute the Script:

» Read more
, ,

SQL Server – The multi-part identifier could not be bound State – Msg 4104 – Fix

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Error Msg Server: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier could not be bound. Cause of this Error Msg: Case: 1 This error usually occurs when an alias is used when referencing a column in a SELECT statement and the alias used is not defined anywhere in the FROM clause. Case: 2 This error occurs if we are using the tables from the two different databases in the join statement and not using the table alias

» Read more
, ,

Best Way to Configure Firewall for SQL Server Access

Guest Post, SQL, SQL-Advanced, SQL-General, SQL-Tips and Tricks

Windows firewall is nothing but the software and hardware based network security. It generally controls the transaction traffic mixed up with data packets. Simultaneously it determines whether the upcoming signal is eligible for further routing or not. The threats coming from outside can be prevented with this protective shield. On the other hand, SQL server is a categorized section of relation database management system. Here the data are stored and retrieved to reduce the workload. Principal steps: When the basic

» Read more
, , , , ,

SQL Server 2008 – Create Temp table Permission/Access

SQL, SQL-Advanced, SQL-Tips and Tricks, Varinder Sandhu

Question:  Do we need any permission to create temp table in SQL Server? Or Question: Which permission is required to create temp table in SQL Server? Answer: No permissions are required to create temp tables.  Any user can create temp table, as long as they can connect to the SQL Server. Example: As user does not have permission to create a regular table But user can create a temp table as Temp Table as

» Read more
, ,

SQL Server 2008 – Auto Generate Insert Statement

SQL, SQL-Advanced, SQL-Tips and Tricks, Varinder Sandhu

This is very quick way to generate the Insert Statement for all of the data in your table. This is really very useful feature of SQL Server Summary: Right click on the database and go to Tasks -> Generate Scripts Select the tables for that you want to generate the script Click on the Advanced button. In the General category, go to Type of data to script. There are 3 options: Schema Only, Data Only, and Schema and Data. Select

» Read more
, ,

SQL Server – Export the Data from SQL Server to Oracle Database – Import-Export Wizard

SQL, SQL-Advanced, SQL-General, SQL-SSIS, Varinder Sandhu

The SQL Server Import and Export Wizard provides the simplest method of copying data between data sources. In this article given an example to copy the data from SQL Server to Oracle database. Click here to download the full PDF version of this article. After this article,for more detail about SQL Server Import and Export Wizard. please check http://msdn.microsoft.com/en-us/library/ms141209

» Read more
, ,
1 2 3 4 … 8