Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

3 Answers 3

up vote 4 down vote accepted

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
share|improve this answer
    
If the databases are under 4gb you should also be able to use the express edition –  Kevin Jan 10 at 18:05
    
Server is a Production Server. Just now i noticed, we have express edition. All database are less than 5 GB –  goofyui Jan 10 at 19:16
1  
backup restore is the way to go. I have rarely seen Production server running sql express. –  Kin Jan 10 at 19:21
    
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) –  goofyui Jan 10 at 19:25
2  
You should better know what you are working on.. as you said it is express edition and now it has changed ! –  Kin Jan 10 at 19:48

If your 12 databases are quite small - less then 10Gb I think is the current limit - you could use the free MSSQL 2012 server database and copy the databases into your own desktop. If they are larger then 10 Gb then it is possible to copy the structures over to the free instance and then populate with test data as required.

https://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx

share|improve this answer
    
Yes true. But i am using Windows XP. 2012 will not run on my Desktop –  goofyui Jan 10 at 19:16
    
There are 2008 versions around, I think that the limit is lower but might still work. I ran 2008 on my XP, though I haven't tried 2012. I think this might help. microsoft.com/en-gb/download/details.aspx?id=22973 –  EJW Jan 29 at 11:18

Assuming your copy of SQL Server is properly licensed for the physical machine, you can run multiple instances of SQL Server. You can crreate a "test" instance, do a full backup of you production databases and then restore them to the new instance. If you're worried about resource contention, you can configure settings ont the new instance like max memory and maxdop.

share|improve this answer
    
This is a Production Server. I am not sure,whether i am allowed to create another instance –  goofyui Jan 10 at 19:17
    
i think we will not be able to install the same version of sql copy in a system. Might have to go for SQLExpress or another version!! –  goofyui Jan 10 at 19:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.