--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('d')
GO
Am I correct in creating the database and the way of backup?
Would you teach me the senarios that restore the database to 'a','b','c','d'
states?
Thanks in advance.You'll need to add another backup to backup 'd', but something like this
should do:
--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('d')
GO
--whole backup 3
BACKUP DATABASE Test TO mydiskdump
go
restore headeronly from mydiskdump
go
restore database Test from mydiskdump with file = 1, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 1, norecovery
restore database Test from mydiskdump with file = 2, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 3, norecovery
restore database Test from mydiskdump with file = 4, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 5, recovery
select * from Test.dbo.tblTest
go
Note that dump devices can store multiple backup sets. You can get the
backup sets that a dump device contains using the restore headeronly command
which lets you know which backup sets to restore. I'd suggest you consider
naming your backup sets though as this can help work out which backups to
restore as well..
HTH
Regards,
Greg Linwood
SQL Server MVP
"haode" <haode@.hao.com> wrote in message
news:%23eNTtec9DHA.1504@.TK2MSFTNGP12.phx.gbl...
> --Drop database
> if exists (select * from master.dbo.sysdatabases where name='Test')
> drop database Test
> GO
> --Drop device
> if exists (select * from master.dbo.sysdevices where name='mydiskdump')
> exec master.dbo.sp_dropdevice mydiskdump
> GO
> --Create Test database
> CREATE DATABASE Test
> ON
> ( NAME = Test_dat,
> FILENAME = 'c:\Test.mdf',
> SIZE = 5,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
> LOG ON
> ( NAME = 'Test_log',
> FILENAME = 'c:\Test.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
> GO
> --add device
> EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
> GO
> CREATE TABLE Test.dbo.tblTest (
> col1 char(1)
> )
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values ('a')
> GO
> --whole backup 1
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('b')
> GO
> --differential backup 1
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> --whole backup 2
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('c')
> GO
> --differential backup 2
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('d')
> GO
> Am I correct in creating the database and the way of backup?
> Would you teach me the senarios that restore the database to
'a','b','c','d'
> states?
> Thanks in advance.
>
Showing posts with label drop. Show all posts
Showing posts with label drop. Show all posts
Wednesday, March 28, 2012
restore databases in T-SQL.
--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('d')
GO
Am I correct in creating the database and the way of backup?
Would you teach me the senarios that restore the database to 'a','b','c','d'
states?
Thanks in advance.You'll need to add another backup to backup 'd', but something like this
should do:
--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('d')
GO
--whole backup 3
BACKUP DATABASE Test TO mydiskdump
go
restore headeronly from mydiskdump
go
restore database Test from mydiskdump with file = 1, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 1, norecovery
restore database Test from mydiskdump with file = 2, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 3, norecovery
restore database Test from mydiskdump with file = 4, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 5, recovery
select * from Test.dbo.tblTest
go
Note that dump devices can store multiple backup sets. You can get the
backup sets that a dump device contains using the restore headeronly command
which lets you know which backup sets to restore. I'd suggest you consider
naming your backup sets though as this can help work out which backups to
restore as well..
HTH
Regards,
Greg Linwood
SQL Server MVP
"haode" <haode@.hao.com> wrote in message
news:%23eNTtec9DHA.1504@.TK2MSFTNGP12.phx.gbl...
> --Drop database
> if exists (select * from master.dbo.sysdatabases where name='Test')
> drop database Test
> GO
> --Drop device
> if exists (select * from master.dbo.sysdevices where name='mydiskdump')
> exec master.dbo.sp_dropdevice mydiskdump
> GO
> --Create Test database
> CREATE DATABASE Test
> ON
> ( NAME = Test_dat,
> FILENAME = 'c:\Test.mdf',
> SIZE = 5,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
> LOG ON
> ( NAME = 'Test_log',
> FILENAME = 'c:\Test.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
> GO
> --add device
> EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
> GO
> CREATE TABLE Test.dbo.tblTest (
> col1 char(1)
> )
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values ('a')
> GO
> --whole backup 1
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('b')
> GO
> --differential backup 1
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> --whole backup 2
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('c')
> GO
> --differential backup 2
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('d')
> GO
> Am I correct in creating the database and the way of backup?
> Would you teach me the senarios that restore the database to
'a','b','c','d'
> states?
> Thanks in advance.
>sql
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('d')
GO
Am I correct in creating the database and the way of backup?
Would you teach me the senarios that restore the database to 'a','b','c','d'
states?
Thanks in advance.You'll need to add another backup to backup 'd', but something like this
should do:
--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('d')
GO
--whole backup 3
BACKUP DATABASE Test TO mydiskdump
go
restore headeronly from mydiskdump
go
restore database Test from mydiskdump with file = 1, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 1, norecovery
restore database Test from mydiskdump with file = 2, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 3, norecovery
restore database Test from mydiskdump with file = 4, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 5, recovery
select * from Test.dbo.tblTest
go
Note that dump devices can store multiple backup sets. You can get the
backup sets that a dump device contains using the restore headeronly command
which lets you know which backup sets to restore. I'd suggest you consider
naming your backup sets though as this can help work out which backups to
restore as well..
HTH
Regards,
Greg Linwood
SQL Server MVP
"haode" <haode@.hao.com> wrote in message
news:%23eNTtec9DHA.1504@.TK2MSFTNGP12.phx.gbl...
> --Drop database
> if exists (select * from master.dbo.sysdatabases where name='Test')
> drop database Test
> GO
> --Drop device
> if exists (select * from master.dbo.sysdevices where name='mydiskdump')
> exec master.dbo.sp_dropdevice mydiskdump
> GO
> --Create Test database
> CREATE DATABASE Test
> ON
> ( NAME = Test_dat,
> FILENAME = 'c:\Test.mdf',
> SIZE = 5,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
> LOG ON
> ( NAME = 'Test_log',
> FILENAME = 'c:\Test.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
> GO
> --add device
> EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
> GO
> CREATE TABLE Test.dbo.tblTest (
> col1 char(1)
> )
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values ('a')
> GO
> --whole backup 1
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('b')
> GO
> --differential backup 1
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> --whole backup 2
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('c')
> GO
> --differential backup 2
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('d')
> GO
> Am I correct in creating the database and the way of backup?
> Would you teach me the senarios that restore the database to
'a','b','c','d'
> states?
> Thanks in advance.
>sql
Friday, March 23, 2012
restore database from ldf file only
Hello guys,
is it possible to restore an SQL server database from the .ldf file only?
The last operation is a script execution that have drop and create all the tables.
have you some idea?
Thanks to allldf typically contains only logs, which record changes to the database. Any restore must begin by reloading a backup, followed by whatever logs are required/desired.|||thanks for the fast reply.
I have an old backup that is from december, and the log file that is till now.
Do I have some chance?|||Yes, if you have continuous logs.
You can certainly restore the backup from December. If your logs are continuous since then, then you can do a point in time restore. Your best bet is to do it using Enterprise Manager, assuming that it has kept a record of backups and log dumps.|||First check if your database has been in FULL recovery mode. If it is set to SIMPLE than you don't stand a chance.
The simplist way to check this is in EM. Look at the properties of the database, tab "options".|||First check if your database has been in FULL recovery mode. If it is set to SIMPLE than you don't stand a chance.
The simplist way to check this is in EM. Look at the properties of the database, tab "options".
I could wrong but I do not think you can even back up the log in SIMPLE.|||Nope you can't. And even if you could there wouldn't be anything of use to you in there.
But it sounds like they didn't backup logs, there's only talk about a full backup. So if there are no log backups and the database is in FULL recovery mode (that ldf file could be huge!) he could try a restore with a roll forward (I think that's the theory, never done it before).
Who is it that has that nice tag line: "did you hug your backup today?" :D
Lex
is it possible to restore an SQL server database from the .ldf file only?
The last operation is a script execution that have drop and create all the tables.
have you some idea?
Thanks to allldf typically contains only logs, which record changes to the database. Any restore must begin by reloading a backup, followed by whatever logs are required/desired.|||thanks for the fast reply.
I have an old backup that is from december, and the log file that is till now.
Do I have some chance?|||Yes, if you have continuous logs.
You can certainly restore the backup from December. If your logs are continuous since then, then you can do a point in time restore. Your best bet is to do it using Enterprise Manager, assuming that it has kept a record of backups and log dumps.|||First check if your database has been in FULL recovery mode. If it is set to SIMPLE than you don't stand a chance.
The simplist way to check this is in EM. Look at the properties of the database, tab "options".|||First check if your database has been in FULL recovery mode. If it is set to SIMPLE than you don't stand a chance.
The simplist way to check this is in EM. Look at the properties of the database, tab "options".
I could wrong but I do not think you can even back up the log in SIMPLE.|||Nope you can't. And even if you could there wouldn't be anything of use to you in there.
But it sounds like they didn't backup logs, there's only talk about a full backup. So if there are no log backups and the database is in FULL recovery mode (that ldf file could be huge!) he could try a restore with a roll forward (I think that's the theory, never done it before).
Who is it that has that nice tag line: "did you hug your backup today?" :D
Lex
Tuesday, March 20, 2012
Restore Database --> drop users
Third question:
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.
Why this happens??
Regards,
ManolisThat problem is due to the way that SQL Server implements the connection between logins and users. A simpler suggestion would be to use sp_change_users_login (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp) instead.
-PatP
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.
Why this happens??
Regards,
ManolisThat problem is due to the way that SQL Server implements the connection between logins and users. A simpler suggestion would be to use sp_change_users_login (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp) instead.
-PatP
Subscribe to:
Posts (Atom)