transfer Data From Acces: restore

Showing posts with label restore. Show all posts

Friday, March 30, 2012

How Do I Automate A Recovery Process With Lots of Transaction Logs

I have to automate a process that will restore the latest BAK file and all the TRN files that occured afterwards on a remote server. I can't use the log shipping process. The BAK file is created nightly, and TRN files are created every 15 mins. I'm using a maintenance plan, so the file names change
Can anyone think of a good way to recover all those files, without all the clicking and typing
Jeff ZuerleinJeff
Create JOB under Management -SQL Server Agent folders with RESTORE DATABASE
..... command.
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:367639E5-7A83-4D3D-8FA4-2507A221F958@.microsoft.com...
> I have to automate a process that will restore the latest BAK file and all
the TRN files that occured afterwards on a remote server. I can't use the
log shipping process. The BAK file is created nightly, and TRN files are
created every 15 mins. I'm using a maintenance plan, so the file names
change.
> Can anyone think of a good way to recover all those files, without all the
clicking and typing?
> Jeff Zuerlein|||Jeff,
I'm interested in why you can't use log shipping. If it is because you're
not using Enterprise Edition, then there are scripts in the Resource Kit to
do it manually for Standard Edition and below, or online there are a few
people who provide them for free:
http://www.sql-server-performance.com/sql_server_log_shipping.asp
HTH,
Paul Ibison|||It's purely political
I'm using a maintenance plan, so the names of the transaction logs change
I think I could code my way out, but I hate to spend the time if there is a better solution
Jeff|||To load logs from a folder you can get the file names into a temp table in
date order (earliest first) using something like this pseudo code
declare @.files int
create table #files(filename varchar(255))
insert #files exec master..xp_cmdshell 'dir /B /A-D /O-D c:\logs\*.trn'
delete #files where filename is null or filename like '%File Not Found%'
select @.files = count(*) from #files
If @.files >0
begin
-- loop through files in a cursor issuing a restore log command
end
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:FCFA3898-9DAB-46EA-8BD8-0AF5982572EF@.microsoft.com...
> It's purely political.
> I'm using a maintenance plan, so the names of the transaction logs change.
> I think I could code my way out, but I hate to spend the time if there is
a better solution.
> Jeffsql

How Do I Automate A Recovery Process With Lots of Transaction Logs

I have to automate a process that will restore the latest BAK file and all t
he TRN files that occured afterwards on a remote server. I can't use the lo
g shipping process. The BAK file is created nightly, and TRN files are crea
ted every 15 mins. I'm usi
ng a maintenance plan, so the file names change.
Can anyone think of a good way to recover all those files, without all the c
licking and typing?
Jeff ZuerleinJeff
Create JOB under Management -SQL Server Agent folders with RESTORE DATABASE
..... command.
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:367639E5-7A83-4D3D-8FA4-2507A221F958@.microsoft.com...
> I have to automate a process that will restore the latest BAK file and all
the TRN files that occured afterwards on a remote server. I can't use the
log shipping process. The BAK file is created nightly, and TRN files are
created every 15 mins. I'm using a maintenance plan, so the file names
change.
> Can anyone think of a good way to recover all those files, without all the
clicking and typing?
> Jeff Zuerlein|||Jeff,
I'm interested in why you can't use log shipping. If it is because you're
not using Enterprise Edition, then there are scripts in the Resource Kit to
do it manually for Standard Edition and below, or online there are a few
people who provide them for free:
http://www.sql-server-performance.c...og_shipping.asp
HTH,
Paul Ibison|||It's purely political.
I'm using a maintenance plan, so the names of the transaction logs change.
I think I could code my way out, but I hate to spend the time if there is a
better solution.
Jeff|||To load logs from a folder you can get the file names into a temp table in
date order (earliest first) using something like this pseudo code
declare @.files int
create table #files(filename varchar(255))
insert #files exec master..xp_cmdshell 'dir /B /A-D /O-D c:\logs\*.trn'
delete #files where filename is null or filename like '%File Not Found%'
select @.files = count(*) from #files
If @.files >0
begin
-- loop through files in a cursor issuing a restore log command
end
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:FCFA3898-9DAB-46EA-8BD8-0AF5982572EF@.microsoft.com...
> It's purely political.
> I'm using a maintenance plan, so the names of the transaction logs change.
> I think I could code my way out, but I hate to spend the time if there is
a better solution.
> Jeff

Wednesday, March 28, 2012

How do auto rename datafiles on a multiple restore?

Hello,
i have a SQL 2005 question.
I create multiple databases from a specific backup file.
I want the database to rename the data & log files automatically if
they already exist (due to the prior creation). I know it is possible,
I do not know how to configure it.
YanivYaniv,shalom
Take a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm
"ALTER DATABASE" topic in the BOL
<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
> Yaniv
>|||Hi Uri, thanks for the answer.
Could you gimme a focus (which option are you referring to)?
Shouldn't that be on the instance level?
Yaniv
Uri Dimant wrote:
> Yaniv,shalom
> Take a look at
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm
> "ALTER DATABASE" topic in the BOL
>
>
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > Hello,
> > i have a SQL 2005 question.
> > I create multiple databases from a specific backup file.
> >
> > I want the database to rename the data & log files automatically if
> > they already exist (due to the prior creation). I know it is possible,
> > I do not know how to configure it.
> >
> > Yaniv
> >|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
>
Check out RESTORE DATABASE ... WITH MOVE in BOL
David|||That's what I am using currently.
I want to be able automatically restore any file I get,
without manually rename each logical file into a new physical one.
David Browne wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > Hello,
> > i have a SQL 2005 question.
> > I create multiple databases from a specific backup file.
> >
> > I want the database to rename the data & log files automatically if
> > they already exist (due to the prior creation). I know it is possible,
> > I do not know how to configure it.
> >
>
> Check out RESTORE DATABASE ... WITH MOVE in BOL
>
> David|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> That's what I am using currently.
> I want to be able automatically restore any file I get,
> without manually rename each logical file into a new physical one.
Not clear then exactly what you're missing then.
Build the string for the file name and use dynamic SQL to execute it.
> David Browne wrote:
> > <yaniv.harpaz@.gmail.com> wrote in message
> > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > Hello,
> > > i have a SQL 2005 question.
> > > I create multiple databases from a specific backup file.
> > >
> > > I want the database to rename the data & log files automatically if
> > > they already exist (due to the prior creation). I know it is possible,
> > > I do not know how to configure it.
> > >
> >
> >
> > Check out RESTORE DATABASE ... WITH MOVE in BOL
> >
> >
> > David
>|||I wanted to know if I can manage without it.
Restore and let the server generate the data & log files automatically
on the default locations.
Greg D. Moore (Strider) wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > That's what I am using currently.
> > I want to be able automatically restore any file I get,
> > without manually rename each logical file into a new physical one.
> Not clear then exactly what you're missing then.
> Build the string for the file name and use dynamic SQL to execute it.
>
> >
> > David Browne wrote:
> > > <yaniv.harpaz@.gmail.com> wrote in message
> > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > Hello,
> > > > i have a SQL 2005 question.
> > > > I create multiple databases from a specific backup file.
> > > >
> > > > I want the database to rename the data & log files automatically if
> > > > they already exist (due to the prior creation). I know it is possible,
> > > > I do not know how to configure it.
> > > >
> > >
> > >
> > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > >
> > >
> > > David
> >|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> I wanted to know if I can manage without it.
> Restore and let the server generate the data & log files automatically
> on the default locations.
No. The restore process restores the database to the location indicated
within the backup.
The only way I'm aware of changing this is the WITH MOVE option.
> Greg D. Moore (Strider) wrote:
> > <yaniv.harpaz@.gmail.com> wrote in message
> > news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > > That's what I am using currently.
> > > I want to be able automatically restore any file I get,
> > > without manually rename each logical file into a new physical one.
> >
> > Not clear then exactly what you're missing then.
> >
> > Build the string for the file name and use dynamic SQL to execute it.
> >
> >
> > >
> > > David Browne wrote:
> > > > <yaniv.harpaz@.gmail.com> wrote in message
> > > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > > Hello,
> > > > > i have a SQL 2005 question.
> > > > > I create multiple databases from a specific backup file.
> > > > >
> > > > > I want the database to rename the data & log files automatically
if
> > > > > they already exist (due to the prior creation). I know it is
possible,
> > > > > I do not know how to configure it.
> > > > >
> > > >
> > > >
> > > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > > >
> > > >
> > > > David
> > >
>|||I am trying this on one of my servers and it is working and on the
other it doesn't. how come?
I am using the same backup file.
create database "George_1Month001"
go
restore database "George_1Month001" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month002"
go
restore database "George_1Month002" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month003"
go
restore database "George_1Month003" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
Greg D. Moore (Strider) wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> > I wanted to know if I can manage without it.
> > Restore and let the server generate the data & log files automatically
> > on the default locations.
> No. The restore process restores the database to the location indicated
> within the backup.
> The only way I'm aware of changing this is the WITH MOVE option.
>
> >
> > Greg D. Moore (Strider) wrote:
> > > <yaniv.harpaz@.gmail.com> wrote in message
> > > news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > > > That's what I am using currently.
> > > > I want to be able automatically restore any file I get,
> > > > without manually rename each logical file into a new physical one.
> > >
> > > Not clear then exactly what you're missing then.
> > >
> > > Build the string for the file name and use dynamic SQL to execute it.
> > >
> > >
> > > >
> > > > David Browne wrote:
> > > > > <yaniv.harpaz@.gmail.com> wrote in message
> > > > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > > > Hello,
> > > > > > i have a SQL 2005 question.
> > > > > > I create multiple databases from a specific backup file.
> > > > > >
> > > > > > I want the database to rename the data & log files automatically
> if
> > > > > > they already exist (due to the prior creation). I know it is
> possible,
> > > > > > I do not know how to configure it.
> > > > > >
> > > > >
> > > > >
> > > > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > > > >
> > > > >
> > > > > David
> > > >
> >|||yaniv.harpaz@.gmail.com wrote:
> I am trying this on one of my servers and it is working and on the
> other it doesn't. how come?
> I am using the same backup file.
> create database "George_1Month001"
> go
> restore database "George_1Month001" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month002"
> go
> restore database "George_1Month002" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month003"
> go
> restore database "George_1Month003" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
>
>
Could you post the error message(s) you get ?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||for Example -
Msg 1834, Level 16, State 1, Line 1
The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
is being used by database 'Cyprus_1Month002'.
Msg 3156, Level 16, State 4, Line 1
File 'CleanDB_1001371' cannot be restored to
'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
It is being used by database 'Cyprus_1Month002'.
Msg 3156, Level 16, State 4, Line 1
File 'CleanDB_1001371_log' cannot be restored to
'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement.
Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Steen Persson (DK) wrote:
> yaniv.harpaz@.gmail.com wrote:
> > I am trying this on one of my servers and it is working and on the
> > other it doesn't. how come?
> > I am using the same backup file.
> >
> > create database "George_1Month001"
> > go
> >
> > restore database "George_1Month001" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> > -- Restore into a new database
> > create database "George_1Month002"
> > go
> >
> > restore database "George_1Month002" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> > -- Restore into a new database
> > create database "George_1Month003"
> > go
> >
> > restore database "George_1Month003" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> >
> >
> Could you post the error message(s) you get ?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||yaniv.harpaz@.gmail.com wrote:
> for Example -
> Msg 1834, Level 16, State 1, Line 1
> The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
> is being used by database 'Cyprus_1Month002'.
> Msg 3156, Level 16, State 4, Line 1
> File 'CleanDB_1001371' cannot be restored to
> 'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Msg 1834, Level 16, State 1, Line 1
> The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
> It is being used by database 'Cyprus_1Month002'.
> Msg 3156, Level 16, State 4, Line 1
> File 'CleanDB_1001371_log' cannot be restored to
> 'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 3119, Level 16, State 1, Line 1
> Problems were identified while planning for the RESTORE statement.
> Previous messages provide details.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
But then you have the solution in the error message - use the MOVE
option. SQL server tries to restore to the original files but those
files are in use by another database.
Look up RESTORE command in Books On Line - that will give you all the
options and syntax you need.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||have a look at one of the prior posts I wrote.
I want it to take place automatically, without manually telling the SQL
Server which physical path the data files should have.
there is one server in which I can do it, but I do not know what is the
difference in the configuration. this one works:
create database "George_1Month001"
go
restore database "George_1Month001" from
disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month002"
go
restore database "George_1Month002" from
disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
Steen Persson (DK) wrote:
> yaniv.harpaz@.gmail.com wrote:
> > for Example -
> > Msg 1834, Level 16, State 1, Line 1
> > The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
> > is being used by database 'Cyprus_1Month002'.
> > Msg 3156, Level 16, State 4, Line 1
> > File 'CleanDB_1001371' cannot be restored to
> > 'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
> > location for the file.
> > Msg 1834, Level 16, State 1, Line 1
> > The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
> > It is being used by database 'Cyprus_1Month002'.
> > Msg 3156, Level 16, State 4, Line 1
> > File 'CleanDB_1001371_log' cannot be restored to
> > 'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
> > valid location for the file.
> > Msg 3119, Level 16, State 1, Line 1
> > Problems were identified while planning for the RESTORE statement.
> > Previous messages provide details.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> >
> But then you have the solution in the error message - use the MOVE
> option. SQL server tries to restore to the original files but those
> files are in use by another database.
> Look up RESTORE command in Books On Line - that will give you all the
> options and syntax you need.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||yaniv.harpaz@.gmail.com wrote:
> have a look at one of the prior posts I wrote.
> I want it to take place automatically, without manually telling the SQL
> Server which physical path the data files should have.
> there is one server in which I can do it, but I do not know what is the
> difference in the configuration. this one works:
> create database "George_1Month001"
> go
> restore database "George_1Month001" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month002"
> go
> restore database "George_1Month002" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
>
There must be a difference in these 2 servers. The errormessage you
posted, says that the mdf file can't be overwritten because it's used by
another database. Try to check if any other database on that server, is
using that mdf file. What about the disk an directory layout - is that
the same on both servers?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator

Friday, March 23, 2012

How could I restore a backup automatically?

How could I restore a backup automatically?Hi,
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison

How could I restore a backup automatically?

How could I restore a backup automatically?Hi,
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>
|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison

Monday, March 12, 2012

How Can We Restore Database To New Database with Applying Diff. and Log Backup?

Hi,
I have trouble restoring database from backup device set. The device is valid. I want to restore the database to a new database. This step is succeed. The problem is that I only restore for full backup only, but I haven't applied differential and
transactional backup. I also want this set of backup move to new database. If I choose diff backup in restore dialog, then its only give one choice. The error message is I have to restore with norevocery or stanby opotion. I've already try from T-SQL, but
its still fail. My questions are:
1. How do I restore backup device to new database with applying diff and log backup?
2. Can I restore the diif and log backup to new location? (with full backup first)
Thanks.
Rahmadi Aji
When you restore a database and want to restore transaction logs right click on the database in question and in the options tab choose Leave database nonoperational but able to restore additional transaction logs. You can then apply transaction logs.
If you want to restore a backup to a different database than the original create the new database and then choose restore with the 'Force restore over existing database' option ticked.
|||>>If you want to restore a backup to a different database than the original create the new database and then choose[vbcol=seagreen]
In this case, I want to restore database to a new database in the same original database. And I already have tried all of
the alternatives including 'Force restore.... Because I want to restore it on different physical name. None are succeceed.
If I use same physical name it will overwrite the original.
|||<< In this case, I want to restore database to a new database in the same original database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...[vbcol=seagreen]
choose
> In this case, I want to restore database to a new database in the same original database. And I already have
tried all of
> the alternatives including 'Force restore.... Because I want to restore it on different physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.
|||If you want to restore a Database (from a backup) to a new Database, the 'logical' data and transaction log filenames must be the same and the file locations can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new Database with the SAME logical "Filename" for the data and transaction logs as the original but a different "location". If using EM, right click on the new database and choose
all tasks then restore database. Choose from device, add device and browse out to find the .bak file you want to restore. Back on the options tab, Choose the 'force restore over existing' and specify your new file locations for both the data and tra
nsaction logs under 'move to physical file name' - leave the logical filenames as is - those should be the same as your original database. If you want just to restore a full backup, leave the 'leave database operational' box checked. If you also wan
t to restore transaction log backups, choose one of the other options. if you are restoring more than one transaction log backup, on the LAST transaction log restore you should leave the 'leave database operational' box checked.
Knowledge base article 31456 also explains how to do this using query analyzer as well.
|||I mean in the same machine(server). I want to restore to a new database.
-- Tibor Karaszi wrote: --
<< In this case, I want to restore database to a new database in the same original database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...[vbcol=seagreen]
choose[vbcol=seagreen]
tried all of
> the alternatives including 'Force restore.... Because I want to restore it on different physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.
|||You're right about that, but I want to restore with differential and log backup. I have tried you're advice, it didn't work. I restore full backup ('leave database nonoperational') and then I add differential backup ('leave database operational'). When I
add diff. backup I use 'force restore over existing' and 'move to physical file name' with same logical name, It doesn't work.
-- jpotucek wrote: --
If you want to restore a Database (from a backup) to a new Database, the 'logical' data and transaction log filenames must be the same and the file locations can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new Database with the SAME logical "Filename" for the data and transaction logs as the original but a different "location". If using EM, right click on the new database and c
hoose all tasks then restore database. Choose from device, add device and browse out to find the .bak file you want to restore. Back on the options tab, Choose the 'force restore over existing' and specify your new file locations for both the data an
d transaction logs under 'move to physical file name' - leave the logical filenames as is - those should be the same as your original database. If you want just to restore a full backup, leave the 'leave database operational' box checked. If you als
o want to restore transaction log backups, choose one of the other options. if you are restoring more than one transaction log backup, on the LAST transaction log restore you should leave the 'leave database operational' box checked.
Knowledge base article 31456 also explains how to do this using query analyzer as well.
|||Again, If you post the RESTORE commands you used for all RESTORE commands (RESTORE DATABASE ..., RESTORE LOG
...) it is much easier to spot what might be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:6EE5847B-C35E-42E4-9AD4-93C799F70631@.microsoft.com...
> I mean in the same machine(server). I want to restore to a new database.
> -- Tibor Karaszi wrote: --
> << In this case, I want to restore database to a new database in the same original database.>>
> Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE
commands[vbcol=seagreen]
> (RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
> news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
and then[vbcol=seagreen]
> choose
already have[vbcol=seagreen]
> tried all of
name. None
> are succeceed.
>
>

How Can We Restore Database To New Database with Applying Diff. and Log Backup?

Hi,
I have trouble restoring database from backup device set. The device is vali
d. I want to restore the database to a new database. This step is succeed. T
he problem is that I only restore for full backup only, but I haven't applie
d differential and
transactional backup. I also want this set of backup move to new database. I
f I choose diff backup in restore dialog, then its only give one choice. The
error message is I have to restore with norevocery or stanby opotion. I've
already try from T-SQL, but
its still fail. My questions are:
1. How do I restore backup device to new database with applying diff and log
backup?
2. Can I restore the diif and log backup to new location? (with full backup
first)
Thanks.
Rahmadi AjiWhen you restore a database and want to restore transaction logs right click
on the database in question and in the options tab choose Leave database no
noperational but able to restore additional transaction logs. You can then a
pply transaction logs.
If you want to restore a backup to a different database than the original cr
eate the new database and then choose restore with the 'Force restore over e
xisting database' option ticked.|||>>If you want to restore a backup to a different database than the original create the new
database and then choose
In this case, I want to restore database to a new database in the same origi
nal database. And I already have tried all of
the alternatives including 'Force restore.... Because I want to restore it
on different physical name. None are succeceed.
If I use same physical name it will overwrite the original.|||<< In this case, I want to restore database to a new database in the same or
iginal database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you
used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might
be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
choose
> In this case, I want to restore database to a new database in the same original da
tabase. And I already have
tried all of
> the alternatives including 'Force restore.... Because I want to restore it on dif
ferent physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.|||If you want to restore a Database (from a backup) to a new Database, the 'lo
gical' data and transaction log filenames must be the same and the file loca
tions can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new
Database with the SAME logical "Filename" for the data and transaction logs
as the original but a different "location". If using EM, right click on
the new database and choose
all tasks then restore database. Choose from device, add device and brows
e out to find the .bak file you want to restore. Back on the options tab, C
hoose the 'force restore over existing' and specify your new file locations
for both the data and tra
nsaction logs under 'move to physical file name' - leave the logical filena
mes as is - those should be the same as your original database. If you w
ant just to restore a full backup, leave the 'leave database operational' bo
x checked. If you also wan
t to restore transaction log backups, choose one of the other options. if y
ou are restoring more than one transaction log backup, on the LAST transacti
on log restore you should leave the 'leave database operational' box checked
.
Knowledge base article 31456 also explains how to do this using query analyz
er as well.|||I mean in the same machine(server). I want to restore to a new database.
-- Tibor Karaszi wrote: --
<< In this case, I want to restore database to a new database in the same or
iginal database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you
used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might
be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
choose[vbcol=seagreen]
tried all of[vbcol=seagreen]
> the alternatives including 'Force restore.... Because I want to restore it on dif
ferent physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.|||You're right about that, but I want to restore with differential and log bac
kup. I have tried you're advice, it didn't work. I restore full backup ('lea
ve database nonoperational') and then I add differential backup ('leave data
base operational'). When I
add diff. backup I use 'force restore over existing' and 'move to physical
file name' with same logical name, It doesn't work.
-- jpotucek wrote: --
If you want to restore a Database (from a backup) to a new Database, the 'lo
gical' data and transaction log filenames must be the same and the file loca
tions can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new
Database with the SAME logical "Filename" for the data and transaction logs
as the original but a different "location". If using EM, right click on
the new database and c
hoose all tasks then restore database. Choose from device, add device and
browse out to find the .bak file you want to restore. Back on the options
tab, Choose the 'force restore over existing' and specify your new file loc
ations for both the data an
d transaction logs under 'move to physical file name' - leave the logical f
ilenames as is - those should be the same as your original database. If
you want just to restore a full backup, leave the 'leave database operationa
l' box checked. If you als
o want to restore transaction log backups, choose one of the other options.
if you are restoring more than one transaction log backup, on the LAST tran
saction log restore you should leave the 'leave database operational' box ch
ecked.
Knowledge base article 31456 also explains how to do this using query analyz
er as well.|||Again, If you post the RESTORE commands you used for all RESTORE commands (
RESTORE DATABASE ..., RESTORE LOG
...) it is much easier to spot what might be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:6EE5847B-C35E-42E4-9AD4-93C799F70631@.microsoft.com...
> I mean in the same machine(server). I want to restore to a new database.
> -- Tibor Karaszi wrote: --
> << In this case, I want to restore database to a new database in the
same original database.>>
> Above seems like a contradiction to me. If you post the RESTORE commands you
used for all RESTORE
commands
> (RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what
might be the problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
> news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
and then[vbcol=seagreen]
> choose
already have[vbcol=seagreen]
> tried all of
name. None[vbcol=seagreen]
> are succeceed.
>
>

How Can We Restore Database To New Database with Applying Diff. and Log Backup?

Hi
I have trouble restoring database from backup device set. The device is valid. I want to restore the database to a new database. This step is succeed. The problem is that I only restore for full backup only, but I haven't applied differential an
transactional backup. I also want this set of backup move to new database. If I choose diff backup in restore dialog, then its only give one choice. The error message is I have to restore with norevocery or stanby opotion. I've already try from T-SQL, but its still fail. My questions are
1. How do I restore backup device to new database with applying diff and log backup
2. Can I restore the diif and log backup to new location? (with full backup first)
Thanks
Rahmadi AjiWhen you restore a database and want to restore transaction logs right click on the database in question and in the options tab choose Leave database nonoperational but able to restore additional transaction logs. You can then apply transaction logs
If you want to restore a backup to a different database than the original create the new database and then choose restore with the 'Force restore over existing database' option ticked.|||>>If you want to restore a backup to a different database than the original create the new database and then choose
>> restore with the 'Force restore over existing database' option ticked
In this case, I want to restore database to a new database in the same original database. And I already have tried all o
the alternatives including 'Force restore.... Because I want to restore it on different physical name. None are succeceed
If I use same physical name it will overwrite the original.|||<< In this case, I want to restore database to a new database in the same original database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
> >>If you want to restore a backup to a different database than the original create the new database and then
choose
> >> restore with the 'Force restore over existing database' option ticked.
> In this case, I want to restore database to a new database in the same original database. And I already have
tried all of
> the alternatives including 'Force restore.... Because I want to restore it on different physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.|||If you want to restore a Database (from a backup) to a new Database, the 'logical' data and transaction log filenames must be the same and the file locations can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new Database with the SAME logical "Filename" for the data and transaction logs as the original but a different "location". If using EM, right click on the new database and choose all tasks then restore database. Choose from device, add device and browse out to find the .bak file you want to restore. Back on the options tab, Choose the 'force restore over existing' and specify your new file locations for both the data and transaction logs under 'move to physical file name' - leave the logical filenames as is - those should be the same as your original database. If you want just to restore a full backup, leave the 'leave database operational' box checked. If you also want to restore transaction log backups, choose one of the other options. if you are restoring more than one transaction log backup, on the LAST transaction log restore you should leave the 'leave database operational' box checked
Knowledge base article 31456 also explains how to do this using query analyzer as well.|||I mean in the same machine(server). I want to restore to a new database.
-- Tibor Karaszi wrote: --
<< In this case, I want to restore database to a new database in the same original database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
>>If you want to restore a backup to a different database than the original create the new database and then
choose
>> restore with the 'Force restore over existing database' option ticked.
>> In this case, I want to restore database to a new database in the same original database. And I already have
tried all of
> the alternatives including 'Force restore.... Because I want to restore it on different physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.|||You're right about that, but I want to restore with differential and log backup. I have tried you're advice, it didn't work. I restore full backup ('leave database nonoperational') and then I add differential backup ('leave database operational'). When I add diff. backup I use 'force restore over existing' and 'move to physical file name' with same logical name, It doesn't work
-- jpotucek wrote: --
If you want to restore a Database (from a backup) to a new Database, the 'logical' data and transaction log filenames must be the same and the file locations can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new Database with the SAME logical "Filename" for the data and transaction logs as the original but a different "location". If using EM, right click on the new database and choose all tasks then restore database. Choose from device, add device and browse out to find the .bak file you want to restore. Back on the options tab, Choose the 'force restore over existing' and specify your new file locations for both the data and transaction logs under 'move to physical file name' - leave the logical filenames as is - those should be the same as your original database. If you want just to restore a full backup, leave the 'leave database operational' box checked. If you also want to restore transaction log backups, choose one of the other options. if you are restoring more than one transaction log backup, on the LAST transaction log restore you should leave the 'leave database operational' box checked
Knowledge base article 31456 also explains how to do this using query analyzer as well.|||Again, If you post the RESTORE commands you used for all RESTORE commands (RESTORE DATABASE ..., RESTORE LOG
...) it is much easier to spot what might be the problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:6EE5847B-C35E-42E4-9AD4-93C799F70631@.microsoft.com...
> I mean in the same machine(server). I want to restore to a new database.
> -- Tibor Karaszi wrote: --
> << In this case, I want to restore database to a new database in the same original database.>>
> Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE
commands
> (RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
> news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
> >>If you want to restore a backup to a different database than the original create the new database
and then
> choose
> >> restore with the 'Force restore over existing database' option ticked.
> >> In this case, I want to restore database to a new database in the same original database. And I
already have
> tried all of
> > the alternatives including 'Force restore.... Because I want to restore it on different physical
name. None
> are succeceed.
> > If I use same physical name it will overwrite the original.
>
>

Friday, March 9, 2012

How can take Back-Up and restore database from a web form wsing asp.net(vb.net)

I want to give a facility to backup database restore database to the customer in my project.
Database is stored in sql server 2000.
From the website the browser can take backup by clicking BACKUP button and
restore database by clicking RESTORE button.
is it possiable in asp.net programatically ?

In SQL Server you backup a database using the "backup database" command. You restore a database using the "restore database" commands. You can lookup the complete syntax to these commands.

You issue these SQL commands from asp.net just like you would any other SQL command. The backup command will work even if others are using the database at the moment you do the backup. The biggest problem would be the restore. If you are going to restore a database over an existing database (overwrite it) then that database must not be in use or you will get an error.

Brian

|||i do not really recommend this approach whether you can or not !!!!
this is an admin job and it is better not to have a wide access to do this kind of operation..... i am not sure about if you can do it or not ... !!|||Its definitely possible, take a look at this page on MSDNhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp This details the TSQL commands that are necessary to perform the backup/restore. Once you create the commands just execute them with SqlClient.
Hope This Helps

How can revert a SQL2K DB back to SQL7?

Hello. I have a SQL2K DB that needs to go on an SQL 7.0 Server. How can I
make it restore to the 7.0 Server?
Any help appreciated!
Thanks,
Tom
You could use DTS and copy Database. Unfortunately you cannot just do a
restore or an attach.
Jeff Duncan
MCDBA, MCSE+I
"Tom" <none@.none.com> wrote in message
news:OiUcI%23VWEHA.2716@.tk2msftngp13.phx.gbl...
> Hello. I have a SQL2K DB that needs to go on an SQL 7.0 Server. How can
> I
> make it restore to the 7.0 Server?
> Any help appreciated!
> Thanks,
>
> Tom
>
|||: (
Okay.. Thanks Jeff!
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:%23C7ZAHWWEHA.3012@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> You could use DTS and copy Database. Unfortunately you cannot just do a
> restore or an attach.
> --
> Jeff Duncan
> MCDBA, MCSE+I
> "Tom" <none@.none.com> wrote in message
> news:OiUcI%23VWEHA.2716@.tk2msftngp13.phx.gbl...
can
>

How can one edit the sysusers table under SQL 2005?

ur database needs to run under a specific user, so we
have it under sysusers table. When we do a restore or a copy,
we used to go into that table rename the user and go into SQL security
and create that user and assign the DB, we no longer can do that.
How can one edit the sysusers table under SQL 2005?
Please help...
Hi
It is strongly recommended to not alter system tables directly
How about CREATE USER command?
Also search om internet for two stored procedures('sp_help_revlogin') that
provided by MS transfer the USERS wi6th their oridinal SID
"API Conektia" <api@.online.nospam> wrote in message
news:zezu4tlp82yz.1l9ba905uokev.dlg@.40tude.net...
> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...
|||You can no longer view the systables in SQL2005 they are hidden now. Look for
compatibility Views in BOL
Regards
"API Conektia" wrote:

> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...
>
|||BOL? I can see the table by doing a select * from sysusers, but not thru
the GUI.
I fixed my issue doing:
USE BD;
GO
EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
GO
thanks for all replies
On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:
[vbcol=seagreen]
> You can no longer view the systables in SQL2005 they are hidden now. Look for
> compatibility Views in BOL
> Regards
> "API Conektia" wrote:
|||BOL is Books Online - the SQL Server help file.
Sysusers is not a table in 2005 - it's a view provided for
backwards compatibility. It's listed under the system views
in the GUI. You have to use the built in procedures,
functions or other supported methods to modify system data
in 2005 - no more hacking system tables.
-Sue
On Wed, 22 Feb 2006 15:08:14 +0100, API Conektia
<api@.online.nospam> wrote:
[vbcol=seagreen]
>BOL? I can see the table by doing a select * from sysusers, but not thru
>the GUI.
>I fixed my issue doing:
>USE BD;
>GO
>EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
>GO
>thanks for all replies
>
>On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:

How can one edit the sysusers table under SQL 2005?

ur database needs to run under a specific user, so we
have it under sysusers table. When we do a restore or a copy,
we used to go into that table rename the user and go into SQL security
and create that user and assign the DB, we no longer can do that.
How can one edit the sysusers table under SQL 2005?
Please help...Hi
It is strongly recommended to not alter system tables directly
How about CREATE USER command?
Also search om internet for two stored procedures('sp_help_revlogin') that
provided by MS transfer the USERS wi6th their oridinal SID
"API Conektia" <api@.online.nospam> wrote in message
news:zezu4tlp82yz.1l9ba905uokev.dlg@.40tude.net...
> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...|||You can no longer view the systables in SQL2005 they are hidden now. Look for
compatibility Views in BOL
Regards
"API Conektia" wrote:
> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...
>|||BOL? I can see the table by doing a select * from sysusers, but not thru
the GUI.
I fixed my issue doing:
USE BD;
GO
EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
GO
thanks for all replies
On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:
> You can no longer view the systables in SQL2005 they are hidden now. Look for
> compatibility Views in BOL
> Regards
> "API Conektia" wrote:
>> ur database needs to run under a specific user, so we
>> have it under sysusers table. When we do a restore or a copy,
>> we used to go into that table rename the user and go into SQL security
>> and create that user and assign the DB, we no longer can do that.
>> How can one edit the sysusers table under SQL 2005?
>> Please help...|||BOL is Books Online - the SQL Server help file.
Sysusers is not a table in 2005 - it's a view provided for
backwards compatibility. It's listed under the system views
in the GUI. You have to use the built in procedures,
functions or other supported methods to modify system data
in 2005 - no more hacking system tables.
-Sue
On Wed, 22 Feb 2006 15:08:14 +0100, API Conektia
<api@.online.nospam> wrote:
>BOL? I can see the table by doing a select * from sysusers, but not thru
>the GUI.
>I fixed my issue doing:
>USE BD;
>GO
>EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
>GO
>thanks for all replies
>
>On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:
>> You can no longer view the systables in SQL2005 they are hidden now. Look for
>> compatibility Views in BOL
>> Regards
>> "API Conektia" wrote:
>> ur database needs to run under a specific user, so we
>> have it under sysusers table. When we do a restore or a copy,
>> we used to go into that table rename the user and go into SQL security
>> and create that user and assign the DB, we no longer can do that.
>> How can one edit the sysusers table under SQL 2005?
>> Please help...

How can one edit the sysusers table under SQL 2005?

ur database needs to run under a specific user, so we
have it under sysusers table. When we do a restore or a copy,
we used to go into that table rename the user and go into SQL security
and create that user and assign the DB, we no longer can do that.
How can one edit the sysusers table under SQL 2005?
Please help...Hi
It is strongly recommended to not alter system tables directly
How about CREATE USER command?
Also search om internet for two stored procedures('sp_help_revlogin') that
provided by MS transfer the USERS wi6th their oridinal SID
"API Conektia" <api@.online.nospam> wrote in message
news:zezu4tlp82yz.1l9ba905uokev.dlg@.40tude.net...
> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...|||You can no longer view the systables in SQL2005 they are hidden now. Look fo
r
compatibility Views in BOL
Regards
"API Conektia" wrote:

> ur database needs to run under a specific user, so we
> have it under sysusers table. When we do a restore or a copy,
> we used to go into that table rename the user and go into SQL security
> and create that user and assign the DB, we no longer can do that.
> How can one edit the sysusers table under SQL 2005?
> Please help...
>|||BOL? I can see the table by doing a select * from sysusers, but not thru
the GUI.
I fixed my issue doing:
USE BD;
GO
EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
GO
thanks for all replies
On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:
[vbcol=seagreen]
> You can no longer view the systables in SQL2005 they are hidden now. Look
for
> compatibility Views in BOL
> Regards
> "API Conektia" wrote:
>|||BOL is Books Online - the SQL Server help file.
Sysusers is not a table in 2005 - it's a view provided for
backwards compatibility. It's listed under the system views
in the GUI. You have to use the built in procedures,
functions or other supported methods to modify system data
in 2005 - no more hacking system tables.
-Sue
On Wed, 22 Feb 2006 15:08:14 +0100, API Conektia
<api@.online.nospam> wrote:
[vbcol=seagreen]
>BOL? I can see the table by doing a select * from sysusers, but not thru
>the GUI.
>I fixed my issue doing:
>USE BD;
>GO
>EXEC sp_change_users_login 'Auto_Fix', 'myuser', NULL, 'myuser';
>GO
>thanks for all replies
>
>On Wed, 22 Feb 2006 04:51:26 -0800, Zekske wrote:
>

Friday, March 30, 2012

How Do I Automate A Recovery Process With Lots of Transaction Logs

How Do I Automate A Recovery Process With Lots of Transaction Logs

Wednesday, March 28, 2012

How do auto rename datafiles on a multiple restore?

How do auto rename datafiles on a multiple restore?

Friday, March 23, 2012

How could I restore a backup automatically?

How could I restore a backup automatically?

How could I restore a backup automatically?

Monday, March 12, 2012

How Can We Restore Database To New Database with Applying Diff. and Log Backup?

How Can We Restore Database To New Database with Applying Diff. and Log Backup?

How Can We Restore Database To New Database with Applying Diff. and Log Backup?

Friday, March 9, 2012

How can take Back-Up and restore database from a web form wsing asp.net(vb.net)

How can revert a SQL2K DB back to SQL7?

How can one edit the sysusers table under SQL 2005?

How can one edit the sysusers table under SQL 2005?

How can one edit the sysusers table under SQL 2005?

transfer Data From Acces

Blog Archive

About Me