[posted and mailed, please reply in news]
raj (jkamaraj@hotmail.com) writes:
[quoted text, click to view] > In the way of resolving my customer's problem, I am trying to get his
> complete sql server database so that I can debug a problem.
>
> I tried the following way:
>
> 1. Received the table and stored procedure scripts and data in text
> file
> 2. Create the table,sp and imported the data using the wizard.
>
> However, the stored proceudre return 1 row instead of 234500 rows.
> Obviously I am missing some other functions,sp(?) from his database.
> What is the best way
> to export his entire database and the best way to import the exported
> database in my machine.
The difference could be due to difference in settings or whatever.
If you want to transfer the entire database, this is probably the simplest
way. On the customer machine:
BACKUP DATABASE db TO DISK='C:\temp\database.tmp'
Then comes the tricky part, get this file to your machine. For a small
database that fits on a DVD, no sweat, but if the backup is some 30 GB
it may not be equally practical.
Once you have brought home the bacon, do:
RESTORE DATABASE db FROM DISK='C:\temp\database.tmp' WITH
MOVE 'db_file' TO 'E:\mssql\database.mdf,
MOVE 'db_log' TO 'E:\mssql\database.ldf',
REPLACE
All file locations are up your own choice. The names db_file and db_log
stands for the logical names within the database for the files. You
can find these by doing sp_helpdb on the database, or using
"RESTORE FILELISTONLYL db" on the dump file. Note that a database can
have more than two files.
If moving the entire dump is not practical, then I would create scripts
for the database (unless I have them at home under version control) and
bulk out all tables. While it is not likely that you can fit a dump into
a ZIP archive, there are better changes that you can do this with the
individual bulk files.
As for the Import/Export wizard, I have never run it, so I cannot give
any tips which includes it.
--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se
Books Online for SQL Server SP3 at
