Knowledge Base - .NET, C#, SQL Server 2005, PHP and Actionscript 2.0 / 3.0 etc

This makes managing SQL server so much easier - especially when you have a HUGE sql file to run and enterprise manager won’t even open it for you!

SQLCMD/? will give you all the options available.

An example of piping in a file to a defined instance using a a username and password:

SQLCMD -S localhost\SQLSERVER2005 -U username -P password -i sql-file-to-run.sql

easy peasy! Just make sure the user you are logging in as has all of the permissions required to perform all of the actions in the sql file! (watch the console output!)

This is a bit of an odd request but I had to pre-pend a string to a result (a returned ID) so I looked at doing this in the query. It is dead easy - all you have to do is make sure that the return type is correct so you may have to convert:

select ‘40′ + CONVERT(varchar(12), intValueID) as whatIwantTheOutputToBeCalled ..blah rest of query WHERE blah

http://msdn.microsoft.com/en-us/library/ms187928.aspx

This should be an easy procedure but its not.

Something I found really useful was this:

RESTORE HEADERONLY FROM DISK = ‘c:\xxx.bak’
RESTORE FILELISTONLY FROM DISK = ‘c:\xxx.bak’

Basically from the SQL above you can find out the correct names of the xxx_Data and xxx_Log files for the next command:

RESTORE DATABASE newXXXX
FROM DISK = ‘c:\xxx.bak’
WITH REPLACE,
MOVE ‘xxxx_Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xxxx.mdf’
, MOVE ‘xxxx_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xxxxx_log.ldf’

Currently I have the database ‘(Restoring…)’ in SQL Server 2005 Express but I cannot seem to get them out of their ‘Restoring’ process…..

These were useful:

http://www.mssqltips.com/tip.asp?tip=1150

http://doc.ddart.net/mssql/sql70/ra-rz_11.htm
Still got it ‘(Restoring…)’ but this may help someone:

Restore VerifyOnly From Disk=’c:xxxx.bak’

http://msdn.microsoft.com/en-us/library/ms186724.aspx

Its really easy to do - best to do all of this on the database - that’s what it is for!

This finds stuff that is from 6 months ago until today:

WHERE yourDate BETWEEN DATEADD(MONTH, -6, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP

restore database [DATABASE_NAME] from disk = ‘C:\wherever\your_backup.bak’ with replace

http://weblogs.asp.net/plip/archive/2006/10/26/SQL-Server-2005-Backups.aspx

Running a store procedure in query analyzer:
declare @success int
exec sptriggerexport @retval = @success out
print @success

realy easy:

select * from forum.dbo.tblauthor

select * from [database-name].[owner].[table]

Also useful to point out that if you are selecting from a database with - or other characters in the name then just use [:

select * from [rails-07-2].dbo.tblauthor

EASY!

truncate table jibble;

dbcc checkident(jibble, reseed, 0);

Also useful:

http://ryanfarley.com/blog/archive/2004/12/19/1313.aspx

After lots of uploading and playing around - finally I have my Windows Virtual server running. I was told that it was going to be delivered with MS SQL Server 2005 but it did not. It came with 2000 - great news BUT I had been developing with 2005 - as I thought that would be what was going to be there.

Anyway to cut a long story short - you cannot attach or restore a SQL Server 2005 database to a 2000 one. To find out the version you are running try this SQL:

SELECT @@VERSION

This will return the current version.

I then tried to retore to the database name I wanted. This also failed - GREAT.

Remember a little setting in the options section when restoring:

“Force Restore over existing database” - thank God for Google!

These were useful:

http://www.developersdex.com/sql/message.asp?p=580&r=5100689

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=138591&SiteID=1