Sql-server – Creating a copy of primary database and attaching the copy database in the same sql instance

sql serversql-server-2012

I just got into a project in which we have only one SQL Server 2012 copy. We have 12 databases attached to the primary instance of SQL Server 2012.

As I don't have SQL Server 2012 or supporting OS, I have to depend on the primary instance of SQL Server 2012.

I am not sure whether it is good to create copy/clone of all 12 databases and attach it to same instance and work on the copy for data manipulation and query testing purpose.

Based on Kin's query

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + ' 
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation

Following is the output. I ignored PhysicalMachineName and SQLServerName.

ServerEdition : Business Intelligence Edition (64-bit)
ProductVersion : SQL Server 2012 + RTM + (Build11.0.2100.60)
O.S.           : Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 
Collation      : SQL_Latin1_General_CP1_CI_AS

Best Answer

IF the server is NOT a PRODUCTION server, then you can backup the databases you need for Data manipulation and restore them with databaseName_test.

Keep in mind that you will still be competing for resources on the server by creating another set of databases.

Why cant you install another instance of sql server ? Even the enterprise edition is free for 30 days (for evaluation purpose ONLY) or just buy a Developer edition which is much cheaper. If you have MSDN subscription, you will be able to get one. Windows Azure is also another option.

Below will give you the correct version and some more info :

     SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + ' 
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation

Related Solutions

Sql-server – blocking ‘SELECT… WITH(NOLOCK)’

If the rebuild is not specified as ONLINE (which you can't do except on Enterprise Edition), the SELECT is blocked because of LCK_M_SCH_S, and if you execute sp_lock you will see it is an exclusive lock. Even NOLOCK can't penetrate that. You can simulate this by:

In one window, start a transaction which rebuilds offline (this is so that you can investigate beyond the rebuild instead of trying to force a really long rebuild). Make note of the spid:
```
USE AdventureWorks2012;
SELECT @@SPID;
BEGIN TRANSACTION;
ALTER TABLE Sales.SalesOrderHeader REBUILD WITH (ONLINE = OFF);
```
In a second window, execute your NOLOCK query (again, make note of the spid):
```
SELECT @@SPID;
SELECT * FROM Sales.SalesOrderHeader WITH (NOLOCK);
```

In a third window, check:

SELECT session_id, blocking_session_id, last_wait_type 
  FROM sys.dm_exec_requests 
  WHERE blocking_session_id IN (two spids above)
     OR session_id IN (two spids above);

EXEC sp_lock;

Scan the second resultset for all of the rows involved with the spid that is the main blocker.

Don't forget to rollback or commit...

Sql-server – Differentiate SQL Server Express from SQL Server installed in a server

The MSSQLSERVER name in the services does not mean anything other than SQL Server (any edition) was installed as the default (unnamed) instance. It does NOT give you any information as to what version or edition it is! This can be SQL Server Express!

You need to connect to that database instance and run

SELECT @@VERSION

to find out what exact version/edition it is.

Having the two SQL Server service entries just means you have two instances - which edition each instance is cannot be determined from their names alone. OK - SQLEXPRESS is the default name used for SQL Server Express - but you could install a Standard or Enterprise edition with that instance name - just for fun.

Best Answer

Related Solutions

Sql-server – blocking ‘SELECT… WITH(NOLOCK)’

Sql-server – Differentiate SQL Server Express from SQL Server installed in a server

Related Question