0

I'm not much into MSSQL, but I need to do a full backup of a remote MSSQL-2005 Database. I have a computer with Visual Studio 2013 Prof. installed. I can use the Server-Explorer of Visual Studio to look into the database, but there seem to be no way to backup it there.

I found C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2\SqlPubWiz.exe installed on my computer. But starting it with doubleclick, it just crashes.

I tried starting it via console with:

"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2\sqlpubwiz.exe" script -d mydatabase -S myserver -U myuser -P mypass g:\mybackup.sql

but it just says:

Microsoft (R) SQL Server Datenbankveröffentlichungs-Assistent
Version (10.0.0.0)
Copyright (c) Microsoft Corporation. Alle Rechte vorbehalten.

Done.

Without outputting anything to g:\mybackup.sql

Any ideas?

Werner
  • 1,695
  • 3
  • 21
  • 42
  • Do you mean database backup (which can be created only on SQL server, not on your local PC) or some script, able to recreate your database (which can be created anywhere)? – Arvo Mar 15 '16 at 11:12
  • We are closing the service down, but there is a small chance we might need to look again at the data in the future (for example for legal reasons). So it would be nice to have a txt-file with inserts and creates (just to browse the file), but I guess setting up a local mssql-server and restore the data (just in case it's needed) might be possible as well?! – Werner Mar 15 '16 at 11:25

1 Answers1

0

You should install SQL Server Management Studio (There is an express edition which is free). If you connect to the sql instance you can right-click the database go to "All tasks" and there is an option to backup the entire database (Schema and data).

Jamiec
  • 133,658
  • 13
  • 134
  • 193
  • Thanks, this sounds prominsing. I downloaded: https://www.microsoft.com/en-US/download/details.aspx?id=8961 But while installing it says: An error occured, error code 29506. – Werner Mar 15 '16 at 11:37
  • Okay, it worked with this download: https://www.microsoft.com/en-us/download/details.aspx?id=7011 But it seems I can only backup it on the server, not download the backup. Is that correct? I need to download the backup, because the MSSQL-Server is not hosted by us and I have no file-access on it. – Werner Mar 15 '16 at 11:44
  • You cant ever backup a sql database to a machine not on the same network. You could try exporting the data (using the same right-click menu in SSMS) – Jamiec Mar 15 '16 at 11:58
  • Thanks again for your reply. I'm sorry if I've used the wrong terminology. Yes, I want to export the data from the remote database to my local computer. When right-clicking on the database, I get: New Database | New Query | Script database as | Tasks | Reports | Rename | Delete | Refresh | Properties. When clicking "Tasks" I get Detach | Shrink | Back up | Restore | Generate Script. But no option "All tasks" or "export". – Werner Mar 15 '16 at 12:04
  • I tried: Task -> Generate Scripts -> Selected all object types, all assemblies, all stored procedures, all tables, all users, all views -> Script to file. This seemed promising, but it broke with an error: "Either the object or one of its properties is not supported on the target server version. (Microsoft.SqlServer.Express.Smo)" Clicking on the link "for help, click" brings me to a webpage only with advertising for MS-Office... Well. – Werner Mar 15 '16 at 12:09
  • Is there no option for "Export data..." under "Tasks"? As an alternative, speak to the hosting provider who should be able to back up the data (or the database itself) and send it to you. – Jamiec Mar 15 '16 at 12:10
  • "Generate scripts" will not take the data, only the structure of the database. – Jamiec Mar 15 '16 at 12:11
  • Thanks again. I thought "Generate scripts" would be like using sqlpubwiz.exe. I know I used sqlpubwiz.exe before to get create and insert-statements from a remote library, but it just doesn't work anymore. – Werner Mar 15 '16 at 12:12
  • You're right - never knew it could do that. Follow these steps perhaps- http://stackoverflow.com/questions/2321052/get-script-of-sql-server-data – Jamiec Mar 15 '16 at 12:19
  • Thanks again. The Express-Version doesn't seem to have this option (as it doesn't have the export-option). Didn't know it's so hard just to export a database... – Werner Mar 15 '16 at 16:03