3

Hi guys got a problem with a database.

I've been asked to perform some indexing and delete queries on a live database, so first of I wanted to get the database to our dev server so I can test out some things before I commit to live.

Unfortunately the dev is SQL Server 2008 and the live is SQL Server 2012. So I've had to try and downgrade the database.

Database is a lovely 33gig. and I've started to use the generate script wizard to create a 2008 version.

Problem is it gets so far then errors on random tables. So far I have no idea what to do to solve the issue. In the advanced settings of the wizard I select schema and data (yup going to be huge)

Error is due to my pc running out of disk space. I have a problem, how do I recreate this DB in 2008 that's both fast and easy? generating the script like that hasn't worked.

Open to ideas guys!

marc_s
  • 8,932
  • 6
  • 45
  • 51
lemunk
  • 143
  • 1
  • 6
  • 6
    Can you actually install 2012 version - that would be the easiest way? –  Jan 15 '14 at 14:58
  • yeh ive been asking my lead to install it on our dev, but he said he wants to do a clean up 1st if were going to do that, but this needs sorting out by friday and so this needs to be done before it..... :( –  Jan 15 '14 at 15:00
  • 4
    Gotta agree... I've done downgrades from 2008 to 2005 and it was an absolute nightmare. –  Jan 15 '14 at 15:00
  • 1
    Why do you have a development system based on SQL 2008 when you have 2012 in production? That's not wise at all. – Marian Jan 15 '14 at 15:02
  • Try watching Pinal Dave's tutorial on this, it might be of some help? http://www.youtube.com/watch?v=lSh3fq-MikE –  Jan 15 '14 at 15:03
  • @FreshPrinceOfSO but what you say is valid i may post on there –  Jan 15 '14 at 15:03
  • @Marian the live server is brand spanking new, literally a month old, dev and local system are still using 2008, so theres migration happening, as you could guess it gets hairy lol –  Jan 15 '14 at 15:04
  • 1
    Your lead is catastrophically wrong. There will be far fewer problems if you upgrade dev and it will take less time. – HLGEM Jan 15 '14 at 15:12
  • agree, on a side note, this could be seen as a dupe, but the accepted answer on the link provided does not work, the sql wizard is the problem, im looking for an alternative such as "upgrade dev server" etc – lemunk Jan 15 '14 at 15:13
  • 2
    @StevenSmith a duplicate is not useful only due to the accepted answer (this is the whole reason we keep other answers around on all questions, even when one has been accepted - the accepted answer is the most useful to the asker, but the other answers might be more useful to other readers). Did you look at all of the answers? – Aaron Bertrand Jan 15 '14 at 15:23
  • good point, however i thought as this is a 2012 downgrade that those answers may not be 100% applicable, silly of me to assume. cheers Aaron, thanks for looking into my multiple posts too – lemunk Jan 15 '14 at 15:25
  • 2
    The issue is the same whether you are trying to go from 2012 -> 2008 or 2008 -> 2005 or 2014 -> 2008 R2. The database files are simply not backward compatible, and you need to use one of the other suggested approaches. I don't know of any alternate solutions that are only viable on one specific downgrade path. And still, I agree with @HLGEM that you need to push harder on just getting them to upgrade. Will be so much easier than what you're trying to do now. – Aaron Bertrand Jan 15 '14 at 15:27
  • yeh i agree, too much headaches involved, much quicker implementation to simply upgrade dev server. But when you have you hands tied you need the strange and the generally avoided workarounds. thanks for the post – lemunk Jan 15 '14 at 15:30
  • @StevenSmith, you have to tell them the risks and the costs in time and money. They upgrade path is far less risky and the workaround is likely to take weeks or months. – HLGEM Jan 15 '14 at 16:00
  • 1
    @StevenSmith Have a look at How to move a database from SQL Server 2012 to SQL Server 2005. You will get all the necessary steps with script as the concept is same. – Kin Shah Jan 15 '14 at 20:11

1 Answers1

2

One option is third party tools: Red Gate's SQL Compare and SQL Data compare are very good for this sort of thing, and what we use internally. They will let you handle the data table by table, so that might help keep size down. Not sure if they any upper limits on size.

Jamie F
  • 313
  • 2
  • 7