Table Backup with native SQL, Process 4

-- Table Backup with native SQL, Process 4
Version : SQL 2008 or later.

Table Backup.
You can get the result in 4 types...
 Process 1 : Generating scripts
 Process 2 : Import/Export Option (T-SQL/SSIS)
 Process 3 : Inserting into new table (Select * into newtab from baktab)
 Process 4 : Inserting into existing table (Create newtable with same structure as
 Original table, Insert into newtable select * from backtab)

Here we can see Process 4 : We discussed other 3 ways in earlier posts..Process1, Process2 and Process3

So two steps first copy the skeleton/Structure/schema of the table and in next step load that table.

       Step 1:

SELECT * FROM OriginalTab_Backup FROM OriginalTab where 1 = 2
You can use any type of comparison in where condition, those should not equal.

It will just print the structure of the 'OriginalTab' table in 'OriginalTab_Backup'.
      
       Step 2:

Now we have to load the table, We can use few other ways to do so. But I am using the below.
INSERT INTO OriginalTab_Backup SELECT * FROM OriginalTab

You can also use Bulk Insert to load the table, which is most used one. I will post this in future.
By using this process, You can take the backup of full table or selected columns.
like below..

SELECT Col1,Col2,Col3 FROM OriginalTab_Backup FROM OriginalTab where 1 = 2

INSERT INTO OriginalTab_Backup SELECT  Col1,Col2,Col3 FROM OriginalTab.

Table Backup with native SQL, Process 3

-- Table Backup with native SQL, Process 3
Version : SQL 2008 or later.

Table Backup.
You can get the result in 4 types...
 Process 1 : Generating scripts
 Process 2 : Import/Export Option (T-SQL/SSIS)
 Process 3 : Inserting into new table (Select * into newtab from baktab)
 Process 4 : Inserting into existing table (Create newtable with same structure as
 Original table, Insert into newtable select * from backtab)

Here we can see  Process 3 : Please click here for Process1 or Process2
You can do this same job by using a simple T-SQL line.

SELECT * INTO OriginalTable_Bak FROM OriginalTable

Note: By using this process you can backup the table data. You cant get keys, references and indexes directly.

Describe INTERSECT and EXCEPT

INTERSECT and EXCEPT

To better understanding please follow this.
Intersect :  It will give you mutual records from Left query and Right query of the Intersect.
Example: 1) A Intersect B or you can follow the below example queries.

Except : It will give you only the left query or Left table result. It means, wouldn't count the mutual records
Example: 1) A Except B -- All the rows from A, which avoids If any same rows from B.


Table A has 50 rows  10 of them are copied from Table B
Table B has 70 rows 20 of them are copied from Table A
                  So Now A INTERSECT B : 10 + 20 = 30
                   
                  So A EXCEPT B : 50 - 30 = 20
                  So B EXCEPT A : 70 - 30 = 40


Small example :

I have database named mydb, consists 5 tables. I got Full backup on 08/10/2010
2 weeks later I had to compare the database with the old backup.
I have installed the database on the same server with different name as mydb_Test.

Note: Of Course If you have the old database available on one of your test servers, which had configured linked server set up .. (No need to install the database again..) Just use the Fully Qualified Object Name as LinkedServerName.DatabaseName.sys.tables
Where,LinkedServerName : should be one of the row from the following query.
            select name as ServerName from sys.servers


Now, My job is to findout the new objects introduced in the current version of
the database as well the dropped objects. Such as Tables, Stored Procs, Triggers, Functions..

Of course, We definitely have couple of other procedures. But I have used this and gotten results fine.

----------------Tables

-- Find new tables

SELECT Name AS NewTables FROM mydb.sys.tables
EXCEPT   -- Will give all the extra tables from mydb.
SELECT Name FROM myDB_Test.sys.tables
ORDER BY name


-- Find Mutual tables

SELECT name AS MutualTables FROM myDB_Test.sys.tables 

INTERSECT
SELECT name  FROM mydb.sys.tables
ORDER BY name

-- Find Dropped tables

SELECT Name AS DroppedTables FROM myDB_Test.sys.tables
EXCEPT   -- Will give all the extra tables from mydb.
SELECT Name AS NewTables FROM mydb.sys.tables
ORDER BY name

This is the same procedure to find stored procedures and Triggers. Just replace tables with procedures and triggers in the code above.

For the functions, Please follow..


SELECT name AS NewFunctionName ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'
EXCEPT
SELECT name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'

SELECT name AS MutualFunctionName ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'
INTERSECT
SELECT name,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'

SELECT name AS DroppedFunction ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'
EXCEPT
SELECT name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'

Table Backup with native SQL, Process 2

-- Table Backup with native SQL, Process 2
Version : SQL 2008 or later.

Table Backup.
You can get the result in 4 types...
 Process 1 : Generating scripts
 Process 2 : Import/Export Option (T-SQL/SSIS)
 Process 3 : Inserting into new table (Select * into newtab from baktab)
 Process 4 : Inserting into existing table (Create newtable with same structure as
Original table, Insert into newtable select * from backtab)

Here we can see Process 2 :
You have an option with Import/Export Wizard.
See below how it works..  

Right Click on the database Tasks -> and click Import Data... (OR) Export Data...as below

fig : 1 DBProperties_rightClick
both bring the same window as below...

fig : 1 a) Import and Export Wizard
Cleck next and Choose the source, where you wanted to retrive data from(SQL,EXCEL,Flat File and etc...) see below.

fig : 2 Select Source

Click next and Choose the destination, where you wanted to load the data(SQL,EXCEL,Flat File and etc...) see below..

fig : 3 Select Destination

Choose one of this as the following figure....

fig : 4 Type

Now you have to configure the destination file as bleow.. As I have selected my destination is Flat file, I just need to select Row Delimiter and Column delimiter from the drop down.

fig : 5 Configuring Destination

So, Now you have two options here, Select Run Immediately to have the import/export task done now.
Or you can check the other box as Save SSIS Package and will let you run whenever you want in future.
Please look at below.

                                           fig : 6 Save and Run

fig : 7 Finish

fig : 8 Result

Script to find Log file Original Size and Growth value.

Here is a small script you to run.
You can get various results from the same script by small modifications.

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name as 'Locate/PhysicalName', (size*8)/1024 SizeMB,
(growth*8)/1024 Growth
FROM sys.master_files
WHERE database_id >4 -- to avoid system databases
and DB_NAME(database_id) <> ('ReportServerTempDB')
and Name like ('%_Log') -- Just log files