Bernds blog

Probably most people already know – but it still happens often to me: When restoring a SQL database you cannot login even though the database user matches the SQL Server login. This can easily be fixed with the following command:

sp_change_users_login ‘AUTO_FIX’, ‘ [User_Name]’

Here are the T-SQL database scripts I most frequently use when trouble-shooting.

– Empty transaction log (log file)
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

– Restore database from file
restore filelistonly from disk = ‘c:\temp\Name.bak’
restore database [Name] from disk = ‘c:\temp\Name.bak’ with move ‘name_dat’ to ‘c:\Program Files\Microsoft SQL Server\MSSQL\Data\Name.mdf’, move ‘name_log’ to ‘c:\Program [...]

Hvis det er nødvendigt at slette al data i alle tabeller i en SQL Server database kan følgende T-SQL script hjælpe:

– Source: http://vadivel.blogspot.com/2006/07/easiest-fastest-way-to-delete-all.html Set nocount on

Exec sp_MSForEachTable ‘Alter Table ? NoCheck Constraint All’

Exec sp_MSForEachTable ‘ If ObjectProperty(Object_ID(”?”), ”TableHasForeignRef”)=1 [...]

Der findes nogle muligheder for at sende e-mail fra en SQL 2000 server med fordele og ulemper:

SQL e-mail. Det er sql serverens indbyggede e-mail forsendelse, som kræver at en e-mail klient er installeret på serveren. Det vil man ikke have!

Egen stored procedure som benytter cdosys. [...]