SQL SERVER – Refresh Database Using T-SQL

Yesterday I received following questions on blog. Ashish Agarwal asked following question.

Hi Pinal,

Can we refresh a database (like we do by right clicking database node in object explorer and clicking on refresh) thru SQL Query?
If yes, can you please tell me the query?

Thanks,
Ashish Agarwal

Answer to above question is NO. It is not possible to do the same task using SQL Query.

However, if you have changed some SP or any other object and if they are cached in the database, database can be refreshed using DBCC commands.

Read my previous article about SQL SERVER – Clear SQL Server Memory Caches.

Reference: Pinal Dave (http://www.SQLAuthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – 5 Millions Visitors – 2 Anniversary – Authors Note on Economy Slow Down and Job Opportunity – SQL Server
Next Post
SQL SERVER 2008 – Connect Visual Studio 2005 Patch Download

Related Posts

11 Comments. Leave new

  • Hi Pinal,

    I am using SQL SERVER 2005. So I am working on this project, where I have an ITEM_PURCHASE table and an WAREHOUSE table
    I want users to be able to input data into the ITEM_PURCHASE table thru a form and the WAREHOUSE table will automatically populate from certain fields in ITEM_PURCHASE. In this scenario should I use a trigger or stored procedure? Can someone provide the SQL code to get me started? Can someone help me out here, it will be greatly appreciated.

    Thanks,

    Wakil

    Reply
  • Hi.

    I m using SQL Server 2005, 64 bit edition. Our problem is .. We need to restart or Refersh sql services mostly to increase the performence of Serve. My server configration is very good but the performence goes down suddenly. Please help me regarding this issue.

    Reply
  • Dear all

    I am using ms sql 2005 for database.

    i want if i make any changes in database or add any thing.

    it should be refresh automatic i don’t have to refresh my website or software just like cricket’s website.

    if someone is using my website it should be automatic refresh in every 1/2 minutes refresh.

    can someone help me.

    regards all

    firoz khan

    Reply
  • Imran Mohammed
    July 16, 2009 9:06 am

    @Firoz.

    That should be done through front End interface / Web interface.

    ~ IM.

    Reply
  • Hi Wakil

    You can use below code

    begin try

    begin transaction updateITEM_PURCHASE_and_WAREHOUSE

    — tsql here
    insert into ITEM_PURCHASE
    insert into WAREHOUSE

    commit transaction updateITEM_PURCHASE_and_WAREHOUSE

    commit transaction updateITEM_PURCHASE_and_WAREHOUSE
    end try
    begin catch
    IF @@TRANCOUNT > 0
    rollback transaction updateITEM_PURCHASE_and_WAREHOUSE
    insert into errortble (ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,Errorline,ErrorMessage)(SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage)
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;
    end catch

    Regards
    Jayant Das

    Reply
  • ‘Connect to the local, default instance of SQL Server.
    Dim srv As Server
    srv = New Server

    ‘Reference the AdventureWorks database.
    Dim db As Database
    db = srv.Databases(“AdventureWorks”)

    ‘Rename the database
    db.Rename(“AdventureWorks2”)

    ‘Refresh the properties on the database.
    db.Refresh()
    db.Rename(“AdventureWorks”)

    Reply
  • Hi Pinal,

    I have to restore a production database in QA. What are the steps to be followed? Please guide. Need to know how to script the login,users.

    Thanks
    Arun

    Reply
  • HI Arun/Pinal,

    I am in need of DB Refresh process…
    please provide me the steps to be followed for db refresh? Please guide. Need to know how to script the login,users

    Reply
  • Hi,

    I wanna know what will happen to the stored procedures while doing the refreshing process to an instance. Will they also be refreshed? Kindly help me out asap.

    Reply
  • to get the backup the database level permissions please view the below code

    https://gallery.technet.microsoft.com/Extract-Database-dfa53d5a?redir=0

    (not advertising my code but its reall yo long to post it here)

    to refresh a production to uat , please read the below comments properly before you do
    anything

    /*creating a procedure in master database to restore the required database*/
    create procedure dba_restore @backupFile varchar(max),@dbName varchar(256)
    as
    declare @headers table
    (
    LogicalName nvarchar(128)
    ,PhysicalName nvarchar(260)
    ,Type char(1)
    ,FileGroupName nvarchar(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0),
    FileId tinyint,
    CreateLSN numeric(25,0),
    DropLSN numeric(25, 0),
    UniqueID uniqueidentifier,
    ReadOnlyLSN numeric(25,0),
    ReadWriteLSN numeric(25,0),
    BackupSizeInBytes bigint,
    SourceBlockSize int,
    FileGroupId int,
    LogGroupGUID uniqueidentifier,
    DifferentialBaseLSN numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnly bit,
    IsPresent bit,
    TDEThumbprint varbinary(32)
    )
    insert into @headers exec(‘restore filelistonly from disk = ”’+ @backupFile +””);
    declare @h1count int=(select COUNT(* ) from @headers )
    declare @h2count int=(select COUNT(*) from sys.master_files where database_id=DB_ID(@dbName))
    if (@h1count=@h2count)
    begin
    declare @temp table(query varchar (2000));
    insert into @temp
    select ‘restore database ‘+@dbName+’ from disk=”’+@backupFile +”’ with’;
    insert into @temp
    select ‘move ”’+h1.LogicalName+”’ to ”’+h2.physical_name+”’,’ from @headers as h1 inner join
    sys.master_files as h2 on h2.file_id=h1.FileId
    where h2.database_id=DB_ID(@dbName);
    insert into @temp
    select ‘replace,stats=1;’
    declare @restore nvarchar(max)
    set @restore=(SELECT STUFF((SELECT ‘ ‘ + query FROM @temp FOR XML PATH(”)) ,1,1,”) AS Txt)
    exec sp_executesql @restore
    end
    else if (@h2count=0)
    print ‘there is no database called ‘+@dbname+’ in the present server’
    else print ‘un equal number files’
    go

    /*excuting the procedure
    -within the first quotes mention the full backup path and in the second qoutes mention
    -the database to which the backup has to be refreshed
    -if no database is present you will get a error like
    (there is no database called ITSM1 in the present server)
    -if there are unequal number of files you will get a error like
    (unequal number of files)
    -IMPORTANT when you get a error like that please go with the
    manual refresh*/

    master..dba_restore ‘c:\ITSM.dmp’,’ITSM’

    /*executing without the procedure*/

    declare @backupFile varchar(max)=’full dbpath’
    declare @dbName varchar(256)=’dbname’

    declare @headers table
    (
    LogicalName nvarchar(128)
    ,PhysicalName nvarchar(260)
    ,Type char(1)
    ,FileGroupName nvarchar(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0),
    FileId tinyint,
    CreateLSN numeric(25,0),
    DropLSN numeric(25, 0),
    UniqueID uniqueidentifier,
    ReadOnlyLSN numeric(25,0),
    ReadWriteLSN numeric(25,0),
    BackupSizeInBytes bigint,
    SourceBlockSize int,
    FileGroupId int,
    LogGroupGUID uniqueidentifier,
    DifferentialBaseLSN numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnly bit,
    IsPresent bit,
    TDEThumbprint varbinary(32)
    )
    insert into @headers exec(‘restore filelistonly from disk = ”’+ @backupFile +””);
    declare @h1count int=(select COUNT(* ) from @headers )
    declare @h2count int=(select COUNT(*) from sys.master_files where database_id=DB_ID(@dbName))
    if (@h1count=@h2count)
    begin
    declare @temp table(query varchar (2000));
    insert into @temp
    select ‘restore database ‘+@dbName+’ from disk=”’+@backupFile +”’ with’;
    insert into @temp
    select ‘move ”’+h1.LogicalName+”’ to ”’+h2.physical_name+”’,’ from @headers as h1 inner join
    sys.master_files as h2 on h2.file_id=h1.FileId
    where h2.database_id=DB_ID(@dbName);
    insert into @temp
    select ‘replace,stats=1;’
    declare @restore nvarchar(max)
    set @restore=(SELECT STUFF((SELECT ‘ ‘ + query FROM @temp FOR XML PATH(”)) ,1,1,”) AS Txt)
    exec sp_executesql @restore
    end
    else if (@h2count=0)
    print ‘there is no database called ‘+@dbname+’ in the present server’
    else print ‘un equal number files’

    Reply

Leave a Reply

Menu