Data Modeler for SQL Server

“Data Modeler” is a free tool to create Microsoft SQL Server databases.

If you are trying to design a databse correctly and this is one of the good database design GUI.

Some of the popular features of Data Modeler are

Real-time model validation
DDL regeneration
Table data synchronization
Drag ‘n’ drop foreign key creation
Automatic query script creation
Automatic relationship line placement
Object-level DDL previews
Bulk undo/redo
Schema support
Sub-models
Data dictionary
Update/delete foreign key automation
Customizable fonts and colors

For more details, Please refer http://sourceforge.net/projects/sqldatamodeler/

You can download it from here – http://sourceforge.net/projects/sqldatamodeler/files/latest/download

Advertisements

Backup All the Database in Sql Server

Backup All the Database in Sql Server

DECLARE @DataBaseName VARCHAR(MAX)
DECLARE @FileName VARCHAR(MAX)
DECLARE @FileDate VARCHAR(20)
DECLARE @Path VARCHAR(MAX)

--Set the Path Where You want to take the DataBase BackUp
SET @Path='D:\SQL DB\BACKUP\'
--Get the Date to append it in Backup File name
SET @FileDate=REPLACE((SELECT CONVERT(VARCHAR(20),GETDATE(),101)),'/','_')

DECLARE DataBaseCursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
--Exclude the System Database from Backing up
WHERE name NOT IN ('master','tempdb','model','msdb')

OPEN DataBaseCursor
FETCH NEXT FROM DataBaseCursor into @DataBaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName= @Path + @DataBaseName + '_' + @FileDate + '.BAK'
--BackUp task happening here
BACKUP DATABASE @DataBaseName TO DISK = @FileName

FETCH NEXT FROM DataBaseCursor into @DataBaseName
END

CLOSE DataBaseCursor
DEALLOCATE DataBaseCursor

Automate/Schedule the Database Restore

Already in this Post, we have seen how to Automate/Schedule the Database Backup
Today we are going to see how to automate the database restore task.

1. Open the SQL Server Management Studio. Connect to the database server. I have connected to localhost database server.
2. Expand the “Management” Option. Under that You can able to see an option called “Maintenance Plans”
3. Right Click on the Maintenance Plan and Select “New Maintenance Plan”

AR1

4. Enter the Name for the Maintenance Plan. I have given name as “AutoRestore-AdventureWorksDB” and Give Ok.
This will navigate you to the design page of that plan.

AR2

5. Enter Description as “To Restore the Database on a daily basis”
6. For Scheduling this job, Click on the Calendar Like icon on the SubPlan Schedule Column.

AR3

7. This will open the “Job Schedule Properties” window. Select the “Frequency Occurs” as “Daily”. Leave remaining things as it is.
8. If you are restoring a database in your local machine, give the time as “12:00:00 PM” or the time you want.
When the job runs during that time, the machine/server should not be shut down/switched off.

AR4

9. Give Ok. Now Click on the Toolbox tab in the “Design” Page and Double Click “Execute T-SQL Statement Task”

AR5

10. This will create the “Execute T-SQL Statement Task” in the Design page.

AR6

11. Double Click on the “Execute T-SQL Statement Task” in the Design Page and this will open the “Execute T-SQL Statement Task” window.

AR7

12. Enter the following Query in the window and give OK.

RESTORE DATABASE [AdventureWorks2008R2]
FROM  
DISK = N'D:\SQL DB\BACKUP\AdventureWorks2008R2.bak' 
WITH  FILE = 1,  
MOVE N'AdventureWorks2008R2_Data' TO N'D:\SQL DB\DATA\AdventureWorks2008R2_Data.mdf',  
MOVE N'AdventureWorks2008R2_Log' TO N'D:\SQL DB\DATA\AdventureWorks2008R2_Log.LDF',  
NOUNLOAD,  
STATS = 10
GO

13. Save Selected Items. That’s it. You have automated the restore database task.

Optionally you can create one more “Execute T-SQL Statement Task” and you may delete the database using the following query.

USE [master]
GO
ALTER Database [AdventureWorks2008R2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'AdventureWorks2008R2')
DROP DATABASE [AdventureWorks2008R2]
GO

Automate/Schedule the Database Back Up

Today we are going to see how to automate the backing up a database.
If we are frequently making changes in a database on a daily basis, then it is a good practice to backup the database
before making Changes. Instead of doing this manually, we can also automate the database backup task in MS-SQL Server.
It is very easy and very simple.

1. Open the SQL Server Management Studio. Connect to the database server. I have connected to localhost database server.
2. Expand the “Management” Option. Under that You can able to see an option called “Maintenance Plans”
3. Right Click on the Maintenance Plan and Select “New Maintenance Plan”

Image

4. Enter the Name for the Maintenance Plan. I have given name as “AutoBackup-AdventureWorksDB” and Give Ok.
This will navigate you to the design page of that plan.

Image

5. Enter Description as “To Automate the Database Backup on a daily basis”
6. For Scheduling this job, Click on the Calendar Like icon on the SubPlan Schedule Column.

Image

7. This will open the “Job Schedule Properties” window. Select the “Frequency Occurs” as “Daily”. Leave remaining things as it is.

Image

