DB Restore

What's the best way to restore or create a copy of production db (15 GB) on
development server and also another copy on same sql server with different
name.
The copies need to be refreshed on a monthly basis.
1. For doing above things what's best and can some one post sctips or TSQL
statements to do so...
2. what's the importance of move statement in a restore?
Thanks
BVRIf it only needs to be done monthly, backup / restore is probably the
easiest option.
eg:
--on prod server
backup database [dbname] to disk=c:\dbname.fbak
--on dev server
restore database [dbname] from disk=c:\dbname.fbak
You'll probably be able to access the .fbak from your existing production
backups. The above assumes that the database already exists. The database
can be created initially by issuing the restore command with replace with
replace, move etc. The point of the move statement is that, when you create
a fresh database from a backup, the o/s files that support the database need
to be given a directory location. If you're creating the new database on the
same server as the database that the backup came from, a different directory
will need to be given for the o/s files under the new db.
Regards,
Greg Linwood
SQL Server MVP
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> What's the best way to restore or create a copy of production db (15 GB)
on
> development server and also another copy on same sql server with different
> name.
> The copies need to be refreshed on a monthly basis.
> 1. For doing above things what's best and can some one post sctips or TSQL
> statements to do so...
> 2. what's the importance of move statement in a restore?
> Thanks
> BVR
>
>|||Uhway
Take a look at WITH MOVE option of RESTORE command in BOL.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> What's the best way to restore or create a copy of production db (15 GB)
on
> development server and also another copy on same sql server with different
> name.
> The copies need to be refreshed on a monthly basis.
> 1. For doing above things what's best and can some one post sctips or TSQL
> statements to do so...
> 2. what's the importance of move statement in a restore?
> Thanks
> BVR
>
>|||Hi,
Fast method will be ,
1. From the actual server, Execute SP_DETACH DB database name
2. COpy the mdf and ldf files to a diff location
3.USE SP_ATTACH_DB in actual server to connect the database back
4. From the copied location copy the files to devolpment server
5. From Devolopment server Query ANalyzer execute SP_ATTACH_DB to attach the
database
6. In the actual server use SP_ATTACH_DB command . Use the filenames from
the copied folder while attaching.
Refer BOL for SP_ATTACH_DB and SP_DETACH_DB
Otherwise you can use RETORE DATABASE command with move option. But this
will take longer to restore the 15 GB database.
Thanks
Hari
MCDBA
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> What's the best way to restore or create a copy of production db (15 GB)
on
> development server and also another copy on same sql server with different
> name.
> The copies need to be refreshed on a monthly basis.
> 1. For doing above things what's best and can some one post sctips or TSQL
> statements to do so...
> 2. what's the importance of move statement in a restore?
> Thanks
> BVR
>
>|||This is ok as long as you don't mind taking the production database
offline..
Regards,
Greg Linwood
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u5MCby86DHA.1632@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Fast method will be ,
> 1. From the actual server, Execute SP_DETACH DB database name
> 2. COpy the mdf and ldf files to a diff location
> 3.USE SP_ATTACH_DB in actual server to connect the database back
> 4. From the copied location copy the files to devolpment server
> 5. From Devolopment server Query ANalyzer execute SP_ATTACH_DB to attach
the
> database
> 6. In the actual server use SP_ATTACH_DB command . Use the filenames from
> the copied folder while attaching.
> Refer BOL for SP_ATTACH_DB and SP_DETACH_DB
> Otherwise you can use RETORE DATABASE command with move option. But this
> will take longer to restore the 15 GB database.
>
> Thanks
> Hari
> MCDBA
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > What's the best way to restore or create a copy of production db (15 GB)
> on
> > development server and also another copy on same sql server with
different
> > name.
> >
> > The copies need to be refreshed on a monthly basis.
> >
> > 1. For doing above things what's best and can some one post sctips or
TSQL
> > statements to do so...
> >
> > 2. what's the importance of move statement in a restore?
> >
> > Thanks
> > BVR
> >
> >
> >
>|||On the same server, I need to restore on to a 6 month old copy (with
different name). The o/s files already exist. Still, Do I need to use move
statements in this case?
The main problem is, the ex dba created data and log files for the original
production db and for copy in the same directory. I don't want to restore
on to a original o/s files (loose data during restore) and it need to be
scheduled. Also, no chance to test before scheduling.
Thanks
BVR
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uAyN3Y56DHA.2412@.TK2MSFTNGP09.phx.gbl...
> If it only needs to be done monthly, backup / restore is probably the
> easiest option.
> eg:
> --on prod server
> backup database [dbname] to disk=c:\dbname.fbak
> --on dev server
> restore database [dbname] from disk=c:\dbname.fbak
> You'll probably be able to access the .fbak from your existing production
> backups. The above assumes that the database already exists. The database
> can be created initially by issuing the restore command with replace with
> replace, move etc. The point of the move statement is that, when you
create
> a fresh database from a backup, the o/s files that support the database
need
> to be given a directory location. If you're creating the new database on
the
> same server as the database that the backup came from, a different
directory
> will need to be given for the o/s files under the new db.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > What's the best way to restore or create a copy of production db (15 GB)
> on
> > development server and also another copy on same sql server with
different
> > name.
> >
> > The copies need to be refreshed on a monthly basis.
> >
> > 1. For doing above things what's best and can some one post sctips or
TSQL
> > statements to do so...
> >
> > 2. what's the importance of move statement in a restore?
> >
> > Thanks
> > BVR
> >
> >
> >
>|||I'm not 100% that I understand what you mean here, but if you want to
restore a 6 month old backup onto the same server, leaving the existing
database there then you do need to use the with move option in the restore
command (that's basically what it's for). Of course, you'd also need to give
the database a new name too..
Regards,
Greg Linwood
SQL Server MVP
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uDcSoCG7DHA.4060@.tk2msftngp13.phx.gbl...
> On the same server, I need to restore on to a 6 month old copy (with
> different name). The o/s files already exist. Still, Do I need to use
move
> statements in this case?
> The main problem is, the ex dba created data and log files for the
original
> production db and for copy in the same directory. I don't want to restore
> on to a original o/s files (loose data during restore) and it need to be
> scheduled. Also, no chance to test before scheduling.
> Thanks
> BVR
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uAyN3Y56DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > If it only needs to be done monthly, backup / restore is probably the
> > easiest option.
> >
> > eg:
> > --on prod server
> > backup database [dbname] to disk=c:\dbname.fbak
> > --on dev server
> > restore database [dbname] from disk=c:\dbname.fbak
> >
> > You'll probably be able to access the .fbak from your existing
production
> > backups. The above assumes that the database already exists. The
database
> > can be created initially by issuing the restore command with replace
with
> > replace, move etc. The point of the move statement is that, when you
> create
> > a fresh database from a backup, the o/s files that support the database
> need
> > to be given a directory location. If you're creating the new database on
> the
> > same server as the database that the backup came from, a different
> directory
> > will need to be given for the o/s files under the new db.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> > news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > > What's the best way to restore or create a copy of production db (15
GB)
> > on
> > > development server and also another copy on same sql server with
> different
> > > name.
> > >
> > > The copies need to be refreshed on a monthly basis.
> > >
> > > 1. For doing above things what's best and can some one post sctips or
> TSQL
> > > statements to do so...
> > >
> > > 2. what's the importance of move statement in a restore?
> > >
> > > Thanks
> > > BVR
> > >
> > >
> > >
> >
> >
>|||By default, a restore command will use the same file names as the original
database. You can use the WITH MOVE option to specify different paths. Use
the FILELISTONLY option to list the logical file names that you'll need to
specify on the MOVE clause. For example:
--list logical file Names
RESTORE FILELISTONLY
FROM DISK='c:\MyDatabase.bak'
--restore db with different physical file names
RESTORE DATABASE MyDatabase
FROM DISK='c:\MyDatabase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\Data\MyDatabase_Restored.mdf',
MOVE 'MyDatabase_Log' TO 'C:\Data\MyDatabase_Log_Restored.ldf',
STATS=10
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uDcSoCG7DHA.4060@.tk2msftngp13.phx.gbl...
> On the same server, I need to restore on to a 6 month old copy (with
> different name). The o/s files already exist. Still, Do I need to use
move
> statements in this case?
> The main problem is, the ex dba created data and log files for the
original
> production db and for copy in the same directory. I don't want to restore
> on to a original o/s files (loose data during restore) and it need to be
> scheduled. Also, no chance to test before scheduling.
> Thanks
> BVR
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uAyN3Y56DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > If it only needs to be done monthly, backup / restore is probably the
> > easiest option.
> >
> > eg:
> > --on prod server
> > backup database [dbname] to disk=c:\dbname.fbak
> > --on dev server
> > restore database [dbname] from disk=c:\dbname.fbak
> >
> > You'll probably be able to access the .fbak from your existing
production
> > backups. The above assumes that the database already exists. The
database
> > can be created initially by issuing the restore command with replace
with
> > replace, move etc. The point of the move statement is that, when you
> create
> > a fresh database from a backup, the o/s files that support the database
> need
> > to be given a directory location. If you're creating the new database on
> the
> > same server as the database that the backup came from, a different
> directory
> > will need to be given for the o/s files under the new db.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> > news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > > What's the best way to restore or create a copy of production db (15
GB)
> > on
> > > development server and also another copy on same sql server with
> different
> > > name.
> > >
> > > The copies need to be refreshed on a monthly basis.
> > >
> > > 1. For doing above things what's best and can some one post sctips or
> TSQL
> > > statements to do so...
> > >
> > > 2. what's the importance of move statement in a restore?
> > >
> > > Thanks
> > > BVR
> > >
> > >
> > >
> >
> >
>