Restore TSQL Scripts

Hi
I would like to creat a job in MS SQL Server 2000 to do the following:
1. Drop Test_DB
2. Restore Current_DB (latest backup in my_dir) as Test_DB
Can anyone help me on this? I think it is a simple as
--start code
DROP DATABASE Test_DB
GO
RESTORE DATABASE Test_DB
FROM Current_DB ?
GO
--end code
It is the second line that bothers me. I want to restore the most
recent backup of Current_DB. How can I get and specify this info?
Also, when I use EM to restore it gives me the option to rename the
files, can I specify this too?
thanks
dabenTry something like:
RESTORE DATABASE Test_DB
FROM DISK='C:\My_Dir\Current_DB.bak'
WITH
MOVE 'Current_DB' TO 'E:\DataFiles\Test_DB.mdf',
MOVE 'Current_DB_Log' TO 'C:\LogFiles\Test_DB_Log.ldf',
REPLACE
GO
You find the names log the logical files for the above command with
RESTORE FILELISTONLY:
RESTORE FILELISTONLY
FROM DISK='C:\MyDir\Current_DB.bak'
GO
See RESTORE in the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"daben" <dabenpb@.yahoo.com> wrote in message
news:1adbeff3.0309111224.1fe1b9ac@.posting.google.com...
> Hi
> I would like to creat a job in MS SQL Server 2000 to do the following:
> 1. Drop Test_DB
> 2. Restore Current_DB (latest backup in my_dir) as Test_DB
> Can anyone help me on this? I think it is a simple as
> --start code
> DROP DATABASE Test_DB
> GO
> RESTORE DATABASE Test_DB
> FROM Current_DB ?
> GO
> --end code
> It is the second line that bothers me. I want to restore the most
> recent backup of Current_DB. How can I get and specify this info?
> Also, when I use EM to restore it gives me the option to rename the
> files, can I specify this too?
> thanks
> daben

Restore SQL databes to diffrent server using Scripts

I hope someone here can point me in the right direction.
We currently have a "Live" server and a "Backup" server both using SQL
server 2000.
We are trying automating the database restore between these servers if
possible.
It is currently being done by hand.
We have 40 Databases to recover. They are not that big, just time
consuming to restore the data and fix the assigned users which are not
recreated during the restore.
We use the "sp_change_users_login auto_fix, <username>" to recreate the
link between the user and its database.
Is there a way to script this? I'm not very fluent with SQL and queries
so I'm not sure if scripting this or a stored procedure would be easy
to setup.
Any help will be gratefully received!
Bill C
You could consider creating a DTS Package to do this for you. Here are some
articles that will help :-
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
HTH. Ryan
"Williamc_sircon" <williamc@.sircon.co.uk> wrote in message
news:1139826814.497790.140590@.f14g2000cwb.googlegr oups.com...
>I hope someone here can point me in the right direction.
> We currently have a "Live" server and a "Backup" server both using SQL
> server 2000.
> We are trying automating the database restore between these servers if
> possible.
> It is currently being done by hand.
> We have 40 Databases to recover. They are not that big, just time
> consuming to restore the data and fix the assigned users which are not
> recreated during the restore.
> We use the "sp_change_users_login auto_fix, <username>" to recreate the
> link between the user and its database.
> Is there a way to script this? I'm not very fluent with SQL and queries
> so I'm not sure if scripting this or a stored procedure would be easy
> to setup.
>
> Any help will be gratefully received!
> Bill C
>
|||Many thanks!!
These will keep me going for a while by the looks of it!!
Laters
Bill C
sql

Restore SQL databes to diffrent server using Scripts

I hope someone here can point me in the right direction.
We currently have a "Live" server and a "Backup" server both using SQL
server 2000.
We are trying automating the database restore between these servers if
possible.
It is currently being done by hand.
We have 40 Databases to recover. They are not that big, just time
consuming to restore the data and fix the assigned users which are not
recreated during the restore.
We use the "sp_change_users_login auto_fix, <username>" to recreate the
link between the user and its database.
Is there a way to script this? I'm not very fluent with SQL and queries
so I'm not sure if scripting this or a stored procedure would be easy
to setup.
Any help will be gratefully received!
Bill CYou could consider creating a DTS Package to do this for you. Here are some
articles that will help :-
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
HTH. Ryan
"Williamc_sircon" <williamc@.sircon.co.uk> wrote in message
news:1139826814.497790.140590@.f14g2000cwb.googlegroups.com...
>I hope someone here can point me in the right direction.
> We currently have a "Live" server and a "Backup" server both using SQL
> server 2000.
> We are trying automating the database restore between these servers if
> possible.
> It is currently being done by hand.
> We have 40 Databases to recover. They are not that big, just time
> consuming to restore the data and fix the assigned users which are not
> recreated during the restore.
> We use the "sp_change_users_login auto_fix, <username>" to recreate the
> link between the user and its database.
> Is there a way to script this? I'm not very fluent with SQL and queries
> so I'm not sure if scripting this or a stored procedure would be easy
> to setup.
>
> Any help will be gratefully received!
> Bill C
>|||Many thanks!!
These will keep me going for a while by the looks of it!!
Laters
Bill C

Restore SQL databes to diffrent server using Scripts

I hope someone here can point me in the right direction.
We currently have a "Live" server and a "Backup" server both using SQL
server 2000.
We are trying automating the database restore between these servers if
possible.
It is currently being done by hand.
We have 40 Databases to recover. They are not that big, just time
consuming to restore the data and fix the assigned users which are not
recreated during the restore.
We use the "sp_change_users_login auto_fix, <username>" to recreate the
link between the user and its database.
Is there a way to script this? I'm not very fluent with SQL and queries
so I'm not sure if scripting this or a stored procedure would be easy
to setup.
Any help will be gratefully received!
Bill CYou could consider creating a DTS Package to do this for you. Here are some
articles that will help :-
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
--
HTH. Ryan
"Williamc_sircon" <williamc@.sircon.co.uk> wrote in message
news:1139826814.497790.140590@.f14g2000cwb.googlegroups.com...
>I hope someone here can point me in the right direction.
> We currently have a "Live" server and a "Backup" server both using SQL
> server 2000.
> We are trying automating the database restore between these servers if
> possible.
> It is currently being done by hand.
> We have 40 Databases to recover. They are not that big, just time
> consuming to restore the data and fix the assigned users which are not
> recreated during the restore.
> We use the "sp_change_users_login auto_fix, <username>" to recreate the
> link between the user and its database.
> Is there a way to script this? I'm not very fluent with SQL and queries
> so I'm not sure if scripting this or a stored procedure would be easy
> to setup.
>
> Any help will be gratefully received!
> Bill C
>|||Many thanks!!
These will keep me going for a while by the looks of it!!
Laters
Bill C