January 22, 2009 at 2:57 am
Hi,
I need a workaround for a SQL Server Express database. The main database sits on the server, and basically it needs to be transferred onto a laptop so that out of office hours can have the information they require. It does not need to copy any changes back, just be able to create a fresh copy each evening.
I need to do this in the form of a script or batch file that can be run by a user from the laptop without access to the server or studio express etc. Just copying the data is no good as I can't copy the open file.
Any ideas or pointers gratefully received.
Matt
January 22, 2009 at 3:39 am
What is the size of the database?
How many users wants to access this database from their laptops?
Let us know the answers of these question so that we can provide a solution.
-Vikas Bindra
January 22, 2009 at 3:41 am
Hi,
Database is no more than 2-3gb
Looking at just one user. Basically the function is recruitment service that has on-call facility. They need all the data such as worker details, next of kin etc out of office hours, but don't need the ability to make changes to the database.
Thanks,
Matt
January 22, 2009 at 4:06 am
If it is pretty small then you can follow this:
1. Take backup on server.
2. Copy backup file on remote machine (laptop).
3. Drop the existing DB on the remote machine.
4. Restore the database on the remote machine.
This can be done in a single SQL script that can be executed using sqlcmd.exe from a batch file from the laptop.
But in the above scenario, the user will be able to update the data in the local copy of the database.
-Vikas Bindra
January 22, 2009 at 4:09 am
Vikas,
Thank you for your help, I haven't formulated a script like this before.
Espescially curious to know how I specifiy the backup of a database from a different machine.
Any help or script examples would be great.
Matt
January 22, 2009 at 4:34 am
Just a simple backup from the machine will do, specify the remote location as backup location. 🙂
January 22, 2009 at 4:43 am
First of all, I have mentioned that it can be done in a single script file, but can't be. Sorry for that. You will need 2 script files.
Now coming to the solution...
The batch file will
- execute the first script that will
1. delete any existing files backup files from the local server, say, d:\sql\backup. (this location is used in the next step to take the new backup). You can use xp_cmdshell for this
2. Take backup on the server (locally) d:\sql\backup. Even if you execute the script from laptop mentioning the above path (d:\sql\backup) the backup file will be created on the server and not on the laptop
- copy the file from the \\servername\d$\sql\backup of the server to (say) D:\backup on laptop. You can use XCOPY
- execute the second script
1. drop the existing database on the laptop.
2. restore the database on the laptop (using RESTORE DB), remeber to use WITH MOVE option to place the file at the correct location on the laptop.
Also,
1. Both the sql scripts will reside on your laptop.
2. batch script(residing on the laptop) will use sqlcmd.exe with -S [ServerName] option. This enables the execution of script on remote server.
Hope the solution is making sense.
-Vikas Bindra
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply