Thread: [Mssqlcompressed-users] norecovery, replace and move?

Brought to you by: xclayl

mssqlcompressed-users

[Mssqlcompressed-users] norecovery, replace and move?

From: Antonio R. Z. <Ant...@me...> - 2010-03-12 16:57:02

Hi,

Is it possible to use the NORECOVERY, REPLACE AND MOVE restore commands with msbp?

For example, currently I have a job setup which restores a database using this command:
------------------
RESTORE DATABASE myDatabase 
FROM BDVmyDatabase1,BDVmyDatabase2,BDVmyDatabase3
with 
move 'mydb_primary_data' to 'd:\myDatabase\mydb_primary_data.mdf',
move 'mydb_daily_data1' to 'd:\myDatabase\mydb_daily_data1.ndf',
move 'mydb_daily_data2' to 'd:\myDatabase\mydb_daily_data2.ndf',
move 'mydb_daily_data3' to 'd:\myDatabase\mydb_daily_data3.ndf',
move 'mydb_daily_data4' to 'd:\myDatabase\mydb_daily_data4.ndf',
move 'mydb_rare_data20' to 'd:\myDatabase\mydb_rare_data20.ndf',
move 'mydb_indx' to 'd:\myDatabase\mydb_indx.ndf',
move 'mydb_log' to 'd:\myDatabase\mydb_log.ldf',
REPLACE,
NORECOVERY
------------------

The database uses three Backup devices:
------------------
BDVmyDatabase1 D:\backup_files\BDVmyDatabase1.bak
BDVmyDatabase2 D:\backup_files\BDVmyDatabase2.bak
BDVmyDatabase3 D:\backup_files\BDVmyDatabase3.bak
------------------

Those backup devices are getting pretty big, so I would prefer to keep them compressed as .bak.gz files, and use msbp to restore the database directly from the compressed files.

However, I have not been able to figure out how the restore command would look like. I.e.
-------------------------
msbp.exe restore "local(path=D:\backup_files\BDVmyDatabase1.bak.gz;path=D:\backup_files\BDVmyDatabase2.bak.gz;path=D:\backup_files\BDVmyDatabase3.bak.gz;)" "gzip()" "db(database=myDatabase)" 
-------------------------
but where do I state the MOVE, REPLACE and NORECOVERY options?

Thanks for your time!

Cheers,


Antonio

[Mssqlcompressed-users] norecovery, replace and move?

From: Antonio R. Z. <Ant...@me...> - 2010-03-12 16:51:20

Hi,

Is it possible to use the NORECOVERY, REPLACE AND MOVE restore commands with msbp?

For example, currently I have a job setup which restores a database using this command:
------------------
RESTORE DATABASE myDatabase 
FROM BDVmyDatabase1,BDVmyDatabase2,BDVmyDatabase3
with 
move 'mydb_primary_data' to 'd:\myDatabase\mydb_primary_data.mdf',
move 'mydb_daily_data1' to 'd:\myDatabase\mydb_daily_data1.ndf',
move 'mydb_daily_data2' to 'd:\myDatabase\mydb_daily_data2.ndf',
move 'mydb_daily_data3' to 'd:\myDatabase\mydb_daily_data3.ndf',
move 'mydb_daily_data4' to 'd:\myDatabase\mydb_daily_data4.ndf',
move 'mydb_rare_data20' to 'd:\myDatabase\mydb_rare_data20.ndf',
move 'mydb_indx' to 'd:\myDatabase\mydb_indx.ndf',
move 'mydb_log' to 'd:\myDatabase\mydb_log.ldf',
REPLACE,
NORECOVERY
------------------

The database uses three Backup devices:
------------------
BDVmyDatabase1 D:\backup_files\BDVmyDatabase1.bak
BDVmyDatabase2 D:\backup_files\BDVmyDatabase2.bak
BDVmyDatabase3 D:\backup_files\BDVmyDatabase3.bak
------------------

Those backup devices are getting pretty big, so I would prefer to keep them compressed as .bak.gz files, and use msbp to restore the database directly from the compressed files.

However, I have not been able to figure out how the restore command would look like. I.e.
-------------------------
msbp.exe restore "local(path=D:\backup_files\BDVmyDatabase1.bak.gz;path=D:\backup_files\BDVmyDatabase2.bak.gz;path=D:\backup_files\BDVmyDatabase3.bak.gz;)" "gzip()" "db(database=myDatabase)" 
-------------------------
but where do I state the MOVE, REPLACE and NORECOVERY options?

Thanks for your time!

Cheers,


Antonio

Re: [Mssqlcompressed-users] norecovery, replace and move?

From: Clay L. <cl...@le...> - 2010-03-14 10:45:10

It is possible. An example would be:

msbp.exe restore "local(path=c:\model.bak.gz)" gzip()
"db(database=model;filegroup=primary;checksum;MOVE='modeldev'TO'c:\model.mdf';MOVE='modellog'TO'c:\model.ldf';NORECOVERY;REPLACE;)"


You can find all the options here:

http://mssqlcompressed.sourceforge.net/doc_plugins_db.shtml



On 12 March 2010 16:56, Antonio Ruiz Zwollo <
Ant...@me...> wrote:

> Hi,
>
> Is it possible to use the NORECOVERY, REPLACE AND MOVE restore commands
> with msbp?
>
> For example, currently I have a job setup which restores a database using
> this command:
> ------------------
> RESTORE DATABASE myDatabase
> FROM BDVmyDatabase1,BDVmyDatabase2,BDVmyDatabase3
> with
> move 'mydb_primary_data' to 'd:\myDatabase\mydb_primary_data.mdf',
> move 'mydb_daily_data1' to 'd:\myDatabase\mydb_daily_data1.ndf',
> move 'mydb_daily_data2' to 'd:\myDatabase\mydb_daily_data2.ndf',
> move 'mydb_daily_data3' to 'd:\myDatabase\mydb_daily_data3.ndf',
> move 'mydb_daily_data4' to 'd:\myDatabase\mydb_daily_data4.ndf',
> move 'mydb_rare_data20' to 'd:\myDatabase\mydb_rare_data20.ndf',
> move 'mydb_indx' to 'd:\myDatabase\mydb_indx.ndf',
> move 'mydb_log' to 'd:\myDatabase\mydb_log.ldf',
> REPLACE,
> NORECOVERY
> ------------------
>
> The database uses three Backup devices:
> ------------------
> BDVmyDatabase1 D:\backup_files\BDVmyDatabase1.bak
> BDVmyDatabase2 D:\backup_files\BDVmyDatabase2.bak
> BDVmyDatabase3 D:\backup_files\BDVmyDatabase3.bak
> ------------------
>
> Those backup devices are getting pretty big, so I would prefer to keep them
> compressed as .bak.gz files, and use msbp to restore the database directly
> from the compressed files.
>
> However, I have not been able to figure out how the restore command would
> look like. I.e.
> -------------------------
> msbp.exe restore
> "local(path=D:\backup_files\BDVmyDatabase1.bak.gz;path=D:\backup_files\BDVmyDatabase2.bak.gz;path=D:\backup_files\BDVmyDatabase3.bak.gz;)"
> "gzip()" "db(database=myDatabase)"
> -------------------------
> but where do I state the MOVE, REPLACE and NORECOVERY options?
>
> Thanks for your time!
>
> Cheers,
>
>
> Antonio
>
>
>
>
>
>
>
> ------------------------------------------------------------------------------
> Download Intel&#174; Parallel Studio Eval
> Try the new software tools for yourself. Speed compiling, find bugs
> proactively, and fine-tune applications for parallel performance.
> See why Intel Parallel Studio got high marks during beta.
> http://p.sf.net/sfu/intel-sw-dev
> _______________________________________________
> Mssqlcompressed-users mailing list
> Mss...@li...
> https://lists.sourceforge.net/lists/listinfo/mssqlcompressed-users
>

Re: [Mssqlcompressed-users] norecovery, replace and move?

From: Antonio R. Z. <Ant...@me...> - 2010-03-16 12:58:33

Hi Clay,

Thanks for your help. Just to let you know it worked flawlessly. Last night I restored a 127 GB database from 10 .bak.gz files (2.5 GB each). It only took 50 minutes:
-------------
Restore has started
  0.00% Complete. Estimated End: 16/11/2073 16:50:17 
 11.12% Complete. Estimated End: 16/03/2010 01:44:20 
 25.64% Complete. Estimated End: 16/03/2010 00:52:29 
 45.47% Complete. Estimated End: 16/03/2010 00:35:36 
 77.45% Complete. Estimated End: 16/03/2010 00:24:21 
Completed Successfully. 00:50:47.0770990
-------------

The bak files were created by a traditional MSSQL backup job and then used gzip to compress them (thus the .bak.gz files had not been created by msbp); it's nice to see it works perfectly with third party .bak.gz files too.

Cheers,


Antonio

Clay Lenhart wrote:
> It is possible. An example would be:
> 
> msbp.exe restore "local(path=c:\model.bak.gz)" gzip()
> "db(database=model;filegroup=primary;checksum;MOVE='modeldev'TO'c:\model.mdf';MOVE='modellog'TO'c:\model.ldf';NORECOVERY;REPLACE;)"
> 
> 
> You can find all the options here:
> 
> http://mssqlcompressed.sourceforge.net/doc_plugins_db.shtml
> 
> 
> 
> On 12 March 2010 16:56, Antonio Ruiz Zwollo
> <Ant...@me...
> <mailto:Ant...@me...>> wrote:
> 
>     Hi,
> 
>     Is it possible to use the NORECOVERY, REPLACE AND MOVE restore
>     commands with msbp?
> 
>     For example, currently I have a job setup which restores a database
>     using this command:
>     ------------------
>     RESTORE DATABASE myDatabase
>     FROM BDVmyDatabase1,BDVmyDatabase2,BDVmyDatabase3
>     with
>     move 'mydb_primary_data' to 'd:\myDatabase\mydb_primary_data.mdf',
>     move 'mydb_daily_data1' to 'd:\myDatabase\mydb_daily_data1.ndf',
>     move 'mydb_daily_data2' to 'd:\myDatabase\mydb_daily_data2.ndf',
>     move 'mydb_daily_data3' to 'd:\myDatabase\mydb_daily_data3.ndf',
>     move 'mydb_daily_data4' to 'd:\myDatabase\mydb_daily_data4.ndf',
>     move 'mydb_rare_data20' to 'd:\myDatabase\mydb_rare_data20.ndf',
>     move 'mydb_indx' to 'd:\myDatabase\mydb_indx.ndf',
>     move 'mydb_log' to 'd:\myDatabase\mydb_log.ldf',
>     REPLACE,
>     NORECOVERY
>     ------------------
> 
>     The database uses three Backup devices:
>     ------------------
>     BDVmyDatabase1 D:\backup_files\BDVmyDatabase1.bak
>     BDVmyDatabase2 D:\backup_files\BDVmyDatabase2.bak
>     BDVmyDatabase3 D:\backup_files\BDVmyDatabase3.bak
>     ------------------
> 
>     Those backup devices are getting pretty big, so I would prefer to
>     keep them compressed as .bak.gz files, and use msbp to restore the
>     database directly from the compressed files.
> 
>     However, I have not been able to figure out how the restore command
>     would look like. I.e.
>     -------------------------
>     msbp.exe restore
>     "local(path=D:\backup_files\BDVmyDatabase1.bak.gz;path=D:\backup_files\BDVmyDatabase2.bak.gz;path=D:\backup_files\BDVmyDatabase3.bak.gz;)"
>     "gzip()" "db(database=myDatabase)"
>     -------------------------
>     but where do I state the MOVE, REPLACE and NORECOVERY options?
> 
>     Thanks for your time!
> 
>     Cheers,
> 
> 
>     Antonio
> 
> 
> 
> 
> 
> 
>     ------------------------------------------------------------------------------
>     Download Intel&#174; Parallel Studio Eval
>     Try the new software tools for yourself. Speed compiling, find bugs
>     proactively, and fine-tune applications for parallel performance.
>     See why Intel Parallel Studio got high marks during beta.
>     http://p.sf.net/sfu/intel-sw-dev
>     _______________________________________________
>     Mssqlcompressed-users mailing list
>     Mss...@li...
>     <mailto:Mss...@li...>
>     https://lists.sourceforge.net/lists/listinfo/mssqlcompressed-users
> 
>

Re: [Mssqlcompressed-users] norecovery, replace and move?

From: Clay L. <cl...@le...> - 2010-03-16 15:09:15

I'm glad it worked. :)

I think it important to use standard bak and compression file formats.
For example if there is file corruption on the backup medium, there
will be tools to address this.  I don't want to leave a DBA stuck in a
tight situation.

Clay

On Tuesday, March 16, 2010, Antonio Ruiz Zwollo
<Ant...@me...> wrote:
> Hi Clay,
>
> Thanks for your help. Just to let you know it worked flawlessly. Last night I restored a 127 GB database from 10 .bak.gz files (2.5 GB each). It only took 50 minutes:
> -------------
> Restore has started
>   0.00% Complete. Estimated End: 16/11/2073 16:50:17
>  11.12% Complete. Estimated End: 16/03/2010 01:44:20
>  25.64% Complete. Estimated End: 16/03/2010 00:52:29
>  45.47% Complete. Estimated End: 16/03/2010 00:35:36
>  77.45% Complete. Estimated End: 16/03/2010 00:24:21
> Completed Successfully. 00:50:47.0770990
> -------------
>
> The bak files were created by a traditional MSSQL backup job and then used gzip to compress them (thus the .bak.gz files had not been created by msbp); it's nice to see it works perfectly with third party .bak.gz files too.
>
> Cheers,
>
>
> Antonio
>
> Clay Lenhart wrote:
>> It is possible. An example would be:
>>
>> msbp.exe restore "local(path=c:\model.bak.gz)" gzip()
>> "db(database=model;filegroup=primary;checksum;MOVE='modeldev'TO'c:\model.mdf';MOVE='modellog'TO'c:\model.ldf';NORECOVERY;REPLACE;)"
>>
>>
>> You can find all the options here:
>>
>> http://mssqlcompressed.sourceforge.net/doc_plugins_db.shtml
>>
>>
>>
>> On 12 March 2010 16:56, Antonio Ruiz Zwollo
>> <Ant...@me...
>> <mailto:Ant...@me...>> wrote:
>>
>>     Hi,
>>
>>     Is it possible to use the NORECOVERY, REPLACE AND MOVE restore
>>     commands with msbp?
>>
>>     For example, currently I have a job setup which restores a database
>>     using this command:
>>     ------------------
>>     RESTORE DATABASE myDatabase
>>     FROM BDVmyDatabase1,BDVmyDatabase2,BDVmyDatabase3
>>     with
>>     move 'mydb_primary_data' to 'd:\myDatabase\mydb_primary_data.mdf',
>>     move 'mydb_daily_data1' to 'd:\myDatabase\mydb_daily_data1.ndf',
>>     move 'mydb_daily_data2' to 'd:\myDatabase\mydb_daily_data2.ndf',
>>     move 'mydb_daily_data3' to 'd:\myDatabase\mydb_daily_data3.ndf',
>>     move 'mydb_daily_data4' to 'd:\myDatabase\mydb_daily_data4.ndf',
>>     move 'mydb_rare_data20' to 'd:\myDatabase\mydb_rare_data20.ndf',
>>     move 'mydb_indx' to 'd:\myDatabase\mydb_indx.ndf',
>>     move 'mydb_log' to 'd:\myDatabase\mydb_log.ldf',
>>     REPLACE,
>>     NORECOVERY
>>     ------------------
>>
>>     The database uses three Backup devices:
>>     ------------------
>>     BDVmyDatabase1 D:\backup_files\BDVmyDatabase1.bak
>>     BDVmyDatabase2 D:\backup_files\BDVmyDatabase2.bak
>>     BDVmyDatabase3 D:\backup_files\BDVmyDatabase3.bak
>>     ------------------
>>
>>     Those backup devices are getting pretty big, so I would prefer to
>>     keep them compressed as .bak.gz files, and use msbp to restore the
>>     database directly from the compressed files.
>>
>>     However, I have not been able to figure out how the restore command
>>     would look like. I.e.
>>     -------------------------
>>     msbp.exe restore
>>     "local(path=D:\backup_files\BDVmyDatabase1.bak.gz;path=D:\backup_files\BDVmyDatabase2.bak.gz;path=D:\backup_files\BDVmyDatabase3.bak.gz;)"
>>     "gzip()" "db(database=myDatabase)"
>>     -------------------------
>>     but where do I state the MOVE, REPLACE and NORECOVERY options?
>>
>>     Thanks for your time!
>>
>>     Cheers,
>>
>>
>>     Antonio
>>
>>
>>
>>
>>
>>
>>     ------------------------------------------------------------------------------
>>     Download Intel&#174; Parallel Studio Eval
>>     Try the new software tools for yourself. Speed compiling, find bugs
>>     proactively, and fine-tune applications for parallel performance.
>>     See why Intel Parallel Studio got high marks during beta.
>>     http://p.sf.net/sfu/intel-sw-dev
>>     _______________________________________________
>>     Mssqlcompressed-users mailing list
>>     Mss...@li...
>>     <mailto:Mss...@li...>
>>     https://lists.sourceforge.net/lists/listinfo/mssqlcompressed-users
>>
>>
>