|
Home > Archive > MS SQL Server > October 2005 > Set simple recovery model to existing DB via sql
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Set simple recovery model to existing DB via sql
|
|
|
| I need to change DB recovery model from full to simple in existing DB.
Of course i know how to do this from SQL manager, but is there any way to do
this from sql batch ?
I am using sql sript to restore db and i want to incorporate seting db to
simple recovery mode straight into this script. This should happen right
after db is restored
my DB restore script:
USE myDB
GO
ALTER DATABASE myDB SET single_user WITH ROLLBACK IMMEDIATE
USE MASTER
GO
DECLARE @source NVARCHAR(500)
DECLARE @data NVARCHAR(500)
DECLARE @log NVARCHAR(500)
DECLARE @move INT
-- #### SETTINGS ### --
SET @source = 'E:\backup\dailybuil
d.bck'
SET @move = 1
SET @data = 'E:\sql\test\myDB_Da
ta.MDF'
SET @log = 'E:\sql\test\myDB_Lo
g.LDF'
-- #### SETTINGS ### --
IF @move = 1
BEGIN
RESTORE DATABASE myDB
FROM DISK = @source
WITH
MOVE 'myDB_Data' TO @data
,MOVE 'myDB_Log' TO @log
,REPLACE
,STATS = 5
END
ELSE
BEGIN
RESTORE DATABASE myDB
FROM DISK = @source
WITH
REPLACE
,STATS = 5
END
| |
| Uri Dimant 2005-10-31, 7:23 am |
| ALTER DATABASE test SET RECOVERY SIMPLE
"Mikus" <Mikus@discussions.microsoft.com> wrote in message
news:9EC2EDFF-2F45-463D-A534- 03264E3092E2@microso
ft.com...
>I need to change DB recovery model from full to simple in existing DB.
> Of course i know how to do this from SQL manager, but is there any way to
> do
> this from sql batch ?
>
> I am using sql sript to restore db and i want to incorporate seting db to
> simple recovery mode straight into this script. This should happen right
> after db is restored
>
> my DB restore script:
>
> USE myDB
> GO
>
> ALTER DATABASE myDB SET single_user WITH ROLLBACK IMMEDIATE
>
> USE MASTER
> GO
>
> DECLARE @source NVARCHAR(500)
> DECLARE @data NVARCHAR(500)
> DECLARE @log NVARCHAR(500)
> DECLARE @move INT
>
> -- #### SETTINGS ### --
> SET @source = 'E:\backup\dailybuil
d.bck'
> SET @move = 1
> SET @data = 'E:\sql\test\myDB_Da
ta.MDF'
> SET @log = 'E:\sql\test\myDB_Lo
g.LDF'
> -- #### SETTINGS ### --
>
> IF @move = 1
> BEGIN
> RESTORE DATABASE myDB
> FROM DISK = @source
> WITH
> MOVE 'myDB_Data' TO @data
> ,MOVE 'myDB_Log' TO @log
> ,REPLACE
> ,STATS = 5
> END
> ELSE
> BEGIN
> RESTORE DATABASE myDB
> FROM DISK = @source
> WITH
> REPLACE
> ,STATS = 5
> END
|
|
|
|
|