Can you still create a backup device in SQL 2005 and if so How?
Thanks!Yes you can still use sp_addumpdevice or in SSMS you can go to the server
objects and add one there.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<msnews.microsoft.com> wrote in message
news:Obr1jyo6HHA.1900@.TK2MSFTNGP02.phx.gbl...
> Can you still create a backup device in SQL 2005 and if so How?
> Thanks!
>
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Tuesday, March 20, 2012
Backup device
Hi all,
I tried to create the new backup device with UNC path on the remote server
\\remoteserver\share on the sql server. But, I got "can not verify the
path" message and I have no problem to access the share through the sql
server. I checked the share and file security on the share and file and they
are fine.
I can not verify why I can not create UNC backup device on this sql server.
(windows 2003 R2 SP2 and sql 2000)
Can anyone help?
Try using sp_addumpdevice directly. It might just be the GUI which try to "assist" you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.discussions.microsoft.com> wrote in message
news:75486005-3FAB-499B-9C38-7C128149BFF9@.microsoft.com...
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?
|||> I have no problem to access the share through the sql
> server.
How did you access the share through the SQL instance?
Linchi
"John" wrote:
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?
I tried to create the new backup device with UNC path on the remote server
\\remoteserver\share on the sql server. But, I got "can not verify the
path" message and I have no problem to access the share through the sql
server. I checked the share and file security on the share and file and they
are fine.
I can not verify why I can not create UNC backup device on this sql server.
(windows 2003 R2 SP2 and sql 2000)
Can anyone help?
Try using sp_addumpdevice directly. It might just be the GUI which try to "assist" you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.discussions.microsoft.com> wrote in message
news:75486005-3FAB-499B-9C38-7C128149BFF9@.microsoft.com...
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?
|||> I have no problem to access the share through the sql
> server.
How did you access the share through the SQL instance?
Linchi
"John" wrote:
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?
Backup device
Hi all,
I tried to create the new backup device with UNC path on the remote server
\\remoteserver\share on the sql server. But, I got "can not verify the
path" message and I have no problem to access the share through the sql
server. I checked the share and file security on the share and file and they
are fine.
I can not verify why I can not create UNC backup device on this sql server.
(windows 2003 R2 SP2 and sql 2000)
Can anyone help?Try using sp_addumpdevice directly. It might just be the GUI which try to "assist" you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.discussions.microsoft.com> wrote in message
news:75486005-3FAB-499B-9C38-7C128149BFF9@.microsoft.com...
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?|||> I have no problem to access the share through the sql
> server.
How did you access the share through the SQL instance?
Linchi
"John" wrote:
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?sql
I tried to create the new backup device with UNC path on the remote server
\\remoteserver\share on the sql server. But, I got "can not verify the
path" message and I have no problem to access the share through the sql
server. I checked the share and file security on the share and file and they
are fine.
I can not verify why I can not create UNC backup device on this sql server.
(windows 2003 R2 SP2 and sql 2000)
Can anyone help?Try using sp_addumpdevice directly. It might just be the GUI which try to "assist" you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.discussions.microsoft.com> wrote in message
news:75486005-3FAB-499B-9C38-7C128149BFF9@.microsoft.com...
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?|||> I have no problem to access the share through the sql
> server.
How did you access the share through the SQL instance?
Linchi
"John" wrote:
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?sql
Sunday, March 11, 2012
Backup database design
How do I export/create a script into a file with create table...index...
views etc for an existing database ?
Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
views etc for an existing database ?
Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Backup database design
How do I export/create a script into a file with create table...index...
views etc for an existing database ?Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
views etc for an existing database ?Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
backup database by maintenance plan
1. create new MaintenancePlan to get a full backup of myDb by Wizzard,... the message is:
create failed for JobStep 'Subplan' ... the specified '@.subsystem' is invalid .. Error 14234
2. create new MaintenancePlan without the Wizzard, by drag and drop "Back Up Database Task" and configured to full backup myDb scheduled daily the message is: create failed for Job 'MaintenancePlan'
the plan exists without scheduling, after modifying get the message
Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-...).
? can I get help ? - Thanks
There is a KB article to resolve the above error.
http://support.microsoft.com/?kbid=922651
backup database by maintenance plan
1. create new MaintenancePlan to get a full backup of myDb by Wizzard,... the message is:
create failed for JobStep 'Subplan' ... the specified '@.subsystem' is invalid .. Error 14234
2. create new MaintenancePlan without the Wizzard, by drag and drop "Back Up Database Task" and configured to full backup myDb scheduled daily the message is: create failed for Job 'MaintenancePlan'
the plan exists without scheduling, after modifying get the message
Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-...).
? can I get help ? - Thanks
There is a KB article to resolve the above error.
http://support.microsoft.com/?kbid=922651
backup database by maintenance plan
1. create new MaintenancePlan to get a full backup of myDb by Wizzard,... the message is:
create failed for JobStep 'Subplan' ... the specified '@.subsystem' is invalid .. Error 14234
2. create new MaintenancePlan without the Wizzard, by drag and drop "Back Up Database Task" and configured to full backup myDb scheduled daily the message is: create failed for Job 'MaintenancePlan'
the plan exists without scheduling, after modifying get the message
Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-...).
? can I get help ? - Thanks
There is a KB article to resolve the above error.
http://support.microsoft.com/?kbid=922651
Backup database
I have couple of questions about backing up a database
1. How to create a backup file (using script) which only contain the data of a specific time range?
2. How to restore the database with serval backup files; for example, I got backup for june and july, how can I restore them into one mdf file which contains the data of both june and july.
3. how can I backup a database to a remote computer?
Thanks for your concern on my Qestions
hi,
Frankie wrote:
I have couple of questions about backing up a database
1. How to create a backup file (using script) which only contain the data of a specific time range?
2. How to restore the database with serval backup files; for example, I got backup for june and july, how can I restore them into one mdf file which contains the data of both june and july.
I do strongly suggest you to start reading about backup functionality in SQL Server starting from this overview, as it seems you really did not understand what a backup is
3. how can I backup a database to a remote computer?
this can be easely done, even if not directly recommended (at least not by me).. usually you perform a "local" backup and, later, you push or pull that backup remotely via scheduled tasks, or scripts or the like, in order not to load to much the SQL Server service with tasks not directly related to it's main activity.. anyway, you have to grant the account running SQL Server service enougth NTFS permissions on the remote share you are dealing with... in our case, SQLExpress usually runs under Local System, Local Service or Network Service builtin accounts, and you can not add remote permissions to these ones.. you have to define a domain account to use for the service and grant this one appropriate permissions (AD and NTFS) to complete the task on the remote share..
regards|||In addition to Andrea, the transfer to normal UNC paths are not reliable, leading to the problem that the transfer could stop somewhere in the middle of the process, unless you use something like a SAN or NAS storage which has a much more reliable way for tranferring. Use the local copy to copy the files (e.g. usign Xcopy with a restartable copy process) to make sure the file was really copied).
Jens K. Suessmeyer
http://www.sqlserver2005.de
Thursday, March 8, 2012
Backup check
Hello,
I want to create a stored procedure to
check if backups ended successfully at remote databases.
Which query can i run?
Many Thanks in Advance,
Galit.Gal
Assuming you are running your backups as jobs. The
information you need should be on sysjobhistory on the
msdb database. You can write a stored procedure to get the
information from there.
Regards
John
I want to create a stored procedure to
check if backups ended successfully at remote databases.
Which query can i run?
Many Thanks in Advance,
Galit.Gal
Assuming you are running your backups as jobs. The
information you need should be on sysjobhistory on the
msdb database. You can write a stored procedure to get the
information from there.
Regards
John
Backup Automatisation in SQL Server 2000 - Need your help.
I am trying to setup my SQL server and I want to plan the backups
My ideas were to create a sys_dat_bak device for the full backup of
all the system databases (master, model, msdb) and a sys_logs_bak for
the transaction log backup of the model db. The backup files created
by MS SQL server would then be backupped again by our normal central
backup system in filesystem mode. Ditto for the user databases, a
app_data_bak and app_logs_bak. For the user database I would schedule
extra tlog-backups during the day.
I have successfully created the backup disk-devices with T-SQL.
When I try to automate the actual backup commands in Enterprise
Manager, I can get the backups to succeed, but when I veryfy with
FILELIST then I seem to be missing files and not getting what I want
on this device.
Here's what I do :
I have a job called Full backup systemdatabases (data and tlog).
Step 1 (= daily full backup of master and clear file before backup)
I use master
BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 2 (= daily full backup of model and append to backupdevice)
I use master
BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 3 (= Tlog backup of model and clear file before backup)
I use master
BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME =
N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
Transaction log', NOFORMAT , NO_TRUNCATE
On success goto next step
Step 4 (= daily full backup of msdb and append to backupdevice)
I use master
BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
MSDB database', NOFORMAT
All these steps finish with success
When I check with restore headeronly, then I can see that 3 session
were started and finished to device sys_data_bak and 1 session to
sys_logs_bak. That's exactly what I hoped for.
When I check with restore filelistonly, then I see on :
- sys_data_bak : two files written : master.mdf and mastlog.ldf
- sys_logs_bak : two files written : model.mdf and modellog.ldf
And now I am puzzled.
Why would this backup write only master.mdf and mastlog.ldf on
sys_data_bak ?
Why would this backup write only model.mdf and model.ldf on
sys_logs_bak ? Model.mdf shouldn't even be on that device.
And where's msdb backup ?
Can someone explain please? In need to understand what is happening
here. Also if you have suggestions how to improve the scripts please
do.
Hi
1. Use separate device for each database.
2. Use separate device for backup tasks on database.
Eg:
SAMPLE_DATABASE
Create devices:
-SAMPLE_DATABASE_FULL
-SAMPLE_DATABASE_DIFF
-SAMPLE_DATABASE_LOG_1
-SAMPLE_DATABASE_LOG_2
-SAMPLE_DATABASE_LOG_3
-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and backup the file to tape.
-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with overwrite) and backup the file to tape.
-And by day use log backup devices , also with overwrite
You can this method easy scripting. Also retore methods.
3. Configure Your database defaults on model, then backup once. This database used only when you create a new database on server, not store data.
Andras Jakus MCDBA
"citizen" wrote:
> I am trying to setup my SQL server and I want to plan the backups
> My ideas were to create a sys_dat_bak device for the full backup of
> all the system databases (master, model, msdb) and a sys_logs_bak for
> the transaction log backup of the model db. The backup files created
> by MS SQL server would then be backupped again by our normal central
> backup system in filesystem mode. Ditto for the user databases, a
> app_data_bak and app_logs_bak. For the user database I would schedule
> extra tlog-backups during the day.
> I have successfully created the backup disk-devices with T-SQL.
> When I try to automate the actual backup commands in Enterprise
> Manager, I can get the backups to succeed, but when I veryfy with
> FILELIST then I seem to be missing files and not getting what I want
> on this device.
> Here's what I do :
> I have a job called Full backup systemdatabases (data and tlog).
> Step 1 (= daily full backup of master and clear file before backup)
> I use master
> BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
> NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 2 (= daily full backup of model and append to backupdevice)
> I use master
> BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
> NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 3 (= Tlog backup of model and clear file before backup)
> I use master
> BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME =
> N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> Transaction log', NOFORMAT , NO_TRUNCATE
> On success goto next step
> Step 4 (= daily full backup of msdb and append to backupdevice)
> I use master
> BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
> NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> MSDB database', NOFORMAT
> All these steps finish with success
> When I check with restore headeronly, then I can see that 3 session
> were started and finished to device sys_data_bak and 1 session to
> sys_logs_bak. That's exactly what I hoped for.
> When I check with restore filelistonly, then I see on :
> - sys_data_bak : two files written : master.mdf and mastlog.ldf
> - sys_logs_bak : two files written : model.mdf and modellog.ldf
> And now I am puzzled.
> Why would this backup write only master.mdf and mastlog.ldf on
> sys_data_bak ?
> Why would this backup write only model.mdf and model.ldf on
> sys_logs_bak ? Model.mdf shouldn't even be on that device.
> And where's msdb backup ?
> Can someone explain please? In need to understand what is happening
> here. Also if you have suggestions how to improve the scripts please
> do.
>
|||You are seeing this because you probably did not specify the WITH FILE =
parameter for the RESTORE FILELISTONLY command. If you do not specify this
parameter, it will show you the files that were backed up for the first
backup in the device. Refer to Books Online for more information on how to
use this parameter.
However as suggested by Andras, you may want to create separate devices to
reduce the confusion.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||On Fri, 30 Jul 2004 08:45:03 -0700, Andras Jakus
<AndrasJakus@.discussions.microsoft.com> wrote:
>Hi
>1. Use separate device for each database.
>2. Use separate device for backup tasks on database.
>Eg:
>SAMPLE_DATABASE
>Create devices:
>-SAMPLE_DATABASE_FULL
>-SAMPLE_DATABASE_DIFF
>-SAMPLE_DATABASE_LOG_1
>-SAMPLE_DATABASE_LOG_2
>-SAMPLE_DATABASE_LOG_3
>-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and backup the file to tape.
>-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with overwrite) and backup the file to tape.
>-And by day use log backup devices , also with overwrite
>You can this method easy scripting. Also retore methods.
>3. Configure Your database defaults on model, then backup once. This database used only when you create a new database on server, not store data.
>Andras Jakus MCDBA
>
Since the database will be very small, we will not be making
differential backups, only full backup's. Those backupfiles will get
backuped again by our central backup system.
I don't think I will have much trouble with the app. database backup,
but I don't know why the backup of the systemdatabases is behaving
like it is now. Do you know ?
|||On Fri, 30 Jul 2004 16:49:39 GMT, pankaja@.online.microsoft.com (Pankaj
Agarwal [MSFT]) wrote:
>You are seeing this because you probably did not specify the WITH FILE =
>parameter for the RESTORE FILELISTONLY command. If you do not specify this
>parameter, it will show you the files that were backed up for the first
>backup in the device. Refer to Books Online for more information on how to
>use this parameter.
>However as suggested by Andras, you may want to create separate devices to
>reduce the confusion.
>Thank you for using Microsoft newsgroups.
>Sincerely
>Pankaj Agarwal
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
OK, found it. I must indeed specify with file =
So all the files that must be there, I have found now.
But is there no way to let sql server show ALL the files available ?
Now one must know which fileset and guess how many there are ...
if they don't know all of the backupprocedure.
My ideas were to create a sys_dat_bak device for the full backup of
all the system databases (master, model, msdb) and a sys_logs_bak for
the transaction log backup of the model db. The backup files created
by MS SQL server would then be backupped again by our normal central
backup system in filesystem mode. Ditto for the user databases, a
app_data_bak and app_logs_bak. For the user database I would schedule
extra tlog-backups during the day.
I have successfully created the backup disk-devices with T-SQL.
When I try to automate the actual backup commands in Enterprise
Manager, I can get the backups to succeed, but when I veryfy with
FILELIST then I seem to be missing files and not getting what I want
on this device.
Here's what I do :
I have a job called Full backup systemdatabases (data and tlog).
Step 1 (= daily full backup of master and clear file before backup)
I use master
BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 2 (= daily full backup of model and append to backupdevice)
I use master
BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 3 (= Tlog backup of model and clear file before backup)
I use master
BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME =
N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
Transaction log', NOFORMAT , NO_TRUNCATE
On success goto next step
Step 4 (= daily full backup of msdb and append to backupdevice)
I use master
BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
MSDB database', NOFORMAT
All these steps finish with success
When I check with restore headeronly, then I can see that 3 session
were started and finished to device sys_data_bak and 1 session to
sys_logs_bak. That's exactly what I hoped for.
When I check with restore filelistonly, then I see on :
- sys_data_bak : two files written : master.mdf and mastlog.ldf
- sys_logs_bak : two files written : model.mdf and modellog.ldf
And now I am puzzled.
Why would this backup write only master.mdf and mastlog.ldf on
sys_data_bak ?
Why would this backup write only model.mdf and model.ldf on
sys_logs_bak ? Model.mdf shouldn't even be on that device.
And where's msdb backup ?
Can someone explain please? In need to understand what is happening
here. Also if you have suggestions how to improve the scripts please
do.
Hi
1. Use separate device for each database.
2. Use separate device for backup tasks on database.
Eg:
SAMPLE_DATABASE
Create devices:
-SAMPLE_DATABASE_FULL
-SAMPLE_DATABASE_DIFF
-SAMPLE_DATABASE_LOG_1
-SAMPLE_DATABASE_LOG_2
-SAMPLE_DATABASE_LOG_3
-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and backup the file to tape.
-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with overwrite) and backup the file to tape.
-And by day use log backup devices , also with overwrite
You can this method easy scripting. Also retore methods.
3. Configure Your database defaults on model, then backup once. This database used only when you create a new database on server, not store data.
Andras Jakus MCDBA
"citizen" wrote:
> I am trying to setup my SQL server and I want to plan the backups
> My ideas were to create a sys_dat_bak device for the full backup of
> all the system databases (master, model, msdb) and a sys_logs_bak for
> the transaction log backup of the model db. The backup files created
> by MS SQL server would then be backupped again by our normal central
> backup system in filesystem mode. Ditto for the user databases, a
> app_data_bak and app_logs_bak. For the user database I would schedule
> extra tlog-backups during the day.
> I have successfully created the backup disk-devices with T-SQL.
> When I try to automate the actual backup commands in Enterprise
> Manager, I can get the backups to succeed, but when I veryfy with
> FILELIST then I seem to be missing files and not getting what I want
> on this device.
> Here's what I do :
> I have a job called Full backup systemdatabases (data and tlog).
> Step 1 (= daily full backup of master and clear file before backup)
> I use master
> BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
> NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 2 (= daily full backup of model and append to backupdevice)
> I use master
> BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
> NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 3 (= Tlog backup of model and clear file before backup)
> I use master
> BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME =
> N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> Transaction log', NOFORMAT , NO_TRUNCATE
> On success goto next step
> Step 4 (= daily full backup of msdb and append to backupdevice)
> I use master
> BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
> NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> MSDB database', NOFORMAT
> All these steps finish with success
> When I check with restore headeronly, then I can see that 3 session
> were started and finished to device sys_data_bak and 1 session to
> sys_logs_bak. That's exactly what I hoped for.
> When I check with restore filelistonly, then I see on :
> - sys_data_bak : two files written : master.mdf and mastlog.ldf
> - sys_logs_bak : two files written : model.mdf and modellog.ldf
> And now I am puzzled.
> Why would this backup write only master.mdf and mastlog.ldf on
> sys_data_bak ?
> Why would this backup write only model.mdf and model.ldf on
> sys_logs_bak ? Model.mdf shouldn't even be on that device.
> And where's msdb backup ?
> Can someone explain please? In need to understand what is happening
> here. Also if you have suggestions how to improve the scripts please
> do.
>
|||You are seeing this because you probably did not specify the WITH FILE =
parameter for the RESTORE FILELISTONLY command. If you do not specify this
parameter, it will show you the files that were backed up for the first
backup in the device. Refer to Books Online for more information on how to
use this parameter.
However as suggested by Andras, you may want to create separate devices to
reduce the confusion.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||On Fri, 30 Jul 2004 08:45:03 -0700, Andras Jakus
<AndrasJakus@.discussions.microsoft.com> wrote:
>Hi
>1. Use separate device for each database.
>2. Use separate device for backup tasks on database.
>Eg:
>SAMPLE_DATABASE
>Create devices:
>-SAMPLE_DATABASE_FULL
>-SAMPLE_DATABASE_DIFF
>-SAMPLE_DATABASE_LOG_1
>-SAMPLE_DATABASE_LOG_2
>-SAMPLE_DATABASE_LOG_3
>-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and backup the file to tape.
>-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with overwrite) and backup the file to tape.
>-And by day use log backup devices , also with overwrite
>You can this method easy scripting. Also retore methods.
>3. Configure Your database defaults on model, then backup once. This database used only when you create a new database on server, not store data.
>Andras Jakus MCDBA
>
Since the database will be very small, we will not be making
differential backups, only full backup's. Those backupfiles will get
backuped again by our central backup system.
I don't think I will have much trouble with the app. database backup,
but I don't know why the backup of the systemdatabases is behaving
like it is now. Do you know ?
|||On Fri, 30 Jul 2004 16:49:39 GMT, pankaja@.online.microsoft.com (Pankaj
Agarwal [MSFT]) wrote:
>You are seeing this because you probably did not specify the WITH FILE =
>parameter for the RESTORE FILELISTONLY command. If you do not specify this
>parameter, it will show you the files that were backed up for the first
>backup in the device. Refer to Books Online for more information on how to
>use this parameter.
>However as suggested by Andras, you may want to create separate devices to
>reduce the confusion.
>Thank you for using Microsoft newsgroups.
>Sincerely
>Pankaj Agarwal
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
OK, found it. I must indeed specify with file =
So all the files that must be there, I have found now.
But is there no way to let sql server show ALL the files available ?
Now one must know which fileset and guess how many there are ...
if they don't know all of the backupprocedure.
Backup Automatisation in SQL Server 2000 - Need your help.
I am trying to setup my SQL server and I want to plan the backups
My ideas were to create a sys_dat_bak device for the full backup of
all the system databases (master, model, msdb) and a sys_logs_bak for
the transaction log backup of the model db. The backup files created
by MS SQL server would then be backupped again by our normal central
backup system in filesystem mode. Ditto for the user databases, a
app_data_bak and app_logs_bak. For the user database I would schedule
extra tlog-backups during the day.
I have successfully created the backup disk-devices with T-SQL.
When I try to automate the actual backup commands in Enterprise
Manager, I can get the backups to succeed, but when I veryfy with
FILELIST then I seem to be missing files and not getting what I want
on this device.
Here's what I do :
I have a job called Full backup systemdatabases (data and tlog).
Step 1 (= daily full backup of master and clear file before backup)
I use master
BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 2 (= daily full backup of model and append to backupdevice)
I use master
BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD
,
NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 3 (= Tlog backup of model and clear file before backup)
I use master
BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME
=
N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
Transaction log', NOFORMAT , NO_TRUNCATE
On success goto next step
Step 4 (= daily full backup of msdb and append to backupdevice)
I use master
BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
MSDB database', NOFORMAT
All these steps finish with success
When I check with restore headeronly, then I can see that 3 session
were started and finished to device sys_data_bak and 1 session to
sys_logs_bak. That's exactly what I hoped for.
When I check with restore filelistonly, then I see on :
- sys_data_bak : two files written : master.mdf and mastlog.ldf
- sys_logs_bak : two files written : model.mdf and modellog.ldf
And now I am puzzled.
Why would this backup write only master.mdf and mastlog.ldf on
sys_data_bak ?
Why would this backup write only model.mdf and model.ldf on
sys_logs_bak ? Model.mdf shouldn't even be on that device.
And where's msdb backup '
Can someone explain please? In need to understand what is happening
here. Also if you have suggestions how to improve the scripts please
do.Hi
1. Use separate device for each database.
2. Use separate device for backup tasks on database.
Eg:
SAMPLE_DATABASE
Create devices:
-SAMPLE_DATABASE_FULL
-SAMPLE_DATABASE_DIFF
-SAMPLE_DATABASE_LOG_1
-SAMPLE_DATABASE_LOG_2
-SAMPLE_DATABASE_LOG_3
-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and bac
kup the file to tape.
-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with ove
rwrite) and backup the file to tape.
-And by day use log backup devices , also with overwrite
You can this method easy scripting. Also retore methods.
3. Configure Your database defaults on model, then backup once. This databas
e used only when you create a new database on server, not store data.
Andras Jakus MCDBA
"citizen" wrote:
> I am trying to setup my SQL server and I want to plan the backups
> My ideas were to create a sys_dat_bak device for the full backup of
> all the system databases (master, model, msdb) and a sys_logs_bak for
> the transaction log backup of the model db. The backup files created
> by MS SQL server would then be backupped again by our normal central
> backup system in filesystem mode. Ditto for the user databases, a
> app_data_bak and app_logs_bak. For the user database I would schedule
> extra tlog-backups during the day.
> I have successfully created the backup disk-devices with T-SQL.
> When I try to automate the actual backup commands in Enterprise
> Manager, I can get the backups to succeed, but when I veryfy with
> FILELIST then I seem to be missing files and not getting what I want
> on this device.
> Here's what I do :
> I have a job called Full backup systemdatabases (data and tlog).
> Step 1 (= daily full backup of master and clear file before backup)
> I use master
> BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD
,
> NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 2 (= daily full backup of model and append to backupdevice)
> I use master
> BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOA
D ,
> NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 3 (= Tlog backup of model and clear file before backup)
> I use master
> BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAM
E =
> N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> Transaction log', NOFORMAT , NO_TRUNCATE
> On success goto next step
> Step 4 (= daily full backup of msdb and append to backupdevice)
> I use master
> BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD
,
> NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> MSDB database', NOFORMAT
> All these steps finish with success
> When I check with restore headeronly, then I can see that 3 session
> were started and finished to device sys_data_bak and 1 session to
> sys_logs_bak. That's exactly what I hoped for.
> When I check with restore filelistonly, then I see on :
> - sys_data_bak : two files written : master.mdf and mastlog.ldf
> - sys_logs_bak : two files written : model.mdf and modellog.ldf
> And now I am puzzled.
> Why would this backup write only master.mdf and mastlog.ldf on
> sys_data_bak ?
> Why would this backup write only model.mdf and model.ldf on
> sys_logs_bak ? Model.mdf shouldn't even be on that device.
> And where's msdb backup '
> Can someone explain please? In need to understand what is happening
> here. Also if you have suggestions how to improve the scripts please
> do.
>|||You are seeing this because you probably did not specify the WITH FILE =
parameter for the RESTORE FILELISTONLY command. If you do not specify this
parameter, it will show you the files that were backed up for the first
backup in the device. Refer to Books Online for more information on how to
use this parameter.
However as suggested by Andras, you may want to create separate devices to
reduce the confusion.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||On Fri, 30 Jul 2004 08:45:03 -0700, Andras Jakus
<AndrasJakus@.discussions.microsoft.com> wrote:
>Hi
>1. Use separate device for each database.
>2. Use separate device for backup tasks on database.
>Eg:
>SAMPLE_DATABASE
>Create devices:
>-SAMPLE_DATABASE_FULL
>-SAMPLE_DATABASE_DIFF
>-SAMPLE_DATABASE_LOG_1
>-SAMPLE_DATABASE_LOG_2
>-SAMPLE_DATABASE_LOG_3
>-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and ba
ckup the file to tape.
>-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with ov
erwrite) and backup the file to tape.
>-And by day use log backup devices , also with overwrite
>You can this method easy scripting. Also retore methods.
>3. Configure Your database defaults on model, then backup once. This databa
se used only when you create a new database on server, not store data.
>Andras Jakus MCDBA
>
Since the database will be very small, we will not be making
differential backups, only full backup's. Those backupfiles will get
backuped again by our central backup system.
I don't think I will have much trouble with the app. database backup,
but I don't know why the backup of the systemdatabases is behaving
like it is now. Do you know ?|||On Fri, 30 Jul 2004 16:49:39 GMT, pankaja@.online.microsoft.com (Pankaj
Agarwal [MSFT]) wrote:
>You are seeing this because you probably did not specify the WITH FILE =
>parameter for the RESTORE FILELISTONLY command. If you do not specify this
>parameter, it will show you the files that were backed up for the first
>backup in the device. Refer to Books Online for more information on how to
>use this parameter.
>However as suggested by Andras, you may want to create separate devices to
>reduce the confusion.
>Thank you for using Microsoft newsgroups.
>Sincerely
>Pankaj Agarwal
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
OK, found it. I must indeed specify with file =
So all the files that must be there, I have found now.
But is there no way to let sql server show ALL the files available ?
Now one must know which fileset and guess how many there are ...
if they don't know all of the backupprocedure.
My ideas were to create a sys_dat_bak device for the full backup of
all the system databases (master, model, msdb) and a sys_logs_bak for
the transaction log backup of the model db. The backup files created
by MS SQL server would then be backupped again by our normal central
backup system in filesystem mode. Ditto for the user databases, a
app_data_bak and app_logs_bak. For the user database I would schedule
extra tlog-backups during the day.
I have successfully created the backup disk-devices with T-SQL.
When I try to automate the actual backup commands in Enterprise
Manager, I can get the backups to succeed, but when I veryfy with
FILELIST then I seem to be missing files and not getting what I want
on this device.
Here's what I do :
I have a job called Full backup systemdatabases (data and tlog).
Step 1 (= daily full backup of master and clear file before backup)
I use master
BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 2 (= daily full backup of model and append to backupdevice)
I use master
BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD
,
NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 3 (= Tlog backup of model and clear file before backup)
I use master
BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME
=
N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
Transaction log', NOFORMAT , NO_TRUNCATE
On success goto next step
Step 4 (= daily full backup of msdb and append to backupdevice)
I use master
BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
MSDB database', NOFORMAT
All these steps finish with success
When I check with restore headeronly, then I can see that 3 session
were started and finished to device sys_data_bak and 1 session to
sys_logs_bak. That's exactly what I hoped for.
When I check with restore filelistonly, then I see on :
- sys_data_bak : two files written : master.mdf and mastlog.ldf
- sys_logs_bak : two files written : model.mdf and modellog.ldf
And now I am puzzled.
Why would this backup write only master.mdf and mastlog.ldf on
sys_data_bak ?
Why would this backup write only model.mdf and model.ldf on
sys_logs_bak ? Model.mdf shouldn't even be on that device.
And where's msdb backup '
Can someone explain please? In need to understand what is happening
here. Also if you have suggestions how to improve the scripts please
do.Hi
1. Use separate device for each database.
2. Use separate device for backup tasks on database.
Eg:
SAMPLE_DATABASE
Create devices:
-SAMPLE_DATABASE_FULL
-SAMPLE_DATABASE_DIFF
-SAMPLE_DATABASE_LOG_1
-SAMPLE_DATABASE_LOG_2
-SAMPLE_DATABASE_LOG_3
-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and bac
kup the file to tape.
-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with ove
rwrite) and backup the file to tape.
-And by day use log backup devices , also with overwrite
You can this method easy scripting. Also retore methods.
3. Configure Your database defaults on model, then backup once. This databas
e used only when you create a new database on server, not store data.
Andras Jakus MCDBA
"citizen" wrote:
> I am trying to setup my SQL server and I want to plan the backups
> My ideas were to create a sys_dat_bak device for the full backup of
> all the system databases (master, model, msdb) and a sys_logs_bak for
> the transaction log backup of the model db. The backup files created
> by MS SQL server would then be backupped again by our normal central
> backup system in filesystem mode. Ditto for the user databases, a
> app_data_bak and app_logs_bak. For the user database I would schedule
> extra tlog-backups during the day.
> I have successfully created the backup disk-devices with T-SQL.
> When I try to automate the actual backup commands in Enterprise
> Manager, I can get the backups to succeed, but when I veryfy with
> FILELIST then I seem to be missing files and not getting what I want
> on this device.
> Here's what I do :
> I have a job called Full backup systemdatabases (data and tlog).
> Step 1 (= daily full backup of master and clear file before backup)
> I use master
> BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD
,
> NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 2 (= daily full backup of model and append to backupdevice)
> I use master
> BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOA
D ,
> NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 3 (= Tlog backup of model and clear file before backup)
> I use master
> BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAM
E =
> N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> Transaction log', NOFORMAT , NO_TRUNCATE
> On success goto next step
> Step 4 (= daily full backup of msdb and append to backupdevice)
> I use master
> BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD
,
> NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> MSDB database', NOFORMAT
> All these steps finish with success
> When I check with restore headeronly, then I can see that 3 session
> were started and finished to device sys_data_bak and 1 session to
> sys_logs_bak. That's exactly what I hoped for.
> When I check with restore filelistonly, then I see on :
> - sys_data_bak : two files written : master.mdf and mastlog.ldf
> - sys_logs_bak : two files written : model.mdf and modellog.ldf
> And now I am puzzled.
> Why would this backup write only master.mdf and mastlog.ldf on
> sys_data_bak ?
> Why would this backup write only model.mdf and model.ldf on
> sys_logs_bak ? Model.mdf shouldn't even be on that device.
> And where's msdb backup '
> Can someone explain please? In need to understand what is happening
> here. Also if you have suggestions how to improve the scripts please
> do.
>|||You are seeing this because you probably did not specify the WITH FILE =
parameter for the RESTORE FILELISTONLY command. If you do not specify this
parameter, it will show you the files that were backed up for the first
backup in the device. Refer to Books Online for more information on how to
use this parameter.
However as suggested by Andras, you may want to create separate devices to
reduce the confusion.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||On Fri, 30 Jul 2004 08:45:03 -0700, Andras Jakus
<AndrasJakus@.discussions.microsoft.com> wrote:
>Hi
>1. Use separate device for each database.
>2. Use separate device for backup tasks on database.
>Eg:
>SAMPLE_DATABASE
>Create devices:
>-SAMPLE_DATABASE_FULL
>-SAMPLE_DATABASE_DIFF
>-SAMPLE_DATABASE_LOG_1
>-SAMPLE_DATABASE_LOG_2
>-SAMPLE_DATABASE_LOG_3
>-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and ba
ckup the file to tape.
>-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with ov
erwrite) and backup the file to tape.
>-And by day use log backup devices , also with overwrite
>You can this method easy scripting. Also retore methods.
>3. Configure Your database defaults on model, then backup once. This databa
se used only when you create a new database on server, not store data.
>Andras Jakus MCDBA
>
Since the database will be very small, we will not be making
differential backups, only full backup's. Those backupfiles will get
backuped again by our central backup system.
I don't think I will have much trouble with the app. database backup,
but I don't know why the backup of the systemdatabases is behaving
like it is now. Do you know ?|||On Fri, 30 Jul 2004 16:49:39 GMT, pankaja@.online.microsoft.com (Pankaj
Agarwal [MSFT]) wrote:
>You are seeing this because you probably did not specify the WITH FILE =
>parameter for the RESTORE FILELISTONLY command. If you do not specify this
>parameter, it will show you the files that were backed up for the first
>backup in the device. Refer to Books Online for more information on how to
>use this parameter.
>However as suggested by Andras, you may want to create separate devices to
>reduce the confusion.
>Thank you for using Microsoft newsgroups.
>Sincerely
>Pankaj Agarwal
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
OK, found it. I must indeed specify with file =
So all the files that must be there, I have found now.
But is there no way to let sql server show ALL the files available ?
Now one must know which fileset and guess how many there are ...
if they don't know all of the backupprocedure.
Backup Automatisation in SQL Server 2000 - Need your help.
I am trying to setup my SQL server and I want to plan the backups
My ideas were to create a sys_dat_bak device for the full backup of
all the system databases (master, model, msdb) and a sys_logs_bak for
the transaction log backup of the model db. The backup files created
by MS SQL server would then be backupped again by our normal central
backup system in filesystem mode. Ditto for the user databases, a
app_data_bak and app_logs_bak. For the user database I would schedule
extra tlog-backups during the day.
I have successfully created the backup disk-devices with T-SQL.
When I try to automate the actual backup commands in Enterprise
Manager, I can get the backups to succeed, but when I veryfy with
FILELIST then I seem to be missing files and not getting what I want
on this device.
Here's what I do :
I have a job called Full backup systemdatabases (data and tlog).
Step 1 (= daily full backup of master and clear file before backup)
I use master
BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 2 (= daily full backup of model and append to backupdevice)
I use master
BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 3 (= Tlog backup of model and clear file before backup)
I use master
BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME = N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
Transaction log', NOFORMAT , NO_TRUNCATE
On success goto next step
Step 4 (= daily full backup of msdb and append to backupdevice)
I use master
BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
MSDB database', NOFORMAT
All these steps finish with success
When I check with restore headeronly, then I can see that 3 session
were started and finished to device sys_data_bak and 1 session to
sys_logs_bak. That's exactly what I hoped for.
When I check with restore filelistonly, then I see on :
- sys_data_bak : two files written : master.mdf and mastlog.ldf
- sys_logs_bak : two files written : model.mdf and modellog.ldf
And now I am puzzled.
Why would this backup write only master.mdf and mastlog.ldf on
sys_data_bak ?
Why would this backup write only model.mdf and model.ldf on
sys_logs_bak ? Model.mdf shouldn't even be on that device.
And where's msdb backup '
Can someone explain please? In need to understand what is happening
here. Also if you have suggestions how to improve the scripts please
do.You are seeing this because you probably did not specify the WITH FILE =parameter for the RESTORE FILELISTONLY command. If you do not specify this
parameter, it will show you the files that were backed up for the first
backup in the device. Refer to Books Online for more information on how to
use this parameter.
However as suggested by Andras, you may want to create separate devices to
reduce the confusion.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||On Fri, 30 Jul 2004 08:45:03 -0700, Andras Jakus
<AndrasJakus@.discussions.microsoft.com> wrote:
>Hi
>1. Use separate device for each database.
>2. Use separate device for backup tasks on database.
>Eg:
>SAMPLE_DATABASE
>Create devices:
>-SAMPLE_DATABASE_FULL
>-SAMPLE_DATABASE_DIFF
>-SAMPLE_DATABASE_LOG_1
>-SAMPLE_DATABASE_LOG_2
>-SAMPLE_DATABASE_LOG_3
>-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and backup the file to tape.
>-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with overwrite) and backup the file to tape.
>-And by day use log backup devices , also with overwrite
>You can this method easy scripting. Also retore methods.
>3. Configure Your database defaults on model, then backup once. This database used only when you create a new database on server, not store data.
>Andras Jakus MCDBA
>
Since the database will be very small, we will not be making
differential backups, only full backup's. Those backupfiles will get
backuped again by our central backup system.
I don't think I will have much trouble with the app. database backup,
but I don't know why the backup of the systemdatabases is behaving
like it is now. Do you know ?|||On Fri, 30 Jul 2004 16:49:39 GMT, pankaja@.online.microsoft.com (Pankaj
Agarwal [MSFT]) wrote:
>You are seeing this because you probably did not specify the WITH FILE =>parameter for the RESTORE FILELISTONLY command. If you do not specify this
>parameter, it will show you the files that were backed up for the first
>backup in the device. Refer to Books Online for more information on how to
>use this parameter.
>However as suggested by Andras, you may want to create separate devices to
>reduce the confusion.
>Thank you for using Microsoft newsgroups.
>Sincerely
>Pankaj Agarwal
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
OK, found it. I must indeed specify with file =So all the files that must be there, I have found now.
But is there no way to let sql server show ALL the files available ?
Now one must know which fileset and guess how many there are ...
if they don't know all of the backupprocedure.
My ideas were to create a sys_dat_bak device for the full backup of
all the system databases (master, model, msdb) and a sys_logs_bak for
the transaction log backup of the model db. The backup files created
by MS SQL server would then be backupped again by our normal central
backup system in filesystem mode. Ditto for the user databases, a
app_data_bak and app_logs_bak. For the user database I would schedule
extra tlog-backups during the day.
I have successfully created the backup disk-devices with T-SQL.
When I try to automate the actual backup commands in Enterprise
Manager, I can get the backups to succeed, but when I veryfy with
FILELIST then I seem to be missing files and not getting what I want
on this device.
Here's what I do :
I have a job called Full backup systemdatabases (data and tlog).
Step 1 (= daily full backup of master and clear file before backup)
I use master
BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 2 (= daily full backup of model and append to backupdevice)
I use master
BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 3 (= Tlog backup of model and clear file before backup)
I use master
BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME = N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
Transaction log', NOFORMAT , NO_TRUNCATE
On success goto next step
Step 4 (= daily full backup of msdb and append to backupdevice)
I use master
BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
MSDB database', NOFORMAT
All these steps finish with success
When I check with restore headeronly, then I can see that 3 session
were started and finished to device sys_data_bak and 1 session to
sys_logs_bak. That's exactly what I hoped for.
When I check with restore filelistonly, then I see on :
- sys_data_bak : two files written : master.mdf and mastlog.ldf
- sys_logs_bak : two files written : model.mdf and modellog.ldf
And now I am puzzled.
Why would this backup write only master.mdf and mastlog.ldf on
sys_data_bak ?
Why would this backup write only model.mdf and model.ldf on
sys_logs_bak ? Model.mdf shouldn't even be on that device.
And where's msdb backup '
Can someone explain please? In need to understand what is happening
here. Also if you have suggestions how to improve the scripts please
do.You are seeing this because you probably did not specify the WITH FILE =parameter for the RESTORE FILELISTONLY command. If you do not specify this
parameter, it will show you the files that were backed up for the first
backup in the device. Refer to Books Online for more information on how to
use this parameter.
However as suggested by Andras, you may want to create separate devices to
reduce the confusion.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||On Fri, 30 Jul 2004 08:45:03 -0700, Andras Jakus
<AndrasJakus@.discussions.microsoft.com> wrote:
>Hi
>1. Use separate device for each database.
>2. Use separate device for backup tasks on database.
>Eg:
>SAMPLE_DATABASE
>Create devices:
>-SAMPLE_DATABASE_FULL
>-SAMPLE_DATABASE_DIFF
>-SAMPLE_DATABASE_LOG_1
>-SAMPLE_DATABASE_LOG_2
>-SAMPLE_DATABASE_LOG_3
>-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and backup the file to tape.
>-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with overwrite) and backup the file to tape.
>-And by day use log backup devices , also with overwrite
>You can this method easy scripting. Also retore methods.
>3. Configure Your database defaults on model, then backup once. This database used only when you create a new database on server, not store data.
>Andras Jakus MCDBA
>
Since the database will be very small, we will not be making
differential backups, only full backup's. Those backupfiles will get
backuped again by our central backup system.
I don't think I will have much trouble with the app. database backup,
but I don't know why the backup of the systemdatabases is behaving
like it is now. Do you know ?|||On Fri, 30 Jul 2004 16:49:39 GMT, pankaja@.online.microsoft.com (Pankaj
Agarwal [MSFT]) wrote:
>You are seeing this because you probably did not specify the WITH FILE =>parameter for the RESTORE FILELISTONLY command. If you do not specify this
>parameter, it will show you the files that were backed up for the first
>backup in the device. Refer to Books Online for more information on how to
>use this parameter.
>However as suggested by Andras, you may want to create separate devices to
>reduce the confusion.
>Thank you for using Microsoft newsgroups.
>Sincerely
>Pankaj Agarwal
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
OK, found it. I must indeed specify with file =So all the files that must be there, I have found now.
But is there no way to let sql server show ALL the files available ?
Now one must know which fileset and guess how many there are ...
if they don't know all of the backupprocedure.
backup and restoring
I used backup and restore to upgrade a database from sql 2000 to sql 2005. Is it necessary to create the mdf and ldf on the new server at the time of restore under options or should I copy them to the new server in the data folder? I am new and not quite sure what the log files hold.
Thanksif you have created a backup of a database, you don't need anything but the .bak file.
when you restore from a bak file, the data and log files will be created automatically by the server.|||Edit: You don't need to create them, the restore does.
How backup works:
When SQL Server backs up a database, it backs up the data file first. During the backup of the data file, no changes are written to the data file, only to the log. When the bacup of the data file is complete, the changes written to the log file is backed up. In other words, backup of a SQL Server restores to the point of time when the backup finished, not started. Furthermore, your log file will be used to keep track of transactions afer the backup have restored, so you will need the file.|||Ok. If I don't specify in options at the time of restore, I can't find where the the mdf and ldf files were created. And are these new mdf and ldf files or do they hold the same data as the database before they were created on the new server.
thanks|||this will tell you where they are:
exec sp_helpfile
Thanksif you have created a backup of a database, you don't need anything but the .bak file.
when you restore from a bak file, the data and log files will be created automatically by the server.|||Edit: You don't need to create them, the restore does.
How backup works:
When SQL Server backs up a database, it backs up the data file first. During the backup of the data file, no changes are written to the data file, only to the log. When the bacup of the data file is complete, the changes written to the log file is backed up. In other words, backup of a SQL Server restores to the point of time when the backup finished, not started. Furthermore, your log file will be used to keep track of transactions afer the backup have restored, so you will need the file.|||Ok. If I don't specify in options at the time of restore, I can't find where the the mdf and ldf files were created. And are these new mdf and ldf files or do they hold the same data as the database before they were created on the new server.
thanks|||this will tell you where they are:
exec sp_helpfile
Wednesday, March 7, 2012
backup and restore script
I want to be able to create a development database based on our live databas
e
-- on an ongoing basis. I think the easiest way is to backup the live
database, and restore it as a different name. Because I will be doing this
often, I would like to run this as a script.
Does anyone have a script that would do that ?
Is this a good way to create a development database ?
Thanks,
Craig"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:FB8C7E76-586B-4902-90BF-7925C70B6299@.microsoft.com...
> I want to be able to create a development database based on our live
database
> -- on an ongoing basis. I think the easiest way is to backup the live
> database, and restore it as a different name. Because I will be doing this
> often, I would like to run this as a script.
> Does anyone have a script that would do that ?
> Is this a good way to create a development database ?
> Thanks,
> Craig
Works well if you need up to the second. If not, just grab the last db
backups and restore them.
Take a look at the BACKUP DATABASE and RESTORE DATABASE commands in the BOL.
I do this often myself as I need to ensure changes apply properly from
various database versions of our system.
I have 22 code bases to choose from now. (Gakk)
Each set of restore commands are currently saved as a .sql file.
I even included a generic kill sproc to remove any users from the databases
that will be update.
RestoreVer_2.1.1.10.sql
RestoreVer_2.1.1.11.sql and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
e
-- on an ongoing basis. I think the easiest way is to backup the live
database, and restore it as a different name. Because I will be doing this
often, I would like to run this as a script.
Does anyone have a script that would do that ?
Is this a good way to create a development database ?
Thanks,
Craig"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:FB8C7E76-586B-4902-90BF-7925C70B6299@.microsoft.com...
> I want to be able to create a development database based on our live
database
> -- on an ongoing basis. I think the easiest way is to backup the live
> database, and restore it as a different name. Because I will be doing this
> often, I would like to run this as a script.
> Does anyone have a script that would do that ?
> Is this a good way to create a development database ?
> Thanks,
> Craig
Works well if you need up to the second. If not, just grab the last db
backups and restore them.
Take a look at the BACKUP DATABASE and RESTORE DATABASE commands in the BOL.
I do this often myself as I need to ensure changes apply properly from
various database versions of our system.
I have 22 code bases to choose from now. (Gakk)
Each set of restore commands are currently saved as a .sql file.
I even included a generic kill sproc to remove any users from the databases
that will be update.
RestoreVer_2.1.1.10.sql
RestoreVer_2.1.1.11.sql and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
Saturday, February 25, 2012
Back-Up and restore
Hi Guys,
I have a database, which I recently backed up. Now I would like to
restore this back-up on another server. So basically I m trying to
create a copy of my database on the other server.
How can I restore this back-up file on the other server. When I try
doing that from the Enterprise Manager, it says that I have to choose
from an existing backup file. I m using SQL Server 2000 on Win 2K.
So basically what I want is to create the database from scratch on the
new server using that back up file from the other database. Can somebody
tell me the procedure for this. I m a newbie at SQL Server.
Thanks.
Ricky
--
Posted via http://dbforums.comRicky, it should be fairly straight forward. Use Enterprise Manager and
fire off the restore wizard. Just restore database from device and navigate
to the backup file. If you have problems, I highly recommend going to the
Books online and lookup the topic "RESTORE DATABASE" and do the restore from
Query Analyzer in T-SQL. You may find T-SQL a little more difficult, but
it's more rewarding and more flexible that the GUI for all database
functions. The only thing that comed to mind that I've never done with QA
is to get a table in design view and rearrange the column order... and that
is just cosmetic anyway.
hth,
Eric
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3082099.1057594899@.dbforums.com...
> Hi Guys,
> I have a database, which I recently backed up. Now I would like to
> restore this back-up on another server. So basically I m trying to
> create a copy of my database on the other server.
> How can I restore this back-up file on the other server. When I try
> doing that from the Enterprise Manager, it says that I have to choose
> from an existing backup file. I m using SQL Server 2000 on Win 2K.
> So basically what I want is to create the database from scratch on the
> new server using that back up file from the other database. Can somebody
> tell me the procedure for this. I m a newbie at SQL Server.
> Thanks.
> Ricky
> --
> Posted via http://dbforums.com|||Hi Ricky,
You said :
>>'I have a database, which I recently backed up. Now I would like torestore
this back-up on another server. So basically I m trying to create a copy of
my database on the other server'. <<
It seems to me that you already have a backup available that you want to
restore...do the following :
1) Copy the backup file across to the new server.
2) On the new server, create a database device ( Expand: Management > Right
click on 'Backup' > New backup device, note the path and give it a
descrpiptive name (somedbname.bak)
3) Right click on the database you want to restore, All Tasks > Restore
Database
4) Click on 'From Device' and click 'Select Devices' on device name > click
Add.
5) click on filename and browse to the location where you copied the old
backup file and click ok.
6) Now, don't close all the windows, come back to the restore database
window and click on 'Options' tab and select the 'Force restore over
existing database'
7) in the Logical filename, check the old server for the logica filename
(somedbname) and enter it there, do the same for both log and mdf files.
8) In the 'Move to Physical filename' make sure it is pointing to the data
and log files of this new database.
Note that, the logical filename is not the path, but just a name, you can
get it by going to the old server, right click the database > properties --
the 'Filename' here is the logical filename -- do the same for the
Transaction log tab as well.
After you hit restore...it will complete the restore and give u a message
saying that restore is complete...
Hope this helps.
Regards,
Andy.
PS: This information is Ad-hoc and i cannot be held responsible for any
action you might take, I recommend that you consult a few people and then go
about it.
Thanks
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3082099.1057594899@.dbforums.com...
> Hi Guys,
> I have a database, which I recently backed up. Now I would like to
> restore this back-up on another server. So basically I m trying to
> create a copy of my database on the other server.
> How can I restore this back-up file on the other server. When I try
> doing that from the Enterprise Manager, it says that I have to choose
> from an existing backup file. I m using SQL Server 2000 on Win 2K.
> So basically what I want is to create the database from scratch on the
> new server using that back up file from the other database. Can somebody
> tell me the procedure for this. I m a newbie at SQL Server.
> Thanks.
> Ricky
> --
> Posted via http://dbforums.com
I have a database, which I recently backed up. Now I would like to
restore this back-up on another server. So basically I m trying to
create a copy of my database on the other server.
How can I restore this back-up file on the other server. When I try
doing that from the Enterprise Manager, it says that I have to choose
from an existing backup file. I m using SQL Server 2000 on Win 2K.
So basically what I want is to create the database from scratch on the
new server using that back up file from the other database. Can somebody
tell me the procedure for this. I m a newbie at SQL Server.
Thanks.
Ricky
--
Posted via http://dbforums.comRicky, it should be fairly straight forward. Use Enterprise Manager and
fire off the restore wizard. Just restore database from device and navigate
to the backup file. If you have problems, I highly recommend going to the
Books online and lookup the topic "RESTORE DATABASE" and do the restore from
Query Analyzer in T-SQL. You may find T-SQL a little more difficult, but
it's more rewarding and more flexible that the GUI for all database
functions. The only thing that comed to mind that I've never done with QA
is to get a table in design view and rearrange the column order... and that
is just cosmetic anyway.
hth,
Eric
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3082099.1057594899@.dbforums.com...
> Hi Guys,
> I have a database, which I recently backed up. Now I would like to
> restore this back-up on another server. So basically I m trying to
> create a copy of my database on the other server.
> How can I restore this back-up file on the other server. When I try
> doing that from the Enterprise Manager, it says that I have to choose
> from an existing backup file. I m using SQL Server 2000 on Win 2K.
> So basically what I want is to create the database from scratch on the
> new server using that back up file from the other database. Can somebody
> tell me the procedure for this. I m a newbie at SQL Server.
> Thanks.
> Ricky
> --
> Posted via http://dbforums.com|||Hi Ricky,
You said :
>>'I have a database, which I recently backed up. Now I would like torestore
this back-up on another server. So basically I m trying to create a copy of
my database on the other server'. <<
It seems to me that you already have a backup available that you want to
restore...do the following :
1) Copy the backup file across to the new server.
2) On the new server, create a database device ( Expand: Management > Right
click on 'Backup' > New backup device, note the path and give it a
descrpiptive name (somedbname.bak)
3) Right click on the database you want to restore, All Tasks > Restore
Database
4) Click on 'From Device' and click 'Select Devices' on device name > click
Add.
5) click on filename and browse to the location where you copied the old
backup file and click ok.
6) Now, don't close all the windows, come back to the restore database
window and click on 'Options' tab and select the 'Force restore over
existing database'
7) in the Logical filename, check the old server for the logica filename
(somedbname) and enter it there, do the same for both log and mdf files.
8) In the 'Move to Physical filename' make sure it is pointing to the data
and log files of this new database.
Note that, the logical filename is not the path, but just a name, you can
get it by going to the old server, right click the database > properties --
the 'Filename' here is the logical filename -- do the same for the
Transaction log tab as well.
After you hit restore...it will complete the restore and give u a message
saying that restore is complete...
Hope this helps.
Regards,
Andy.
PS: This information is Ad-hoc and i cannot be held responsible for any
action you might take, I recommend that you consult a few people and then go
about it.
Thanks
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3082099.1057594899@.dbforums.com...
> Hi Guys,
> I have a database, which I recently backed up. Now I would like to
> restore this back-up on another server. So basically I m trying to
> create a copy of my database on the other server.
> How can I restore this back-up file on the other server. When I try
> doing that from the Enterprise Manager, it says that I have to choose
> from an existing backup file. I m using SQL Server 2000 on Win 2K.
> So basically what I want is to create the database from scratch on the
> new server using that back up file from the other database. Can somebody
> tell me the procedure for this. I m a newbie at SQL Server.
> Thanks.
> Ricky
> --
> Posted via http://dbforums.com
backup and resore encrypted data
I have two databases, one production and other a production copy.
One column of the production table is encrypted by the following syntax.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
--
I have stored procedure to add records to a production table like this.
ALTER Procedure AddRecord
@.id int,
@.var1 varchar(100)
AS
BEGIN
DECLARE @.var2 varbinary(256)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @.var2 = EncryptByKey(
key_guid( 'symkey_demo' ),
@.var1 )
insert into dbo.Test(id,Data,EData)
values(@.id,@.var1,@.var2)
CLOSE SYMMETRIC KEY symkey_demo
END
Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.
Create procedure ViewData
@.id int
AS
BEGIN
DECLARE @.var1 varbinary(256)
DECLARE @.var2 varchar(100)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
select @.var1=EData from Test where id=@.id
Set @.var2 = convert( varchar(100), DecryptByKey( @.var1))
print @.var2
END
It doesn't print the enctypted data. Please suggest what goes wrong.
The certificate protecting the key encrypting your data is protected by the DB master key (DBMK). In the server where the DBMK was created, the DBMK is protected by the password and by the service master kit (SMK). The SMK protection allows to use the DBMK automatically (i.e. without the need to open the DBMK explicitly).
When you move the DB to a different server, the protection by the SMK is no longer valid, and you need to manually open the DBMK and encrypt it with the current SMK. This is a one-time operation.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<password>>'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
For more detailed information go to BOL:
http://msdn2.microsoft.com/en-us/library/ms174433.aspx
http://msdn2.microsoft.com/en-us/library/ms186937.aspx
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks Raul.
I had done two things.
1.The service master key doesn't working. So I have to manually open the DBMK and encrypt with the current SMK.
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password!'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
2. Again the symmetric key doesn't work.
Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases.
so I have to change the symmetric key generation code to,
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES,
identity_value = 'Test Data encryption key',
key_source = 'test key'
ENCRYPTION BY CERTIFICATE cert_demo
Now I backed up the production database and restore it in a different server. I followed the stpe 1 and it works. Now I am able to decrypt the data in the production copy database.
Thanks
Amit
|||I see, when you described a copy of the datbase I assumed (incorrectly) that you were referring to a copy based on a backup.
Thanks a lot for letting us know, and let us know if you have further questions or comments.
-Raul Garcia
SDE/T
SQL Server Engine
backup and resore encrypted data
I have two databases, one production and other a production copy.
One column of the production table is encrypted by the following syntax.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
--
I have stored procedure to add records to a production table like this.
ALTER Procedure AddRecord
@.id int,
@.var1 varchar(100)
AS
BEGIN
DECLARE @.var2 varbinary(256)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @.var2 = EncryptByKey(
key_guid( 'symkey_demo' ),
@.var1 )
insert into dbo.Test(id,Data,EData)
values(@.id,@.var1,@.var2)
CLOSE SYMMETRIC KEY symkey_demo
END
Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.
Create procedure ViewData
@.id int
AS
BEGIN
DECLARE @.var1 varbinary(256)
DECLARE @.var2 varchar(100)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
select @.var1=EData from Test where id=@.id
Set @.var2 = convert( varchar(100), DecryptByKey( @.var1))
print @.var2
END
It doesn't print the enctypted data. Please suggest what goes wrong.
The certificate protecting the key encrypting your data is protected by the DB master key (DBMK). In the server where the DBMK was created, the DBMK is protected by the password and by the service master kit (SMK). The SMK protection allows to use the DBMK automatically (i.e. without the need to open the DBMK explicitly).
When you move the DB to a different server, the protection by the SMK is no longer valid, and you need to manually open the DBMK and encrypt it with the current SMK. This is a one-time operation.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<password>>'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
For more detailed information go to BOL:
http://msdn2.microsoft.com/en-us/library/ms174433.aspx
http://msdn2.microsoft.com/en-us/library/ms186937.aspx
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks Raul.
I had done two things.
1.The service master key doesn't working. So I have to manually open the DBMK and encrypt with the current SMK.
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password!'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
2. Again the symmetric key doesn't work.
Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases.
so I have to change the symmetric key generation code to,
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES,
identity_value = 'Test Data encryption key',
key_source = 'test key'
ENCRYPTION BY CERTIFICATE cert_demo
Now I backed up the production database and restore it in a different server. I followed the stpe 1 and it works. Now I am able to decrypt the data in the production copy database.
Thanks
Amit
|||I see, when you described a copy of the datbase I assumed (incorrectly) that you were referring to a copy based on a backup.
Thanks a lot for letting us know, and let us know if you have further questions or comments.
-Raul Garcia
SDE/T
SQL Server Engine
Friday, February 24, 2012
Backup a Database with a number as name
I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?
Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?
Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>
Backup a Database with a number as name
I've got some numbered databases in an SQL Server 2000 instance with SP3a (e
g
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a
(eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>
g
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a
(eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>
Subscribe to:
Posts (Atom)