Scheduling Backups for SQL Server 2005 Express

SQL Server 2005 Express edition does not have scheduled task and cannot make automatic backups. Here is a simple solution which use windows scheduled tasks, and MS Server SQLCMD file.

1. Create a store procedure to backup a database to folder. File naming is YYYYMMDD_TIME_DatabaseName
which automatically sorts backups by days when you list them in explorer


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[BackupDatabase]
@databaseName sysname,
@folder nvarchar(1024)
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 ' + @databaseName +
' TO DISK = ''' + @folder + @dateTime + '_' + @databaseName + '.BAK'''

EXECUTE sp_executesql @sqlCommand
END

2. Create a sql file which use the store procedure you create in step 1. Save it as you wish, for example c:\backups\backup.sql

BackupDatabase 'master', 'c:\backups\'
GO
BackupDatabase 'ima.bg', 'c:\backups\'
GO
QUIT

3. Use windows scheduled tasks. In wizard step where you have to select program click browse button, and enter
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE

Schedule task every day or as often you want, and before to finish the wizard check "Open advanced properties for this task when i press finish"

4. In Task tab edit Run text box

sqlcmd -S serverName\Instance -E -i c:\backups\backup.sql

That is all story. You just have to update c:\backups\backup.sql if you want to backup more databases

Insert us states in database

INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'AK', 'Alaska')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'AL', 'Alabama')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'AR', 'Arkansas')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'AZ', 'Arizona')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'CA', 'California')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'CO', 'Colorado')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'CT', 'Connecticut')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'DC', 'District of Columbia')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'DE', 'Delaware')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'FL', 'Florida')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'GA', 'Georgia')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'HI', 'Hawaii')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'IA', 'Iowa')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'ID', 'Idaho')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'IL', 'Illinois')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'IN', 'Indiana')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'KS', 'Kansas')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'KY', 'Kentucky')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'LA', 'Louisiana')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'MA', 'Massachusetts')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'MD', 'Maryland')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'ME', 'Maine')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'MI', 'Michigan')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'MN', 'Minnesota')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'MO', 'Missouri')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'MS', 'Mississippi')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'MT', 'Montana')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'NC', 'North Carolina ')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'ND', 'North Dakota')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'NE', 'Nebraska')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'NH', 'New Hampshire')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'NJ', 'New Jersey')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'NM', 'New Mexico')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'NV', 'Nevada')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'NY', 'New York')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'OH', 'Ohio')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'OK', 'Oklahoma')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'OR', 'Oregon')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'PA', 'Pennsylvania')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'RI', 'Rhode Island')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'SC', 'South Carolina')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'SD', 'South Dakota')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'TN', 'Tennessee')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'TX', 'Texas')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'UT', 'Utah')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'VA', 'Virginia')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'VT', 'Vermont')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'WA', 'Washington')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'WI', 'Wisconsin')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'WV', 'West Virginia')
INSERT INTO States (ISOCode, StateAbbr, Name) VALUES('US', 'WY', 'Wyoming')

Cannot add diagram to MSSQL 2005 database

That works perfectly for me:

EXEC sp_dbcmptlevel 'genii2', '90';
go
ALTER AUTHORIZATION ON DATABASE::genii2 TO sa
go
use [genii2]
go
EXECUTE AS USER = N'dbo' REVERT
go

Sql LEFT JOIN "problem". (Does not returns results)

Select x.*, y.one, y.two
From x left join y on x.someid = x.someid

works fine

but

Select x.*, y.one, y.two
From x left join y on x.someid = x.someid
Where y=somevalue

does not work.

The solution is simple. Move where clause in join clause
Select x.*, y.one, y.two
From x left join y on x.someid = x.someid and y=somevalue

MSSQL 2000 Query results in random order

I have a web application displaying jokes. One of the functionalities it supports is showing a random joke.
I need the get the MSSQL server to randomize the jokes for me.

Here is my very simple solution:

SELECT *
FROM joke
ORDER BY NEWID()

Each execution generates different random results.

Define a datatable sub set using a function in MSSQL Server

Lets have a Projects table which contains our projects.
In our queries we want to use sub set of that table data depends on some parameters, UserID for example. If the relation is simple it is easy, just add it in where clause.

We want to use that sub set in like a table

CREATE FUNCTION Func_UserProjects (@UserID int) RETURNS TABLE
as
RETURN ( SELECT Distinct p.*
From Projects p
Join ProjectPositions pp on p.ProjectID = pp.ProjectID
Join UsersOnProjectPositions up on up.ProjectPositionID = pp.ProjectPositionID
Where ((UserID = @UserID )

After definition we can use it in select statements for example

Declare @UserID int
Set @UserID int

Select *
FROM SceneComments sc WITH(NOLOCK)
Inner Join Scenes S WITH(NOLOCK) On S.SceneID = sc.SceneID
Inner Join Episodes E WITH(NOLOCK) On E.EpisodeID = S.EpisodeID
Inner Join Func_UserProjects(@UserID) P on P.ProjectID = E.ProjectID

Microsoft SQL SERVER - @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT

SELECT @@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it. SELECT SCOPE_IDENTITY() It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function. SELECT IDENT_CURRENT(’tablename’) It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.