How can I restore an .sql file?

[draneb]

I am moving hosting companies, switching from a shared hosting environment to a Windows 2008 VPS machine.

I used Microsoft Database Publishing Wizard to get an .sql file of the old database.
I want to execute that on the new server so that I can replicate the database but I can't figure out how.

Both databases are SQL 2005 and both are run on dedicated machines separate from the hosting. I have been using Microsoft Studio Management Studio Express to tinker around with things but I am lost...

The .sql file is about 250MB. I am just wasting days trying to search Google on how to do this. I thought I was ask you extremely intelligent folks how to accomplish this.

Thank you very much!

[sdlong02]

Right click "Databases", choose "Restore Database", source should be "From device:" browse for database file

Directions are for SQL Server Management Studio, but they should be similar enough

[draneb]

Hello,

That seems to only work with .bak files.

I was only able to get a .sql file using SQL Database Publishing Wizard. I do have Microsoft Studio Management Studio Express but the file is too large to copy and paste the contents into the SQL Query window.

Is there a command I can enter to execute the sql script from my hard drive or from the new server?

Thank you.

[jmestep]

You can click on the database in Mgmt studio, then do file-->open and search for the .sql file. It might work, but you also might get a not enough memory error.

[nickc]

The "sqlcmd" (or older "osql") command line utilities are probably your best bet. Use "sqlcmd -?" to get full syntax.
Expect you would use something like "sqlcmd -E -S<ServerName> -d<DatabaseName> -i<NameOfYourBigSQLFile> -o<FileToSaveOutputResult>".

You don't indicate if the input is just data, or data+schema. You'll need to plan around what is in the .sql file and what is in the db you run it against; for instance if the target db is blank (schema included), does the file also contain user/login info, etc. - otherwise you'll have to create and map yourself once executed.

Too bad you couldn't get a bak file. Much simpler.

Cheers,
-Nick