Automating Bkp & restore from command line

Currently developing a small app (Access) I'm using the same app on 3 SQL
Databases:
1 - Production: MS SQL 2000 server
2 - Test: MS SQL 2000 server
3 - Test: MSDE
As I'm modifyng extensively db structure I have to manually move modified
dbs between the 3 servers daily. multiple times, and got bored to manually
select each time paths and options (overwrite, force restore, devices etc)
So I wrote a small script in QA and thought if possible to runnit from
commandline (OSQL would do the job?). But I still miss a statement to
specifiy to wich DB the script must connect before doing th rest:
Here's my code and I've got a trutsed connection, so y would run:
osql -q "Restore.sql" -E:
The script contains:
USE MASTER
GO
RESTORE DATABASE Test
FROM DISK = 'C:\test.BAK'
WITH REPLACE,
MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.LDF',
MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
What am I missing to connect to the prper server?
Thanks
You first say:

> But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
And then:

> What am I missing to connect to the proper server?
Which one is it? Database or server? To specify a server, you use the -S switch for OSQL. The db is a bit more
problematic as it is included in the RESTORE command, but my guess is that it is server you are after.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atlas" <atlaspeak@.my-deja.com> wrote in message news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
|||osql -S [servername] ?
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server
2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
|||Hi,
OSQL -S Server_name -E -i c:\Restore.sql -oc:\restore.log
Note: -q option is to give the query in the OSQL itself. Use iption "i" to
input a file.
Thanks
Hari
MCDBA
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server
2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>