So, lately I had the dilemma of having done a bunch of preliminary SQL work in 2008 Express, then needing to port my results over to a staging SQL 2005 server at work. I casually backed up the 2008 database and lo and behold, couldn’t restore it! SSIS wasn’t set up, and I had to gets stuff done ASAP! I searched on Google, and all I got were either “use SSIS” or “it can’t be done.” In other words, those who’ve done this before haven’t shared with the rest of us.
So a proc I wrote came in handy…
I threw together this stored procedure, linked to the remote 2005 server, and used this proc to move all my data. It’s very simple and WYSIWYG-ish right now, as it:
- doesn’t copy over indexes
- doesn’t copy over table schemas
- just copies the tables into a new database with the same table names and data
A Quick Sample Run-Through
So, first let’s create a database named SimpleSample. We’ll create two tables, one with prime numbers and one with the beginning of the Fibonacci sequence.
For our primes:
set@counter = 1
while (@counter< 1000)
set@counter = 2
Now for Fibonacci:
Now that we have two sample tables we’re going to copy, create a second database named SimpleSample2. Our routine will be simple: get a list of the names of our source tables, and for each name in the list, SELECT from the table with that name in our source database INTO a new table with that same name in our destination database.
There are many ways to get the list of tables. I ended up using the sys.sp_tables proc:
set@sourceDatabase = 'SimpleSample'
set@spTables = 'exec '+ @sourceDatabase +'.sys.sp_tables'
select* from @temp
After that, I cleaned out the data to just dbo created tables:
whereTABLE_TYPE <> 'TABLE'
select TABLE_NAME from @temp
Finally, throw those tables into the destination database:
The SQL sources are here. I’m sure it can be spruced up to do many more great things, such as those I initially listed. If you decide to add anything, please post in the comments or try to get your additions / changes to me and I’ll update the SQL. You’ll even have the extra bonus of being credited on this immensely popular blog!