Recover Production Box

1 - Copy the master.mdf & .ldf files from the corresponding dev/test server. It should then be at the same SP level and collation as the production box. This will allow SQL to start up successfully, but other databases and logins will be missing and probably marked suspect.
2 - Restore master from the backup. This will put the logins back and you will definitely be back at the correct SP level and have the info about what databases were on the box. In addition, the admin procs will be there for you if there are any.
3 - Restore the LiteSpeed database to get the LiteSpeed backup history and any procs needed to perform further restores if you use Litespeed for backup/restore.
4 - Restore msdb, model, admin db's. The SQL agent should now be able to start, the jobs should all be there, the jobs will run successfully.
5 - Restore the remainder of the databases and take backups.

how to find object in SQL server !

-- Part 1
Declare @sqlstr nvarchar(200)

-- Part 2
/* drop the temporary table if already exists */
If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table #tblDBObjects
/* create temporary table */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)

-- Part 3
/*assign string value to variable */
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''
/* execute SQL string */
Exec sp_executesql @sqlstr

-- Part 4
/* select from temp table */
Select * From #tblDBObjects Where objname like 'PORTFOLIO_GROUP%'
RETURN

How to identify your SQL Server version and edition

To determine which version of Microsoft SQL Server 2005 is running, connect to SQL Server 2005 by using SQL Server Management Studio, and then run the following Transact-SQL statement:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The results are:
• The product version (for example, "9.00.1399.06").
• The product level (for example, "RTM").
• The edition (for example, "Enterprise Edition").

For example, the result looks similar to:9.00.1399.06 RTM Enterprise Edition
The following table lists the Sqlservr.exe version number: Release Sqlservr.exe
RTM 2005.90.1399
SQL Server 2005 Service Pack 1 2005.90.2047

How to determine which version of SQL Server 2000 is running

To determine which version of SQL Server 2000 is running, connect to SQL Server 2000 by using Query Analyzer, and then run the following code:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The results are:

• The product version (for example, 8.00.534).
• The product level (for example, "RTM" or "SP2").
• The edition (for example, "Standard Edition"). For example, the result looks similar to:

8.00.534 RTM Standard Edition
The following table lists the Sqlservr.exe version number:
Release Sqlservr.exe RTM 2000.80.194.0
SQL Server 2000 SP1 2000.80.384.0
SQL Server 2000 SP2 2000.80.534.0
SQL Server 2000 SP3 2000.80.760.0
SQL Server 2000 SP3a 2000.80.760.0
SQL Server 2000 SP4 2000.8.00.2039


How to determine which version of SQL Server 7.0 is running
To determine which version of SQL Server 7.0 is running, connect to SQL Server 7.0 by using Query Analyzer, and then run the following code:

SELECT @@VERSION

The results look similar to the following:Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Desktop Edition on Windows NT 5.1 (Build 2600: )
Note In this example, the version number is 7.00.623.

Use the version number in the following table to identify the product or service pack level:
Version Number Service Pack
7.00.1063 SQL Server 7.0 Service Pack 4 (SP4)
7.00.961 SQL Server 7.0 Service Pack 3 (SP3)
7.00.842 SQL Server 7.0 Service Pack 2 (SP2)
7.00.699 SQL Server 7.0 Service Pack 1 (SP1)
7.00.623 SQL Server 7.0 RTM (Release To Manufacturing)
If the version number that is reported by @@VERSION is not listed in this table, SQL Server is running with a hotfix or a security update build. For example, if @@VERSION reports a version number of 7.00.859, you are running SQL Server 7.0 SP2 with a hotfix installed. The version number increases with each new version of the Sqlservr.exe executable file. See to the Readme.txt file for your hotfix or security update for more information.


How to determine which version of SQL Server 6.5 is running
To determine which version of Microsoft SQL Server 6.5 is running, connect to SQL Server 6.5 by using Isql_w, and then run the following code:SELECT @@VERSION
Use the version number in the following table to identify the product or service pack level:Version Number Service Pack
6.50.479 SQL Server 6.5 Service Pack 5a (SP5a) Update
6.50.416 SQL Server 6.5 Service Pack 5a (SP5a)
6.50.415 SQL Server 6.5 Service Pack 5 (SP5)
6.50.281 SQL Server 6.5 Service Pack 4 (SP4)
6.50.258 SQL Server 6.5 Service Pack 3 (SP3)
6.50.240 SQL Server 6.5 Service Pack 2 (SP2)
6.50.213 SQL Server 6.5 Service Pack 1 (SP1)
6.50.201 SQL Server 6.5 RTM

If the version number that is reported by @@VERSION is not listed in this table, SQL Server is running with a hotfix or a security update build. The version number increases with each new version of the Sqlservr.exe executable file. See to the Readme.txt file for your hotfix or security update for more information.

