Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to move a database from our production server to our test server. I've used Microsoft's Database Publishing Wizard to create a script containing the schema and data for the production database.

However I can't figure out how to load this script into our test database.The resulting script is about 500mb, which isn't 'large' by database standards but large enough that I can't copy and paste it into the query analyzer. Alternatively, when I try and open the file directly with the SQL Server Management Studio it complains

The operation could not be completed. Not enough storage is available for this operation.

This error happens before I select a database, so I believe it is the Management Studio complaining about not having enough memory to load a 500mb file.

Any advice?

share|improve this question
add comment

3 Answers 3

up vote 7 down vote accepted

If you're only trying to copy it into the query analyzer to run it, maybe you could just create a batch file using OSQL to run it from the command prompt.

Here's the MSDN for OSQL: http://msdn.microsoft.com/en-us/library/aa213090.aspx

share|improve this answer
2  
I'd vote you up but it requires 15 rep, but this does seem to be working, thank you. –  James McMahon Jun 19 '09 at 13:58
    
No problem. Glad it's working. –  Sean Howat Jun 19 '09 at 14:18
2  
The poster doesn't mention the version of SQL Server. On later versions I would use sqlcmd, this is functionally equivalent to osql though. –  Joel Mansford Jun 20 '09 at 9:02
    
@Joel, The remote server is SQL server 2000, but locally I have the 2005 tools. –  James McMahon Jun 23 '09 at 17:50
add comment

You can always take your database offline and detach it. Then you can just copy the physical files over to the other server and attach it. Easy way.

share|improve this answer
add comment

In the future you may want to just use the backup and restore option to move from one server to another.

share|improve this answer
    
Yeah I don't have admin privileges on the production machine, but I have enough privileges to generate a creation script. –  James McMahon Jun 19 '09 at 14:31
add comment

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.