Getting backup of remote SQL Server Database to local drive
Getting backup of remote SQL Server using database backup options generating scripts & import/export data
This article is about how do we get backup of remote of SQL database, here I am going to discuss on different methods of getting sql backup.
- Generating the SQL Script
- Importing/Exporting the Data
Generating the SQL Script
Open the Microsoft SQL Server Management Studio now connect to remote sql server. Go to object explorer select the database you want to get backup and right click on it, in the context menu go to Tasks > Generate Scripts.
A dialoag box will be open click on next option here we can select entire database or select specific tables, stored procedures, functions and users to be scripted.
I have selected first option to Select entire database and all objects, again click on next this will take you to Set Scripting Options page now click on the Advanced button. By default indexes are not scripted so we need to set Script Indexes option to True so that indexes are included. Full-text indexes should be enabled if used by default it is disabled.
Also we can select Type of data to script options are:
- Data only
- Schema and Data
- Schema only
Schema only will generate script for database schema and data will have insert rows for all table data. To get the full backup select Schema and Data next time we can select Data only if we already have same database schema.
Now click ok for advanced options and give the filename to be saved in our local hard drive in save to file option. Click on next to complete the wizard after successful completion run sql file in localhost.
Importing/Exporting the Data
This option requires that we have already created the database having same schema and structure on local machine. To backup go to the database and right click & select Tasks menu, again select Import Data option.
A Sql Server Import and Export Wizard will open click next to choose a Data Source. Choose Sql Server Authentication & provide username and password for remote sql server. Select database to get backup from dropdown.
Click next to choose destination server and database. Again click next and just select the tables you want to copy data. Make sure tables are highlighted and select Edit Table Mappings. This allows you to set table mapping rules for all tables.
Check Enable identity insert option which allows to insert rows from data source containing values for identity field. If table does not exist Create destination table radio button will be selected and the other radio buttons will be disabled. If table already exists we can delete or append rows.
Click next to run & also we can create SSIS Package and save it to Sql Server.