backup and restore database every day at sql express 2008 r2

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hello,

i need to take backup and restore from one server database to another server database remotely
1) first i take backup from original server database to another server database
2) again i have to restore anohter server database to original server database

i am doing by task scheduler for in C drive

but i dont want to do the backup in the c drive.

i want to kw how to take backup into another server database

Posted 10-Jul-15 0:41am

sadhana4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-07-10T00:56:00

Solution 1

Task scheduler doesn't define where the backup is taken, the script you run does.

From what I gather from your question,
- use sqlcmd[^] utility to run the SQL scripts
- create one sqlcmd run for the backup and define the backup to be taken where you like
- if necessary add a step in your script where you copy the backup from a location to another
- create one sqlcmd run for the backup and define the restore of the backup to another database
- schedule this script using task scheduler

For defitions for the backup and restore commands to run, please refer to BACKUP[^] and RESTORE[^]

For sqlcmd see sqlcmd Utility[^]

Posted 10-Jul-15 0:56am

Wendelius

Updated 10-Jul-15 0:57am

v2

Comments

sadhana4 10-Jul-15 8:13am

sqlcmd -S .\sa -E -Q
"BACKUP DATABASE MASTER TO TESTBACKUP"
--- < End Code Batch Script > -----

..where "MYSERVER" is the name of the SQL Server physical machine.
..where "OFFICESERVERS" is the name of the SQL Server.
..where "TESTBACKUP" is the name of the backup job.
..where "MASTER" is the name of the database.

i kw i did the script for backup and run from sqlcmd using task schedule
but how to define the path another database connection

how to defined another server database connection in the script

Wendelius 10-Jul-15 8:18am

If I understand you correctly, you don't define the connection to another server in the restore script. You use the parameters of the sqlcmd. Something like

sqlcmd -S tcp:another_computer_name\instancename, 1433 -U another_user_name -P password_for_the_user -Q ...

sadhana4 15-Jul-15 4:05am

if i need backup all database like 45 database to another database daily at 10 pm
and again i have to restore all this database from another server to main server database.i kw i did the backup by using sqlcmd and taskschduler daily at 10 but again i have to restore all database to main server database
how to do restore by using sqlcmd to main server or another server

Wendelius 15-Jul-15 4:12am

I'm sorry but I don't quite understand the question. If you want to restore a database from a file, you can define the file location in the RESTORE command. Just make sure that the SQL Server service has access to the location of the file.

sadhana4 15-Jul-15 4:48am

example A is Main server having 6 database and B is another server which don't any have database.by using sqlcmd how can i backup to B server database at 10pm daily and again
in the morning restore B server database to A server database.
i kw how to backup to local drive for example (C:/,D:/).but i am not getting
how to connect both server A to B for backup and restore daily
Please help me

Wendelius 15-Jul-15 8:51am

The key is to have a location (folder) which is shared between the computers one way or another. Because of this I suggest, in your Windows script
- step 1: take the backup to local drive using sqlcmd
- step 2: using windows copy or xcopy command, copy the file to a folder from where the other database can read it, use UNC paths if necessary
- step 3: using sqlcmd again execute restore command in the other database server restoring the copied file

sadhana4 15-Jul-15 6:26am

How to restore multiple databases from multiple .bak files at once

Wendelius 15-Jul-15 8:51am

You don't restore them at the same time. You restore them one by one

sadhana4 16-Jul-15 2:25am

i kw it restore one by one but this all process done by task scheduler

Wendelius 16-Jul-15 2:35am

I understand. If you want to restore say 45 databases you will have 45 sqlcmd commands in the script you run using the task scheduler. One restore per each database

sadhana4 16-Jul-15 2:33am

actually i take backup in the c:\dbbackup drive in the main server by using sqlcmd and task scheduler.but i don't kw how transfer all .bak files from main server to another server database and transfer and restore all files.and second day again i have to take .bak files from another server and transfer and restore to main server database using
sqlcmd and task scheduler
how to do ??

Wendelius 16-Jul-15 2:36am

Use xcopy in the command script. You use windows commands to copy the files, not sqlcmd commands

sadhana4 16-Jul-15 6:25am

if my backup and restore is not successful then how to send email
that bakupand restore failed

Wendelius 16-Jul-15 6:35am

This is going way beyond the scope of the question but you can possibly use the Task Scheduler's ability to send email. Investigate the return value or write log info to a file and send that.

sadhana4 18-Jul-15 2:03am

i did the .bak files in main server local drive by using task scheduler and using xcopy i move files from one location server to another location server but when i was restoring database for example
if i have 5 database name is
1)A Database name
2)B Database name
3)C Database name
4)D database name

and i have backup a.bak,b.bak,c.bak,d.back
how to restore specific database to specific database
for exmple a.bak to A Database
b.bak to B Database

i need know

i have defined for example
RESTORE DATABASE [A]
FROM DISK = 'C:\EDrive\a.bak'
WITH RECOVERY, REPLACE
GO

RESTORE DATABASE [b]
FROM DISK = 'C:\EDrive\b.bak'
WITH RECOVERY, REPLACE
GO

i have to defined different types of command for restoring database

sadhana4 18-Jul-15 2:07am

means i have to defined static database name in my scripts
or i can dynamic restore database????

Wendelius 19-Jul-15 2:52am

The command for restore is static but you can always generate the script you're running. Create a small program etc and create the restore commands dynamically using that. Then use that generated script to execute the restore.