How to copy a SQL Server 2005 database from local

问题:

I want to export database from local computer to database server in SQL Server 2005.

How can I restore database without having access to file system of database server

回答1:

You can use SQL Server's built-in backup and restore functions.
Make a backup of your local database, and write it on some network share (can be anywhere in the network, doesn't have to be on the database server):

BACKUP DATABASE MyDatabase TO DISK = '\\Servername\Share\MyBackup.bak'

Then, you can restore the database on the database server, reading the backup directly from the network share:

RESTORE DATABASE MyDatabase FROM DISK = '\\Servername\Share\MyBackup.bak'

Note that you have to everything in T-SQL to be able to read and write directly to network shares!
(You can also backup and restore databases from the GUI in SQL Server Management Studio, but you can only use local folders there)

回答2:

Your situation is, you want to copy the database from your local to remote database server but you do have access to the remote machine thats why you cannot use a restore or attach database function.

Solution: For me the best thing to do is generate a script that will include the schema and the data of the database. This will serve as a script backup of your local database which can be run to any sql servers that you wanted. Sql Server 2008 already have the interface for this but for Sql Server 2005 you will need to have Sql Publishing Wizard.

You can download it from here: (remove spaces)

http: //www.codeplex.com/sqlhost/Wiki /View.aspx?title=Database%20Publishing%20Wizard

To create a script you can do this:

Command to run which will create schema and database: C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql”

Command to run which will create schema: C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql” -schemaonly

Command to run which will create data: C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql” -dataonly

For more details please read...(Remove Spaces)

http: //blog.sqlauthority.com/2007/11/16/ sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

This worked for me a lot of times.

回答3:

You will need access to the file system of the database server. You have to detach the database from your local server and then copy the related .mdf and .ldf files to the target database server and then attach the files in the SQL server management studio of that database server.