Thursday, August 6, 2009

How to copy a MS SQL database from one server to another without server admin rights on source machine

Step 1: Generate a SQL script including data by using SQL Server Database Publishing Wizard:
The wizard is integrated in Visual Studio Server Manager. Alternate download for older versions: http://www.microsoft.com/Downloads/details.aspx?displaylang=en&FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a

Step 2: Use SQLCMD.exe to execute the file.
Normally you can't use the query analyzer because it would throw an exception of type System.OutOfMemoryException:

sqlcmd -S servername -d TargetDBName -i C:\SQLFileGeneratedByDBPubWizard.sql -o C:\DBImportLog.txt

Have a look at the log file to see what's going on.

If you can not execute sqlcmd on the target machine you might for example want to write a command line tool that executes the sql commands in smaller blocks.

No comments: