We have a SQL2000 database that is created via a dump from a third party
product. Being as this dump runs every day, we don't need to back up the
data. However we need the schema, views, triggers, etc. Everything except
the actual data in the tables. Is there a way to make a backup of this?
Thanks!
BrianYes, use some tool that generates DDL script for the database:
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian" <me@.here.there> wrote in message news:O%2323gnK8HHA.484@.TK2MSFTNGP06.phx.gbl...
> We have a SQL2000 database that is created via a dump from a third party product. Being as this
> dump runs every day, we don't need to back up the data. However we need the schema, views,
> triggers, etc. Everything except the actual data in the tables. Is there a way to make a backup of
> this?
> Thanks!
> Brian
>
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
Backup Database Task
Hi,
I am trying in the Back Up Database Task to set the Value of SelectedDatabases via Expression.
Is there any way?
Thanks,
SQL Server 2005 SP 1 + Hotfix
You cannot set the SelectedDatabases property with an Expression. Try using an Execute SQL Task and an expression to set the SqlStatementSourceBackup Database Task
Hi,
I am trying in the Back Up Database Task to set the Value of SelectedDatabases via Expression.
Is there any way?
Thanks,
SQL Server 2005 SP 1 + Hotfix
You cannot set the SelectedDatabases property with an Expression. Try using an Execute SQL Task and an expression to set the SqlStatementSource
Sunday, March 11, 2012
BackUp database
Hi all
I use this code to bak up database
and its working fine but i want run this code via ASP
can anyone write it for me in ASP format?
USE master
EXEC sp_addumpdevice 'disk', 'test_2',
'C:\test_2.dat'
USE master
EXEC sp_addumpdevice 'disk', 'testlog',
'C:\testlog.dat'
BACKUP DATABASE test TO test_2
BACKUP LOG test TO testlog
thanksAll you need to do is put each TSQL command as relevant ASP command.
In your case, you need to execute 4 commands so you would need
SQLStmt, SQLstmt1,2,3 and 4 lots of RS = Connection.Execute(SQLStmt)
e.g. - example of one connection & execution :
Dim UID
Dim SQLStmt, SQLStmt1, SQLStmt2, SQLStmt3
Dim Connection,
Dim RS, RS1, RS2, RS3
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=server1;UID=test;PWD=password;DATABASE=data base1"
SQLStmt = "SELECT * FROM TBL_Test where server_name = 'SERVER1' and job_run_time > (getdate() - 7)"
Set RS = Connection.Execute(SQLStmt)
I use this code to bak up database
and its working fine but i want run this code via ASP
can anyone write it for me in ASP format?
USE master
EXEC sp_addumpdevice 'disk', 'test_2',
'C:\test_2.dat'
USE master
EXEC sp_addumpdevice 'disk', 'testlog',
'C:\testlog.dat'
BACKUP DATABASE test TO test_2
BACKUP LOG test TO testlog
thanksAll you need to do is put each TSQL command as relevant ASP command.
In your case, you need to execute 4 commands so you would need
SQLStmt, SQLstmt1,2,3 and 4 lots of RS = Connection.Execute(SQLStmt)
e.g. - example of one connection & execution :
Dim UID
Dim SQLStmt, SQLStmt1, SQLStmt2, SQLStmt3
Dim Connection,
Dim RS, RS1, RS2, RS3
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=server1;UID=test;PWD=password;DATABASE=data base1"
SQLStmt = "SELECT * FROM TBL_Test where server_name = 'SERVER1' and job_run_time > (getdate() - 7)"
Set RS = Connection.Execute(SQLStmt)
Thursday, March 8, 2012
Backup and Windows Event Log
Is it possible to stop SQL Server from writing to windows eventLog after
completing a Log backup?
Thanks.
Egbon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Trace flag 3226 can be used for that purpose.
"Egbon V." <vnjowusi@.gosps.com> wrote in message
news:uxOZ1rfiDHA.548@.TK2MSFTNGP11.phx.gbl...
> Is it possible to stop SQL Server from writing to windows eventLog after
> completing a Log backup?
> Thanks.
> Egbon
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks a lot! Where can I find documentation to read more about this.
Egbon
"Anonymous" <noone@.anon.com> wrote in message
news:3f7e2c34$1@.news.microsoft.com...
> Trace flag 3226 can be used for that purpose.
> "Egbon V." <vnjowusi@.gosps.com> wrote in message
> news:uxOZ1rfiDHA.548@.TK2MSFTNGP11.phx.gbl...
> > Is it possible to stop SQL Server from writing to windows eventLog after
> > completing a Log backup?
> >
> > Thanks.
> >
> > Egbon
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>
completing a Log backup?
Thanks.
Egbon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Trace flag 3226 can be used for that purpose.
"Egbon V." <vnjowusi@.gosps.com> wrote in message
news:uxOZ1rfiDHA.548@.TK2MSFTNGP11.phx.gbl...
> Is it possible to stop SQL Server from writing to windows eventLog after
> completing a Log backup?
> Thanks.
> Egbon
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks a lot! Where can I find documentation to read more about this.
Egbon
"Anonymous" <noone@.anon.com> wrote in message
news:3f7e2c34$1@.news.microsoft.com...
> Trace flag 3226 can be used for that purpose.
> "Egbon V." <vnjowusi@.gosps.com> wrote in message
> news:uxOZ1rfiDHA.548@.TK2MSFTNGP11.phx.gbl...
> > Is it possible to stop SQL Server from writing to windows eventLog after
> > completing a Log backup?
> >
> > Thanks.
> >
> > Egbon
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>
Wednesday, March 7, 2012
Backup and restore to a different DB
Hello,
I am trying to get all the data from one DB and put it in another DB. I
want to do this via TSQL so that I can script it to run daily.
I have tried the following:
****************************************************************************
USE master
EXEC sp_addumpdevice 'disk', 'SMSBackup',
'F:\MSSQL\backup\SMSBackup.dat'
Backup Database SMS_S01 to SMSBackup
Restore filelistonly from SMSBackup
Restore Database Inventory from SMSBackup with Move 'SMSBackup' TO
'F:\MSSQL\Data\Inventory.mdf'
****************************************************************************
I get the following results:
****************************************************************************
(1 row(s) affected)
'Disk' device added.
Processed 24344 pages for database 'SMS_S01', file 'SMS_S01_Data' on
file 4.
Processed 1 pages for database 'SMS_S01', file 'SMS_S01_Log' on file 4.
BACKUP DATABASE successfully processed 24345 pages in 13.192 seconds
(15.117 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 7
Logical file 'SMSBackup' is not part of database 'Inventory'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
****************************************************************************
What am I doing wrong here? Should I be trying to use a file rather
than disk device?
Cheers-- Backup Database
BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
GO
-- Show File info
RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
GO
-- Restore the files for DatabaseName2 new db
RESTORE DATABASE DatabaseName2
FROM DISK = 'D:\DatabaseName.bak' -- backuped file
WITH RECOVERY,
MOVE 'DatabaseName_Data' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
MOVE 'DatabaseName_Log' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
GO|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Thanks,
Sree
"dishan@.gmail.com" wrote:
> -- Backup Database
> BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
> GO
> -- Show File info
> RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
> GO
> -- Restore the files for DatabaseName2 new db
> RESTORE DATABASE DatabaseName2
> FROM DISK = 'D:\DatabaseName.bak' -- backuped file
> WITH RECOVERY,
> MOVE 'DatabaseName_Data' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
> MOVE 'DatabaseName_Log' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
> GO
>|||Sreejith G wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
>
> as input of
>
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
>
> Thanks,
> Sree
Thanks. Does that mean that it cannot be done as an automated script,
or is there a way to pass the output of the RESTORE FILELISTONLY to the
RESTORE DATABASE command (of does your command do that, but I just
can't tell)?|||There should be no proble with diskdevice.
Do,
restore database inventory
from smsbackup
with move
<logical name for data file> to <physical path>,
move
<logical name for log file> to <physical path>
Regards
Amish Shah|||> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command
Not sure exactly what you are looking for, but I have some code at
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp that might be useful, with some
changes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cqmman" <cqmman@.yahoo.co.uk> wrote in message
news:1139917015.033030.137950@.g44g2000cwa.googlegroups.com...
> Sreejith G wrote:
>> You need to provide the logical filename that you got from the output of
>> "RESTORE FILELISTONLY" +>
>> RESTORE FILELISTONLY
>> FROM DISK = 'F:\backupfilename.bak'
>>
>> as input of
>>
>> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
>> FROM DISK = 'F:\backupfilename.bak'
>> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
>> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
>> STATS = 1, REPLACE
>> GO
>>
>> Thanks,
>> Sree
> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command (of does your command do that, but I just
> can't tell)?
>|||Tibor Karaszi wrote:
> Not sure exactly what you are looking for, but I have some code at
> http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp that might be useful, with some
> changes.
>
Thanks I will take a look.
Bascially, I have a database, and I want to perform queries on it from
remote workstations. I don't want to be querying the original DB, so
want a DB which is effectively a copy. I want to make this copy (or
update it) automatically (daily), and thought that the best way to do
this would be a scheduled TSQL script.. So I am trying to get a TSQL
script that will take a copy of the DB, and put that data into a
different DB.
Cheers
I am trying to get all the data from one DB and put it in another DB. I
want to do this via TSQL so that I can script it to run daily.
I have tried the following:
****************************************************************************
USE master
EXEC sp_addumpdevice 'disk', 'SMSBackup',
'F:\MSSQL\backup\SMSBackup.dat'
Backup Database SMS_S01 to SMSBackup
Restore filelistonly from SMSBackup
Restore Database Inventory from SMSBackup with Move 'SMSBackup' TO
'F:\MSSQL\Data\Inventory.mdf'
****************************************************************************
I get the following results:
****************************************************************************
(1 row(s) affected)
'Disk' device added.
Processed 24344 pages for database 'SMS_S01', file 'SMS_S01_Data' on
file 4.
Processed 1 pages for database 'SMS_S01', file 'SMS_S01_Log' on file 4.
BACKUP DATABASE successfully processed 24345 pages in 13.192 seconds
(15.117 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 7
Logical file 'SMSBackup' is not part of database 'Inventory'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
****************************************************************************
What am I doing wrong here? Should I be trying to use a file rather
than disk device?
Cheers-- Backup Database
BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
GO
-- Show File info
RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
GO
-- Restore the files for DatabaseName2 new db
RESTORE DATABASE DatabaseName2
FROM DISK = 'D:\DatabaseName.bak' -- backuped file
WITH RECOVERY,
MOVE 'DatabaseName_Data' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
MOVE 'DatabaseName_Log' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
GO|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Thanks,
Sree
"dishan@.gmail.com" wrote:
> -- Backup Database
> BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
> GO
> -- Show File info
> RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
> GO
> -- Restore the files for DatabaseName2 new db
> RESTORE DATABASE DatabaseName2
> FROM DISK = 'D:\DatabaseName.bak' -- backuped file
> WITH RECOVERY,
> MOVE 'DatabaseName_Data' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
> MOVE 'DatabaseName_Log' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
> GO
>|||Sreejith G wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
>
> as input of
>
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
>
> Thanks,
> Sree
Thanks. Does that mean that it cannot be done as an automated script,
or is there a way to pass the output of the RESTORE FILELISTONLY to the
RESTORE DATABASE command (of does your command do that, but I just
can't tell)?|||There should be no proble with diskdevice.
Do,
restore database inventory
from smsbackup
with move
<logical name for data file> to <physical path>,
move
<logical name for log file> to <physical path>
Regards
Amish Shah|||> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command
Not sure exactly what you are looking for, but I have some code at
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp that might be useful, with some
changes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cqmman" <cqmman@.yahoo.co.uk> wrote in message
news:1139917015.033030.137950@.g44g2000cwa.googlegroups.com...
> Sreejith G wrote:
>> You need to provide the logical filename that you got from the output of
>> "RESTORE FILELISTONLY" +>
>> RESTORE FILELISTONLY
>> FROM DISK = 'F:\backupfilename.bak'
>>
>> as input of
>>
>> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
>> FROM DISK = 'F:\backupfilename.bak'
>> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
>> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
>> STATS = 1, REPLACE
>> GO
>>
>> Thanks,
>> Sree
> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command (of does your command do that, but I just
> can't tell)?
>|||Tibor Karaszi wrote:
> Not sure exactly what you are looking for, but I have some code at
> http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp that might be useful, with some
> changes.
>
Thanks I will take a look.
Bascially, I have a database, and I want to perform queries on it from
remote workstations. I don't want to be querying the original DB, so
want a DB which is effectively a copy. I want to make this copy (or
update it) automatically (daily), and thought that the best way to do
this would be a scheduled TSQL script.. So I am trying to get a TSQL
script that will take a copy of the DB, and put that data into a
different DB.
Cheers
Backup and restore to a different DB
Hello,
I am trying to get all the data from one DB and put it in another DB. I
want to do this via TSQL so that I can script it to run daily.
I have tried the following:
****************************************
************************************
USE master
EXEC sp_addumpdevice 'disk', 'SMSBackup',
'F:\MSSQL\backup\SMSBackup.dat'
Backup Database SMS_S01 to SMSBackup
Restore filelistonly from SMSBackup
Restore Database Inventory from SMSBackup with Move 'SMSBackup' TO
'F:\MSSQL\Data\Inventory.mdf'
****************************************
************************************
I get the following results:
****************************************
************************************
(1 row(s) affected)
'Disk' device added.
Processed 24344 pages for database 'SMS_S01', file 'SMS_S01_Data' on
file 4.
Processed 1 pages for database 'SMS_S01', file 'SMS_S01_Log' on file 4.
BACKUP DATABASE successfully processed 24345 pages in 13.192 seconds
(15.117 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 7
Logical file 'SMSBackup' is not part of database 'Inventory'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
****************************************
************************************
What am I doing wrong here? Should I be trying to use a file rather
than disk device?
Cheers-- Backup Database
BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
GO
-- Show File info
RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
GO
-- Restore the files for DatabaseName2 new db
RESTORE DATABASE DatabaseName2
FROM DISK = 'D:\DatabaseName.bak' -- backuped file
WITH RECOVERY,
MOVE 'DatabaseName_Data' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
MOVE 'DatabaseName_Log' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
GO|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Thanks,
Sree
"dishan@.gmail.com" wrote:
> -- Backup Database
> BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
> GO
> -- Show File info
> RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
> GO
> -- Restore the files for DatabaseName2 new db
> RESTORE DATABASE DatabaseName2
> FROM DISK = 'D:\DatabaseName.bak' -- backuped file
> WITH RECOVERY,
> MOVE 'DatabaseName_Data' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
> MOVE 'DatabaseName_Log' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
> GO
>|||Sreejith G wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
>
> as input of
>
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf
',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
>
> Thanks,
> Sree
Thanks. Does that mean that it cannot be done as an automated script,
or is there a way to pass the output of the RESTORE FILELISTONLY to the
RESTORE DATABASE command (of does your command do that, but I just
can't tell)?|||There should be no proble with diskdevice.
Do,
restore database inventory
from smsbackup
with move
<logical name for data file> to <physical path>,
move
<logical name for log file> to <physical path>
Regards
Amish Shah|||> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command
Not sure exactly what you are looking for, but I have some code at
http://www.karaszi.com/SQLServer/ut...all_in_file.asp that might be
useful, with some
changes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cqmman" <cqmman@.yahoo.co.uk> wrote in message
news:1139917015.033030.137950@.g44g2000cwa.googlegroups.com...
> Sreejith G wrote:
> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command (of does your command do that, but I just
> can't tell)?
>|||Tibor Karaszi wrote:
> Not sure exactly what you are looking for, but I have some code at
> http://www.karaszi.com/SQLServer/ut...all_in_file.asp that might b
e useful, with some
> changes.
>
Thanks I will take a look.
Bascially, I have a database, and I want to perform queries on it from
remote workstations. I don't want to be querying the original DB, so
want a DB which is effectively a copy. I want to make this copy (or
update it) automatically (daily), and thought that the best way to do
this would be a scheduled TSQL script.. So I am trying to get a TSQL
script that will take a copy of the DB, and put that data into a
different DB.
Cheers
I am trying to get all the data from one DB and put it in another DB. I
want to do this via TSQL so that I can script it to run daily.
I have tried the following:
****************************************
************************************
USE master
EXEC sp_addumpdevice 'disk', 'SMSBackup',
'F:\MSSQL\backup\SMSBackup.dat'
Backup Database SMS_S01 to SMSBackup
Restore filelistonly from SMSBackup
Restore Database Inventory from SMSBackup with Move 'SMSBackup' TO
'F:\MSSQL\Data\Inventory.mdf'
****************************************
************************************
I get the following results:
****************************************
************************************
(1 row(s) affected)
'Disk' device added.
Processed 24344 pages for database 'SMS_S01', file 'SMS_S01_Data' on
file 4.
Processed 1 pages for database 'SMS_S01', file 'SMS_S01_Log' on file 4.
BACKUP DATABASE successfully processed 24345 pages in 13.192 seconds
(15.117 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 7
Logical file 'SMSBackup' is not part of database 'Inventory'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
****************************************
************************************
What am I doing wrong here? Should I be trying to use a file rather
than disk device?
Cheers-- Backup Database
BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
GO
-- Show File info
RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
GO
-- Restore the files for DatabaseName2 new db
RESTORE DATABASE DatabaseName2
FROM DISK = 'D:\DatabaseName.bak' -- backuped file
WITH RECOVERY,
MOVE 'DatabaseName_Data' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
MOVE 'DatabaseName_Log' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
GO|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Thanks,
Sree
"dishan@.gmail.com" wrote:
> -- Backup Database
> BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
> GO
> -- Show File info
> RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
> GO
> -- Restore the files for DatabaseName2 new db
> RESTORE DATABASE DatabaseName2
> FROM DISK = 'D:\DatabaseName.bak' -- backuped file
> WITH RECOVERY,
> MOVE 'DatabaseName_Data' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
> MOVE 'DatabaseName_Log' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
> GO
>|||Sreejith G wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
>
> as input of
>
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf
',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
>
> Thanks,
> Sree
Thanks. Does that mean that it cannot be done as an automated script,
or is there a way to pass the output of the RESTORE FILELISTONLY to the
RESTORE DATABASE command (of does your command do that, but I just
can't tell)?|||There should be no proble with diskdevice.
Do,
restore database inventory
from smsbackup
with move
<logical name for data file> to <physical path>,
move
<logical name for log file> to <physical path>
Regards
Amish Shah|||> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command
Not sure exactly what you are looking for, but I have some code at
http://www.karaszi.com/SQLServer/ut...all_in_file.asp that might be
useful, with some
changes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cqmman" <cqmman@.yahoo.co.uk> wrote in message
news:1139917015.033030.137950@.g44g2000cwa.googlegroups.com...
> Sreejith G wrote:
> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command (of does your command do that, but I just
> can't tell)?
>|||Tibor Karaszi wrote:
> Not sure exactly what you are looking for, but I have some code at
> http://www.karaszi.com/SQLServer/ut...all_in_file.asp that might b
e useful, with some
> changes.
>
Thanks I will take a look.
Bascially, I have a database, and I want to perform queries on it from
remote workstations. I don't want to be querying the original DB, so
want a DB which is effectively a copy. I want to make this copy (or
update it) automatically (daily), and thought that the best way to do
this would be a scheduled TSQL script.. So I am trying to get a TSQL
script that will take a copy of the DB, and put that data into a
different DB.
Cheers
Backup and restore to a different DB
Hello,
I am trying to get all the data from one DB and put it in another DB. I
want to do this via TSQL so that I can script it to run daily.
I have tried the following:
************************************************** **************************
USE master
EXEC sp_addumpdevice 'disk', 'SMSBackup',
'F:\MSSQL\backup\SMSBackup.dat'
Backup Database SMS_S01 to SMSBackup
Restore filelistonly from SMSBackup
Restore Database Inventory from SMSBackup with Move 'SMSBackup' TO
'F:\MSSQL\Data\Inventory.mdf'
************************************************** **************************
I get the following results:
************************************************** **************************
(1 row(s) affected)
'Disk' device added.
Processed 24344 pages for database 'SMS_S01', file 'SMS_S01_Data' on
file 4.
Processed 1 pages for database 'SMS_S01', file 'SMS_S01_Log' on file 4.
BACKUP DATABASE successfully processed 24345 pages in 13.192 seconds
(15.117 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 7
Logical file 'SMSBackup' is not part of database 'Inventory'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
************************************************** **************************
What am I doing wrong here? Should I be trying to use a file rather
than disk device?
Cheers
-- Backup Database
BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
GO
-- Show File info
RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
GO
-- Restore the files for DatabaseName2 new db
RESTORE DATABASE DatabaseName2
FROM DISK = 'D:\DatabaseName.bak' -- backuped file
WITH RECOVERY,
MOVE 'DatabaseName_Data' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
MOVE 'DatabaseName_Log' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
GO
|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Thanks,
Sree
"dishan@.gmail.com" wrote:
> -- Backup Database
> BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
> GO
> -- Show File info
> RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
> GO
> -- Restore the files for DatabaseName2 new db
> RESTORE DATABASE DatabaseName2
> FROM DISK = 'D:\DatabaseName.bak' -- backuped file
> WITH RECOVERY,
> MOVE 'DatabaseName_Data' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
> MOVE 'DatabaseName_Log' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
> GO
>
|||Sreejith G wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
>
> as input of
>
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
>
> Thanks,
> Sree
Thanks. Does that mean that it cannot be done as an automated script,
or is there a way to pass the output of the RESTORE FILELISTONLY to the
RESTORE DATABASE command (of does your command do that, but I just
can't tell)?
|||There should be no proble with diskdevice.
Do,
restore database inventory
from smsbackup
with move
<logical name for data file> to <physical path>,
move
<logical name for log file> to <physical path>
Regards
Amish Shah
|||> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command
Not sure exactly what you are looking for, but I have some code at
http://www.karaszi.com/SQLServer/uti...ll_in_file.asp that might be useful, with some
changes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cqmman" <cqmman@.yahoo.co.uk> wrote in message
news:1139917015.033030.137950@.g44g2000cwa.googlegr oups.com...
> Sreejith G wrote:
> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command (of does your command do that, but I just
> can't tell)?
>
|||Tibor Karaszi wrote:
> Not sure exactly what you are looking for, but I have some code at
> http://www.karaszi.com/SQLServer/uti...ll_in_file.asp that might be useful, with some
> changes.
>
Thanks I will take a look.
Bascially, I have a database, and I want to perform queries on it from
remote workstations. I don't want to be querying the original DB, so
want a DB which is effectively a copy. I want to make this copy (or
update it) automatically (daily), and thought that the best way to do
this would be a scheduled TSQL script.. So I am trying to get a TSQL
script that will take a copy of the DB, and put that data into a
different DB.
Cheers
I am trying to get all the data from one DB and put it in another DB. I
want to do this via TSQL so that I can script it to run daily.
I have tried the following:
************************************************** **************************
USE master
EXEC sp_addumpdevice 'disk', 'SMSBackup',
'F:\MSSQL\backup\SMSBackup.dat'
Backup Database SMS_S01 to SMSBackup
Restore filelistonly from SMSBackup
Restore Database Inventory from SMSBackup with Move 'SMSBackup' TO
'F:\MSSQL\Data\Inventory.mdf'
************************************************** **************************
I get the following results:
************************************************** **************************
(1 row(s) affected)
'Disk' device added.
Processed 24344 pages for database 'SMS_S01', file 'SMS_S01_Data' on
file 4.
Processed 1 pages for database 'SMS_S01', file 'SMS_S01_Log' on file 4.
BACKUP DATABASE successfully processed 24345 pages in 13.192 seconds
(15.117 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 7
Logical file 'SMSBackup' is not part of database 'Inventory'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
************************************************** **************************
What am I doing wrong here? Should I be trying to use a file rather
than disk device?
Cheers
-- Backup Database
BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
GO
-- Show File info
RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
GO
-- Restore the files for DatabaseName2 new db
RESTORE DATABASE DatabaseName2
FROM DISK = 'D:\DatabaseName.bak' -- backuped file
WITH RECOVERY,
MOVE 'DatabaseName_Data' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
MOVE 'DatabaseName_Log' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
GO
|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Thanks,
Sree
"dishan@.gmail.com" wrote:
> -- Backup Database
> BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
> GO
> -- Show File info
> RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
> GO
> -- Restore the files for DatabaseName2 new db
> RESTORE DATABASE DatabaseName2
> FROM DISK = 'D:\DatabaseName.bak' -- backuped file
> WITH RECOVERY,
> MOVE 'DatabaseName_Data' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
> MOVE 'DatabaseName_Log' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
> GO
>
|||Sreejith G wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
>
> as input of
>
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
>
> Thanks,
> Sree
Thanks. Does that mean that it cannot be done as an automated script,
or is there a way to pass the output of the RESTORE FILELISTONLY to the
RESTORE DATABASE command (of does your command do that, but I just
can't tell)?
|||There should be no proble with diskdevice.
Do,
restore database inventory
from smsbackup
with move
<logical name for data file> to <physical path>,
move
<logical name for log file> to <physical path>
Regards
Amish Shah
|||> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command
Not sure exactly what you are looking for, but I have some code at
http://www.karaszi.com/SQLServer/uti...ll_in_file.asp that might be useful, with some
changes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cqmman" <cqmman@.yahoo.co.uk> wrote in message
news:1139917015.033030.137950@.g44g2000cwa.googlegr oups.com...
> Sreejith G wrote:
> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command (of does your command do that, but I just
> can't tell)?
>
|||Tibor Karaszi wrote:
> Not sure exactly what you are looking for, but I have some code at
> http://www.karaszi.com/SQLServer/uti...ll_in_file.asp that might be useful, with some
> changes.
>
Thanks I will take a look.
Bascially, I have a database, and I want to perform queries on it from
remote workstations. I don't want to be querying the original DB, so
want a DB which is effectively a copy. I want to make this copy (or
update it) automatically (daily), and thought that the best way to do
this would be a scheduled TSQL script.. So I am trying to get a TSQL
script that will take a copy of the DB, and put that data into a
different DB.
Cheers
backup and restore Question
I had used to make full backup of a database and using winzip to compress
the file to transfer it via email to another location. And because of the
big email attachement of the compressed database I didn't succeed in
transfering the data. Then I tried to alter the database as adding a
secondary group file with 2 files.
and I tried to backup the files each indivually and on the another location
(SERVER) I tried to restore the files but I didn't succeed.
How Can I achieve this goal without any errors ?How about just backup the db and then handling both files? When you backup
the db, both files are backed up.
"Awada" <awada@.pharmacare-ltd.com> wrote in message
news:OAnA47wPDHA.1148@.TK2MSFTNGP11.phx.gbl...
> I had used to make full backup of a database and using winzip to compress
> the file to transfer it via email to another location. And because of the
> big email attachement of the compressed database I didn't succeed in
> transfering the data. Then I tried to alter the database as adding a
> secondary group file with 2 files.
> and I tried to backup the files each indivually and on the another
location
> (SERVER) I tried to restore the files but I didn't succeed.
> How Can I achieve this goal without any errors ?
>|||Whatever number of files you have, the db backup should back them all up.
Then you can compress them all, mail them all, restore the db with them
all -- treating the group of files the same as if there were only one file.
"Awada" <awada@.pharmacare-ltd.com> wrote in message
news:OFvJ0PxPDHA.3236@.TK2MSFTNGP10.phx.gbl...
> The Goal is to get 3 or more files to compress and send via email not one
> big file
>
> "Quentin Ran" <quentinran@.yahoo.com> wrote in message
> news:#m6oUIxPDHA.3192@.TK2MSFTNGP10.phx.gbl...
> > How about just backup the db and then handling both files? When you
> backup
> > the db, both files are backed up.
> >
> > "Awada" <awada@.pharmacare-ltd.com> wrote in message
> > news:OAnA47wPDHA.1148@.TK2MSFTNGP11.phx.gbl...
> > > I had used to make full backup of a database and using winzip to
> compress
> > > the file to transfer it via email to another location. And because of
> the
> > > big email attachement of the compressed database I didn't succeed in
> > > transfering the data. Then I tried to alter the database as adding a
> > > secondary group file with 2 files.
> > > and I tried to backup the files each indivually and on the another
> > location
> > > (SERVER) I tried to restore the files but I didn't succeed.
> > > How Can I achieve this goal without any errors ?
> > >
> > >
> >
> >
>|||I backup DBs on production server, zip them into a RAR file (170 MB) and
schedule a DTS job to FTP the file to an off-site location. How big is your
attachment?
"Awada" <awada@.pharmacare-ltd.com> wrote in message
news:OAnA47wPDHA.1148@.TK2MSFTNGP11.phx.gbl...
> I had used to make full backup of a database and using winzip to compress
> the file to transfer it via email to another location. And because of the
> big email attachement of the compressed database I didn't succeed in
> transfering the data. Then I tried to alter the database as adding a
> secondary group file with 2 files.
> and I tried to backup the files each indivually and on the another
location
> (SERVER) I tried to restore the files but I didn't succeed.
> How Can I achieve this goal without any errors ?
>|||Doesn't winzip give an option to split the zip file into
multiple files? Don't have it installed here to check.
>--Original Message--
>Whatever number of files you have, the db backup should
back them all up.
>Then you can compress them all, mail them all, restore
the db with them
>all -- treating the group of files the same as if there
were only one file.
>"Awada" <awada@.pharmacare-ltd.com> wrote in message
>news:OFvJ0PxPDHA.3236@.TK2MSFTNGP10.phx.gbl...
>> The Goal is to get 3 or more files to compress and send
via email not one
>> big file
>>
>> "Quentin Ran" <quentinran@.yahoo.com> wrote in message
>> news:#m6oUIxPDHA.3192@.TK2MSFTNGP10.phx.gbl...
>> > How about just backup the db and then handling both
files? When you
>> backup
>> > the db, both files are backed up.
>> >
>> > "Awada" <awada@.pharmacare-ltd.com> wrote in message
>> > news:OAnA47wPDHA.1148@.TK2MSFTNGP11.phx.gbl...
>> > > I had used to make full backup of a database and
using winzip to
>> compress
>> > > the file to transfer it via email to another
location. And because of
>> the
>> > > big email attachement of the compressed database I
didn't succeed in
>> > > transfering the data. Then I tried to alter the
database as adding a
>> > > secondary group file with 2 files.
>> > > and I tried to backup the files each indivually and
on the another
>> > location
>> > > (SERVER) I tried to restore the files but I didn't
succeed.
>> > > How Can I achieve this goal without any errors ?
>> > >
>> > >
>> >
>> >
>>
>
>.
>|||Awada -
I follow this process -
1. Backup the Database
2. Rar the DB (Ours is too large for winzip)
3. FTP the database to the remote server
4. Unrar and restore on remote server
This whole process can be automated in a couple of different ways if
you want to (I did).
HTH
"Awada" <awada@.pharmacare-ltd.com> wrote in message news:<OAnA47wPDHA.1148@.TK2MSFTNGP11.phx.gbl>...
> I had used to make full backup of a database and using winzip to compress
> the file to transfer it via email to another location. And because of the
> big email attachement of the compressed database I didn't succeed in
> transfering the data. Then I tried to alter the database as adding a
> secondary group file with 2 files.
> and I tried to backup the files each indivually and on the another location
> (SERVER) I tried to restore the files but I didn't succeed.
> How Can I achieve this goal without any errors ?
the file to transfer it via email to another location. And because of the
big email attachement of the compressed database I didn't succeed in
transfering the data. Then I tried to alter the database as adding a
secondary group file with 2 files.
and I tried to backup the files each indivually and on the another location
(SERVER) I tried to restore the files but I didn't succeed.
How Can I achieve this goal without any errors ?How about just backup the db and then handling both files? When you backup
the db, both files are backed up.
"Awada" <awada@.pharmacare-ltd.com> wrote in message
news:OAnA47wPDHA.1148@.TK2MSFTNGP11.phx.gbl...
> I had used to make full backup of a database and using winzip to compress
> the file to transfer it via email to another location. And because of the
> big email attachement of the compressed database I didn't succeed in
> transfering the data. Then I tried to alter the database as adding a
> secondary group file with 2 files.
> and I tried to backup the files each indivually and on the another
location
> (SERVER) I tried to restore the files but I didn't succeed.
> How Can I achieve this goal without any errors ?
>|||Whatever number of files you have, the db backup should back them all up.
Then you can compress them all, mail them all, restore the db with them
all -- treating the group of files the same as if there were only one file.
"Awada" <awada@.pharmacare-ltd.com> wrote in message
news:OFvJ0PxPDHA.3236@.TK2MSFTNGP10.phx.gbl...
> The Goal is to get 3 or more files to compress and send via email not one
> big file
>
> "Quentin Ran" <quentinran@.yahoo.com> wrote in message
> news:#m6oUIxPDHA.3192@.TK2MSFTNGP10.phx.gbl...
> > How about just backup the db and then handling both files? When you
> backup
> > the db, both files are backed up.
> >
> > "Awada" <awada@.pharmacare-ltd.com> wrote in message
> > news:OAnA47wPDHA.1148@.TK2MSFTNGP11.phx.gbl...
> > > I had used to make full backup of a database and using winzip to
> compress
> > > the file to transfer it via email to another location. And because of
> the
> > > big email attachement of the compressed database I didn't succeed in
> > > transfering the data. Then I tried to alter the database as adding a
> > > secondary group file with 2 files.
> > > and I tried to backup the files each indivually and on the another
> > location
> > > (SERVER) I tried to restore the files but I didn't succeed.
> > > How Can I achieve this goal without any errors ?
> > >
> > >
> >
> >
>|||I backup DBs on production server, zip them into a RAR file (170 MB) and
schedule a DTS job to FTP the file to an off-site location. How big is your
attachment?
"Awada" <awada@.pharmacare-ltd.com> wrote in message
news:OAnA47wPDHA.1148@.TK2MSFTNGP11.phx.gbl...
> I had used to make full backup of a database and using winzip to compress
> the file to transfer it via email to another location. And because of the
> big email attachement of the compressed database I didn't succeed in
> transfering the data. Then I tried to alter the database as adding a
> secondary group file with 2 files.
> and I tried to backup the files each indivually and on the another
location
> (SERVER) I tried to restore the files but I didn't succeed.
> How Can I achieve this goal without any errors ?
>|||Doesn't winzip give an option to split the zip file into
multiple files? Don't have it installed here to check.
>--Original Message--
>Whatever number of files you have, the db backup should
back them all up.
>Then you can compress them all, mail them all, restore
the db with them
>all -- treating the group of files the same as if there
were only one file.
>"Awada" <awada@.pharmacare-ltd.com> wrote in message
>news:OFvJ0PxPDHA.3236@.TK2MSFTNGP10.phx.gbl...
>> The Goal is to get 3 or more files to compress and send
via email not one
>> big file
>>
>> "Quentin Ran" <quentinran@.yahoo.com> wrote in message
>> news:#m6oUIxPDHA.3192@.TK2MSFTNGP10.phx.gbl...
>> > How about just backup the db and then handling both
files? When you
>> backup
>> > the db, both files are backed up.
>> >
>> > "Awada" <awada@.pharmacare-ltd.com> wrote in message
>> > news:OAnA47wPDHA.1148@.TK2MSFTNGP11.phx.gbl...
>> > > I had used to make full backup of a database and
using winzip to
>> compress
>> > > the file to transfer it via email to another
location. And because of
>> the
>> > > big email attachement of the compressed database I
didn't succeed in
>> > > transfering the data. Then I tried to alter the
database as adding a
>> > > secondary group file with 2 files.
>> > > and I tried to backup the files each indivually and
on the another
>> > location
>> > > (SERVER) I tried to restore the files but I didn't
succeed.
>> > > How Can I achieve this goal without any errors ?
>> > >
>> > >
>> >
>> >
>>
>
>.
>|||Awada -
I follow this process -
1. Backup the Database
2. Rar the DB (Ours is too large for winzip)
3. FTP the database to the remote server
4. Unrar and restore on remote server
This whole process can be automated in a couple of different ways if
you want to (I did).
HTH
"Awada" <awada@.pharmacare-ltd.com> wrote in message news:<OAnA47wPDHA.1148@.TK2MSFTNGP11.phx.gbl>...
> I had used to make full backup of a database and using winzip to compress
> the file to transfer it via email to another location. And because of the
> big email attachement of the compressed database I didn't succeed in
> transfering the data. Then I tried to alter the database as adding a
> secondary group file with 2 files.
> and I tried to backup the files each indivually and on the another location
> (SERVER) I tried to restore the files but I didn't succeed.
> How Can I achieve this goal without any errors ?
Saturday, February 25, 2012
Backup and restore database between computers
What is the way(s) to backup a database from a Server and restore it to
another?
Thanks for replying...
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1One way is to do a backup in SQL, then restore it on another server -
however, you have to worry about whether the servers are running the same
version of SQL. If not you need to be aware of what the differences are
and what to do about them.
You can detach, copy the db and attach it on the new server - again see
above about version diffrences.
pedestrian via droptable.com wrote:
> What is the way(s) to backup a database from a Server and restore it to
> another?
> Thanks for replying...
>
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to email|||Thanks for the answer, Brett.
Brett I. Holcomb wrote:
>One way is to do a backup in SQL, then restore it on another server ...
Message posted via http://www.droptable.com
another?
Thanks for replying...
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1One way is to do a backup in SQL, then restore it on another server -
however, you have to worry about whether the servers are running the same
version of SQL. If not you need to be aware of what the differences are
and what to do about them.
You can detach, copy the db and attach it on the new server - again see
above about version diffrences.
pedestrian via droptable.com wrote:
> What is the way(s) to backup a database from a Server and restore it to
> another?
> Thanks for replying...
>
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to email|||Thanks for the answer, Brett.
Brett I. Holcomb wrote:
>One way is to do a backup in SQL, then restore it on another server ...
Message posted via http://www.droptable.com
Backup and restore database between computers
What is the way(s) to backup a database from a Server and restore it to
another?
Thanks for replying...
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1One way is to do a backup in SQL, then restore it on another server -
however, you have to worry about whether the servers are running the same
version of SQL. If not you need to be aware of what the differences are
and what to do about them.
You can detach, copy the db and attach it on the new server - again see
above about version diffrences.
pedestrian via SQLMonster.com wrote:
> What is the way(s) to backup a database from a Server and restore it to
> another?
> Thanks for replying...
>
--
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to email|||Thanks for the answer, Brett.
Brett I. Holcomb wrote:
>One way is to do a backup in SQL, then restore it on another server ...
--
Message posted via http://www.sqlmonster.com
another?
Thanks for replying...
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1One way is to do a backup in SQL, then restore it on another server -
however, you have to worry about whether the servers are running the same
version of SQL. If not you need to be aware of what the differences are
and what to do about them.
You can detach, copy the db and attach it on the new server - again see
above about version diffrences.
pedestrian via SQLMonster.com wrote:
> What is the way(s) to backup a database from a Server and restore it to
> another?
> Thanks for replying...
>
--
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to email|||Thanks for the answer, Brett.
Brett I. Holcomb wrote:
>One way is to do a backup in SQL, then restore it on another server ...
--
Message posted via http://www.sqlmonster.com
Friday, February 10, 2012
Backing up to a Tape Drive
I am setting up a new SQL Server that has an external HP 920 SAS Ultrium tape drive attached to it via a p400 sas card.
I am running 2003 R2 x64 with 2005 SQL Server x64, and I am trying to set up my DB backup plan but for some reason I can't get SQL to recognize my tape drive, alhough it is listed in device manager.
Anyone have any thoughts? I'm kind of stumped.
Thanks in advance,
Mark.
What do you mean SQL Server doesn't recognise it?
It's not listed in the dropdown when you try to create a new backup device in SQL Server Management Studio.|||Sorry ... Yes when trying to add it as a new backup device in Management Studio.
Also I have SP2 installed.
|||Not sure - maybe there's a problem with the compatibility of the drive. I don't know if there might be a problem if the drive wasn't attached and switched on when SQL Server started up.
It is worth trying to run the SQL
EXEC sp_addumpdevice 'tape', 'DumpToTape', '\\.\tape0'
Assuming that it is the first tape device in the machine.
Sorry you are running off the limit of my knowledge.
|||I did that before I started the thread, but i must have done it incorrectly because when i ran it just now it created the device under backup devices and I am now all set.
Thanks for the help.
|||Sorry , I have already added to DumpDevice. but The Error is represent.
would u tell me what to do ? it is SQL Server 2005 X64 Bug ?
thanks
Backing up to a Tape Drive
I am setting up a new SQL Server that has an external HP 920 SAS Ultrium tape drive attached to it via a p400 sas card.
I am running 2003 R2 x64 with 2005 SQL Server x64, and I am trying to set up my DB backup plan but for some reason I can't get SQL to recognize my tape drive, alhough it is listed in device manager.
Anyone have any thoughts? I'm kind of stumped.
Thanks in advance,
Mark.
What do you mean SQL Server doesn't recognise it?
It's not listed in the dropdown when you try to create a new backup device in SQL Server Management Studio.|||
Sorry ... Yes when trying to add it as a new backup device in Management Studio.
Also I have SP2 installed.
|||Not sure - maybe there's a problem with the compatibility of the drive. I don't know if there might be a problem if the drive wasn't attached and switched on when SQL Server started up.
It is worth trying to run the SQL
EXEC sp_addumpdevice 'tape', 'DumpToTape', '\\.\tape0'
Assuming that it is the first tape device in the machine.
Sorry you are running off the limit of my knowledge.
|||I did that before I started the thread, but i must have done it incorrectly because when i ran it just now it created the device under backup devices and I am now all set.
Thanks for the help.
|||Sorry , I have already added to DumpDevice. but The Error is represent.
would u tell me what to do ? it is SQL Server 2005 X64 Bug ?
thanks
Subscribe to:
Posts (Atom)