8. If you are backing up a database in your local machine, give the time as “12:00:00 PM”. When the job runs during that time, the machine/server should not be shut down/switched off.
9. Give Ok. Now Click on the Toolbox tab in the “Design” Page and Double Click “Backup Database Task”

Image

10. This will create the “Back Up Database Task” in the Design page.

Image

11. Double Click on the “Back Up Database Task” in the Design Page and this will open the “Back up Database Task” window.

Image

12. If you are backing up the database in local, then leave the connection as “Local Server Connection”
Or Click on the New button and select the Server. Leave Backup type as “FULL”
Select the Database that you want to backup. I have selected “Adventureworks2008R2” database.

Image

 

13. Enter the Directory for the Database backup. I have given as “D:\SQL DB\BACKUP”. Leave remaining settings as it is and give Ok.

Image

14. Click on “Save” or press “Ctrl+S”.

Thats it. Now you have successfully automated the backup of “AdventureWorks” database.
Now this job will run daily on “12:00:00 PM” and it will create a backup [.bak] file in “D:\SQL DB\BACKUP” directory.

 

Cursors in SQL Server and its Usage:

In SQL Server, we can use While loop to iterate using a Variable. But if we want to iterate through the rows of a result set, we can go for Cursors.
So Cursors is a mechanism to explicitly enumerate through the rows of a result set, rather than retrieving it as it is.
Syntax of a Cursor is,

DECLARE [YOUR CURSOR NAME] CURSOR
 FOR
  [USE YOUR SELECT STATEMENT HERE]
 OPEN [YOUR CURSOR NAME]
 FETCH NEXT FROM [CURSOR NAME]
 WHILE (@@Fetch_Status = 0)
  BEGIN
   [Do Manipulation for every Row Here]
  END
CLOSE [YOUR CURSOR NAME]
DEALLOCATE [YOUR CURSOR NAME]

Now we are going to see an example for CURSOR with better usage:
Consider this scenario. I have a 17 tables in my database with similar name %TenantSummary% like
1. AppTenantSummary
2. ClientTenantSummary
3. RowTenantSummary
.
.
.
.
.
17. DataTenantSummary

I want to insert similar set of values in all these 17 tables. For this, We can create individual Insert statements.
But I am going to simplify this by Creating a Cursor that will insert similar set of values in all these 17 tables.

Following is the code that will insert similar set of values in all the TenantSummary tables.

DECLARE @TableName varchar(200)
DECLARE @Query varchar(Max)

DECLARE TenantSummary CURSOR
FOR
 SELECT '[' + schemas.name + '].[' + tables.name + ']' FROM sys.tables
 INNER JOIN sys.schemas ON schemas.schema_id = tables.schema_id WHERE tables.Name LIKE '%TenantSummary'
 OPEN TenantSummary
 FETCH NEXT FROM TenantSummary into @TableName
 WHILE (@@Fetch_Status = 0)
  BEGIN
   SET @Query = 'INSERT INTO ' + @TableName + ' SELECT ID, Name, 1 FROM Master WHERE ID LIKE ''%DTS%'' GROUP BY ID '
   PRINT @Query
   EXEC(@Query)
   FETCH NEXT FROM TenantSummary into @TableName
  END
CLOSE TenantSummary
DEALLOCATE TenantSummary

But always there is a saying that Cursors will cause severe Performance issues in SQL. So limit the usage
of Cursors. But for QA, if we are not bothered about some extra seconds of execution for SQL Queries, We can do magics using Cursors in SQL.

Restore/Create Database using a database backup in SQL Query

Restore/Create SQL Server Database from a DB BackUp using SQL Query:
1. Connect to the database server in sql server management studio. For example localhost. Right Click on the “Databases” and Choose “Restore Database” option. This will open the “Restore Database” window
2. To extract the query required to restore/Create the database, Click the Script button on the top and select “Script Action to New Query Window”

Image

 

3. This will give you the query to restore a database using an existing backup.

Below mentioned is the Query to create new database “TEST” from the database backup in the “D:\SQL DB\BACKUP\AdventureWorks2008R2.bak” path in device.

RESTORE DATABASE 
[TEST] 
FROM DISK = N'D:\SQL DB\BACKUP\AdventureWorks2008R2.bak' 
WITH FILE = 1, 
MOVE N'AdventureWorks2008R2_Data' 
TO N'D:\SQL DB\DATA\TEST.mdf', 
MOVE N'AdventureWorks2008R2_Log' 
TO N'D:\SQL DB\DATA\TEST_1.LDF', 
NOUNLOAD, 
STATS = 10
GO

You may also change the path of the .mdf and .ldf files if you want. By changing the .mdf and .ldf files, will create the database in a different location that you have specified.

Backup Database using SQL Query

In the last post, we have backed up the database using the SQL Server Management Studio.

Now we are going to do the same using SQL Query.

If you want to extract the Query that the SQL Server is using when backing up a database, it is simple.

Right Click on the Database, –> Tasks –> Backup. This will open the Backup database window.

On the top of Back up database window, Click on the Script button and select option, “Script Action to New Query Window”

Image

 

This will give the SQL Query to backup that database in the new query window.

BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = N'D:\SQL DB\BACKUP\AdventureWorks2008R2.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'AdventureWorks2008R2-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

Give the Path and Name for the backup in the query and Run this query. This will take the backup of that database in that path.