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

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