Archive

Archive for the ‘Database’ Category

How to find Function, Stored Proc, Table, View Details

May 27, 2010 Leave a comment

In Microsoft SQL Enterprise Manager

Open The query Tab for the DB

Write the object name (just name without DBO etc)

Select the object name and press Alt + F1

Will list all information related to that object
i.e. For function

Will list
1) Creation /owner etc details
2) Output details with type etc
3) Input needed by that function

Categories: Database, MSSQL

MS SQL: Backup database Stored Procedure

April 21, 2009 Leave a comment

Here is a stored procedure to create a full backup of the database.
Run the code on the master database.
To backup a db run:
sp_backupdatabase ‘databasename’
To verify the backup we need to use use CHECKSUM.

USE “master”
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE “dbo”.”sp_backupdatabase” (
@DBName nvarchar(50)
)
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),’/’,”) + REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:’,”)
SET @sqlCommand = ‘BACKUP DATABASE ‘ + @DBName + ‘ TO DISK = ”D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\’ + @DBName + ‘_backup_’ + @dateTime + ‘.BAK” WITH CHECKSUM’
EXECUTE sp_executesql @sqlCommand
END

Categories: Database, MSSQL