change of lic Ent to Std - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

change of lic Ent to Std

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2007-01-03 : 22:43:25

It seems as though our last administrator has installed Enterprise Licenses (SQL 2005) We need these to be Standard, can I down grade some how with minimal to no disruption to our databases?

If so How, if not (which i almost positive that you are going to say NO) then i assume I am going to have to reinstall the SQL server. What is the best way going about this with out having to restore all of the databases? Can i copy all of the MDF and LDF files else where and then once the new installation has completed create the users, then databases and then replace the MDF and LDF?

Is there a better way then this if so it would be a huge help.

Thanks

Brad

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-03 : 23:29:10

i think not, but u can script all the backups and restores. put output to text (not grid) and execute these scripts. then copy/paste results for executing.

backup all user dbs and msdb


SELECT	'Backup Database ' + name + '
TO Disk = ''f:\backups\' + name + '.bak' + char(13) + 'GO' + char(13)
FROM	sys.databases
WHERE	database_id >  3

Restore dbs -- this will check for correct physical file names, and uses with move option in case you have different drives/directories on target than source. Again, this does nothing other than print the scripts. Copy into SSMS to execute


use master
go
Create Table #t (
	 dbid int,
	 name varchar(64),
	 datafile varchar(64),
	 datapages int,
	 logfile varchar(64),
	 logpages int
)

Declare c Cursor
Read_Only
For
	SELECT dbid, name
	FROM sysdatabases
	where dbid > 3

Declare @dbid int
Declare @name varchar(60)

Open c
Fetch Next From c into @dbid, @name
While (@@fetch_status <> -1)
Begin
  IF (@@fetch_status <> -2)
  Begin
   exec('
   INSERT #t (dbid, name, datafile, datapages)
   SELECT ' + @dbid + ', ''' + @name + ''', 
     Reverse(left(ltrim(reverse(filename)), charindex(''\'', ltrim(reverse(filename)))-1)),
     size
   from [' + @name + ']..sysfiles
   WHERE fileid = 1
   ')

   exec ('
   UPDATE #t
   Set logfile = (select Reverse(left(ltrim(reverse(filename)), charindex(''\'', ltrim(reverse(filename)))-1)) from [' + @name + ']..sysfiles where fileid = 2),
    logpages = (select size from [' + @name + ']..sysfiles where fileid = 2) 
   where dbid = ' + @dbid
   )
  End
  Fetch Next From c into @dbid, @name
End

close c
deallocate c

select 'EXEC sp_addumpdevice ''disk'', ''rdvc_1'', ''F:\bkp\' + name + '.BAK''' + char(10) + 'GO' + char(10) +
		'RESTORE DATABASE '+ name + char(10) +
		'FROM rdvc_1' + char(10) +
		'with' + char(10) + 
		'     Move ''' + left(datafile, len(datafile)-4) + ''' TO ''F:\MSSQL\' + left(datafile, len(datafile)-4) + '.MDF'',' + char(10) +
		'     Move ''' + left(logfile, len(logfile)-4) + ''' TO ''E:\MSSQL\' + left(logfile, len(logfile)-4) + '.LDF''' + char(10) +
		'GO' + char(10) +
		'sp_dropdevice ''rdvc_1''' + char(10) + 'GO' + char(10) + char(13)
from #t


--select * from #t
drop table #t

this assumes, of course, only one mdf and ldf per database.

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 02:18:54

Assuming your databases are reasonably sizable (> 1GB) then it will be quicker to ATTACH the databases, rather than to restore them. Make sure you have a backup anyway, just in case!

"Can i copy all of the MDF and LDF files else where"

The MDF / LDF files will already be in the correct folders, so nothing crafty required there. The reinstall won't delete the physical user-database files

You WILL need to script all the Logins, plus any user-defined objects in the MASTER database (hopefully you haven't got any!), and you will lose everything in MSDB - but I can't see any reason why you can't just restore MSDB, which will fix that.

Kristen

2revup
Posting Yak Master

112 Posts

Posted - 2007-01-08 : 01:12:39

thanks guys... I wish MS would provided this feature would have saved me a bundle of time. But then again who down grades...

I think the attach Database might be the quest way. I dont have any user defined objects so i am rather safe there as you say the users will be the biggest issue. But that i can script.

Thanks again.
BTW russell thanks for that i have saved that one for a rainy day.

Kristen
Test

22859 Posts

Posted - 2007-01-08 : 02:47:26

Make sure you have backups before you start!!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources