Sql Server Backup/restore Q?

[littlemoon]

[quote name='RockstarSwathiMuthyam' timestamp='1360178224' post='1303236140']
[url="http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/"]http://www.simple-ta...r-2005-backups/[/url]
[/quote][size=4][quote name='Jeedeelu' timestamp='1360177746' post='1303236101'][/size]

[color=#333333][font=verdana, tahoma, arial, sans-serif][size=3]Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.[/size][/font][/color]
[color=#333333][font=verdana, tahoma, arial, sans-serif][size=3][b]Step 1:[/b] Retrive the Logical file name of the database from backup.
[color=blue]RESTORE [/color][color=black]FILELISTONLY[/color]
[color=blue]FROM DISK = [/color][color=red]'D:BackUpYourBaackUpFile.bak'[/color]
[color=black]GO[/color]

[b]Step 2:[/b] Use the values in the LogicalName Column in following Step.
[color=green]----Make Database to single user Mode[/color]
[color=blue]ALTER DATABASE [/color][color=black]YourDB[/color]
[color=blue]SET [/color][color=black]SINGLE_USER [/color][color=blue]WITH
ROLLBACK [/color][color=black]IMMEDIATE[/color][/size][/font][/color]
[color=#333333][font=verdana, tahoma, arial, sans-serif][size=3][color=green]----Restore Database[/color]
[color=blue]RESTORE DATABASE [/color][color=black]YourDB[/color]
[color=blue]FROM DISK = [/color][color=red]'D:BackUpYourBaackUpFile.bak'[/color]
[color=blue]WITH [/color][color=black]MOVE [/color][color=red]'YourMDFLogicalName' [/color][color=blue]TO [/color][color=red]'D:DataYourMDFFile.mdf'[/color][color=gray],[/color]
[color=black]MOVE [/color][color=red]'YourLDFLogicalName' [/color][color=blue]TO [/color][color=red]'D:DataYourLDFFile.ldf'[/color][/size][/font][/color]
[color=#333333][font=verdana, tahoma, arial, sans-serif][size=3][color=green]/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/[/color]
[color=blue]ALTER DATABASE [/color][color=black]YourDB [/color][color=blue]SET [/color][color=black]MULTI_USER
GO[/color][/size][/font][/color]
[/quote]
[quote name='RockstarSwathiMuthyam' timestamp='1360177855' post='1303236107']
[url="http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/"]http://www.mssqltips...rver-databases/[/url]
[/quote] Thank u both...

kani ekada permissions kuda include ayi ledhu ? isn't it a best practice to load data with permissions ?

[150bryant]

permissions ki details unaaye ee link lo..
idont know how helpful but an insight isnt always a bad idea
http://stackoverflow.com/questions/461385/restoring-a-backup-to-a-different-server-user-permissions

[150bryant]

[b] [url="http://www.sqlservercentral.com/blogs/brian_kelley/2009/08/28/quick-2005-2008-script-to-export-permissions/"]Quick 2005/2008 Script to Export Permissions[/url][/b]
[list]
[*]Posted on 28 August 2009
[*][url="http://www.sqlservercentral.com/blogs/brian_kelley/2009/08/28/quick-2005-2008-script-to-export-permissions/#comments"]Comments[/url]
[*][url=""%20style="][img]http://www.sqlservercentral.com/Resources/Images/briefcase.gif[/img] Briefcase[/url]
[*][url="http://www.sqlservercentral.com/blogs/brian_kelley/2009/08/28/quick-2005-2008-script-to-export-permissions/print/"][img]http://www.sqlservercentral.com/Resources/Images/print.gif[/img] Print[/url]
[/list][color=#2A2A2A][font=Arial, Helvetica, sans-serif][size=3]

I had a need to export permissions to be run against the database once an older version was restored (but which didn't have the permissions yet). I'm sure there are 101 versions of the script out there, but here's mine:
[size="2"][font="Courier New"][color=blue]SELECT
[/color][color=#FF00FF]CASE [/color][/font][/size][size="2"][font="Courier New"][color=black]dp.state_desc
[/color][color=blue]WHEN [/color][color=red]'GRANT_WITH_GRANT_OPTION' [/color][color=blue]THEN [/color][/font][/size][size="2"][font="Courier New"][color=red]'GRANT'
[/color][color=blue]ELSE [/color][/font][/size][color=black][font="Courier New"][size="2"]dp.state_desc
[/size][/font][/color][size="2"][font="Courier New"][color=blue]END
[/color][color=gray]+ [/color][color=red]' ' [/color][color=gray]+ [/color][color=black]dp.permission_name [/color][color=gray]+ [/color][color=red]' ON ' [/color][/font][/size][size="2"][font="Courier New"][color=gray]+
[/color][color=#FF00FF]CASE [/color][/font][/size][size="2"][font="Courier New"][color=black]dp.class
[/color][color=blue]WHEN [/color][color=black]0 [/color][color=blue]THEN [/color][color=red]'DATABASE::[' [/color][color=gray]+ [/color][color=#FF00FF]DB_NAME[/color][color=gray]() + [/color][/font][/size][size="2"][font="Courier New"][color=red]']'
[/color][color=blue]WHEN [/color][color=black]1 [/color][color=blue]THEN [/color][color=red]'OBJECT::[' [/color][color=gray]+ [/color][color=black]SCHEMA_NAME[/color][color=gray]([/color][color=black]o.schema_id[/color][color=gray]) + [/color][color=red]'].[' [/color][color=gray]+ [/color][color=black]o.[name] [/color][color=gray]+ [/color][/font][/size][size="2"][font="Courier New"][color=red]']'
[/color][color=blue]WHEN [/color][color=black]3 [/color][color=blue]THEN [/color][color=red]'SCHEMA::[' [/color][color=gray]+ [/color][color=black]SCHEMA_NAME[/color][color=gray]([/color][color=black]dp.major_id[/color][color=gray]) + [/color][/font][/size][color=red][font="Courier New"][size="2"]']'
[/size][/font][/color][size="2"][font="Courier New"][color=blue]END
[/color][color=gray]+ [/color][color=red]' TO [' [/color][color=gray]+ [/color][color=#FF00FF]USER_NAME[/color][color=gray]([/color][color=black]grantee_principal_id[/color][color=gray]) + [/color][color=red]']' [/color][/font][/size][size="2"][font="Courier New"][color=gray]+
[/color][color=#FF00FF]CASE [/color][/font][/size][size="2"][font="Courier New"][color=black]dp.state_desc
[/color][color=blue]WHEN [/color][color=red]'GRANT_WITH_GRANT_OPTION' [/color][color=blue]THEN [/color][/font][/size][size="2"][font="Courier New"][color=red]' WITH GRANT OPTION;'
[/color][color=blue]ELSE [/color][/font][/size][color=red][font="Courier New"][size="2"]';'
[/size][/font][/color][color=blue][font="Courier New"][size="2"]END
[/size][/font][/color][size="2"][font="Courier New"][color=black]COLLATE DATABASE_DEFAULT [/color]
[color=blue]FROM [/color][/font][/size][size="2"][font="Courier New"][color=black]sys.database_permissions dp
[/color][color=#FF00FF]LEFT [/color][color=blue]JOIN [/color][/font][/size][size="2"][font="Courier New"][color=black]sys.all_objects o
[/color][color=blue]ON [/color][color=black]dp.major_id [/color][color=blue]= [/color][color=black]o.[/color][/font][/size][size="2"][font="Courier New"][color=#FF00FF]OBJECT_ID [/color]
[color=blue]WHERE [/color][color=black]dp.class [/color][color=gray]< [/color][/font][/size][size="2"][font="Courier New"][color=black]4
[/color][color=gray]AND [/color][color=black]major_id [/color][color=gray]>= [/color][/font][/size][size="2"][font="Courier New"][color=black]0
[/color][color=gray]AND [/color][color=black]grantee_principal_id [/color][color=gray]<> [/color][color=black]1[/color][color=gray];[/color][/font][/size]
It filters out anything other than database, object, or schema permissions, so if you're looking on exporting permissions on encryption keys, assemblies, etc., you'll need to expand it. It also ensures that permissions against dbo (which you can't do) are filtered out. If you're wondering about the COLLATE DATABASE_DEFAULT, I was having an issue where it was telling me that it couldn't determine the proper collation. That solves that. And I wanted to make sure the WITH GRANT OPTION was covered, so that added a couple of CASE statements.
I haven't tested it against all scenarios, but if you're looking basic permissions, it should grab and script those.

http://www.sqlservercentral.com/blogs/brian_kelley/2009/08/28/quick-2005-2008-script-to-export-permissions/[/size][/font][/color]

[150bryant]

[b] [url="http://www.sqlservercentral.com/blogs/brian_kelley/2009/08/28/quick-2005-2008-script-to-export-permissions/"]Quick 2005/2008 Script to Export Permissions[/url][/b]
[list]
[*]Posted on 28 August 2009
[*][url="http://www.sqlservercentral.com/blogs/brian_kelley/2009/08/28/quick-2005-2008-script-to-export-permissions/#comments"]Comments[/url]
[*][url=""%20style="][img]http://www.sqlservercentral.com/Resources/Images/briefcase.gif[/img] Briefcase[/url]
[*][url="http://www.sqlservercentral.com/blogs/brian_kelley/2009/08/28/quick-2005-2008-script-to-export-permissions/print/"][img]http://www.sqlservercentral.com/Resources/Images/print.gif[/img] Print[/url]
[/list][color=#2A2A2A][font=Arial, Helvetica, sans-serif][size=3]

I had a need to export permissions to be run against the database once an older version was restored (but which didn't have the permissions yet). I'm sure there are 101 versions of the script out there, but here's mine:
[size="2"][font="Courier New"][color=blue]SELECT
[/color][color=#FF00FF]CASE [/color][/font][/size][size="2"][font="Courier New"][color=black]dp.state_desc
[/color][color=blue]WHEN [/color][color=red]'GRANT_WITH_GRANT_OPTION' [/color][color=blue]THEN [/color][/font][/size][size="2"][font="Courier New"][color=red]'GRANT'
[/color][color=blue]ELSE [/color][/font][/size][color=black][font="Courier New"][size="2"]dp.state_desc
[/size][/font][/color][size="2"][font="Courier New"][color=blue]END
[/color][color=gray]+ [/color][color=red]' ' [/color][color=gray]+ [/color][color=black]dp.permission_name [/color][color=gray]+ [/color][color=red]' ON ' [/color][/font][/size][size="2"][font="Courier New"][color=gray]+
[/color][color=#FF00FF]CASE [/color][/font][/size][size="2"][font="Courier New"][color=black]dp.class
[/color][color=blue]WHEN [/color][color=black]0 [/color][color=blue]THEN [/color][color=red]'DATABASE::[' [/color][color=gray]+ [/color][color=#FF00FF]DB_NAME[/color][color=gray]() + [/color][/font][/size][size="2"][font="Courier New"][color=red]']'
[/color][color=blue]WHEN [/color][color=black]1 [/color][color=blue]THEN [/color][color=red]'OBJECT::[' [/color][color=gray]+ [/color][color=black]SCHEMA_NAME[/color][color=gray]([/color][color=black]o.schema_id[/color][color=gray]) + [/color][color=red]'].[' [/color][color=gray]+ [/color][color=black]o.[name] [/color][color=gray]+ [/color][/font][/size][size="2"][font="Courier New"][color=red]']'
[/color][color=blue]WHEN [/color][color=black]3 [/color][color=blue]THEN [/color][color=red]'SCHEMA::[' [/color][color=gray]+ [/color][color=black]SCHEMA_NAME[/color][color=gray]([/color][color=black]dp.major_id[/color][color=gray]) + [/color][/font][/size][color=red][font="Courier New"][size="2"]']'
[/size][/font][/color][size="2"][font="Courier New"][color=blue]END
[/color][color=gray]+ [/color][color=red]' TO [' [/color][color=gray]+ [/color][color=#FF00FF]USER_NAME[/color][color=gray]([/color][color=black]grantee_principal_id[/color][color=gray]) + [/color][color=red]']' [/color][/font][/size][size="2"][font="Courier New"][color=gray]+
[/color][color=#FF00FF]CASE [/color][/font][/size][size="2"][font="Courier New"][color=black]dp.state_desc
[/color][color=blue]WHEN [/color][color=red]'GRANT_WITH_GRANT_OPTION' [/color][color=blue]THEN [/color][/font][/size][size="2"][font="Courier New"][color=red]' WITH GRANT OPTION;'
[/color][color=blue]ELSE [/color][/font][/size][color=red][font="Courier New"][size="2"]';'
[/size][/font][/color][color=blue][font="Courier New"][size="2"]END
[/size][/font][/color][size="2"][font="Courier New"][color=black]COLLATE DATABASE_DEFAULT [/color]
[color=blue]FROM [/color][/font][/size][size="2"][font="Courier New"][color=black]sys.database_permissions dp
[/color][color=#FF00FF]LEFT [/color][color=blue]JOIN [/color][/font][/size][size="2"][font="Courier New"][color=black]sys.all_objects o
[/color][color=blue]ON [/color][color=black]dp.major_id [/color][color=blue]= [/color][color=black]o.[/color][/font][/size][size="2"][font="Courier New"][color=#FF00FF]OBJECT_ID [/color]
[color=blue]WHERE [/color][color=black]dp.class [/color][color=gray]< [/color][/font][/size][size="2"][font="Courier New"][color=black]4
[/color][color=gray]AND [/color][color=black]major_id [/color][color=gray]>= [/color][/font][/size][size="2"][font="Courier New"][color=black]0
[/color][color=gray]AND [/color][color=black]grantee_principal_id [/color][color=gray]<> [/color][color=black]1[/color][color=gray];[/color][/font][/size]
It filters out anything other than database, object, or schema permissions, so if you're looking on exporting permissions on encryption keys, assemblies, etc., you'll need to expand it. It also ensures that permissions against dbo (which you can't do) are filtered out. If you're wondering about the COLLATE DATABASE_DEFAULT, I was having an issue where it was telling me that it couldn't determine the proper collation. That solves that. And I wanted to make sure the WITH GRANT OPTION was covered, so that added a couple of CASE statements.
I haven't tested it against all scenarios, but if you're looking basic permissions, it should grab and script those.

http://www.sqlservercentral.com/blogs/brian_kelley/2009/08/28/quick-2005-2008-script-to-export-permissions/[/size][/font][/color]

[150bryant]

Quick 2005/2008 Script to Export Permissions
Posted on 28 August 2009Comments Briefcase Print
I had a need to export permissions to be run against the database once an older version was restored (but which didn't have the permissions yet). I'm sure there are 101 versions of the script out there, but here's mine:
SELECT
CASE dp.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'
ELSE dp.state_desc
END
+ ' ' + dp.permission_name + ' ON ' +
CASE dp.class
WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'
WHEN 1 THEN 'OBJECT::[' + SCHEMA_NAME(o.schema_id) + '].[' + o.[name] + ']'
WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(dp.major_id) + ']'
END
+ ' TO [' + USER_NAME(grantee_principal_id) + ']' +
CASE dp.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'
ELSE ';'
END
COLLATE DATABASE_DEFAULT
FROM sys.database_permissions dp
LEFT JOIN sys.all_objects o
ON dp.major_id = o.OBJECT_ID
WHERE dp.class < 4
AND major_id >= 0
AND grantee_principal_id <> 1;
It filters out anything other than database, object, or schema permissions, so if you're looking on exporting permissions on encryption keys, assemblies, etc., you'll need to expand it. It also ensures that permissions against dbo (which you can't do) are filtered out. If you're wondering about the COLLATE DATABASE_DEFAULT, I was having an issue where it was telling me that it couldn't determine the proper collation. That solves that. And I wanted to make sure the WITH GRANT OPTION was covered, so that added a couple of CASE statements.
I haven't tested it against all scenarios, but if you're looking basic permissions, it should grab and script those.

http://www.sqlservercentral.com/blogs/brian_kelley/2009/08/28/quick-2005-2008-script-to-export-permissions/

[150bryant]

Quick 2005/2008 Script to Export Permissions
Posted on 28 August 2009Comments Briefcase Print
I had a need to export permissions to be run against the database once an older version was restored (but which didn't have the permissions yet). I'm sure there are 101 versions of the script out there, but here's mine:
SELECT
CASE dp.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'
ELSE dp.state_desc
END
+ ' ' + dp.permission_name + ' ON ' +
CASE dp.class
WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'
WHEN 1 THEN 'OBJECT::[' + SCHEMA_NAME(o.schema_id) + '].[' + o.[name] + ']'
WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(dp.major_id) + ']'
END
+ ' TO [' + USER_NAME(grantee_principal_id) + ']' +
CASE dp.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'
ELSE ';'
END
COLLATE DATABASE_DEFAULT
FROM sys.database_permissions dp
LEFT JOIN sys.all_objects o
ON dp.major_id = o.OBJECT_ID
WHERE dp.class < 4
AND major_id >= 0
AND grantee_principal_id <> 1;
It filters out anything other than database, object, or schema permissions, so if you're looking on exporting permissions on encryption keys, assemblies, etc., you'll need to expand it. It also ensures that permissions against dbo (which you can't do) are filtered out. If you're wondering about the COLLATE DATABASE_DEFAULT, I was having an issue where it was telling me that it couldn't determine the proper collation. That solves that. And I wanted to make sure the WITH GRANT OPTION was covered, so that added a couple of CASE statements.
I haven't tested it against all scenarios, but if you're looking basic permissions, it should grab and script those.

[Ranga_The_Donga]

[quote name='littlemoon' timestamp='1360177927' post='1303236111']
will chk the link thank u ...

kani detach enduku ? prod DB's ivi can't take them offline... n na degara backup files unayi..
asalu ardham kani point naku [b]how to write a query to auto copy the .bak file from 1 server to another[/b].. linked server emaina cheyala?
[/quote]
for that... obviously you need to create job... just use the same script in sql server agent job...

[150bryant]

for mutliple postings.

[Ranga_The_Donga]

[quote name='littlemoon' timestamp='1360179128' post='1303236201']
[size=4]
Thank u both...

kani ekada permissions kuda include ayi ledhu ? isn't it a best practice to load data with permissions ?
[/quote]
already back up chesina DB ni restore chesthundhi.....nenu ichina code lo...

back up chesthun appudu roles anni correct back up chesthe chalu anukunta... wen u restore..same previlages restore avuthayi...

[ChampakDas]

[img]http://www.desigifs.com/sites/default/files/CroppedBrahmishock_zps694518cc.gif?1350460826[/img]

[150bryant]

my office blocks any copyrighted material content and websites
dbing thru phone now

[SonyVaio]

naa databse lo oka table undi, table lo 4 existing columns unnayi ahaa 4 long back create chesina
ipudu same table lo inkoka 4 columns add chedam antay error msg vastundi

[b]"********saving changes is not permitted. the changes you have made require the following tables to be dropped and re-created. you have either made changes to a table that can't be re-created or enabled the option prevent saving changes that require the table to be re-created************".[/b]

ee error msg vastundi, ila enduku vastundi?
new columns add cheyalantay table drop chesi recreate cheyamantundi endi?

ipudu nenu em cheyali?

[Ranga_The_Donga]

[quote name='SonyVaio' timestamp='1360180012' post='1303236271']
naa databse lo oka table undi, table lo 4 existing columns unnayi ahaa 4 long back create chesina
ipudu same table lo inkoka 4 columns add chedam antay error msg vastundi

[b]"********saving changes is not permitted. the changes you have made require the following tables to be dropped and re-created. you have either made changes to a table that can't be re-created or enabled the option prevent saving changes that require the table to be re-created************".[/b]

ee error msg vastundi, ila enduku vastundi?
new columns add cheyalantay table drop chesi recreate cheyamantundi endi?

ipudu nenu em cheyali?
[/quote]
[color=#333333]To change the [/color][b]Prevent saving changes that require the table re-creation[/b][color=#333333] option, follow these steps:[/color][list]
[*]Open SQL Server Management Studio (SSMS).
[*]On the [b]Tools[/b] menu, click [b]Options[/b].
[*]In the navigation pane of the [b]Options[/b] window, click [b]Designers[/b].
[*]Select or clear the [b]Prevent saving changes that require the table re-creation[/b] check box, and then click [b]OK[/b].
[/list]

[karna11]

cheyyochuuu query evening isthaa, job schedule dwara cheyochuu

[deals2buy]

Yes, mdf file ni backup chesi SSIS package dwara migrate cheyyochu...

Also, data migrate cheyyadam veru.... roles manage cheyyadam veru...adi ela chestaro antha idea ledu

I haven't worked on this so couldn't give you any code