How to determine which edition of SQL Server is running
If you are not sure about what edition of SQL Server you are running, the last line of output that is returned by @@VERSION reports the edition to which you have connected. The example that is used in this article is the Standard Edition of SQL Server 2000 on Windows NT 5.0 (Build 2195: Service Pack 2).

Note The build and service pack information provided earlier is for the operating system, not for SQL Server.Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

more info @ microsoft

How to insert data in table refrenced by Foreign Key Constraints!!

Problem: Table in question is referenced by Foreign Key constraint by another tables.
Workaround: Drop the foregin key constraints from the tables and insert the data and then recreate the constraint.Solution: Following steps to be followed:
1. Create the table structure script of all the tables from which Foreign Key constraint to be dropped.
2. Drop the foreign Key constraint from all the tables which refers to the questioned table.
3. Copy data to the table without any changes to the structure.
4. Create the Foreign key Constraint back to the tables used in step 1.
Script to alter the Foreign Key Constraint:
1. To drop the constraint
ALTER TABLE [dbo].[Table_Name]DROP CONSTRAINT [Constraint_Name]
2.To create back the constraint
ALTER TABLE [dbo].[Table_Name1] ADD CONSTRAINT [Constraint_Name] FOREIGN KEY ( [Field_Name1] ) REFERENCES [dbo].[Table_Name2] ( [Field_Name2] )GO


Any other Ideas...keep posting

How to archive data from my production database?

How to archive data from my production database?

Is there a tool or command built-into SQL Server for this?I often see this type of questions in the SQL Server newsgroups and mailing lists. So, decided to come up with a simple article, that's just good enough to get you started.Archiving is the process of getting rid of old data (or data that is not valid anymore) from the main production databases. There is no built-in command or tool for archiving databases. In this article I'm going to show you how to implement this process.Why archive in the first place? By archiving old data, you can keep your databases smaller and more manageable. With smaller tables, your index rebuild times and backup/restore times will be under control. You will also see performance gains on certain queries that either scan a larger portion of the table or clustered index.Depending on the business needs, one might choose to either delete the unwanted data, or copy the data to a history table and delete that data from the production database.Implementing an archival process is not that complicated. The three logical steps involved in this process are:
Identifying the data to be archived.
Optionally copying the identified data to a history table.
Deleting the identified data from the production database.
You may not always be archiving just a single table. You need to understand how different tables in the database are related to each other through foreign key relationships and then, sequence your delete/archive process, so that you are not violating any of the constraints.
Here's an example scenario of archiving all the orders taken more than 6 months ago, from the Orders and OrderDetails tables. The following stored procedure will copy orders older than 6 months into similar tables of a different database called Archive, and delete those rows from the main database: CREATE PROC dbo.ArchiveData( @CutOffDate datetime = NULL)ASBEGIN SET NOCOUNT ON IF @CutOffDate IS NULL BEGIN SET @CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP) END ELSE BEGIN IF @CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) BEGIN RAISERROR ('Cannot delete orders from last three months', 16, 1) RETURN -1 END END BEGIN TRAN INSERT INTO Archive.dbo.Orders SELECT * FROM dbo.Orders WHERE OrderDate < @CutOffDate IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16, 1) RETURN -1 END INSERT INTO Archive.dbo.OrderDetails SELECT * FROM dbo.OrderDetails WHERE OrderID IN ( SELECT OrderID FROM dbo.Orders WHERE OrderDate < @CutOffDate ) IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error occured while copying data to Archive.dbo.OrderDetails', 16, 1) RETURN -1 END DELETE dbo.OrderDetails WHERE OrderID IN ( SELECT OrderID FROM dbo.Orders WHERE OrderDate < @CutOffDate ) IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16, 1) RETURN -1 END DELETE dbo.Orders WHERE OrderDate < @CutOffDate IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1) RETURN -1 END IF @@TRANCOUNT > 0 BEGIN COMMIT TRAN RETURN 0 END END
You just need to call this stored procedure without any parameters, to get rid of all orders older than 6 months. As you can see, you can pass an optional date parameter, to customize the archiving process. Care is taken not to delete orders placed in the last three months. Depending on your requirements, you can schedule this stored procedure as a job, using SQL Server Agent, so that it runs every week or fortnight.This type of jobs tend to block the table while they are running. So, it's better to schedule these jobs for off-peak hours. Also, keep an eye on the transaction log space utilization. In case of any issues with log space, consider archiving in smaller batches. Here's one thing you should consider: If you are using transactional replication to maintain a reporting database, data will be deleted from the reporting databses also, once you run the archival stored procedure. If that's not what you wanted (that is, maintain complete data in the reporting database), then you might want to consider publishing stored procedures, instead of tables, and customize the archiving stored procedure on the subscriber, so that it does nothing.